Refresh Power BI Dataset from Azure Data Factory - Part 1

By:   |   Comments (19)   |   Related: > Power BI


Problem

I have built a pipeline in Azure Data Factory that runs my daily ETL process, which loads data into an Azure SQL Server database. On top of this database, a Power BI model has been created that imports the data. At the end of the pipeline, I'd like to refresh this model so it contains the latest data. However, there's no built-in functionality in Azure Data Factory to communicate with Power BI. How can I achieve this?

Solution

At the moment, there's no support in Azure Data Factory (ADF) for directly refreshing a Power BI dataset. However, there's a work around you can use by leveraging Azure Logic Apps. Unfortunately, currently even Logic Apps doesn't support full integration with Power BI. There's only a preview feature to add rows to a dataset:

add rows to dataset in logic apps

So, we need to go one step further: we're going to create a Logic Apps custom connector, which will allow Logic Apps to communicate with the Power BI API.

Creating the Logic Apps Custom Connector

Registering a Power BI App

Since we're going to use the Power BI API, we need to register an app at https://dev.powerbi.com/apps. Normally this is used to embed Power BI content, but we need an application ID and an application secret to be able to connect to the API. First you need to log in with your Power BI credentials (make sure you use an account with sufficient privileges).

sign in into Power BI

Once you're registered, fill in the form to register the app:

register app

The following information is required:

  • Application name
  • Application type. Since we're just using this "app" to refresh a dataset, you can choose the server-side web application.
  • As home page URL, you can choose any valid URL.
  • For the redirect URL, we will temporarily use https://login.live.com/oauth20_desktop.srf. We're going to change this later on in the Azure Portal.
  • For access permissions, you can only Read and write all datasets, but I've checked all options. Maybe you need to Logic App custom connector to do more later on than just refreshing datasets.

When you hit Register, you'll receive confirmation and your application ID and secret. Be sure to copy these down to a safe location.

application ID and secret

If you don't have the correct permissions (it's possible to block the registering of apps by users on the tenant level), you'll get the following message:

no dice

Note you can do the same process from within the Azure Portal, by using app registrations:

app registration

More info on using app registrations can be found in the official documentation. Using the Power BI website is a bit more straight forward than using the Azure Portal.

Logic Apps Custom Connector

Now we're going to create a custom connector. In the Azure Portal, click on Create a resource and search for Logic Apps custom connectors.

logic app custom connector

To create a new connector, specify a name, subscription, resource group and location:

create a new connector, specify a name, subscription, resource group and location

Once the connector is created, go to the editor:

edit custom connector

In the General tab, you can specify how you want to create the custom connector. It is possible to define all the actions for the Power BI connector yourself, but luckily there's a swagger file available on Github that defines all the possible actions for you (and there are quite a lot of actions!). A swagger file describes a RESTful API. Download the file from Github to your local machine, and then import it in the custom connector editor.

upload swagger file

If you want, you can specify an icon and a description for the custom connector:

specify icon

Other settings can be left as-is. In the security tab, we need to configure the authentication type.

edit authentication type

For our custom connector, we're going to choose OAuth 2.0.

API Key

Configure the form with the following information:

  • Azure Active Directory as the identity provider.
  • The client ID and secret we created earlier by registering the app at the Power BI website.
  • The login URL can be kept as-is.
  • The tenant ID needs to be common.
oauth specs part 1
oauth specs part 2

Once you save the custom connector, the redirect URL will be generated. We need to copy this and replace the original redirect URL (remember, when we registered the app?) in the Azure Portal.

redirect url

In the Azure Portal, go Azure Active Directory and then App registrations. Find the app with the name you used when registering the app in the Power BI website. Go to Settings and then Reply URLs. There you can replace the original reply URL with the one from the Azure Logic Apps custom connector:

new redirect url

Back to the custom connector. In the third tab, definition, you will see all of the actions and references defined by the swagger file we imported earlier. Most actions will display one or more warnings, most likely because a type or something similar hasn't been defined. These can be ignored. There's one problem however. In the Logic Apps editor, actions that end with a point are not allowed. Unfortunately, almost all actions names and with a period.

There's one problem however. In the Logic Apps editor, actions that end with a point are not allowed

It's still easier to import the swagger file and update the 116 actions than manually defining the actions yourself though. To speed up the process, you can edit only the action we're actually going to use, which is number 49: "Triggers a refresh for the specified dataset from the specified workspace".

Triggers a refresh for the specified dataset from the specified workspace

Make sure you have the action with "dataset from the specified workspace" and not "from My Workspace". The custom connector is now finished and ready to be used in your Azure Logic Apps.

Conclusion

In this part of the tip, we created a Logic Apps custom connector. We imported the Power BI API definitions using a swagger file and registered an app on the Power BI website for authentication purposes. In the next part of the tip, we're going to build a Logic App using the custom connector, so we can refresh a dataset in Power BI from Azure Data Factory.

Next Steps

Learn more about Power BI in this 3 hour training course.


Click here to start the Power BI course






sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, October 5, 2022 - 4:32:48 AM - Koen Verbeeck Back To Top (90563)
Hi Venkat,

you only need one. It's just a method to call the Power BI API from a Logic App.
Another option is to create an Azure Function to call the API directly.

Regards,
Koen

Tuesday, October 4, 2022 - 2:48:47 AM - venkat bodempudi Back To Top (90557)
Hi Verbeeck,

How many custom connector we do need if we have around 10 power bi workspaces?

Thanks
Venkat

Monday, March 7, 2022 - 4:13:06 AM - Koen Verbeeck Back To Top (89856)
Hi Mubarek,

you could put the API call into a ForEach loop in ADF and pass along the name/guid of the dataset into the body of the API call. In the Logic App, you read out the body and retrieve the name of the dataset you need to refresh, and pass that as a parameter to the action that does the refresh.

Tuesday, March 1, 2022 - 3:20:53 AM - Mubarek Back To Top (89840)
Hello Koen, thanks a lot for your replied, I updated my custom connector then it started working and for few days it has been working (didn't encounter any token expired issues. In our case we have 10+ datasets to refresh, we use ADF as ETL tool, any suggestions on how to make this process more dynamic. Your help is appreciated. Thank you!

Saturday, February 26, 2022 - 3:45:27 PM - Koen Verbeeck Back To Top (89836)
Hi Mubarek,

seems there's an issue with tokens, which is weird since a client ID and client secret is used to connect.
Not sure how I can assist further. Maybe you can try the Power BI forums, there's a very active community there.

Regards,
Koen

Friday, February 25, 2022 - 3:58:31 AM - Mubarek Back To Top (89832)
Hello Koen,
Thank you for this great article.
I have an issue, I was able to create the app registration on Azure Active Directory, created the Azure logic app custom connector to get the last refresh history and everything worked find, the next day the same logic app gave me an error: Status code 403 ,
"message": "Access token has expired, resubmit with a new access token"
Can you please help me on this. Thank you for your help

Thursday, September 23, 2021 - 3:47:14 AM - Koen Verbeeck Back To Top (89254)
Hi Sid,

I haven't done it myself to be honest (I did it manually since it was a one-time thing).
I would look into ARM templates maybe? https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-azure-resource-manager-templates-overview

Koen

Wednesday, September 22, 2021 - 1:03:51 PM - Sid Back To Top (89252)
Hi Koen,
Sorry for late reply. I was able to get the custom connector working after appropriate access was given to the registered api.
Currently, I am facing issue i promoting the Custom connector to different environments. Is there a guide that you can help with promoting this Logic app to different enviornments like from Dev to qa and prod.
Thanks,
Sid

Thursday, August 5, 2021 - 5:19:44 AM - Koen Verbeeck Back To Top (89093)
Hi Sid,

are you the admin of the Power BI tenant? When connecting to the Power BI service from the Logic App, this needs to be done by someone with admin permissions (even though you gave the app permissions).

Also keep in mind there's a preview task in Logic Apps now that can refresh a dataset, so you don't need the custom connector anymore, unless you need to do other REST API stuff.

Koen

Wednesday, August 4, 2021 - 10:27:42 AM - Sid Back To Top (89091)
Hi, I am getting 403 Forbidden error in Logic App. Followed all the steps in Registering app and setting up URL.

Thursday, April 8, 2021 - 9:20:33 PM - ibasulto Back To Top (88500)
The swagger file must not exceed 1 MB size when importing in custom connector editor.

Thursday, January 28, 2021 - 10:17:57 AM - Koen Verbeeck Back To Top (88118)
Hi Venu,

it seems someone has taken a backup here:
https://github.com/dataxbi/powerbi-api-swagger/blob/master/swaggerV2.json

Not sure if it supports all the latest and greatest of the Power BI API.

Koen

Thursday, January 28, 2021 - 12:32:11 AM - Venu Back To Top (88113)
Hi,
Even this link does not have the swagger file. Can you point to the right URL, or file location?
Thanks.

Thursday, April 2, 2020 - 12:54:25 PM - Koen Verbeeck Back To Top (85257)

Hi,

you can try this link:
https://github.com/microsoft/powerbi-rest-api-specs/blob/master/swagger.json

Regards,
Koen

Thursday, April 2, 2020 - 8:36:26 AM - Kaviprakash Back To Top (85253)

Hi,

The swagger file tis missing in github. Could you please pass the swagger file ?

Thanks,

Kaviprakash S

Tuesday, October 22, 2019 - 7:27:16 AM - Koen Verbeeck Back To Top (82855)

Hi Jake,

yes, this is article is for a large part obsolete now, thanks to the fast-moving cloud :)
However, although the main goal was to refresh a data set through Logic Apps which is now natively available, there are still dozens of API tasks that are not present natively in Logic Apps. If you follow the article to create your custom connector, you can still use Logic Apps to do all these other tasks.

Regards,
Koen

Monday, October 21, 2019 - 8:44:23 PM - Jake Milford Back To Top (82850)

Hi Koen,

I'm guessing this could be simplified now that you can refresh a dataset using MS Flow...   https://powerbi.microsoft.com/en-us/blog/refresh-your-power-bi-dataset-using-microsoft-flow/

Thursday, May 2, 2019 - 7:32:36 AM - Greg Robidoux Back To Top (79905)

Hi Bill,

here is part 2 - https://www.mssqltips.com/sqlservertip/5979/refresh-power-bi-dataset-using-azure-logic-apps--part-2/

-Greg

Thursday, May 2, 2019 - 12:06:42 AM - Bill Back To Top (79900)

Great part 1 to refreshing power bi dataset from data factory - thanks for your efforts here! But you've left me hanging! I really need to know how to complete the next steps - when can we expect part 2?

Thanks so much

Bill














get free sql tips
agree to terms