In This Article

Introduction

Azure Analysis Services (AAS) provides a powerful platform for building enterprise-grade semantic models. To leverage this, you need to connect your AAS instance to various data sources. This document guides you through the supported data sources and the methods for establishing connections.

Supported Data Sources

Azure Analysis Services can connect to a wide range of data sources, including:

  • Azure SQL Database
  • Azure Synapse Analytics (formerly SQL DW)
  • SQL Server (on-premises and Azure VM)
  • Azure Blob Storage
  • Azure Data Lake Storage Gen1 & Gen2
  • On-premises flat files (CSV, TXT) (requires an On-premises data gateway)
  • Oracle
  • Teradata
  • And many more...

For a comprehensive and up-to-date list, refer to the official Azure Analysis Services data source documentation.

Connection Methods

You can connect to your Azure Analysis Services model using several tools, primarily:

  • SQL Server Management Studio (SSMS): A robust tool for managing and querying Analysis Services.
  • Visual Studio with Analysis Services Projects extension: For developing and deploying tabular models.

Connecting using SQL Server Management Studio (SSMS)

Connecting to Azure Analysis Services using SSMS is straightforward. Ensure you have the latest version of SSMS installed.

  1. Launch SQL Server Management Studio.
  2. In the 'Connect to Server' dialog, for 'Server type', select Analysis Services.
  3. In the 'Server name' field, enter your Azure Analysis Services server name. You can find this in the Azure portal for your AAS resource.
  4. Choose your authentication method:

Azure AD Authentication

This is the recommended and most secure method for connecting to Azure Analysis Services.

  1. Select Azure Active Directory - Universal with MFA or Azure Active Directory - Password depending on your organization's configuration.
  2. Enter your Azure AD username (typically your email address).
  3. If prompted, complete the Multi-Factor Authentication (MFA) challenge.
  4. Click 'Connect'.

You need to have the appropriate Azure AD roles assigned (e.g., Administrator, Data Reader) on your Analysis Services server.

SQL Authentication

While possible, SQL Authentication is generally less secure and less recommended for Azure Analysis Services compared to Azure AD authentication.

  1. Select Windows Authentication if your AAS server is configured for Integrated Security with Azure AD, or if you are connecting from a domain-joined machine.
  2. If you have specific SQL login credentials configured (less common for AAS), you might use those.

Note: For Azure Analysis Services, Azure AD authentication is the primary and preferred method.

Connecting using Visual Studio

Visual Studio, along with the SQL Server Analysis Services Projects extension, is essential for building and deploying tabular models. After installing the extension:

  1. Create a new 'Tabular project' in Visual Studio.
  2. In the 'Tabular Model Designer', you'll be prompted to specify the Data Source.
  3. When configuring your data source within the project, you will be asked to provide the server name (your AAS instance) and credentials, typically using Azure AD authentication.

Visual Studio will use your logged-in Azure AD credentials or prompt you for them when establishing the connection.

Connection Strings

Connection strings are used by applications and tools to connect to your Azure Analysis Services server. A typical connection string for Azure Analysis Services looks like this:

Provider=MSOLAP;Data Source=asazure://[your-region].asazure.windows.net/[your-server-name];Initial Catalog=[your-database-name];Impersonation Level=Impersonate;Authentication='Active Directory Integrated'

Key components:

  • Provider: Usually MSOLAP (Microsoft Analysis Services OLE DB Provider).
  • Data Source: The fully qualified path to your AAS server (e.g., asazure://eastus.asazure.windows.net/My AAS Server).
  • Initial Catalog: The name of the Analysis Services database you want to connect to.
  • Authentication: Specifies the authentication method. 'Active Directory Integrated' is common for Azure AD.

You can retrieve detailed connection strings from the Azure portal or using PowerShell cmdlets.

Troubleshooting

Common Issues:
  • Firewall Rules: Ensure that your Azure Analysis Services server's firewall allows connections from your IP address or virtual network.
  • Permissions: Verify that the user account or service principal used for connection has the necessary roles (e.g., Administrator, Data Reader) assigned on the Analysis Services server.
  • Authentication Errors: Double-check your Azure AD credentials and ensure MFA is handled correctly.
  • Provider Issues: Make sure you have the latest Microsoft OLE DB Provider for Analysis Services installed.

For detailed troubleshooting steps, consult the Azure Analysis Services troubleshooting guide.