Use Azure Analysis Services

Follow the steps below to use Azure Analysis Services as a target for the Semantic Model Endpoint (Analysis Services Tabular) within a Deliver instance.

Prerequisites

If your application server was not deployed from the Azure Marketplace template, download and install the AMO library from Analysis Services Data Providers.

Create an Analysis Services resource in Azure

If you already have an Azure Analysis Services resource, skip this section.

1. In the Azure portal, go to 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 the Analysis Services Server Name, which will be in the format:

   asazure://eastus.asazure.windows.net/xxxxxx

   A firewall rule must be configured on your Azure Analysis Services server to allow inbound connections from Data Integration. Without this, connection attempts will be blocked. For steps, see Configure Azure Analysis Services server firewall.

Create an App Registration

To access the Analysis Services resource from Jet Analytics Data Integration, configure an App Registration in Azure Active Directory.

Note: Azure Administrator access is required to register the application in the Azure Portal.

1. In the Azure portal, go to Azure Active Directory > App Registrations and click New Registration.
2. Enter a name and select Accounts in this organizational directory only. The Redirect URI is the URL where your application is hosted. Click Register.
3. On the new App Registration, go to Certificates & secrets and create a New Client Secret. The secret is encrypted after saving — record it somewhere safe. It will appear after you click Add.
4. Note the following properties for later use:
   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 (SSMS).

1. In SSMS, connect to your Azure Analysis Services server.
2. Go to Server Properties > Security and click Add.
3. Type the name of your App Registration in the search box and click Search.
4. Click the App Registration in the results list and click Add.

   

5. If the App Registration does not appear in search results, enter it manually using the format:

   app:<app-ID>@<tenant-ID>

Verify the service principal account ID and click OK.

Add Tabular Endpoint

In the Jet Analytics Portal, go to Data Estate > Instances, click Add Instance >Add Deliver instance.

The Jet Analytics portal is managed by the insightsoftware Support team. For assistance or additional information, reach out to Product Support.

1. In the Server box, enter the fully-qualified name of your Azure Analysis Services server.
2. In the Database box, enter the name of the database (the new model to be created).
3. In the Deployment target list, select the version of SSAS you are targeting. Automatic (default) or Analysis Services Universal are the recommended settings.
4. In the Compatibility box, enter the compatibility level to use, or leave it blank to use the highest level supported by the server.
5. Set Server authentication to Authentication login.
6. In the Server Username box, enter the App Registration using the format:

   app:<app-ID>

7. In the Server Password box, enter the client secret for the App Registration.
8. In the Processing Authentication list, select how the Tabular service will connect to the data warehouse:
   • Service account: Uses the SQL Server Analysis Services service account.
   • Windows user: Uses a specific user account. Enter the username in the Username box and the corresponding password in the Password box.
9. Select Process model offline to process the model in the background and make deployment seamless for end users. The offline database will have the endpoint name prefixed with Offline_.

In Jet Analytics Data Integration, click Refresh to display the new Deliver instance. Drag objects from the Prepare instance into the Deliver instance, then Deploy and Execute. On deployment, the model is created on the SSAS Tabular server. Data is populated when the model is executed.

Enable use with SQL Managed Instance or on-premises SQL Server

If using Azure SQL Managed Instance for your data warehouse, configure one of the following options to connect to Azure Analysis Services:

• Configure a public endpoint (recommended — easiest option)
• Use an on-premises data gateway

If using an on-premises SQL Server for your data warehouse, use the on-premises data gateway.

Provide users access to the model

Analysis Services requires user permissions to access the model. Access can be granted by adding individual email addresses or by adding Azure Active Directory user groups.

1. Identify the Azure Active Directory Group Object ID for the group to be added.

   

2. In Jet Analytics Data Integration, open the Deliver instance and go to Role > Add Role > Add External Users. Enter the group using the format:

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

   

Troubleshooting

Error:Unable to obtain authentication token using the credentials provided. If your Active Directory tenant administrator has configured Multi-Factor Authentication or if your account is a Microsoft Account, please remove the user name and password from the connection string, and then retry.

Symptoms: An Azure Active Directory user with Analysis Services Admin access cannot authenticate and deploy models.

Root cause: This authentication method only works for Active Directory user accounts that do not use two-factor authentication. Implement one of the following options:

1. Create a dedicated Active Directory user account without two-factor authentication for the endpoint connection.
2. Create an App Registration and follow all the steps in this guide.