In this blog post, we will discuss the process of migrating data from a SQL Server Analysis Services (SSAS) Multidimensional database to a Tabular database. We will provide a step-by-step example to guide you through the process.
Requirements
- SQL Server 2012 or 2014 (we will be using SQL Server 2014 in this example)
- AdventureWorksDW Database (download link provided)
- Tabular Instance (installation instructions provided)
- SQL Server Data Tools (SSDT) for Business Intelligence (should be installed)
Introduction
To import data into a Tabular database, you can usually import the data directly from the SQL Server tables. However, there are cases when the only way to import the data is from a Multidimensional database. This blog post will cover that situation.
Step-by-Step Example
- Launch SQL Server Data Tools (SSDT) and create a new Analysis Services Tabular Project.
- In the Tabular model designer, specify the Tabular Instance Name and optionally rename the project.
- Click on the “Import from Data Source” icon.
- Select the Multidimensional Analysis Services Data Source and specify the connection name, server name, and database name.
- Specify the credentials to connect to Analysis Services.
- Design the MDX query to import the data from the Multidimensional database.
- Drag and drop the desired measures and attributes into the query.
- Download and install the OLE DB Provider for Analysis Services from the feature pack.
- Repeat steps 5 to 8.
- If everything is successful, you will receive a success message.
- You now have the customer information in a Tabular database.
- To test, add a new column and apply a DAX expression.
Conclusion
In conclusion, migrating data from a SQL Server Analysis Services Multidimensional database to a Tabular database can be achieved by using MDX queries and the OLE DB Provider for Analysis Services. By following the step-by-step example provided in this blog post, you can successfully import the data and perform further analysis in the Tabular model.