This article describes how to refresh an Azure Analysis Services (AAS) model that is not a part of your TimeXtender semantic models. Azure Analysis Services tabular models can be processed by using Azure Logic Apps to monitor a log table created in the TimeXtender Classic data warehouse.
Implementation Steps
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 monitor this log table to trigger a refresh as soon as the fact table has completed.
Create the Log Table
- 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
- Right click on the new table > Table Settings > Data Extraction. Uncheck Truncate valid table before data cleansing
- Right-click the new table > Advanced > Add Related Records
- Create Records from (the core fact table of the AAS model) – Purchase Transactions
- Record Condition: Not Exists – This will only update the table if a new batch number is found in the source table.
- 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.
- Field Mapping: DW_Batch: Set this to the DW_Batch field from the Purchase Transactions table.
- Field Mapping: DW_SourceCode: This can be set to any custom or default value (not important).
- Add a Condition: DW_Batch = DW_Batch.
- 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.
Note: Ensure this new “PurchaseModelUpdateLog” table is part of the execution package that updates the Fact table. Furthermore, 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.
