By: Daniel Calbimonte | Updated: 2016-08-18 | Comments (1) | Analysis Services Administration
Is it possible to migrate data from Azure SQL to a SQL Server Analysis Services Tabular Model Database?
Yes. In in this tip, we will show how to create an Azure SQL database with the AdventureworksLT data and how to import the information into a SQL Server Analysis Services Tabular Model Database.
- SQL Server 2012 or later. In this tip, we are using SQL Server 2016.
- You have to install a Tabular Instance. For more information about Tabular Installations, refer to this link.
- SSDT should be installed.
- You will also need an Azure SQL Account.
In this demo, we will create an Azure SQL Database with the AdventureworksLT database and then we will import the information into a Tabular Model.
Creating an Azure SQL Database
- In the Azure Portal, go do Databases and press the Add option:
- Create a database named mssqltips. Create a new group. In Select source, select
sample. In Select sample, select AdventureworksLT:
- Create a new server. In this example, the server name is usaserver. Specify
the administrator login and password to connect to the server and press the
- Once created, we need to configure the firewall. Press the all resources
icon, click the server and select Firewall:
- In the Firewall settings, press Add client IP to add the local machine IP address
and then press Save:
- In the Portal, go to databases and click the mssqltips database. Copy the
Importing data to Tabular
Now that we have the Azure SQL Database ready we need to import the data into the Tabular Model.
- Open the SQL Server Data Tools (SSDT):
- Go to File > New Project in the menu and select the Analysis Services
- Connect to your local Tabular Instance. In this example, the instance name
of the Tabular database is TABULAR:
- In the menu, go to Model > Import from Data Source.
- Copy and paste the Server name from Step 5 above from the Azure SQL creation from
the Azure Portal. Specify the login and password specified in Step 3 above and specify the Azure SQL Database name specified
in Step 2 above:
- Specify the Analysis Services credential to connect to Azure:
- Select the option to select from a list of tables:
- Select the tables and views from Azure SQL that you want to import:
- There is a common error that occurs when you try to import. In this example,
all the tables failed to be imported and 3 views were imported successfully.
Click the error message hyperlink:
- The common error is the following: Failed to save modifications to the
server. Error returned: 'The following exception occurred while the managed
IDbCommand interface was being used: Invalid object name 'Product'..'
- To solve this problem, we will connect to the tables using T-SQL queries.
Go to Model > Existing Connections:
- Press Open:
- Select the option to write a query:
- Specify a friendly name. We will import the SalesLT.Address table first.
Also specify the SQL statement to retrieve the columns of the Azure SQL table:
- The table should import successfully:
- Repeat steps 11 to 15 for all the Azure SQL tables.
- Now, you can use your imported Tabular data:
To import data from Azure SQL, we need to use in several cases SQL statements to import the data. The system fails to import a list of tables. To connect to Azure, we need to enable the local IP in the Azure Portal so we can connect. We also need to specify the Azure Server Name, login and password to connect.
Next StepsFor more information, refer to the following links:
- Import Data (SSAS Tabular)
- Data Sources Supported (SSAS Tabular)
- Import from a Multidimensional Data Source (SSAS Tabular)
- Cloud Computing Tips
- SQL Server Analysis Services Resources
Last Updated: 2016-08-18
About the author
View all my tips