Import data from Azure SQL to Tabular Databases

Datetime:2016-08-23 04:05:13          Topic: SQL           Share

By:Daniel Calbimonte |  |   Related Tips: > Analysis Services Administration

Problem

Is it possible to migrate data from Azure SQL to a SQL Server Analysis Services Tabular Model Database?

Solution

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.

Requirements

  1. SQL Server 2012 or later. In this tip, we are using SQL Server 2016.
  2. You have to install a Tabular Instance. For more information about Tabular Installations, refer tothis link.
  3. SSDT should be installed.
  4. You will also need an Azure SQL Account.

Introduction

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

  1. In the Azure Portal, go do Databases and press the Add option:
  2. Create a database named mssqltips. Create a new group. In Select source , select sample. In Select sample , select AdventureworksLT:
  3. 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 create button:
  4. Once created, we need to configure the firewall. Press the all resources icon, click the server and select Firewall:
  5. In the Firewall settings, press Add client IP to add the local machine IP address and then press Save:
  6. In the Portal, go to databases and click the mssqltips database. Copy the server name:

Importing data to Tabular

Now that we have the Azure SQL Database ready we need to import the data into the Tabular Model.

  1. Open the SQL Server Data Tools (SSDT):
  2. Go to File > New Project in the menu and select the Analysis Services Tabular Project:
  3. Connect to your local Tabular Instance. In this example, the instance name of the Tabular database is TABULAR:
  4. In the menu, go to Model > Import from Data Source.
  5. 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:
  6. Specify the Analysis Services credential to connect to Azure:
  7. Select the option to select from a list of tables:
  8. Select the tables and views from Azure SQL that you want to import:
  9. 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:
  10. 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'..'
  11. To solve this problem, we will connect to the tables using T-SQL queries. Go to Model > Existing Connections:
  12. Press Open:
  13. Select the option to write a query:
  14. 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:
  15. The table should import successfully:
  16. Repeat steps 11 to 15 for all the Azure SQL tables.
  17. Now, you can use your imported Tabular data:

Conclusion

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 Steps

For more information, refer to the following links:

Last Update:

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




About List