Use Azure Analysis Services

Complete the steps below to use Azure Analysis Services as a target for the Semantic Layer Endpoint (Analysis Services Tabular).

Prerequisites

If you created your own application server (not from the Azure Marketplace Template), then you will need to download and install the AMO library which can be found here: Analysis Services Data Providers

Create an Analysis Services Resource in Azure

If you already have an Analysis Services in Azure then you can skip this step.

1. Azure Portal > Create a new Resource > Analysis Services > Create
2. Assign the Server Name, Subscription, Resource group, and Location. 
3. Once deployed, open the resource and note down the Analysis Services Server Name which will look like this: asazure://eastus.asazure.windows.net/xxxxxx

Create an App Registration

In order to access the Analysis Services resource from TimeXtender, you will need to configure an App Registration in the Azure Active Directory. 

1. In the Azure Portal menu, click on Azure Active Directory, then click on App Registrations in the menu bar on the left. Then click New Registration.       
2. Enter a name and select Accounts in this organizational directory only. The value of Redirect URI is the URL at which your application is hosted. Click Register when you are done.
3. For the newly added App Registration, select Certificates & secrets to create a New Client Secret. This key is encrypted after save, so it needs to be documented somewhere safe. The secret will appear after you click Add.
4. Please note the following properties of the App Registration which will be needed later:
   1. Application ID
   2. Client Secret (Application Key)

Assign the App Registration as Analysis Services Admin

This step must be completed using SQL Server Management Studio.

1. In SSMS, connect to your Azure AS server.
2. In Server Properties > Security, click Add.
3. Type the name of your app registration in the the search box and click search.
4. Click the app registration as it appears in the list, click Add.

   

5. If your App registration is not visible in search result, you may enter it manually using the following format: app:<app-ID>@<tenant-ID>

Verify the service principal account ID, and then click OK. 

Add TimeXtender Semantic Model Endpoint

Sign in to the TimeXtender Portal, go to Data Estate > Instances, click Add instance > Add semantic model

Refer to Add a Semantic Model Instance “Tabular” section for details on connection options.

1. In the Server box, type the fully-qualified name of your Azure Analysis Services. 
2. In the Database box, type the name of the database (new model to be created).
3. In the Deployment target list, click the version of SSAS you are targeting. Automatic (default) or Analysis Services Universal are the recommended settings.
4. In the Compatibility box, type the compatibility level you want to use, or leave it blank to use the highest supported by the server.
5. Set Server authentication to authentication login
6. In the Server Username box, type the App Registration (created above), using app:<app-ID> format   .
7.  In the Server Password box, enter the secret for App Registration
8. In the Processing Authentication list, select how the Tabular service will connect to the data warehouse.
   • Click Service account to use the SQL Server Analysis Services service account.
   • Click Windows user to use another user and then enter the user name for the user in the Username box and the corresponding password in the Password box.
9. Select Process model offline to process the model "behind the scenes" and make the deployment seamless for the users. The offline database will have the endpoint's name prefixed with "Offline_".

Run TimeXtender Desktop, click on “refresh” button on the menu bar to see the new semantic model instance you added above.

You can now drag objects from MDW instance to the semantic model instance, then Deploy & Execute your Semantic model.  On deployment, the model is created on the SSAS Tabular server. To get data in the model, you need to execute the model as well.

Enable use with SQL Managed Instance or On-Prem SQL Server

If using Azure SQL Managed Instance for your Data Warehouse, you must configure one of the two following options to connect to Azure Analysis Services:

• Configure public endpoint (Easiest option - Recommended)
• Use an On-premises Data Gateway

When using an On-Prem SQL Server for your Data Warehouse Use an On-premises Data Gateway

Provide Users Access to the Model

Just like in SQL, Analysis Services requires user permissions to access the model.  This is done by adding user's e-mail address, or you can add Azure User Groups as defined below:

1. Identify Azure Active Directory Group Object IDs as shown below:

   

2. In TimeXtender Desktop > Semantic Instance > Role > Add Role > Add External Users, use the following format: obj:<Object-ID>@<tenant-ID>

   

Troubleshooting

Symptoms: You are trying to authenticate an Azure Active Directory user, who is granted access as Analysis services Admin, but unable to authenticate and deploy the models.  You receive this error:

Root cause: This scenario will only work for Active Directory user accounts that do NOT use 2-factor authentication. So, you have to implement one of the following 2 options:

1. Create a new (special) Active Directory user account which does NOT use 2-factor authentication, for endpoint connection.
2. Create an app registration and implement all steps as described above in this guide