Unnie Ayilliath

Skip the blog and check the Github repo for full sample code

Azure Analysis Services is a new service (Paas) in Azure where you can create semantic data models. It provides easier and faster way to query against massive amount of data using clients like Power BI, Excel and other reporting clients (Tableu etc). For those who are already familar with SQL Server Analysis Services (SSAS), you can think this as a Azure Paas service of  SSAS.You can read more about Azure Analysis Services here in Microsoft official documentation.

Okay, now there are many ways to authenticate to Azure Analysis Services and it is all very well documented in official documentation for Authentication scenarios for Azure analysis services. Now if you are looking for connecting to Azure Analysis Services using client apps like Power BI, Excel , please look into official documentation mentioned above, because this blog will not help you. 

This blog is helpful for below scenario:

1. Programmtically connecting to Azure Analysis Services from WebAPI/Websites
2. Programmatically connecting to Azure Analysis Services from Client apps (Console apps/ Windows forms/ Mobile apps etc)

There are 3 client libraries available for connecting to Azure Analysis Service.

1. ADOMD.Net
2. Analysis Services Management Object (AMO)
3. Analysis Service OLE DB Provider (MSOLAP)

You can read more @ Analysis Service client libraries official documentation

This blog explains only connection using ADOMD.Net, because this is one of the popular/common way to connect to any Analysis Service model (on-prem SSAS or Azure version).

Prerquisites:

1. Azure Analysis Service with a model (You can create sample Adventure works model in just one click). See more on how to do this.
2. If you have  Power BI or Excel installed on your machine there is a very good chance that Microsoft.AnalysisServices.AdomdClient dll is already present in your machine. But to connect to Azure Analysis Services you need atleast version 14.0.800.117 (latest while writing this blog) . So to check this you can go to “C:\Program Files\Microsoft.NET\ADOMD.NET” and if you do not see a folder with 140 (most likely to see 130), just go and install the latest libraries from here .

Now the prerquisites are ready, its time to decide on the type of account to be used for connecting to Azure Analysis Services. there are 2 options:

1. Using a user Account: Here you need to create a AAD user account (as a service account) and while connecting , you need to pass the username & password. Here you can also choose to connect using the logged in user credentials.
2. Using a Service Principal: Here you will create an AAD app and connect to Azure Analysis Service using the App.

Recommendation: If you are not planning to connect to Azure Analysis Service using logged in user context, better to connect using Service principal rather than creating a Service account with hardocded username & password.

In this post, I am focussing on using Service principal and not using User Account as almost all the steps are similar.

Step 1: Create a Service Principal a.ka. Register an Azure AD App

Am not going in much detail here, as there are many resources available on how to do this. Simple steps below (You need to be a AAD administrator to do this)

1. Sign in to Azure Portal.
2. Navigate to Azure Active Directory –> App Registrations and Click New application registration.
3. Give some name for your app , Application type= Web app/API , Sign-on URL =http://localhost:500 (Not really important here, you can give anything). Click create and Grant permissions (default permission to AAD read user).

You can skip above steps if you already have an App registered for your app.

Step 2: Grant permission to Azure Analysis Service Model

1. Connect to Analysis Service using SSMS 2017 (I think support for Azure Analysis Service only available for SSMS 2017)
2. Select the database model and click on Roles
3.  Add the Service principal to any role in below format.

app:<appid>@<tenantid>

eg:  app:5B0A1C8F-9406-4C99-AE99-4A369203ED4C@6C5D3D44-301C-465F-99D6-DD62B1D2E332

TenantId–> DirectoryId of AAD (Check in AAD properties)

AppID –> Client id of the service principal registered in AAD.

Note: To add a user , you should enter the email id of the user.

Key learning here for me was, granting access to user or Service principal using Azure Analysis services “Access Control” does not grant them access to the models inside. The only way to grant access to model from Azure portal is by adding them as Administrators (Bad practice unless you really want to make somebody admin).

Voila, now all the set up is done and its time to write some code.

Writing the code

Get access tokens

To connect to Azure Analysis service using Service principal, we need to get the access tokens for the service principal from Azure AD. We can either use MSAL or ADAL for this. In this example I am using ADAL. So in your solution install Microsoft.IdentityModel.Clients.ActiveDirectory from nuget. Below is the code to get service principal access tokens.

Now let’s see how you can access the Azure Analysis Service using this access token.

Connection String format: “Provider=MSOLAP;Data Source=<url of the Azure AS>;Initial Catalog= <modelname>;User ID=;Password=<put access token here>;Persist Security Info=True;Impersonation Level=Impersonate”

Below is sample code to connect to Azure Analysis service using ADOMD

You can see full code of  a sample console application querying Azure Analysis Service in my Github repo here.