Refresh Power BI Semantic Model from MSFT Fabric Data Pipeline

Problem Statement

Is it possible to refresh the Power BI Semantic Model via Microsoft Fabric Data Pipelines as there is no out-of-the-box connector for Power BI?

Prerequisites

1. Power BI Semantic Model
2. Fabric Data Pipeline
3. Service Principal / Organizational Account
   • How to Create a Service Principal

Solution

1. Allow service principal to access power BI APIs

Go to the Power BI Service at https://app.powerbi.com and open the Admin Portal.

Go to the ‘Tenant Settings’ section and scroll to the setting for ‘Service principals can use Fabric APIs’. Enable the setting and either allow the entire organization to use the Rest APIs (not recommended) or specify an Azure Active Directory Group and make sure your Service Principal / Organizational Account is a member of that group.

We would be leveraging the Power BI REST API Datasets – Refresh Dataset for refreshing Power BI Semantic Model and Datasets – Get Refresh History for getting refresh status of Power BI Semantic Model.

2. Grant the service principal / organizational account member access

Grant the Service Principal / Organizational Account, and Member access to the workspace hosting the Power BI Semantic Model.

Overall flow

GitHub code

Where we have parameters defined.

• GroupId: This is the ID of the workspace hosting the Semantic Model.
• SemanticModelId: This represents the Power BI Semantic Model Id.

3. Create a new connection in web activity

Create a new Connection in web activity with Organizational account authentication and appropriate Base URL and Token Audience Uri. Click sign in to proceed.

• Base URL: https://api.powerbi.com/v1.0.
• Token Audience URL: https://analysis.windows.net/powerbi/api

This blog leverages the option to use a web activity with organizational authentication for an easy refresh of Power BI datasets or semantic models. This method eliminates the need for creating service principals or Azure Active Directory applications.

Service principal authentication alternative

Where provide the appropriate Tenant ID, Service Principal Client ID, and Service Principal Key / Secret details.

4. Add relative URL and body appropriately for the trigger refresh web activity settings.

Relative URL: /org/groups/@{pipeline().parameters.GroupId}/datasets/@{pipeline().parameters.SemanticModelId}/refreshes

5. Check the status of the refresh

The Refresh API call is asynchronous. Hence, we do not know whether the Semantic model refresh has succeeded. The successful execution of the web activity does not mean that the refresh was a success. To check the status of the refresh one can leverage Web activity to trigger Power BI Dataset/Semantic Model refresh status via Power BI REST API.

We can leverage the same connection created earlier.

Relative URL: /myorg/groups/@{pipeline().parameters.GroupId}/datasets/@{pipeline().parameters.SemanticModelId}/refreshes?$top=1

6. Implement a polling pattern

We must add a polling pattern to periodically check on the status of the refresh until it is complete. We start with an until activity. In the settings of the until loop, we set the expression so that the loop executes until the output of the above web activity is not equal to Unknown because when a Semantic model is refreshing, “Unknown” is the status returned until it completes or fails.

Expression: @not(equals(first(JSON(string(activity('Get Refresh Details').output)).value).status, 'Unknown'))

Within Until Activity

7. Final activity check refresh status and handle failure

The Final activity is the IF activity that checks the Refresh status and leverages a Fail activity to fail the pipeline in case of refresh failure.

IF Activity Expression: @equals(first(JSON(string(activity('Get Refresh Details').output)).value).status,' Failed')

Fail Activity Message: @string(JSON(first(JSON(string(activity('Get Refresh Details').output))?.value)?.serviceExceptionJson))

Output

Success

Failure