Tabular Model Configuration

On-premise Server

To setup a local server for tabular model, follow this sequence of instructions:

1. Setting Tabular Server Connection String.

   Update the Tabular Server key in the ConnectionStrings.config and provide the Azure Analysis Services Server and database information. Use the instructions in the section Database Connection Strings Configuration to perform this step.

2. Using the Analyis Server console application, run the following commands:

   • Createolap /tabular
     Use the instructions in the section CreateOlap as guide in performing the command.

   • DeployOlap /tabular
     Use the instructions in the section DeployOlap as guide in performing the command.

   • Processolap /tabularonly
     Use the instructions in the section ProcessOlap as guide in performing the command.

   NOTE

   Make sure that the data is successfully loaded and processed in the SQL Server data source for the Tabular Model to process successfully.

3. Save the changes.

Azure Analysis Services Server

To setup the deployment of Tabular model in Azure Analysis Services, follow this sequence of instructions:

1. Create Azure Analysis Services server in Azure Portal. Use the instructions in the Microsoft Learn article – Create a server - Portal

   NOTE

   Use the link to create an AAS in Azure Portal.

2. Register application in Azure Active Directory (Microsoft Entra ID) using App registration.

3. Create Service Principal in Azure Analysis Services using SSMS.

4. Setup Azure SQL Server Connection.

   Use the instructions in the section Azure SQL Database Connection Configuration.

   NOTE

   It is also supported to connect to On-premises SQL Server (local server) and database as data source. But it requires the configuration of On-premises Data Gateway. Use the instructions in this article Azure Analysis Services with On-Premises Data Gateway to perform the installation and configuration.

5. Configure appsetings.config.

   Use the instructions in the section Application Settings Configuration as guide in providing the value for the following keys:

   Example appsetings.config

   <add key="aas:ClientId" value="5c214695-38b5-4296-a2f7-29f558668cb7" /> 
   <add key="aas:ClientSecret" value="OLv8Q~3NTKssEccM6RN620ocYdXGU0iwFO6X9bdK" /> 
   <add key="aas:Domain" value="openit.com" /> 
   <add key="aas:TenantId" value=" b198a08d-7d22-4c5c-80f6-4adc8919af8b" /> 

   Where all the values are from the registered application in AAD. The Domain can be seen in the Overview page of the Microsoft Entra ID (AAD).

6. Configure ConnectionStrings.config.

   Update the Tabular Server key in the ConnectionStrings.config and provide the Azure Analysis Services Server and database information. Use the instructions in the section Database Connection Strings Configuration to perform this step.

7. Using the Analysis Server console application run the following commands:

   • Createolap /tabular
     Use the instructions in the section CreateOlap as guide in performing the command.

   • DeployOlap /tabular
     Use the instructions in the section DeployOlap as guide in performing the command.

   • Processolap /tabularonly
     Use the instructions in the section ProcessOlapas guide in performing the command.

   NOTE

   Make sure that the data is successfully loaded and processed in the SQL Server data source for the Tabular Model to process successfully.

Registering an application in Azure Active Directory (Microsoft Entra ID) in Azure Portal

Make sure to register an application in Azure Portal that will serve as token provider for the authentication in Azure Analysis Services.

NOTE

Use the instructions in the Microsoft Learn article – Register an app with Microsoft Entra ID for a more detailed instructions.

To register an application, please follow these instructions:

1. Go to Microsoft Azure's official website, sign in to your Azure Portal account.

2. Search for Microsoft Entra ID and navigate to it.

3. In the left pane, click Manage > App registrations.

4. Click New registration in the upper left corner of the page.

5. Provide a descriptive name for the application.

6. In the Redirect URI, select Web as platform and http://localhost/ for the URI.

7. Once redirected, click Manage > Certificates & secrets.

8. Click New client secret.

9. Provide a description and select desired expiration days. Click Add.

10. Copy the Value and store it in a safe storage for future use.

11. In the left pane, click Overview. Take note of the following information:

    • Application (client) ID
    • Directory (tenant) ID

Creating a Service Principal in Azure Analysis Services using SSMS

Make sure that token provider is part of the Server users in the Azure Analysis Services. To create, please follow these instructions:

NOTE

The user doing this should be part of the Administrators of the Azure Analysis Services Server. In Azure Portal, navigate to the Azure Analysis Services Server > Settings > Analysis Services Admins. Then, add the account that will perform this task.

NOTE

Use the instructions in the Microsoft Learn article – Add a service principal to the server administrator role for a more detailed instructions.

1. Open SSMS.

2. Connect to Analysis Services using the Server Name of the created Azure Analysis Services.

3. Once connected, right-click the root node and select Properties.

4. The Analysis Server Properties dialog will appear. Click Security, then click Add.

5. This will allow you to use your Azure credentials. Next, the Select a User or Group dialog will appear. Use the Manual Entry textbox to provide the Service Principal credential in the following format: app:<clientid>@<tenantid>, where <clientid> and <tenantid> are from the registered application in AAD. Click Add, then click OK.

6. Save the changes.

Including Tabular Model Processing in Background ETL Process

If you would like the Tabular Model to be included in data daily processing of Analysis Server, follow these instructions:

1. Open the Task Scheduler in the machine where Analysis Server is installed.

2. In the left pane, click Task Scheduler Library.

3. In the list of tasks, look for OpeniT.Server.Etl.Console.exe.

   

   Task Scheduler

   
   

4. Right-click on it and select Properties.

   

   Task Scheduler Library

   
   

5. Click the Actions tab.

   

   Task Scheduler Library Properties

   
   

6. Click Edit and change the argument into:

   If including the processing along with the Multidimensional cube, use this command:

   Example

   OpeniT.Server.Etl.Console.exe runetl /tabular 

   If only the tabular model will be processed and not the Multidimensional cube, use this command:

   Example

   OpeniT.Server.Etl.Console.exe runetl /tabularonly