Import Data from SQL Multidimensional Database to Tabular Database

By: Daniel Calbimonte | Comments (2) | Related: More > Import and Export

Problem

How do you migrate data from a SQL Server Analysis Services (SSAS) Multidimensional database to a Tabular database. Can you provide a step by step example?

Solution

In in this tip, we will show how to import data into a Tabular database from a SSAS Multidimensional database.

Requirements

SQL Server 2012 or 2014. In this tip, we are using SQL Server 2014.
Download the AdventureWorksDW Database.
You have to install a Tabular Instance. For more information about Tabular Installations, refer to this link
The SSDT for Business Intelligence should be installed.

Introduction

To get data into a Tabular database, you could import the data directly from the SQL Server tables since most Multidimensional databases process the data from a relational database which is usually a SQL Server database. If you want to import the data to a Tabular database, you could do so by just clicking on the import icon and selecting the tables from the data source which is a straightforward process.

However, there are some cases when the only way to import the data is from the Multidimensional database and this tip covers that situation.

Launch SSDT and go to File > New Project.
Go to Analysis Services > Analysis Services Tabular Project.
In the Tabular model designer, specify the Tabular Instance Name.
If you want to rename the project, you can right click the project and rename it.
Press the Import from Data Source icon.
As you can see, there are a number of Data Sources. You can import from SQL Server, SQL Azure, Microsoft Access, etc.
In this example, we will import from a Multidimensional Analysis Services Data Source.
Specify the connection name (it can be any name), the Server name and the database name. In this example, we are using the AdventureWorks Multidimensional Database.
Specify the credentials to connect to Analysis Services. Make sure the account has permissions.
When we import data from a Multidimensional database, we need to specify the MDX statement. We could write the MDX statement manually or design the query. We will design the query by pressing the Design... button.
Drag and drop the Internet Extended Amount from the Internet Sales measures.
Drag and drop the Customer attributes like Country, State Province, City and Postal Code.
The query generated will be similar to the following. The query is showing the Internet Extended Amount by different customer properties like the customer geography and postal code.
By default, it will display an error message. Click on the Error Message to see the details.
The error message says that the provider MSOLAP is not registered. By default, the Multidimensional provider is not displayed.
To solve this problem, you need to download the OLE DB provider for Analysis Services from the feature pack. At this time, the latest feature pack available is the Feature Pack for SQL Server 2016.
Download the SQL_AS_OLEDB.msi file (SQL Analysis Services OLE DB). There are 2 installers: one for x86 machines and one for x64 machines. Make sure to install the correct one on your machine.
Once you have the SQL_AS_OLEDB provider installed, repeat steps 5 thru 13. If everything is OK, you will receive a success message:
We now have the customer information in a Tabular database:
Just to test, add a new column by double clicking in the add column.
We will add a DAX expression. We will calculate the tax of 10% of the Measure Internet Freight.
We will add the precision of 4 decimal places:

Conclusion

As you can see, to import data from a Multidimensional database we can use MDX queries. We need to create queries for each dimension and fact table.

To import the data, it is necessary to install the OLE DB Provider for Analysis Services.

Next Steps

For more information, refer to the following links:

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

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

View all my tips

Import Data from a SQL Server Multidimensional Database to a Tabular Database