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
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019