Connect and Query Azure Analysis Services

This document provides essential steps and examples for connecting to and querying your Azure Analysis Services models. Understanding these methods is crucial for leveraging the full power of your tabular or multidimensional data models.

Introduction

Azure Analysis Services (AAS) is a fully managed Platform as a Service (PaaS) that provides enterprise-grade data modeling capabilities, enabling business intelligence professionals and developers to build semantic models that power interactive data visualization and business reporting.

Connecting to your AAS instance allows various client tools and applications to query the data model. The primary query language used is DAX (Data Analysis Expressions) for tabular models and MDX (Multidimensional Expressions) for multidimensional models. This guide focuses primarily on connecting to and querying tabular models.

Prerequisites

An Azure Analysis Services instance deployed and running.
A deployed tabular model on your AAS instance.
Appropriate permissions to connect to the AAS instance (e.g., Reader, Member, or Administrator roles).
Client tools such as Power BI Desktop, Excel, SQL Server Management Studio (SSMS), or Visual Studio with Analysis Services projects.

Connecting to Azure Analysis Services

Using SQL Server Management Studio (SSMS)

SSMS is a versatile tool for managing and querying Analysis Services. It's particularly useful for administrative tasks and ad-hoc querying.

Open SQL Server Management Studio.
In the 'Connect to Server' dialog box, select 'Analysis Services' as the Server type.
Enter your AAS server name. The server name typically follows the format: YourServerName.asazure.windows.net.
Choose an authentication method:
- Windows Authentication: If your Azure AD account is associated with your on-premises Active Directory and has permissions.
- Azure Active Directory - Universal with MFA: Recommended for most users. You'll be prompted to sign in to Azure AD.
- Azure Active Directory - Password: Use with caution, only if other methods are not feasible.
- Azure Active Directory - Integrated: For managed identities or service principals.
Click 'Connect'.

Using Power BI Desktop

Power BI Desktop is the primary tool for creating interactive reports and dashboards. Connecting to AAS allows you to use your existing models.

Open Power BI Desktop.
From the 'Home' tab, click 'Get Data'.
Select 'Azure' and then 'Azure Analysis Services' (or search for it).
Click 'Connect'.
Enter your AAS server name (e.g., YourServerName.asazure.windows.net).
Choose a 'Data connectivity mode':
- Live Connection: Connects directly to the AAS model. Any changes to the model are reflected automatically. This is the most common and recommended mode for AAS.
- Import: Imports a static copy of the data into Power BI Desktop. Not typically used for AAS.
Click 'OK'.
Sign in to Azure AD using your credentials when prompted.
Select the tabular model you want to connect to and click 'Connect'.

Using Excel

Excel can connect to AAS to create PivotTables and other data analysis features.

Open Excel.
Go to the 'Data' tab.
Click 'Get Data' > 'From Azure' > 'From Azure Analysis Services'.
Enter your AAS server name and click 'OK'.
Authenticate with your Azure AD credentials.
Select the model and click 'Connect'.
You can then choose to load the data into a PivotTable report or as a connection.

Querying Azure Analysis Services

Using DAX with SSMS

Once connected via SSMS, you can write and execute DAX queries against your tabular models.

EVALUATE
    CALCULATETABLE (
        'DimProduct',
        'DimProduct'[Color] = "Red"
    )

This query returns all rows from the 'DimProduct' table where the 'Color' is 'Red'.

Using DAX with Power BI Desktop (Live Connection)

In Power BI Desktop, you primarily interact with AAS through visualizations. However, you can write DAX measures and calculated columns within Power BI that are executed against the AAS model.

To create a DAX measure:

In Power BI Desktop, right-click on the table in the 'Fields' pane where you want to add the measure.
Select 'New measure'.
Enter your DAX formula in the formula bar.


Total Sales = SUM('Sales'[Amount])

This measure calculates the sum of the 'Amount' column from the 'Sales' table.

Using MDX (for multidimensional models)

If you are working with multidimensional models, you will use MDX. The connection process is similar, but SSMS provides an 'MDX Query' editor.

SELECT
    [Measures].[Sales Amount] ON COLUMNS,
    [Product].[Category].Members ON ROWS
FROM [AdventureWorks]

Connection Strings

Connection strings are essential for programmatic access and for many client tools. A typical connection string for Azure Analysis Services looks like this:

Provider=MSOLAP;Data Source=asazure://.asazure.windows.net/;Initial Catalog=;User ID=@.com;Password=;

For Azure AD authentication, you might use:

Provider=MSOLAP;Data Source=.asazure.windows.net;Initial Catalog=;Authentication=ActiveDirectoryIntegrated;

Or for Universal with MFA:

Provider=MSOLAP;Data Source=.asazure.windows.net;Initial Catalog=;Authentication=ActiveDirectoryInteractive;

Important: Always use secure methods for managing credentials, especially in applications. Consider using Azure AD authentication with managed identities or service principals for server-to-server communication.

Troubleshooting Common Issues

Connection Refused: Ensure the server name is correct and that your IP address is allowed through the firewall (if configured).
Permission Denied: Verify that your Azure AD account has been granted appropriate roles (e.g., Reader, Member) on the AAS server or database.
Model not found: Double-check the database name in your connection string or the selection in your client tool.

For more detailed troubleshooting, refer to the Azure Analysis Services troubleshooting guide.