In the next step of the wizard, specify MDX query to retrieve desired dataset. 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. 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 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. To choose SSAS Tabular as a data source, click the From Database button, and From Analysis Services or PowerPivot option. 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. 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). In this article, the custom SSAS Tabular model database will be used (“AW Tabular”) as an external data source.īeing a link between the SQL Server and Excel, Power Pivot is the Excel analytical feature which relies is built on SQL Server Analysis Services.
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).
0 kommentar(er)
