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

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.

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.

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
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

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

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.