How to import data from SSAS Multi-dimensional to SSAS Tabular

By:   |   Comments (2)   |   Related: > Analysis Services Administration


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.


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 from here.  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.

Import from data source

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.

ADW Cube

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.

MDX Query

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.

Imported Data

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, December 7, 2017 - 10:42:16 AM - Diptesh Back To Top (73733)

 Hi Siddharth,   

                   Is it possible to Import Data from different Tabular Model and Merge in to Single Tabular Model Solutions.




Tuesday, January 3, 2017 - 1:52:35 AM - Drickus Back To Top (45087)

Good Day Siddharth

Thanks or the tip article.

I have a question around the design and architecture around such a solution. 
I recectly took over a PowerPivot POC project and had convert it to SSAS Tabular and productionize the solution.
Some of the datasources were MDX queries linking to a cube just like to article explains.

My concern around this is Olap cubes already aggegrate the data. The the MDX flattens the data out again into the PowerPivot\Tabular environment.
The Vertipaq engine with DAX  then creates your aggregates again. Is this architecturally a best practise approach?
A solution running in a fully productionised environment has to be easily scalable and maintainable and with the extra Olap layer this make it a bit more cumbursome?

Wouldn't it be better to get the data directly from the source databases into SSAS Tabular. I can understand if you are doing a quick solution as a POC but not a long term solution running in production ?

Thanks, Drickus


get free sql tips
agree to terms