In this blog post we’ll explore how to trigger a Power BI Dataset via Azure Synapse Analytics in 3 simple steps:
Step 1:
Create a new security group called “AzureSynapsePowerBIIntegration” in your Azure Active Directory.
Next add the name of your Azure Synapse Workspace as a member of your new security group. This will be your service principal.
Step 2:
Login to powerbi.com and in the top right hand corner locate “Settings” and then “Admin portal”
Under “Tenant settings” locate “Developer Settings” and then “Allow service principles to user Power BI APIs”.
Set this service to “Enabled” using the toggle. Next under the heading “Apply to:” select “Specific security groups (Recommended)”. Next add the newly created security group “AzureSynapsePowerBIIntegration” and click apply.
Now all that remains in the power bi service is to assign the security group “Member” workspace access to the dataset you want to refresh.
Step 3:
Login to your Azure Synapse Workspace.
Create a new pipeline called “Power BI Dataset Refresh” and then create a new web activity called “Refresh Dataset”.
Create two new parameters “WorkspaceGuidId” and “DatasetGuidId” both as strings.
Locate the WorkspaceGuidId (also known as the GroupId) and the DatasetGuidId from the URL of the dataset you want to refresh via powerbi.com
The URL will look like this below:
https://app.powerbi.com/groups/{groupId}/datasets/{datasetId}/details
Assign the Workspace/GroupId guid and the DatasetId guid to your newly created parameters.
Next within the settings of the “Web” activity paste the following dynamic content into the URL section.
@concat(‘https://api.powerbi.com/v1.0/myorg/groups/’,pipeline().parameters.WorkspaceGuidId,’/datasets/’,pipeline().parameters.DatasetGuidId,’/refreshes’)
And set the other options as per below:
Method:
POST
Body:
{}
Authentication:
System Assigned Managed Identity
Resource:
https://analysis.windows.net/powerbi/api
And that’s it…
All that is left to do now is debug / trigger the pipeline and see the dataset refresh in powerbi.com!
Some final points:
- If the dataset resides on a non premium capacity you can only schedule up to 8 refreshes per day.
- If the dataset resides on a premium capacity you can schedule up to 48 refreshes per day.
- Any datasets on a premium capacity don’t impose limitations for API refreshes.
- The API refresh data request only works when a published dataset is set to import mode rather than direct query.
- You can switch the method to a “GET” request instead and retrieve the refresh history of the dataset. The output of this could be saved as a variable and then subsequently written back to a database via a stored procedure.
