Azure Analysis Services is an analytical data engine (VertiPaq) used in decision support and business analytics. It provides enterprise-grade semantic data models for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools. There are various ways to connect to Azure analysis services for browsing and data analysis. This article describes how to connect to Azure analysis services from SQL Server Management Studio (SSMS).
Prerequisites:
1. Download and install the latest (>18.x) SQL Server Management Studio.
2. Azure active directory user has rights to connect to Azure analysis services.
Steps to connect to Azure analysis services using SQL Server Management Studio.
- Open SSMS
- Click on Connect in Object Explorer.
- Click on Analysis Services.
- In the Server name field, put Azure analysis server name (e.g., asazure://westeurope.asazure.windows.net/yourservername).
- From the Authentication drop-down, select “Azure Active Directory – Universal with MFA”.
- In the User name, put your Domain email address and click Connect.
It may ask you to log in using the email address to MS Azure, and it should take you to databases.
Right-click on the database and click on browse, as shown below
You will see the model as shown in the image below. Drag and Drop the objects from the Model pane and perform the required analysis. Click the auto-execute button in the SSMS toolbar to enable the automatic execution of the queries when you change objects in the execution pane.
Pro tips:
1. Microsoft provides two types of analysis services models; multidimensional and tabular. Read this article to know how they differ from each other.
2. Refer to this post to connect to Azure analysis services from Power BI desktop.
See more
Kunal Rathi
With over a decade of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.
I am always interested in new challenges so if you need consulting help, reach me at kunalrathi55@gmail.com.