0845 643 64 63

How to Trigger a Power BI Dataset Refresh via Azure Synapse Analytics in 3 Simple Steps.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

371,505 Spambots Blocked by Simple Comments

HTML tags are not allowed.

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon