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

 

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


By:   |   Last Updated: 2016-08-02   |   Comments (2)   |   Related Tips: 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:

Last Updated: 2016-08-02


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

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



    



Wednesday, August 03, 2016 - 4:03:43 PM - Daniel Back To Top

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


Wednesday, August 03, 2016 - 2:19:30 PM - Luan Moreno Back To Top

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 

 


Learn more about SQL Server tools