Follow

Configure TimeXtender with Azure Analysis Services

Contents

The requirements

You need an Azure Analysis Services resource created and running. You also need a user account that has rights to use it.  This articles describes two scenarios - where your DWH database is located on an Azure SQL database, or on an Azure SQL Managed Instance.

You will also need to install the AMO library. Refer to the following article: Analysis Services Data Providers

I have an Azure SQL Database

The benefit of this is that it already knows about the Azure Analysis services, so no further setup 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 Services 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 Services

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 those 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 have Azure SQL Managed Instance

When using Azure SQL Managed Instance, you have two options to connect to Azure Analysis Services:

1. Configure public endpoint in Azure SQL Database Managed Instance

or

2. Use an On-premises Data Gateway

The first step is to install the gateway. 

The most important point 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 cannot find the service and you can only set it up once. If you forget this, you may have to uninstall the gateway to set it up. Also, the user account you run as, must be the same as the one used in the gateway installation.

Register

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

Connect server to gateway resource

For additional reference, review Connecting to on-premises data sources with On-premises data gateway

Once the gateway is installed and configured, setting up an endpoint in TimeXtender 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 Services: 

Refer to the earlier section Set up the APP for Analysis Services

Deploy/execute the model. 

 

Permissions required to READ from tabular model

Now that the model exists, Analysis Services requires user permissions to read the model.  This can be done at the user level by adding user's e-mail address, or you can add Azure User Groups as defined below:

1. Azure Analysis Services requires the Azure Active Directory object id. Identify Azure Active Directory Group object IDs as shown below:

mceclip1.png

 

2. In TimeXtender project -> Semantic Layer -> Role -> Add Role -> Add External Users, use the following format:

obj:<Object-ID>@<Tenent-ID>

mceclip0.png

 

 

 

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

0 Comments

Please sign in to leave a comment.