Follow

How to add a Azure Analysis services tabular model

Azure Analysis Services

Since release 17.12 we have had the opportunity to deploy our semantic tabular endpoint to the Azure Analysis Services.

UPDATE!

Since the 19.2.5 release we can now support Analysis services 2019.

Contents

The requirements

You need a Azure Analysis Services resource created and running. You need a user account that has rights to use it. Besides that there are two methods to connect to it when that is set up.

You either need to have your DWH database located on a Azure SQL server, or install a on-premises data-gateway.

You will also need to install the AMO library. A link here Analysis Services Data Providers

I have a Azure SQL Server running

The benefit of this is that it already knows about the Azure Analysis services, so no further setups is needed.

How to set a DWH up on a Azure SQL Server is explained here.

Alternatively a setup could look like this.

01.PNG

The next step is to create the Tabular endpoint. The semantic model you created needs to use tables from the MDW database exclusively. The endpoint should be set up like this.

Take note in the xxxxxxx part in the server name area, that is the name of the azure analysis service itself. You can use the service account, but only if it has rights on the Azure Analysis services server.

Regarding the deployment target, you may need to change it to one of the other options, if your azure service is a different version than your SQL server.

Set up the APP for Analysis

If your Azure Analysis Service server is using SQL2019 version 15.0.10.25. You will need to use an app to circumvent the two factor authentication popup. There is a guide about how to set up the application here Create an Azure AD application

When the app is created you will have the App id and its password. Add them as username and password.

The user you use has to be added on the Analysis Services Admins.

The next step is to give the Application access to the Analysis services.

Connect to the server in SQL management studio.

Then go to the Properties -> Security and click Add.

Search for the name you gave the app click on it so it is marked and click Add again.

That is it. Deploy/execute to add the model.

I will use the gateway

The first step is to install the gateway. Gateway installation guide

The most important bit to note is that the location must be the correct one. In my case it is West Europe. If you don't add the correct one, it cant find the service and you can only set it up once. If you forget this you have to uninstall the gateway to set it up. Also the user account you run as, must be the same you used in the gateway installation.

Register

When it is up and running and you made the connection. You are ready.

Connect server to gateway resource

The next step is similar to the previous method.

Regarding the deployment target, you may need to change it to one of the other options, if your azure service is a different version than your SQL server.

Set up the APP for Analysis

If your Azure Analysis Service server is using SQL2019 version 15.0.10.25. You will need to use an app to circumvent the two factor authentication popup. There is a guide about how to set up the application here Create an Azure AD application

When the app is created you will have the App id and its password. Add them as username and password.

The next step is to give the Application access to the Analysis services.

The user you use has to be added on the Analysis Services Admins.

Also here does the user you use need to be added on the Analysis Services Admins.

The next step is to give the Application access to the Analysis services.

Connect to the server in SQL management studio.

Then go to the Properties -> Security and click Add.

Search for the name you gave the app click on it so it is marked and click Add again.

Then you deploy/execute the model.

 

Was this article helpful?
0 out of 0 found this helpful

0 Comments

Please sign in to leave a comment.