Establish a Robust Monitoring System for your Critical Power BI Semantic Models

When a Power BI semantic model plays a critical role in your organization’s operations, it’s essential to have alert mechanisms in place that notify your data analytics team of any issues before business users are affected. Recently, we developed a Power Automate flow that sends an alert to one of our client’s Slack channel if a semantic model refresh cycle takes longer than 8 minutes or if it fails. This alert could just as easily be sent via email or to a Teams channel. In this blog post, we’ll walk you through how we set it up.

1. Overview of the process

As shown in the image below, the flow begins by retrieving refresh metadata from the semantic model using an HTTP query. This allows us to capture key details such as the refresh start time, end time, and the status (unknown, failed, completed). With this information, we can set up the two conditions that will allow us to determine if the refresh process has failed or if it’s running longer than it should be.

2. Reoccurrence Trigger

In this scenario, the Power Automate trigger is configured to “Recurrence,” with the flow set to refresh every minute. Since our client’s Power BI semantic model is continuously refreshing, we have scheduled the flow to trigger at one-minute intervals to always be able to capture the latest status and refresh start time of the model. We have shown you how to continuously refresh a Power BI model with Power Automate in a previous blog post.

3. HTTP Action

Before making this HTTP request, it’s crucial to first register an Azure app using a service principal account. This registration is necessary for authenticating and getting a response from the REST API. We’ll dive deeper into how to do this in an upcoming blog post, but for now, you can read Microsoft’s documentation for more details. Additionally, note that a Power Automate premium license is required to use the HTTP action.

In this setup, the HTTP action in Power Automate is used to retrieve the refresh history of a Power BI dataset. Below is the URL you’ll need to enter to access the refresh history via the REST API:

https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes

{groupId} needs to be replaced by your tenant id and {datasetId} needs to be replaced by the dataset id for which you want to pull its refresh history. You can find these IDs by navigating to the settings page of your semantic model in the Power BI online portal. The groupID and datasetID can be found in the URL of the settings page in your browser.

After you’ve entered the URL, select the GET method and then you will need to enter the following authentication information:

Authentication Type: Active Directory OAuth

Audience: https://analysis.windows.net/powerbi/api

Tenant, Client ID, Secret: You will have this information in Azure after you register your application

4. Parse JSON

The Parse JSON action in Power Automate is used to interpret and extract data from a JSON-formatted response received from an HTTP request. This allows you to work with the data more easily in the subsequent steps of the flow.

Here’s how you typically use the Parse JSON action:

1. Add the Parse JSON Action:
2. After the HTTP action, add the Parse JSON action.
3. In the Content field, you should reference the JSON data you want to parse. This is usually the Body of the HTTP response.
4. Define the Schema:
5. To help Power Automate understand the structure of the JSON file, you need to define the schema of that file. You can generate this schema automatically by pasting a sample JSON response into the Generate from sample button in the schema section. Power Automate will create the schema based on the structure of this sample.

5. Compose Action to Get 2^nd Refresh Status 

The expression body('Parse_Json')?['value']?[1]?['status'] retrieves the status value of the second item in the value array within the parsed JSON response. Since the refresh is happening continuously, the most recent (or latest) refresh in the list ([0] index) might still be in progress or not fully updated, resulting in a status of “Unknown”. The second item in the list, accessed by [1], is likely to represent the most recently completed refresh, where the status is more meaningful (e.g., “Completed”, “Failed”, etc.). By the time you’re querying the semantic model refresh metadata, the refresh cycle has already moved on from the latest completed refresh, and its status has been fully updated.

6. Compose Action to Get the Start Time of Latest Refresh

The expression body('Parse_Json')?['value']?[0]?['starttime'] is used in Power Automate to extract the start time of the latest Power BI dataset refresh. This expression is typically stored in a Compose action, which can then be used in further calculations, such as determining if the refresh is taking more than x amount minutes.

7. If the Refresh Cycle Takes More than x Minutes

In our Power Automate flow, we’ve configured a parallel branch with two conditions. The first condition uses an AND operation to evaluate two key criteria:

1. Duration Check
   Expression: div(sub(ticks(utcnow()), ticks(outputs('starttime'))), 600000000) > 8
   Purpose: This expression calculates the duration between the current time and the start time of the latest Power BI refresh and checks if it exceeds 8 minutes.
2. Status Check
   Expression: body('Parse_Json')?['value']?[0]?['status'] == 'Unknown'
   Purpose: This checks if the status of the most recent Power BI refresh is “Unknown”.

Once the condition verifies that the refresh has been running for more than 8 minutes and still has a status of “Unknown,” a Slack action triggers to notify the relevant team or channel. This ensures potential issues are quickly identified and addressed.

8. If the Refresh Cycle Fails

To manage Power BI refresh failures in your Power Automate flow, follow these steps:

1. Add a Parallel Conditional Action:
   Create a parallel branch specifically designed to handle failure scenarios.
2. Configure the Condition:
   Use a Compose action with the expression:
   body('Parse_Json')?['value']?[1]?['status']
   This expression verifies if the status of the second most recent refresh is “Failed.”
3. If True:
   Add a “Post message” action using the Slack connector to notify the appropriate Slack channel or group.

By implementing this Power Automate flow, you can establish a robust monitoring system for your critical Power BI semantic models, ensuring timely alerts for refresh delays or failures. Whether you’re notifying your team via Slack, Teams, or email, this solution allows you to take proactive measures before issues impact your business users. The setup detailed in this blog gives you the flexibility to extend these alerts across various channels and personalize the flow to meet your organization’s specific needs. With these steps in place, your data analytics team can stay informed and maintain smooth, uninterrupted operations. Don’t hesitate to contact us if you need need our help!