Connect Clients to Azure Analysis Services

This document outlines how to connect various client applications to your Azure Analysis Services model. Connecting clients allows users to interact with your data, build reports, and gain insights.

Prerequisites:

An Azure Analysis Services server instance.
A deployed tabular or multidimensional model.
Client applications installed on your machine (e.g., Power BI Desktop, Excel, Visual Studio).

Connecting with Power BI Desktop

Power BI Desktop is a primary tool for connecting to and visualizing data from Azure Analysis Services.

Open Power BI Desktop.
On the Home tab, click Get data.
Select Azure, then select Azure Analysis Services.
In the Server name field, enter the server name for your Azure Analysis Services instance. This typically follows the format: your-servername.asazure.windows.net.
Choose the Data Connectivity mode:
- Live Connection: Data remains on Azure Analysis Services, and reports are always up-to-date. This is the recommended mode for Azure Analysis Services.
- Import: Data is imported into Power BI Desktop. This is not typically used for Azure Analysis Services due to performance implications and loss of real-time capabilities.
Click OK.
If prompted, authenticate using your Azure Active Directory credentials.
Select the database (model) you want to connect to and click Connect.

Connecting with Microsoft Excel

Excel can connect to Azure Analysis Services to create PivotTables and PivotCharts.

Open Microsoft Excel.
Go to the Data tab.
Click Get Data > From Other Sources > From Analysis Services.
In the Server name field, enter your Azure Analysis Services server name (e.g., your-servername.asazure.windows.net).
Under Database name, select the desired database from the dropdown list or leave it blank to see all available databases.
Click OK.
Authenticate if prompted.
Choose how you want to view the data (e.g., PivotTable Report).

Connecting with Visual Studio (for Model Development)

Developers use Visual Studio with the SQL Server Data Tools (SSDT) to connect to existing Azure Analysis Services models for development and deployment.

Open Visual Studio with SQL Server Data Tools installed.
Create a new Analysis Services Tabular Project or open an existing one.
In the Solution Explorer, right-click on the Data Sources folder and select Add Data Source.
In the Table Import Wizard, select Azure Analysis Services as the data source type.
Enter your Azure Analysis Services server name.
Click Test Connection to verify.
Click OK to create the data source.

Other Client Applications

Many other business intelligence and data visualization tools support connecting to Azure Analysis Services using the following connection strings or protocols:

ODP.NET, Managed Provider for Oracle: Used by some Oracle applications.
ODBC: Standard connectivity protocol.
ADOMD.NET: The .NET data provider for Analysis Services.

The general connection string format is:

Provider=MSOLAP;Data Source=your-servername.asazure.windows.net;Initial Catalog=your-database-name;Integrated Security=Claims;Impersonation Level=Impersonate

For specific client applications, consult their documentation for detailed connection instructions.

Security: Ensure that users connecting to your Azure Analysis Services server have the appropriate permissions granted through Azure Active Directory roles or Analysis Services roles.

Troubleshooting Common Connection Issues

Incorrect Server Name: Double-check the server name, including the domain suffix.
Authentication Errors: Ensure you are using the correct Azure AD credentials and that your account has access.
Firewall Rules: Verify that your server's firewall allows inbound connections from the client's IP address or network.
Permissions: Confirm that the connecting user has been assigned to a relevant role on the Analysis Services server or database.