Use Analysis Services Tabular Model with Jet Analytics

The article applies to Jet Analytics 2019 and higher.

Overview

In addition to working with OLAP Cubes, Jet Analytics also supports the use of a Tabular Model .  This works well for those environments that do not support the SQL Server Analysis Services required for OLAP Cubes.

Jet Analytics supports both on-premises and Azure analysis services tabular models.

Deploy the Tabular Model

1. Create an Endpoint

   From the Semantic tab of the Jet Data Manager , expand the model you wish to deploy, right-click Endpoints , and select Add Analysis Services Tabular Endpoint.

   

2. On the Add Analysis Services Tabular Endpoint dialog, give your endpoint a unique and descriptive name.  Next, enter the name of the server where you want to create the endpoint and the name of the database which will contain the data.

   Your server instance can be on-premises or can resize within an Azure environment:

   

   

   If you plan on having your data deployed during business hours, you can use the Process model offline option to process the data in the background before replacing the existing database - thus having minimal impact on logged-in users.

   Finally, specify the account that will be used during the Deploy and Execute phase.

3. You next need to define those users who will have access to the Tabular data.  From the same Model in the Jet Data Manager, right-click Roles and then select Add Role.

   

   • If you are using an on-premises database, you will need to add users who are defined within your Active Directory domain.  Click the Add Users... button and use the Select Users or Groups dialog to define the appropriate users.
   • If using an Azure environment, click Add External Users... , and then enter the names of a users already defined within the Microsoft Entra ID domain.

     

   • Note: You will be prompted to sign in so as to have access to the Microsoft Entra ID.

     

4. It is now time to Deploy and Execute your tabular model. Right-click the desired Model within the Semantic tab in the JDM and then select Deploy and Execute

   

   Set your options and click Start. Your Tabular Model will be created.

   

5. Once the Deploy and Execute process has completed, if you look at your server databases, you can see the newly-deployed tabular model contained there.

   

   

6. We can now create a connection to our data.

   Within Jet Reports, access the Data Source Settings and select database type as Cube.

   

7. On the Connection tab, enter the same on-premises server and database names as were used in defining the Analysis Services Tabular Endpoint.

   

8. We can now access our data using the Pivot Table functionality available on the Jet ribbon:

   • Click Pivot Table.

     

   • Select the tabular model database form which you wish to retrieve data:

     

     You can now use Excel's pivot table functionality to gather the data you want.