How to refresh Power BI dataset with REST API using SSIS

Posted on by

Introduction

Power BI IntegrationIn this article, you will learn how to refresh Power BI dataset with REST API using SSIS and ZappySys SSIS PowerPack. Power BI is a Microsoft reporting product that can get data from virtually any source and display it nicely in a report or a dashboard. Each Power BI report contains a dataset, which can be refreshed to show relevant data in the report. Power BI also has a Scheduled refresh capability, in case you want to refresh a dataset periodically. However, you are limited to only 8 specific times per day you can refresh a dataset (as of May 23, 2019). In Power BI Premium license you can do it 48 times per day. But what if you need to refresh it more frequently? Or if you desire to refresh it at a non-predefined time; which is not known in advance? E.g. after an ETL process has finished? Here Power BI REST API comes into rescue and lets you refresh a dataset on demand.

Here are a few use cases when you may want to refresh a dataset from SSIS package, using Power BI REST API:

  • The need to refresh a dataset more often than Power BI allows.
  • The requirement to refresh a dataset just after some process is finished; e.g. after an ETL process.
  • The ability to have absolute control when to refresh multiple datasets. Sometimes, refreshing multiple huge datasets at once could cause memory problems in Power BI service. So, having an orchestrated refresh would solve this problem.

We will use these ZappySys SSIS PowerPack connectors/connections in this article:

Let’s begin!

Prerequisites

  1. You have a Power BI account and you have basic Power BI skills.
  2. A Power BI report uploaded to Power BI service.
  3. A Microsoft Azure subscription (there is a free option with 200 USD credit).
  4. Azure Active Directory set up.
  5. SSIS designer installed. Sometimes it is referred to as SSDT or BIDS (download it from Microsoft site).
  6. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  7. ZappySys SSIS PowerPack is installed (download it).

Getting started

Suppose you have a simple report and you want to refresh its dataset using Power BI REST API and SSIS. So far, the dataset was not refreshed at all:

Preparing to refresh Power BI dataset using SSIS and Power BI REST API.

Preparing to refresh Power BI dataset using SSIS and Power BI REST API.

To start, create an SSIS package and follow the steps below to accomplish the goal.

Step By Step – How to refresh Power BI dataset with REST API using SSIS

Create an OAuth application

Create an Azure Active Directory OAuth application

  1. Firstly, go to Azure Active Directory, click App Registrations, and then register a new application:
  2. Once created, copy/paste application client ID to a notepad, we will need it later:
  3. Afterwards, go to the application configuration and select the Authentication option. Then check the checkbox below:
  4. Next, go to Certificates & secrets and create a new client secret. Copy a new client secret, we will need it later. 
  5. Then go to Quickstart and hit View endpoints:
  6. Finally, copy the v1 Authorization and Token URLs and paste it in a notepad, we will use them later:

Add Power BI permissions to newly created Azure Active Directory app

  1. In the same application configuration page go to API permissions and click Add a permission:
  2. Choose Power BI Service:
  3. ​Select Delegated Permissions and then all permissions (or just Dataset): ​

Grant admin consent for your permissions for your domain

Go to API permissions again, and press Grant admin consent for <domain> button:

Create OAuth Connection Manager in SSIS package

  1. Drag and drop REST API Task from SSIS Toolbox, double click, select Url from connection and then select <New ZS-OAUTH Connection> item:
    Creating OAuth Connection Manager to connect to Power BI OAuth Application from SSIS.

    Creating OAuth Connection Manager to connect to Power BI OAuth Application from SSIS.

  2. Input Client Id from Step 2 when we were creating Azure Active Directory OAuth Application.
    Configuring OAuth Connection Manager to authenticate to Power BI OAuth Application using SSIS.

    Configuring OAuth Connection Manager to authenticate to Power BI OAuth Application using SSIS.

  3. Then proceed and input Client Secret from Step 4 (Create an Azure Active Directory OAuth application section).
  4. Go on, and fill in Authorization Url and Access Token Url properties with values we copied in Step 6 (Create an Azure Active Directory OAuth application section).
  5. In Scopes / Permissions field input “openid“.
  6. Then go to Advanced tab and in Callback/Return Url field input “https://login.live.com/oauth20_desktop.srf“:
    Setting Callback URL in OAuth Connection Manager to authenticate to Power BI OAuth Application using SSIS.

    Setting Callback URL in OAuth Connection Manager to authenticate to Power BI OAuth Application using SSIS.

  7. In Extra Attributes for /token Request field input “resource=https://analysis.windows.net/powerbi/api“.
  8. Then go back to tab General and click Generate Token button.
  9. Once asked for credentials, log in with your organization user; this will generate Refresh and Access tokens.

Retrieve the Id of the Power BI dataset you want to refresh

  1. Go to Power BI datasets.
  2. Then click on Datasets.
  3. Proceed and click the dataset you want to refresh.
  4. Finally, copy the Id of the dataset from the URL, we will use it in the next section.
Getting Power BI dataset ID to refresh it in an HTTP request to Power BI REST API.

Getting Power BI dataset ID to refresh it in an HTTP request to Power BI REST API.

Configure REST API Task to refresh a Power BI dataset

We configured OAuth Connection Manager, retrieved the Id of the dataset we want to refresh, now it’s time to configure REST API Task and refresh the dataset. So do the following:

  1. Go to SSIS package and open REST API Task for configuration.
  2. Input URL in such a format: https://api.powerbi.com/v1.0/myorg/datasets/[dataset-id]/refreshes. Replace [dataset-id] with the actual Id of the dataset. E.g. https://api.powerbi.com/v1.0/myorg/datasets/a39c78fc-8c88-45e3-b961-6dcd3ac1eada/refreshes.
  3. Then set POST as HTTP Request Method.
  4. In the Body field, input this JSON:
    { "notifyOption": "MailOnFailure" }
  5. Press Test Request/Response button to test the request and refresh the dataset at the same time.
Configuring REST API Task to refresh Power BI dataset using Power BI REST API and SSIS.

Configuring REST API Task to refresh Power BI dataset using Power BI REST API and SSIS.

Run the package

Hit F5 and execute the package:

Executing SSIS package and refreshing Power BI dataset.

Executing SSIS package and refreshing Power BI dataset.

The Results

Now it’s time to check the results and see if report dataset was refreshed:

  1. Again, go to Power BI datasets.
  2. Then click on Datasets.
  3. Click the dataset you refreshed.
  4. Then click “Refresh history” to check refresh history. We expect two manual refreshes to be there.
Results: Successful Power BI dataset refresh using Power BI REST API and SSIS.

Results: Successful Power BI dataset refresh using Power BI REST API and SSIS.

Debugging HTTP requests

A common thing you have to do when working with HTTP requests is to debug those requests; e.g. to check what headers, body or URL was passed. To test how things look behind the scenes we strongly suggest to use Fiddler - a popular web debugging tool.

Inside it, you can double-click the URL entry (Right side) to see Request and Response Panels. The top panel is Request (URL, Headers, Body) and Bottom Panel is Response. For https:// (secure URL) make sure you enable HTTPS option in Fiddler (Tools > Options > HTTPS > Check Decrypt https request):

Debugging Web API call using Fiddler in SSIS

Deployment to Production

In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS with your Windows account which you use to create a package. So SSIS will fail to decrypt tokens/passwords when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an OAuth Connection Manager or an HTTP Connection Manager with credentials, etc.), consider using parameters/variables to pass tokens/passwords. In this way, you won’t face authentication related errors when a package is deployed to a production server.

Check our article on how to configure packages with sensitive data on your production or development server.

Conclusion

You learned how to refresh Power BI dataset with REST API using SSIS package. To accomplish the goal, firstly, we created the Azure Active Directory application and configured OAuth Connection Manager based on it. Then we retrieved Power BI dataset Id and used it in ZappySys SSIS PowerPack connector REST API Task. Finally, using the same REST API Task we made the HTTP request to Power BI REST API “refreshes” method and successfully refreshed the dataset. Therefore, if you need to make a call to another Power BI REST API method, you will be more confident in doing that, as you know how to call dataset refresh method already. Finally finally, if you make many and huge requests to Power BI REST API, make sure you check its limitations.

References

https://powerbi.microsoft.com/en-us/

https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/refreshdataset

https://docs.microsoft.com/en-us/rest/api/power-bi/

https://docs.microsoft.com/en-us/power-bi/developer/api-rest-api-limitations

Posted in REST API, REST API Integration, SSIS Connection Manager, SSIS OAuth Connection, SSIS PowerPack, SSIS REST API Task, SSIS Tasks and tagged , , .