Refresh Power BI Semantic Model from MSFT Fabric Data Pipeline

[NandanHegde]

Problem Statement:

Is it possible to refresh 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 on the workspace hosting the Power BI Semantic Model.

Overall flow:

GitHUB code

where we have below 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 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 Uri: 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: /myorg/groups/@{pipeline().parameters.GroupId}/datasets/@{pipeline().parameters.SemanticModelId}/refreshes

5. The Refresh API call is asynchronous. Hence, we do not know whether the Semantic model refresh has actually 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. 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. 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:-