Microsoft Learn

Integrate with Azure Analysis Services

This document guides you through integrating Azure Analysis Services with various client tools and development environments. Understanding how to connect and leverage these tools is crucial for building powerful analytical solutions.

Connecting with Business Intelligence Tools

Azure Analysis Services provides seamless connectivity with leading business intelligence (BI) tools. These tools allow users to visualize data, create reports, and gain insights from your Analysis Services models.

Power BI

Power BI is a popular choice for interactive data visualization and reporting. You can connect to Azure Analysis Services directly from Power BI Desktop or the Power BI service.

• From Power BI Desktop: Select "Get Data" and choose "Azure Analysis Services database".
• From Power BI Service: Create a "Live Connection" to your Azure Analysis Services instance.

For detailed instructions, refer to the Power BI documentation.

Excel

Microsoft Excel can connect to Azure Analysis Services using the "From Other Sources" -> "From Analysis Services" option in the Data tab. This allows users to create PivotTables and PivotCharts.

Ensure you have the appropriate client libraries installed for a smooth connection.

Third-Party Tools

Azure Analysis Services uses the Analysis Services OLE DB Provider or the ADOMD.NET client library. Most BI tools that support standard tabular models or multidimensional models can connect.

Development and Management Tools

Several tools are available for developing, deploying, and managing your Azure Analysis Services models and instances.

SQL Server Data Tools (SSDT)

SQL Server Data Tools (SSDT) in Visual Studio is the primary environment for developing tabular and multidimensional models for Azure Analysis Services. You can create new projects, import data, define relationships, write DAX or MDX expressions, and deploy your models.

Key Features:

• Visual model designer.
• DAX and MDX query editors.
• Deployment wizard.
• Integration with source control.

Visual Studio Code with Extensions

For a lightweight development experience, Visual Studio Code (VS Code) combined with extensions can be used.

• Tabular Editor Extension: Provides an intuitive tree-like interface for managing tabular models, including metadata editing, DAX scripting, and deployment.
• Power Query Extension: Useful for crafting data transformations.

Azure Data Factory

Azure Data Factory can be used to orchestrate data pipelines that load data into your Azure Analysis Services models. You can use its connectors to move data from various sources and then use the "Copy Data" activity or custom activities to populate your models.

Programming with Client Libraries

For custom applications and advanced scenarios, you can use client libraries to interact programmatically with Azure Analysis Services.

ADOMD.NET

ADOMD.NET is a .NET data provider for Analysis Services. It allows you to query the Analysis Services engine, manage databases and models, and retrieve metadata.

Example connection string snippet:

"Provider=MSOLAP;Data Source=asazure://your_region.asazure.windows.net/your_server;Initial Catalog=your_database;Integrated Security=True;Persist Security Info=False;"

AMO (Analysis Management Objects)

AMO is a .NET library used for automating the administration and management of Analysis Services. You can use it to create, alter, and drop databases, manage roles, and perform other administrative tasks.

REST API

Azure Analysis Services also exposes a REST API, enabling interaction with your Analysis Services instances from any platform or language that can make HTTP requests.

Connecting with Management Tools

SQL Server Management Studio (SSMS)

While SSMS is primarily for SQL Server, it can also connect to Azure Analysis Services instances for basic management tasks, browsing objects, and running queries. Ensure you are using a recent version of SSMS.

Azure Portal

The Azure portal provides a web-based interface for managing your Azure Analysis Services server, including scaling, monitoring, and configuring basic settings.

Next Steps

Explore the following resources to deepen your understanding:

• Tutorials on building tabular models
• Using DAX for advanced calculations
• Best practices for performance