Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Refresh Power BI Dataset from Azure Data Factory - Part 1


By:   |   Last Updated: 2019-04-22   |   Comments (2)   |   Related Tips: More > 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


Last Updated: 2019-04-22


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, May 02, 2019 - 7:32:36 AM - Greg Robidoux Back To Top

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 02, 2019 - 12:06:42 AM - Bill Back To Top

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


Learn more about SQL Server tools