Scenario

You want to refresh an Azure Analysis Services (AAS) model that is not a part of your TimeXtender semantic models. This article describes a technique to process the Azure Analysis Services tabular model by using Azure Logic Apps to watch a log table created in the TimeXtender data warehouse. 

Implementation

The following Microsoft article explains how to refresh tabular models using the Azure Analysis Services REST API through Azure Logic Apps.

The above method uses a standard scheduled recurrence trigger. However, by replacing this with a SQL Trigger it is possible to trigger the refresh as soon as the SQL tables are done loading. 

The below method uses Add Related Records to update small log table in the data warehouse with any new batch numbers that appear in the fact table. Then we can use the above mentioned logic app SQL trigger to watch this log table to trigger a refresh as soon as the fact table has completed.

Below are the steps.

Create the Log Table

1. Create a new table called PurchaseModelUpdateLog (or the name of your choice)– This table doesn’t require any additional fields as we will use the system fields only.
   • Right click on the new table > Table Settings > Data Extraction. Uncheck Truncate valid table before data cleansing
   • 
2. Right-click the new table > Advanced > Add Related Records
3. Create Records from (the core fact table of the AAS model) – Purchase Transactions
4. Record Condition: Not Exists – This will only update the table if a new batch number is found in the source table.
5. Data Destination Table: Valid – this will ensure we insert the batch number from the Purchase Transactions table into the new log table, rather than having the cleansing procedure generate it’s own batch.
6. Field Mapping: DW_Batch: Set this to the DW_Batch field from the Purchase Transactions table.
7. Field Mapping: DW_SourceCode: This can be set to any custom or default value (not important).
8. Add a Condition: DW_Batch = DW_Batch.
9. Execute and Preview the tables. You will notice that as new batch numbers appear in the Purchase Transactions table, they will appear in the new table.
10. Important: Be sure this new “PurchaseModelUpdateLog” table is part of the execution package that updates the Fact table.

Important: It is possible to replace the log table and related records insert with a view. However, this can cause the Logic app to trigger before the fact table execution has completed, so it will not work for this solution.

Create the Logic App

In the Azure portal, create a blank logic app, which opens the Logic App Designer. 

Finally, create a logic app with a SQL Trigger and the HTTP Action like this:

Note the URI format in the PSOT above:

https://your server region/servers/aas server name/models/your database name/refreshes

Additional Information

To learn more about forming the HTTP request body, see Asynchronous refresh with the REST API - POST /refreshes.

The following article describes how to process an AAS model using PowerShell.

Executing a PowerShell script from SSMS to operate on a database used in a TimeXtender Project