Importing Data into Azure Analysis Services

This document guides you through the process of importing data into your Azure Analysis Services (AAS) tabular models. Azure Analysis Services provides powerful tools for ingesting data from various sources, transforming it, and preparing it for analysis.

Supported Data Sources

Azure Analysis Services supports a wide range of data sources, including:

• Azure SQL Database
• Azure SQL Data Warehouse (now Azure Synapse Analytics)
• SQL Server (on-premises or on Azure VM)
• Azure Blob Storage
• Azure Data Lake Storage
• SharePoint Lists
• OData Feeds
• And many more through Power Query integration.

Importing Data Using Visual Studio

The most common and flexible method for importing data is by using Visual Studio with the SQL Server Analysis Services Projects extension.

Steps:

1. Open your Azure Analysis Services project in Visual Studio. If you don't have a project, create a new one of type "Analysis Services Tabular Project."
2. Add a new Table or View. Right-click on the "Tables" folder in Solution Explorer and select "Add Table."
3. Connect to a Data Source.
• In the "Select Data Source" dialog, choose your desired data source type (e.g., "Azure SQL Database").
• Click "Create Connection."
• Enter the server name, database name, and authentication credentials.
• Click "Test Connection" to verify.
• Click "OK" to proceed.
4. Select Tables or Views. From the "Navigator" window, select the tables or views you want to import.
5. Transform Data (Optional). After selecting your tables, you can click "Transform Data" to open the Power Query Editor. This allows you to clean, shape, and combine your data before loading it into the model.
6. Load Data. Once you have finished transforming your data, click "Close & Apply" in the Power Query Editor or simply click "OK" in the Navigator if no transformations are needed. The data will be loaded into your tabular model.

Importing Data Using Data Factory

For automated and scheduled data imports, Azure Data Factory (ADF) is a powerful ETL/ELT service. You can use ADF to orchestrate data movement from various sources into Azure Analysis Services.

Key ADF Concepts:

• Linked Services: Define connection strings to your data sources and your Azure Analysis Services instance.
• Datasets: Represent the data structures within the linked services.
• Pipelines: Orchestrate activities, including data copying and Analysis Services processing.
• Copy Activity: Used to move data from source to destination.
• Custom Activity: Can be used to trigger Analysis Services commands (like processing) via the TOM API or PowerShell.

Using Tabular Object Model (TOM) for Programmatic Imports

For advanced scenarios and automation, you can use the Tabular Object Model (TOM) library to programmatically connect to your Analysis Services server and import data.

Example (C#):

1using Microsoft.AnalysisServices.Tabular;
2using System;
3
4public class ImportData
5{
6    public static void ImportFromSQL(string serverName, string databaseName, string connectionString)
7    {
8        string connection = $"DataSource=asazure://{serverName}/{databaseName};" + connectionString;
9        Server server = new Server();
10        server.Connect(connection);
11
12        Database db = server.Databases[databaseName];
13        Table salesTable = new Table(db, "Sales");
14
15        DataSourceView dsv = new DataSourceView(db, "SalesDataSourceView", "SELECT * FROM dbo.SalesData");
16        salesTable.DataSource = dsv;
17        salesTable.Source = new ColumnBinding[] { new ColumnBinding { SourceColumn = "OrderID", TargetColumn = "OrderID" } };
18
19        db.Tables.Add(salesTable);
20        db.Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull);
21
22        Console.WriteLine("Data imported successfully!");
23    }
24}
                

Data Refresh

After importing, your data will be a snapshot. To keep your model up-to-date, you'll need to configure data refreshes. This can be done through:

• Azure Portal: Schedule manual or recurring refreshes for your datasets.
• Azure Data Factory: Orchestrate refresh tasks as part of your pipelines.
• TOM/AMO: Programmatically trigger refreshes.

Next Steps

Once your data is imported, you can proceed to model it by creating relationships, defining calculations, and structuring your model for optimal performance and user experience. Refer to the Modeling Data documentation for more information.