Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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

SSAS

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.

Impersonation

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.

Import

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


Last Update:


signup button

next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, December 07, 2017 - 10:42:16 AM - Diptesh Back To Top

 Hi Siddharth,   

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

 

 

 


Tuesday, January 03, 2017 - 1:52:35 AM - Drickus Back To Top

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

 


Learn more about SQL Server tools