Microsoft Docs

Add Data Sources to Azure Analysis Services

This document guides you through the process of adding data sources to your Azure Analysis Services (AAS) tabular models. Connecting to various data sources is a fundamental step in building your analytical solutions.

Supported Data Sources

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

• Azure SQL Database
• Azure Synapse Analytics
• SQL Server (on-premises and Azure VM)
• Azure Blob Storage
• Azure Data Lake Storage
• SharePoint Lists
• Excel files
• And many more through custom connectors and Power Query.

Steps to Add Data

1. Using Visual Studio with Analysis Services Projects Extension

This is the recommended approach for building complex models.

1. Open your Analysis Services Tabular Project in Visual Studio.
2. In the Solution Explorer, right-click on the Data Sources folder and select Add Data Source.
3. The Table Import Wizard will appear. Click New Source.
4. Select your desired data source type from the list and click Open.
5. Enter the connection details:
   • Server name: The hostname or IP address of your data source.
   • Database name: The name of the database to connect to.
   • Authentication: Choose the appropriate authentication method (e.g., Windows Authentication, SQL Server Authentication, Azure AD).
6. Click Test Connection to verify the credentials and access.
7. Click OK to establish the connection.
8. Select the tables or views you want to import.
9. Configure any data transformations or relationships as needed.
10. Click Finish to import the data.

2. Using Tabular Editor

Tabular Editor is a powerful open-source tool for working with Analysis Services models.

1. Open your AAS model in Tabular Editor.
2. In the TOM Explorer pane, right-click on Data Sources and select Create New Data Source.
3. Configure the ConnectionString and Provider properties for your data source.
4. You may need to use the Impersonation property for credentials.
5. Save your changes.

3. Using SQL Server Management Studio (SSMS)

For simpler scenarios or quick connections.

1. Connect to your Azure Analysis Services server in SSMS.
2. Right-click on the database and select Create New Model or Create New Table.
3. The Table Import Wizard will guide you through the connection process, similar to the Visual Studio approach.

Connection Strings and Credentials

Connection strings vary depending on the data source. For Azure services, you'll often use Azure Active Directory (Azure AD) authentication. For on-premises data sources, you might use SQL Server Authentication or Windows Authentication.

When setting up data sources, consider using secure methods for storing credentials, such as Azure Key Vault or managed identities, especially in production environments.

Example Connection String (Azure SQL Database)

Server=your_server_name.database.windows.net;Database=your_database_name;User ID=your_user_id;Password=your_password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Troubleshooting Common Issues

• Connectivity Errors: Verify network firewalls, VNet configuration, and NSGs.
• Authentication Errors: Double-check usernames, passwords, and permissions. Ensure the account has read access to the data source.
• Provider Errors: Make sure the necessary data providers are installed on the machine where the model is being processed or deployed.