Refresh Azure Analysis Service model using Azure Logic App

Process Azure Analysis Services model using Logic App

In my previous article, I wrote about the steps to create tabular model in Azure Analysis Services. This article is more focused on refresh of the tabular model using logic app(A Serverless application).

To learn more about Logic App service, refer (Source:Microsoft Documentation) https://azure.microsoft.com/en-in/services/logic-apps/#overview

Why Refresh ?

Once the cube model is created, it becomes necessary to refresh the data model on daily or weekly basis, depending on the need, so whenever someone views the model in Power BI dashboard it should reflect the recent snapshot of data. The model may fetch data from Azure Synapse Analytics (formerly known as Azure SQL Data Warehouse), SQL Database or any other sources, it is therefore necessary to ensure once the data gets updated in these sources, same should reflect on the dashboard to maintain the data Integrity.

There are several approaches to refresh the cube model, viz:

1. Using Logic App
2. Using Rest API
3. Using Azure Automation

Note: In this section, I’ll focus on refreshing data model using Logic App.

Prerequisites: You should have the logic app & Azure Data factory (ADF) provisioned in your resource group to follow along with the tutorial & following steps must be already performed in order to proceed further:

1. Create Logic App and Data factory resources, in your resource group
2. Register your Logic app and Data factory instance in app registration, within Azure Active Directory
3. Generate a Secret as that would be required in the later process, if it’s already created you can use that.

Step 1: Login to Azure account → open Logic apps → Go to Logic app designer view from the left menu bar.

Logic App Menu in Azure portal

Step 2: We are building refresh process based on the HTTP request raised by the Azure Data factory component, so we would need a “HTTP request is received connector”. A Blank Workspace will appear in the designer window. Click on “+ New Step” sign to add the first connector i.e. “When a HTTP request is received”.

HTTP request connector as base connector

Note: Once you add this activity the HTTP POST URL will be automatically generated after saving. Keep a note of this URL, as this will required while sending the Rest API request via Azure Data factory web activity. Also, the Request body should be left blank in this case and should be handled by ADF web activity instance.

Step 3: Click on Insert New Step → Add an Action → search “Variable” in the available connectors list.

“Variable” option as second step

After adding variable select an appropriate action type for the selected Variable. In this case, it should be “Initialize variable”.

Variable action type window

Next provide the variable an appropriate Name, Type and Value as shown in below image,

Initialize Variable

The Value section contains the body definition that needs to be passed in the HTTP Activity as described in detail below.

The elements defined in JSON body are:

CommitMode: It defines how the objects in the model will be saved after entire refresh is complete or in batches. There are 3 options: transactional, default & partialBatch.

Max parallelism: It defines the number of threads that will be processed in parallel.

Objects: An Array of tables & partitions that needs to be processed. Passing empty Objects array will consider the entire tables and partitions defined in the model for refresh. (Note: If we do not pass anything in Objects array, then it will consider all the table/partitions defined in the cube model. Hence it is necessary to pass tables / partitions name in the Objects, if we do not want to refresh the entire partitions or tables in the model)

RetryCount: It defines how many times the server will retry before moving into failure state.

Type: It defines the type of refresh that must be enforced while refreshing the model. There are several options available: full, dataOnly, calculate, automatic, clearValues etc. The list may differ in case of tables or partitions except few common values.

Step 4: Select the HTTP connector in next step and configure the following options as listed below.

• Method: POST (used for refresh trigger)
• URI: The URL generated as, “https://<your_server_region>/servers/<aas_server_name>/models/<your_model_name>/refreshes.” In this URl ensure that “your_server_region”, “aas_server_name” & “your_model_name” are to be filled, as per the project. e.g.: https://eastus2.asazure.windows.net/servers/my_aas_server/models/test_model/refreshes
• Headers: Enter key = Content-Type, Enter value = application/json
• Body: When you click in the body text area it will show the Dynamic Content i.e. the variable you have created in step 3, should be passed here. Next you must click on authentication parameter below the Cookie option. This will bring in some more options to be configured.
• Authentication Type: Active Directory OAuth
• Tenant ID: It can be obtained from App registration also termed as Directory ID.
• Audience: https://*.asazure.windows.net
• Client ID: It can be obtained from App registration also termed as Application ID.
• Credential Type: Secret
• Secret: This is created as a part App registration secret creation process.

HTTP Connector Part I

HTTP Connector Part II

After the above steps are completed verify the details to ensure everything is in place, save your logic app. This will complete the logic app configuration setup.

Logic App Refresh Setup

Note: But this isn’t enough, since we are configuring the logic app setup with http request connector, we would need some orchestration tool in order to process our logic app in this case we are using Azure Data Factory (ADF), that will pass the http request call to logic apps & trigger the refresh process.

Step 5: Setup a pipeline with “web activity” in Azure DataFactory. Go to Resource Group → launch Azure Data factory instance & click Author and Monitor → Create a new pipeline with web activity.

Azure Data Factory “Web Activity”

In this web activity,

URL: It should be considered from the step 2 “when the HTTP request is received”.

Method: POST method should be used in order to initiate the refresh process.

Headers: Same as step 4.3, headers.

Body: Please note the body is passed as {} (blank), this is because we have already setup the variable in Logic apps to generate the body at runtime. Hence this should be left blank as “{}”.

This completes our web activity setup in ADF, and it can be triggered to test logic app refresh process. Run the pipeline it may take around couple of minutes to execute and show the status as completed on ADF instance.

The completion status on ADF window does not mean the refresh process is completed. Instead, it signifies that pipeline trigger is completed which has invoked the Logic App by sending the asynchronous HTTP call to Logic app instance to trigger the refresh process. If the data to be refreshed is massive, it may take several minutes/hours depending on your database size to complete the overall process.

The refresh status can be viewed by going to logic app window → Overview → Runs history as seen below. Make sure the status window is refreshed and then click on the topmost row to view the status of activity, whether it has successfully completed or is in progress or failed.

Overview tab in Logic App

Error Handling Scenario: If the status of the logic app activity is “failed” you may first need to verify the status by clicking on activity to see at what step the executed halted. If all the activities are successful executed, then it may be because of the objects passed in the body configuration as shown in step 4.4. To debug this issue, proceed with the below steps.

#1 Fetch the refresh ID: To Identify the status of the refresh process first you must need to fetch its refresh Id, to do so edit the logic app HTTP connector activity “Method as GET”(rather than POST) and remove the variable from the body section by clicking on cross over it, save it and re-trigger the ADF pipeline to get the response from the logic app, similar way we checked the status of completion earlier we can click on run status in overview tab, to see all the refresh Ids responses returned of last 30 days in JSON format.(It only signifies the status as in progress, failed or successful)

#2 Append specific refresh ID to URI: It is better to navigate to the latest date in the response than checking general status using GET, hence it is advisable to copy the refresh ID from the JSON response returned in #1, append the refresh ID (as …/<refresh-id>) next to URI configured in step 4.2. This will help to only fetch the in detailed response status, for the specific refresh ID than all the ID’s from last 30 days and help to debug the issue more easily. Based on the response the issue (e.g. there could be a case with certain dimension table having duplicate rows which will violate the rules, if it is specified in one-to-many relationship) needs to be fixed and pipeline can be re-triggered to ensure successful execution of the refresh process.

This completes the Cube model refresh process setup using Azure Logic Apps. Please post your comments below, if you like this article or if any queries in the above process I’ll be happy to assist.😊

I hope this tutorial is helpful!👍