MSBI (SSIS/SSRS/SSAS) Online Training

Monday, January 18, 2016

MSBI Interview Questions and Answers

Hi Aspirants,
Here are the MSBI interview Questions and Answers..!

·         How to log SSIS Executions?
·         What is FASMI ?
·         What is processing?
·         What are dashboards?
·         What is ROLAP?
·         What is a Universe?
·         What is HOLAP?
·         What is a Master package?
·         What is the control flow?
·         What is a workflow in SSIS ?
·         What are the tools in MS BI?
·         What is a partition?
·         What is OLAP?
·         What is Business Intelligence?
·         What is DOLAP?
·         What is a data flow?
·         What is a RAGGED hierarchy?
·         What is MOLAP?


Power BI - really Powerful !

Hi BI experts,
Today we are previewing new features for Power BI, our self-service business intelligence solution designed for everyone. Power BI reduces the barriers to deploying a business intelligence environment to share and collaborate on data and analytics from anywhere.
We are introducing a number of new Power BI features available for preview including dashboards, new visualizations, support for popular software-as-a-service applications, a native iPad app and live “hybrid” connectivity to on-premise SQL Server Analysis Services tabular models.
These preview features are available for customers with a United States address. We’ll incrementally add new country support as we extend the preview globally in the coming months. Existing customers will find a preview option in their current Power BI sites. For those not currently using Power BI, you can sign up for a Power BI preview which includes the new features here.

Power BI dashboards
Users can create personalized dashboards to monitor their most important data. A dashboard combines on-premises and cloud-born data in a single pane of glass, providing a consolidated view across the organization regardless of where the data lives. 
Users can easily explore all their data using intuitive, natural language capabilities and receive answers in the form of charts and graphs. They can also explore data through detailed reports that target specific aspects of their business. Visuals from these reports can also be pinned to their dashboards for continuous monitoring. As part of this experience new visualizations have been added including combo charts, filled maps, gauges, tree maps, and funnel charts.  
Out-of-the-box connectors for popular SaaS applications
Power BI now provides “out of the box” connectivity to a number of popular SaaS applications. In addition to the existing seamless connection with Microsoft Dynamics CRM Online, with today’s release customers can also connect to their data in Salesforce, Zendesk, Marketo, SendGrid, and GitHub with many more to come in the months ahead. With an existing subscription to one of these services, customers can login from Power BI. In addition to establishing a data connection, Power BI provides pre-built dashboards and reports for each of these applications.  

Excel BI and the Power BI Designer
Excel 2013, which comes standalone and with Office 365 ProPlus, is Microsoft’s premier business analyst tool – it includes rich business intelligence features (Power Query, Power Pivot, Power View, Power Map) fully integrated with the powerful ad hoc analysis capabilities and familiar features of Excel – like Pivot Tables and Excel Charting. With Excel 2013, analysts can publish Excel Workbooks to Power BI, and share data, analysis and reports with Power BI users.
For Power BI customers who don’t have Excel 2013, the new Power BI Designer provides a solution expressly designed for Power BI report creation. It can be used to import and model data, then author and publish Power BI reports to the Power BI service. 

Connect live to on-premises Analysis Services models
With the new Power BI connector for SQL Server Analysis Services, customers can realize the benefits of a cloud-based BI solution without having to move their data to the cloud. Customers can now create a secure connection to an “on-premises” SQL Server Analysis Services server from Power BI in the cloud. When users view and explore dashboards and reports, Power BI will query the on-premise model using the user’s credentials.
With this hybrid solution, organizations can continue to retain, manage and secure their data on-premises, while securely enabling users to benefit from that data via Power BI.

Stay connected from any device
We are working on a wave of native mobile apps for Power BI. These apps will allow users to access their Power BI dashboards and reports through immersive mobile experiences for iPad, iPhone, and Windows tablets. The first of these - the iPad app is available today and can be downloaded from the Apple App Store, with other platforms following in the coming months. All Power BI mobile apps enable users to share insights and collaborate with colleagues so that they can take immediate action, from anywhere, anytime.


New features of SQL Server and BI

We are introducing a number of new Power BI features available for preview including dashboards, new visualizations, support for popular software-as-a-service applications, a native iPad app and live “hybrid” connectivity to on-premise SQL Server Analysis Services tabular models.
These preview features are available for customers with a United States address. We’ll incrementally add new country support as we extend the preview globally in the coming months. Existing customers will find a preview option in their current Power BI sites

Feature number 1 (Revolution):- Column store indexes
Feature number 2 (Evolution):- Sequence objects
Feature number 3 (Revolution):- Pagination
Feature number 4 (Revolution):- Contained database
Feature number 5 (Evolution):- Error handling
Which are the next 5 features?


Monday, January 12, 2015

Top Ten: New Features in SQL Server 2014

Hi All,
I am listing SQL Server  2014 top 10 new features here.

Microsoft introduced some significant enhancements inSQL Server 2014—especially with In-Memory OLTP. However, as you might expect after such a short release cycle, not every subsystem has been updated; there are no major changes to SQL Server Integration Services (SSIS), SQL Server Replication Services, or SQL Server Reporting Services (SSRS). Nonetheless, there are plenty of significant enhancements. Here are 10 new features in SQL Server 2014.

1. In-Memory OLTP Engine

SQL Server 2014 enables memory optimization of selected tables and stored procedures. The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control  mechanism to eliminate locking delays. Microsoft states that customers can expect performance to be up to 20 times better than with SQL Server 2012 when using this new feature. For more information, check out “Rev Up Application Performance with the In-Memory OLTP Engine.”

2. AlwaysOn Enhancements

Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now also available for read workloads, even when the primary replica is unavailable. In addition, SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you create asynchronous secondary replicas in Windows Azure.

3. Buffer Pool Extension

SQL Server 2014 provides a new solid state disk (SSD) integration capability that lets you use SSDs to expand the SQL Server 2014 Buffer Pool as nonvolatile RAM (NvRAM). With the new Buffer Pool Extensions feature, you can use SSD drives to expand the buffer pool in systems that have maxed out their memory. Buffer Pool Extensions can provide performance gains for read-heavy OLTP workloads.

4. Updateable Columnstore Indexes

When Microsoft introduced the columnstore index in SQL Server 2012, it provided improved performance for data warehousing queries. For some queries, the columnstore indexes provided a tenfold performance improvement. However, to utilize the columnstore index, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction with the new updateable Columnstore Index. The SQL Server 2014 Columnstore Index must use all the columns in the table and can’t be combined with other indexes.

5. Storage I/O control

The Resource Governor lets you limit the amount of CPU and memory that a given workload can consume. SQL Server 2014 extends the reach of the Resource Governor to manage storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool.

6. Power View for Multidimensional Models

Power View used to be limited to tabular data. However, with SQL Server 2014, Power View can now be used with multidimensional models (OLAP cubes) and can create a variety of data visualizations including tables, matrices, bubble charts, and geographical maps. Power View multidimensional models also support queries using Data Analysis Expressions (DAX).

7. Power BI for Office 365 Integration

Power BI for Office 365 is a cloud-based business intelligence (BI) solution that provides data navigation and visualization capabilities. Power BI for Office 365 includes Power Query (formerly code-named Data Explorer), Power Map (formerly code-named GeoFlow), Power Pivot, and Power View. You can learn more about Power BI atMicrosoft’s Power BI for Office 365 site.

8. SQL Server Data Tools for Business Intelligence

The new SQL Server Data Tools for BI (SSDT-BI) is used to create SQL Server Analysis Services (SSAS) models, SSRS reports, and SSIS packages. The new SSDT-BI supports SSAS and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL Server 2014. In the pre-release version of SQL Server 2014, SQL Server Setup doesn’t install SSDT-BI. Instead, you must download SSDT-BI separately from the Microsoft Download Center.

9. Backup Encryption

One welcome addition to SQL Server 2014 is the ability to encrypt database backups for at-rest data protection. SQL Server 2014 supports several encryption algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES. You must use a certificate or an asymmetric key to perform encryption for SQL Server 2014 backups.

10. SQL Server Managed Backup to Windows Azure

SQL Server 2014’s native backup supports Windows Azure integration. Although I’m not entirely convinced that I would want to depend on an Internet connection to restore my backups, on-premises SQL Server 2014 and Windows Azure virtual machine (VM) instances support backing up to Windows Azure storage. The Windows Azure backup integration is also fully built into SQL Server Management Studio (SSMS).

Microsoft released CU5 for SQL Server 2014

Hi, This is my first post in 2015 and this update contains hotfixes for issues that were fixed after the release of SQL Server 2014. This build number of this cumulative update package is 12.0.2456.0.
List of fixes related to SQL Server Analysis Services / PowerPivot 2014:
SQL Bug Number
KB Article Number
CU537206642981424FIX: Exception when you select more than one hierarchy from parent/child dimension in SSAS 2012 or in SSAS 2014
CU532606062992069FIX: XMLA and Windows PowerShell return different code for the same SSAS 2012 or SSAS 2014 processing
CU537206352998870FIX: "The record ID is incorrect" error when you use DAX to execute query in a dimension in SSAS 2012 or SSAS 2014
CU534285683016518FIX: "You do not have permission." error when you run a report that connects to a .BISM file in PowerPivot gallery
CU429060842963137FIX: Incorrect result is returned when you query multiple dimension members in MDX and DAX in SSAS 2012 or SSAS 2014
CU429061012965922FIX: Issue when you configure scheduled data refresh for the PowerPivot workbook in SharePoint 2013
CU429060912975385FIX: User obtains incorrect data when two users in the same security role run a client report in SSAS 2012 or SSAS 2014
CU432605882975743FIX: Incorrect result and exception occur when you use MDX query to do session cube grouping in SSAS
CU429061512976172FIX: An access violation occurs when you perform Process Data or Process Full on a partition in a table in SSAS
CU429061492976861FIX: The time is longer and longer to perform process full on an SSAS 2012 or SSAS 2014 tabular model database
CU429061152976953FIX: The DAX query with filter returns incorrect result after you run a DAX query without any filters in SSAS 2012 or in SSAS 2014
CU432605782989512FIX: MDX query returns no result when the cache is not cleared after you run a previous query in OLAP or tabular model
CU432606032989590FIX: Except function does not return the calculated member that is created at the ALL member level in SSAS
CU432606182993483FIX: SSAS processing fails when you perform a Process Full operation on many partitions in a transaction
CU429765642999308Add detailed information to the "Call to Excel Services returned an error" error message in SQL Server 2014 PowerPivot
CU325783022961559FIX: Incorrect folder when deploying SQL Server 2012 or SQL Server 2014 PowerPivot solutions by using SharePoint 2013 Central Admin
CU325783042963372FIX: Sub-total of calculated measure is incorrect (non-filtered) when you execute MDX query in SSAS 2012 or SSAS 2014
CU325156532975434FIX: Error when you complete an SSAS 2014 image after specifying a non-default root directory
CU328951692989715FIX: Dimension attribute is lost in Excel PivotTable Field List when you set AttributeHierarchyDisplayFolder to "" in SSDT-BI
CU225252682932559FIX: Totals are wrong after you filter on a pivot table item and remove the filter in SSAS 2012 or SSAS 2014
CU225252702954480FIX: SSAS instance crashes when you perform writeback action by using the role with cell security defined
CU225252902961254FIX: The execution of an MDX query in SSAS 2012 or SSAS 2014 is much slower than it is in SSAS 2008 R2
CU225252992962650FIX: Access violation occurs when running a Process Recalc on a tabular table in SSAS 2012 or SSAS 2014
CU225935752967900FIX: Data models do not load after you run a PowerPivot scheduled data refresh job when you work with SQL Server 2014
CU222628402968974FIX: You cannot create a power view report against a UDM cube in SSAS 2014
CU222628772974373FIX: Poor performance when you use DAX functions on a String or Boolean column in SSAS 2014
CU223613162962599Can now disable checking whether dimension and partition data originate from the same data source when you run a ROLAP query in SQL Server 2014
CU120340932860446FIX: Unexpected error in "XMCompressionAnalyzer::SelectBitPackCompression" function when you perform a Process command against a partitioned table in SSAS 2012 or SSAS 2014
CU120340992864111FIX: It takes longer than expected to cancel an MDX query that contains many GROUP BY clauses in SSAS 2012 or SSAS 2014
CU119933172864930FIX: You cannot write text that has more than 32,767 characters in Report Query Designer in SQL Server
CU120341152872946FIX: CPU spinning occurs when you use Power View to run a DAX query against a Multidimensional model in SSAS 2012 or SSAS 2014
CU120341282876882FIX: "Internal error: An unexpected error occurred" when you run a "Process" command against the TFS SSAS cube
CU120661692880071FIX: Error messages when you perform a ProcessRecalc operation after a ProcessData operation on an SSAS 2012 or SSAS 2014 tabular model
CU120340812880094FIX: Grand total or subtotal rows display incorrect results in a Power View report that runs against a multidimensional cube in SSAS 2012 or SSAS 2014
CU120341092884124FIX: "Type mismatch" error when you run an MDX query that uses the Member.Name property against an SSAS 2012 or SSAS 2014 database
CU120334062890415Notification messages of data update failure are not sent when you use the PowerPivot for SharePoint 2013 add-in for SQL Server 2012 SP1 or SQL Server 2014
CU119933182890818FIX: Incorrect value is returned when you run an UPDATE CUBE statement that uses the USE_WEIGHTED_INCREMENT allocation method in SSAS
CU118521052895694FIX: Query scope assignment works incorrectly after you run a ClearCache command in SSAS
CU119933162896903FIX: You cannot perform any administrator actions on databases when a data model corruption occurs in SSAS 2012 or SSAS 2014
CU120332732897263"An error occurred when loading VertiPaq data" message after you load a database in SSAS 2012 or SSAS 2014
CU120336672897265FIX: "Invalid column name" error when you run some MDX queries against ROLAP partitions in SSAS 2012 or SSAS 2014
CU120337212897282FIX: Wrong date format will be returned when you run DateAdd or Day function in SSAS 2012 or SSAS 2014 on a non-US locale computer
CU120336172905275FIX: Error messages when you run some MDX or DAX queries in SSAS 2012 or SSAS 2014
CU120333002905298FIX: It takes much longer for a user without administrator permissions to run an MDX query than an administrator user in SSAS 2012 or SSAS 2014
CU120349112912928FIX: SQL Server 2012 or SQL Server 2014 PowerPivot for SharePoint 2010 cannot create databases when the SharePoint server name is FQDN
CU118521132912949FIX: SSAS crashes when more than one thread tries to access an SSAS cube at the same time
CU119933152921630FIX: Incorrect result is returned when you run an MDX query that contains a DRILLTHROUGH statement in SSAS
CU122053552927524FIX: MDX query does not reuse cache after clear cache command on different Measure Group with Non-Admin login in SSAS 2012 or SSAS 2014
CU121013502927748FIX: PowerPivot workbook cannot do data refresh in SharePoint 2013 after you apply SQL Server 2012 CU7 or in SQL Server 2014
CU122053572927844FIX: Large DistinctCount become slower in tabular mode in SQL Server 2012 or SQL Server 2014
CU120768652922799FIX: Unexpected exception is thrown when you convert writeback partition in SSAS