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

By:   |   Updated: 2016-08-02   |   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

  1. SQL Server 2012 or 2014. In this tip, we are using SQL Server 2014.
  2. Download the AdventureWorksDW Database.
  3. You have to install a Tabular Instance. For more information about Tabular Installations, refer to this link
  4. 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.

  1. Launch SSDT and go to File > New Project.
  2. Go to Analysis Services > Analysis Services Tabular Project.

    New SQL Server Analysis Services Tabular project

  3. In the Tabular model designer, specify the Tabular Instance Name.

    Specify the tabular model designer

  4. If you want to rename the project, you can right click the project and rename it.

    Rename the Tabular project

  5. Press the Import from Data Source icon.

    Import from Data Source

  6. As you can see, there are a number of Data Sources. You can import from SQL Server, SQL Azure, Microsoft Access, etc.

    Table import Wizard

  7. In this example, we will import from a Multidimensional Analysis Services Data Source.

    Multidimensional Sources

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

    Analysis Services Connection Information

  9. Specify the credentials to connect to Analysis Services. Make sure the account has permissions.

    Tabular connection credentials

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

    MDX Query

  11. Drag and drop the Internet Extended Amount from the Internet Sales measures.

    Tabular measures

  12. Drag and drop the Customer attributes like Country, State Province, City and Postal Code.

    Tabular dimension attributes

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

    MDX query created

  14. By default, it will display an error message. Click on the Error Message to see the details.

    Error message

  15. The error message says that the provider MSOLAP is not registered. By default, the Multidimensional provider is not displayed.

    Error message details

  16. 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.
  17. 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.
  18. Once you have the SQL_AS_OLEDB provider installed, repeat steps 5 thru 13. If everything is OK, you will receive a success message:

    Success message

  19. We now have the customer information in a Tabular database:

    Tabular database created

  20. Just to test, add a new column by double clicking in the add column.

    Adding a new column

  21. We will add a DAX expression. We will calculate the tax of 10% of the Measure Internet Freight.

    DAX Expression

  22. We will add the precision of 4 decimal places:

    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:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte 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


Article Last Updated: 2016-08-02

Comments For This Article




Wednesday, August 3, 2016 - 4:03:43 PM - Daniel Back To Top (43046)

Thanks. Yes I saw the comment in some forums that it is not possible to migrate.


Wednesday, August 3, 2016 - 2:19:30 PM - Luan Moreno Back To Top (43044)

Daniel, really good article, most of people don't know how to transfer from one model to another one, actually this is the first article that explain in details in how to make it, congratulations for it 

 















get free sql tips
agree to terms