Import data from Azure SQL to Tabular Databases

By:   |   Comments (1)   |   Related: > 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 to this 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:

    Microsoft Azure add SQL Databases


  2. Create a database named mssqltips. Create a new group. In Select source, select sample. In Select sample, select AdventureworksLT:

    Configure a sample database in SQL Azure

  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:

    Specify the user name and password for the SQL Azure instance

  4. Once created, we need to configure the firewall. Press the all resources icon, click the server and select Firewall:

    Configure the firewall in Microsoft Azure

  5. In the Firewall settings, press Add client IP to add the local machine IP address and then press Save:

    Add the client IP address in Microsoft Azure

  6. In the Portal, go to databases and click the mssqltips database. Copy the server name:

    Copy the server name for the Microsoft Azure instance

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):

    Open SQL Server Data Tools to begin the data import

  2. Go to File > New Project in the menu and select the Analysis Services Tabular Project:

    Start a new SQL Server Analysis Services Tabular Project

  3. Connect to your local Tabular Instance. In this example, the instance name of the Tabular database is TABULAR:

    In the Tabular Model Designer specify the server workspace server and compatibility level

  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:

    Table Import Wizard to specifiy the friendly name and credentials for the Azure instance

  6. Specify the Analysis Services credential to connect to Azure:

    Table Import Wizard credentials

  7. Select the option to select from a list of tables:

    Table Import Wizard interface to Choose How to Import the Data

  8. Select the tables and views from Azure SQL that you want to import:

    Table Import Wizard to Select the Tables and Views

  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:

    Table Import Wizard Errors When Importing Data

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

    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:

    Select an existing connection to execute T-SQL code to migrate the data

  12. Press Open:

    Select an Existing Connection to the SQL Azure database

  13. Select the option to write a query:

    Table Import Wizard for Choose How to Import the Data by Writing 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:

    Specify a SQL Query with a Friendly Query Name

  15. The table should import successfully:

    Data Import Success

  16. Repeat steps 11 to 15 for all the Azure SQL tables.
  17. Now, you can use your imported Tabular data:

    Validate the data is successfully loaded

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:


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


Comments For This Article




Friday, March 15, 2019 - 10:25:37 PM - Sireesha Back To Top (79313)

What does the Administrator domain account need access to? I don't think we can have Windows account access to Azure SQL Database.














get free sql tips
agree to terms