How to connect to Azure Analysis Services with username and password?

Step-by-step connecting to Azure Analysis Services from any application

Azure Analysis Services is a cloud-based platform as a service (PaaS) that provides enterprise-level data models. It allows you to combine data from multiple sources using advanced mashup and modeling features, define metrics, and secure your data in a single, trusted tabular semantic data model. Users can then connect to Azure Analysis Services data models using tools like Excel, Power BI and many other applications to create reports and perform ad-hoc data analysis. In this article, we will describe how to connect to Azure Analysis Services from any application using an Azure Active Directory username and password with a newly created Azure AD user account.

Azure Active Directory User

The first step is to ensure you have created at least one Azure AD user in your Azure AD Tenant. Note that having your Microsoft Account in the Azure tenant is not helpful. You need to create an Azure Active Directory user that will be used to create a connection. (There is a difference between a Microsoft Account and an Azure AD account.) To create a new user in Azure Active Directory, you must be an Azure AD Tenant Administrator or Global Administrator. You can create a new user for your organization or invite an external user from the same starting point. Sign in to the Azure portal in the User Administrator role. Navigate to Azure Active Directory > Users. Select either Create New User or Invite External. If you created a new Azure AD user, make sure to sign in with the same user to https://login.microsoftonline.com to reset the user password or you might be getting 'Password expired' messages when trying to connect to Azure Analysis Services.

Azure Analysis Services Instance

After you create an Azure Analysis Services instance and add a model to the instance (in this case we selected the built-in 'Sample Data' model, available to select immediately after creating a new analysis services instance), Azure will display detailed information for the hosted Analysis Services database model. The 'Server Name' field and the 'database name' will be used while preparing a connection string for external applications. In this example, the 'Server Name' is 'asazure://westeurope.asazure.windows.net/kyubitas', while the 'database name' is 'adventureworks'.

Prepare the connection string

Once you have a valid Azure Active Directory user and active Azure Analysis Services instance, you can prepare a connection string that could be used to connect to Azure Analysis Services from various applications. The final connection string, in this example, will be the following line of text "Data Source=Provider=MSOLAP;Data Source=asazure://westeurope.asazure.windows.net/kyubitas;Initial Catalog=adventureworks;User ID=john@kyubit.onmicrosoft.com;Password=******;Persist Security Info=True;Impersonation Level=Impersonate;" where wildcard characters should be replaced with the actual password.

Connection string definition...

Data Source=Provider=MSOLAP;
Data Source=[SERVER NAME];
Initial Catalog=[DATABASE];
User ID=[USER PRINCIPAL NAME];
Password=********;
Persist Security Info=True;
Impersonation Level=Impersonate;

Data Source=Provider=MSOLAP;
Data Source=asazure://westeurope.asazure.windows.net/kyubitas;
Initial Catalog=adventureworks;
User ID=john@kyubit.onmicrosoft.com;
Password=********;
Persist Security Info=True;
Impersonation Level=Impersonate;

Testing the connection

After you prepared the connection string, you can use it from any application with the ability to connect to the Microsoft Analysis Services data source. For example, it could be used with Excel, Microsoft SQL Management Studio, Visual Studio, etc. In this example, we will connect with the Kyubit Business Intelligence application, but the principles are the same for any external application that is using ADOMD.NET components to connect to the Microsoft Analysis Services instance. To create a new data source in the Kyubit application that will connect to the Azure Analysis Services instance, you also need to provide the tabular 'Model' which will be used for data analysis. In this example, the model name is 'Model'. Click on the test button to confirm that the provided connection data are valid.

Azure Analysis Services - Data Analysis

Start a new analysis and drag-and-drop measures, dimension attributes/hierarchies on any of the available axes to start analyzing the data from the Azure Analysis Services tabular model. You can add dimensions and drill-down data on both the category and series axes to get the results of your interest. Add any number of slicers on the filter axis to narrow the analysis to a particular interest. Save the analysis, share it with other users, open it in the report view or schedule to receive the same report at the scheduled time on your email with the fresh data.

Azure Analysis Services - Compose a Dashboard

Once we have created data analysis/report using the Azure Analysis Services data source (in this case sample AdventureWorks database), we can quickly create interactive dashboards that contain multiple visualizations and metrics. Drag-and-drop visualization tiles, choose the appropriate chart type, add slicers, and many more analytic actions to choose from, to get a nice looking dashboard showing the most recent data from the Azure Analysis Services database tabular model.

Azure Analysis Services - Dashboard slicer and analyics

While using the dashboard, a user can drill-down, drill-through, slice and compare to discover any associated data with the Azure Analysis Services tabular model. Dashboards could be securely shared among the users, downloaded as PDF/Excel files or scheduled to receive the same dashboard at the scheduled time with the fresh data. Access to dashboards could be authorized for particular users, based on Windows Active Directory or Kyubit Users Authentication (Users created in the Kyubit application).

ADOMD.NET - Client libraries and Ports used for communication

To connect to the Azure Analysis Services with the application you develop, your application needs ADOMD.NET client libraries reference that actually handles the connection to Microsoft Analysis Services (either classic on-premise version SSAS or Azure Analysis Services). You may already have Microsoft.AnalysisServices.AdomdClient dll on your machine if you use Power BI or Excel. However, you need version 14.0.800.117 or higher to connect to Azure Analysis Services. You can check the version by going to “C:\Program Files\Microsoft.NET\ADOMD.NET”. If you don’t find a folder named 140, you probably have an older version (like 130). The connectivity to Azure Analysis Services is done using port 80 or port 443.

Conclusion

In a few steps, you can configure your Azure Analysis Services instance, and prepare a connections string with a username and password that could be used from various applications (using the ADOMD.NET client libraries) to create a connection and start analyzing data from tabular models on the Azure Analysis Servies.

 Author

Kresimir Korovljevic

Software architect on numerous projects related to Microsoft technologies. Software development and promotion of Business Intelligence software tools is primary focus and driving force.