# Setting up Tabular Model

# 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 Configuring Connections Strings 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.

   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

   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 Connecting to Azure SQL Database.

   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 Configuring Application Settings as guide in providing the value for the following keys:

   Example

   <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 Configuring Database Connection Strings 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 ProcessOlap as guide in performing the command.

   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.

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:

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.

4. Right-click on it and select Properties.

5. Click the Actions tab.

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