Complete the steps below to use Azure Analysis Services as a target for the Semantic Layer Endpoint (Analysis Services Tabular).
- Create an Analysis Services Resource in Azure
- Create an App Registration
- Assign the App Registration as Analysis Services Admin
- Add TimeXtender Semantic Model Endpoint
- If using Azure SQL Managed Instance or an OnPrem SQL server
- Enable users to READ from the tabular model
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.
- Azure Portal -> Create a new Resource -> Analysis Services -> Create
- Assign the Server Name, Subscription, Resource group, and Location.
- 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.
- 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.
- 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.
- 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.
- Please note the following properties of the App Registration which will be needed later:
- Application ID
- Client Secret (Application Key)
Assign the App Registration as Analysis Services Admin
This step must be completed using SQL Server Management Studio.
- In SSMS, connect to your Azure AS server.
- In Server Properties > Security, click Add.
- Type the name of your app registration in the the search box and click search.
- Click the app registration as it appears in the list, click Add.
- Note: 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
Note: If using Multiple Environments you should enter these settings as a global database in Environment Properties instead.
- In TimeXtender, create a new (or navigate to an existing) Semantic Model
- Right-Click on Endpoints > Add Analysis Services Tabular Endpoint.
- Give the Endpoint a name
- Enter the following information:
- Server: This is the Analysis Service Server Name you noted earlier
- Database: This can be anything you decide. A database with this name will be created during deployment.
- Deployment Target & Compatibility level: These can typicall be left as default.
- Server Login > Use Authentical Login > Username: This is the app registration we created earlier. You must prefix the Application ID with "app:"
- Password: This is the Application Client Secret which you noted down earlier.
- Processing > Use Windows user > Username: This should be a SQL or Azure AD user that has read permission on the Data Warehouse Database.
- Password: This is the password for that SQL User.
You can now Deploy & Execute your Semantic model.
Enable use with Azure SQL Managed Instance
If you are not using Azure SQL Managed Instance, or an OnPrem SQL Server you can skip this step.
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. Additionally if you are using an OnPrem SQL server you need to use the second option:
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 project -> Semantic Layer -> Role -> Add Role -> Add External Users, use the following format:
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:
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. You should then be prompted to enter your credentials.
Root cause: This scenario will only work for Active Directory user accounts that do NOT use 2-factor authentication. So, you have 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