Problem
Typically in a large scale analytics project involving SQL Server Analysis Services (SSAS), it’s quite common to find projects implementing SSAS in Multi-dimensional mode as well in Tabular mode. In such environments, the need to import portions of data from a large scale SSAS cube into a SSAS Tabular model is quite common. There is more than one way to import data from a cube to a tabular model and in this tip we will look at the most straight-forward and efficient way to import data.
Solution
SQL Server Data Tools (SSDT) provides a table import wizard to import data using a MDX query from SSAS Multidimensional to SSAS Tabular. In this tip we will look at how to import data from a Multidimensional SSAS Cube to a Tabular SSAS data model. You can download these two example databases. I have both SSAS modes and the sample AdventureWorks databases installed on my development machine. Follow the steps below.
1) Open AdventureWorks tabular solution in SSDT. Click on Model > Import From Data Source… menu option as shown below.
2) Clicking on the menu option pops up a wizard. Select the Microsoft Analysis Services option as shown below and click Next.
3) Select the appropriate server, authentication mechanism, SSAS Multi-dimensional database name and click Next. I have installed the AdventureWorks cube ADW database, which is selected as below.
4) Select the appropriate impersonation account that can be used to connect to the SSAS cube to extract the data as shown below.
5) Provide an appropriate friendly name for the query that you will be using to extract data from the cube. This name will be used as the table name once the import completes. Click on the Design button to open the designer and select the data elements required from the cube by dragging and dropping into the query designer window. I selected Internet Sales Measure split by the Product Categories hierarchy. Once you close the query designer, it generates the corresponding MDX query which is shown below. After the query is generated, click Finish.
6) Once the data is imported, the wizard should look like the below image. Click on the Close button when done.
7) Now you should be able to see the imported data in your Tabular data model.
In this way, using a MDX query we can import data from a multi-dimensional cube into tabular SSAS data model.
Next Steps
- Try out a complex MDX query that involves KPIs, Measures, Hierarchies and Attributes to study how the data gets imported from the cube into a tabular data model.
- Check out these other Analysis Services Administration Tips
