Introduction
In this article, you will learn how to refresh a Power BI dataset with Power BI 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 the 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 a 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 the 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
- You have a Power BI account and you have basic Power BI skills.
- An uploaded Power BI report to Power BI service.
- A Microsoft Azure subscription (there is a free option with a 200 USD credit).
- Azure Active Directory set up.
- SSIS designer installed. Sometimes it is referred to as SSDT or BIDS (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- 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 has not been refreshed at all:
To start, create an SSIS package and follow the steps below to accomplish the goal.
Step By Step – How to refresh a Power BI dataset with REST API using SSIS
Create an OAuth application
Create an Azure Active Directory OAuth application
- Firstly, go to Azure Active Directory, click App Registrations, and then register a new application:
Select option Web and enter this URL into the Redirect URI field:
https://login.microsoftonline.com/common/oauth2/nativeclient
- Once created, copy/paste the Application (client) ID into a notepad, we will need it later:
- Next, go to Certificates & secrets and create a new client secret. Copy a new client secret, we will need it later:
- Then go to Overview and hit Endpoints. Proceed by copying the Authorization and Token URLs and paste them into a notepad; we will use them later:
Create OAuth Connection Manager in SSIS package
- Drag and drop REST API Task from SSIS Toolbox, double click, select Url from connection, and then select <New ZS-OAUTH Connection> item:
- Input Client ID from Step 2 when we were creating an Azure Active Directory OAuth Application.
- Then proceed and input the Client Secret from Step 3, Create an Azure Active Directory OAuth application section.
- Please go on, and fill in Authorization Url and Access Token Url properties with values we copied in Step 4, Create an Azure Active Directory OAuth application section.
- In Scopes / Permissions field input these scopes:
https://analysis.windows.net/powerbi/api/Dataset.ReadWrite.All
offline_access - Then go to the Advanced tab and in the Callback/Return Url field input this value:
https://login.microsoftonline.com/common/oauth2/nativeclient
- Then go back to the tab General and click the Generate Token button.
- 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
- Go to Power BI datasets.
- Then click on Datasets.
- Proceed and click the dataset you want to refresh.
- Finally, copy the ID of the dataset from the URL, we will use it in the next section.
Configure REST API Task to refresh a Power BI dataset
We configured OAuth Connection Manager and 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:
- Go to the SSIS package and open the REST API Task for configuration.
- Input URL in such a format:
https://api.powerbi.com/v1.0/myorg/datasets/DATASET-ID/refreshes
Replace DATASET-ID with an actual ID of the dataset. E.g.:
https://api.powerbi.com/v1.0/myorg/datasets/a38c78fc-8c87-45e3-b961-6dcd3ac1eada/refreshes
NOTE: If your dataset resides in a Workspace, use this format instead:
https://api.powerbi.com/v1.0/WORKSPACE-ID/datasets/DATASET-ID/refreshes
- Then set POST as HTTP Request Method.
- In the Body field, input this JSON:
{ "notifyOption": "MailOnFailure" }
- Press the Test Request/Response button to test the request and refresh the dataset at the same time.
Run the package
Hit F5 and execute the package:
The Results
Now it’s time to check the results and see if the dataset was refreshed successfully:
- Again, go to Power BI datasets.
- Then click on Datasets.
- Click the dataset you refreshed.
- Then click “Refresh history” to check the refresh history. We expect two manual refreshes to be there.
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):
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 a Power BI dataset with REST API using SSIS. To accomplish the goal, firstly, we created the Azure Active Directory application and configured OAuth Connection Manager based on it. Then we retrieved the Power BI dataset ID and used it in the 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. If you need to make a call to another Power BI REST API method, then just configure the REST API Task according to the documentation (for getting data use JSON Source or Web API Destination instead). Finally, if you make many requests to the Power BI REST API service, 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