Prerequisites for using query results from any Analysis Services model database in Excel through Power Pivot add-in are SQL Server 2014 or higher with installed Analysis Services Tabular instance (Multidimensional is installed by default), deployed Multidimensional or Tabular model database (for data source) and Power Pivot add-in for Excel 2010 or higher (Power Pivot is native add-in in Excel 2016).
In this article, the custom SSAS Tabular model database will be used (“AW Tabular”) as an external data source.
Power Pivot overview
Being a link between the SQL Server and Excel, Power Pivot is the Excel analytical feature which relies is built on SQL Server Analysis Services. Even though it is still an Excel add-in, and uses pivot tables, as a main form, Power Pivot is acting like a localized instance of SSAS, incorporating ROLAP (Relational OLAP) storage mode, which includes in-memory data processing and using DAX as the query language, similar to the SSAS Tabular instance characteristics.
Furthermore, with these similarities, SSAS query results can be easily replicated and used in Excel.
Preparing Power Pivot workbook
Create an empty Excel workbook, and select Add to Data Model feature in Power Pivot tab, like in picture below:
After clicking the button, check the option My table has headers in Create Table dialog, to preserve headers of the future data model (which will be as the same as names of the objects in Tabular model database). It is not necessary which range of cells will be selected, because this step creates a blank Power Pivot data model.
Press OK button, and the Power Pivot window will appear:
Adding data source from SSAS Tabular to Power Pivot data model
To choose SSAS Tabular as a data source, click the From Database button, and From Analysis Services or PowerPivot option.
The Table Import Wizard dialog will appear:
Enter the proper name of the SSAS instance and pick the desired model database from the drop-down menu. Press Test Connection button to ensure validity of the connection to the chosen instance. The confirmation should appear:
If the name of the SSAS instance is not properly input, it will not be possible to choose a corresponding model database from the drop-down menu, and the error message will appear:
The following step is crucial in order to retrieve data from SSAS Tabular model which will be used for further analysis and visualization in Excel.
In the next step of the wizard, specify MDX query to retrieve desired dataset.
Just to be mentioned, SSAS Tabular cube data can be queried with basic MDX statements, because in this case, there is no possibility to use DAX, the native query language of Tabular models (and Power Pivot model, as well).
If a query is already prepared, just input that query in the related field in a dialog. Press Validate button to ensure that the query is properly written. If the query has some syntax errors, the warning message will appear and provide details on the error itself.
However, it is possible to preview the result set and then use that query input later in the MDX Statement field, by clicking Design button (marked on the picture above).
Following dialog displays the structure of the actual cube and query dock (with an appearance similar to SQL Server Management Studio MDX query dock):
Execute the query with the button (marked above in the picture), and review the results. The actual result set will display in the same form later in the Power Pivot data model and Excel workbook, as well.
The result set displayed here is entirely for illustrative purpose, and there is no technical limitation on how much data will be retrieved through a query – it depends on needs and purpose of data retrieval.
When the desired result set is acquired, press OK, and the query will appear automatically in the statement field. Validate the query again.
After clicking Finish button, the process of importing data will start. If importing process succeeds, and retrieved data (rows and columns) transfer properly, the following message will appear:
Otherwise, when the importing process fails, the whole procedure must be repeated all over again. If this happens, make sure that every query input in the final step is validated.
Copying processed cube dataset from model into ordinary an Excel workbook
The result set from the query will appear in the Power Pivot window:
At this moment, just select these two columns and paste it in ordinary Excel workbook.
After pasting, they will look like this:
At this point, there are numerous possibilities to customize these query results, to incorporate and use them fully in an Excel workbook, perform additional calculations, add visual elements like charts, add more result sets and placing them in other sheets, and much more.
Enjoy using SSAS query results in Excel, and building your documentation on-the-go!
Useful links:
- Prepare Data for Analysis in PowerPivot
- Get data from Analysis Services
- Learn About PowerPivot Capabilities
Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment.
- How to monitor the SQL Server tempdb database - July 6, 2017
- How to monitor total SQL Server indexes size - June 12, 2017
- How to set and use encrypted SQL Server connections - May 8, 2017