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.įurthermore, with these similarities, SSAS query results can be easily replicated and used in Excel.Ĭreate an empty Excel workbook, and select Add to Data Model feature in Power Pivot tab, like in picture below:Īfter 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). Press OK button, and the Power Pivot window will appear:Īdding data source from SSAS Tabular to Power Pivot data model It is not necessary which range of cells will be selected, because this step creates a blank 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:Įnter the proper name of the SSAS instance and pick the desired model database from the drop-down menu. 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: Press Test Connection button to ensure validity of the connection to the chosen instance. 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).įollowing dialog displays the structure of the actual cube and query dock (with an appearance similar to SQL Server Management Studio MDX query dock):Įxecute 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.Īfter clicking Finish button, the process of importing data will start.