topic Re: How to refresh Datasets with PowerBI REST API - executed in Azure Data Factory Pipeline in Developer

How to refresh Datasets with PowerBI REST API - executed in Azure Data Factory Pipeline

Robinator — Fri, 01 Mar 2019 09:27:16 GMT

Hi,

I've built multiple pipelines in Azure data factory and a few Power BI reports (https://app.powerbi.com) that use data from those pipelines. My ultimate goal is to be able to refresh a Power BI data set at the end of a pipeline. Underneath I'll show a small selection of what i've read and tried so far.

https://medium.com/@Konstantinos_Ioannou/refresh-powerbi-dataset-with-microsoft-flow-73836c727c33

https://mitra.computa.asia/articles/msdn-how-use-power-bi-embedded-rest

I started by registrating at https://dev.powerbi.com/apps and with API Acces "Select All". I checked in the azure portal the app registrations. I registrated twice because i was not sure if the oauth20 (following @konstantinos) would screw thing up:

Then I started in Azure data factory, but after a while (of having failures) I started using Fiddler to send the HTTP Post request.

I use the logic described at https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/refreshdatasetingroup. It says:

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

I get my group and dataset ID at https://app.powerbi.com/....

-> https://api.powerbi.com/v1.0/myorg/groups/ecb52bc1-........................../datasets/ab5d2d28-....................../refreshes

I copy this link into Fiddler:

My best guess would be that i should include authentication into the request body, although i cant find examples online.

Eventually I want to refresh the dataset from a 'web' container in ADF. Although, lets start succesfully refreshing something first 🙂

Re: How to refresh Datasets with PowerBI REST API - executed in Azure Data Factory Pipeline

Robinator — Mon, 04 Mar 2019 08:22:33 GMT

Gonna try this solution. Magic search word = Postman

https://community.powerbi.com/t5/Developer/Power-BI-REST-API-using-postman-generate-embed-token/m-p/310153#M9157

Re: How to refresh Datasets with PowerBI REST API - executed in Azure Data Factory Pipeline

Robinator — Mon, 04 Mar 2019 10:57:09 GMT

yaay, some small progress

Re: How to refresh Datasets with PowerBI REST API - executed in Azure Data Factory Pipeline

Robinator — Mon, 04 Mar 2019 14:09:55 GMT

another step forward, not sure how i get this token into a new API post

Re: How to refresh Datasets with PowerBI REST API - executed in Azure Data Factory Pipeline

Robinator — Tue, 05 Mar 2019 07:59:04 GMT

gonna try creating a powershell script and run that script in ADF using a webhook,

special thanks to https://blog.gbrueckl.at/2017/08/refresh-powerbi-datasets-powershell-azure-runbooks/

https://docs.microsoft.com/en-us/powershell/module/Microsoft.PowerShell.Utility/Invoke-RestMethod?view=powershell-3.0

https://stackoverflow.com/questions/52697381/power-bi-dataset-refresh-using-powershell

Re: How to refresh Datasets with PowerBI REST API - executed in Azure Data Factory Pipeline

Robinator — Thu, 07 Mar 2019 10:20:59 GMT

Used the following as Powershell script, and it worked

$authUrl = "https://login.windows.net/common/oauth2/token/"

$body = @{

"resource" = "https://analysis.windows.net/powerbi/api";

"client_id" = "bf..........";

"grant_type" = "password";

"username" = ".......@.........";

"password" = ".............";

"scope" = "openid"

}

$authResponse = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body

$headers = @{

"Content-Type" = "application/json";

"Authorization" = $authResponse.token_type + " " +

$authResponse.access_token

}

$restURL = "https://api.powerbi.com/v1.0/myorg/groups"

$restResponse = Invoke-RestMethod -Uri $restURL –Method GET -Headers $headers

$datasetId = "ab5d......."

$groupId = "ecb5......"

$restUrl = "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/refreshes"

$body = @{

"notifyOption"= "MailOnFailure"

}

$restResponse = Invoke-RestMethod -Uri $restUrl –Method POST -Headers $headers -Body $body

Re: How to refresh Datasets with PowerBI REST API - executed in Azure Data Factory Pipeline

Robinator — Thu, 07 Mar 2019 11:49:18 GMT

Thanks for the help Robinator!

Re: How to refresh Datasets with PowerBI REST API - executed in Azure Data Factory Pipeline

Robinator — Thu, 07 Mar 2019 11:49:38 GMT

You're welcome