![\"Power](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/power-bi-logo-310x310-150x150.png\"){kind=logo}
In this article, you will learn how to refresh a\u00a0Power BI<\/a>\u00a0dataset with\u00a0Power BI REST API<\/a>\u00a0using SSIS and\u00a0ZappySys SSIS PowerPack<\/a>.\u00a0Power 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<\/em> 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.<\/p>\nHere are a few use cases when you may want to refresh a dataset from a SSIS package, using Power BI REST API:<\/p>\n
- \n
- The need to refresh a dataset more often than Power BI allows.<\/li>\n
- The requirement to refresh a dataset just after some process is finished; e.g. after an ETL process.<\/li>\n
- The ability to have absolute control when<\/em> to refresh multiple<\/em> 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.<\/li>\n<\/ul>\n
We will use these ZappySys SSIS PowerPack connectors\/connections in this article:<\/p>\n
\r\n![\"Web](\"\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/oauth-connection\/ssis-oauth-connection-manager.png\")
<\/div>\r\nOAuth Connection Manager<\/a><\/div>\r\n<\/div><\/div>\n\r\n![\"Web](\"\/\/zappysys.com\/images\/SSIS-PowerPack\/ssis-rest-api-web-service-task.png\")
<\/div>\r\nREST API Task<\/a><\/div>\r\n<\/div><\/div>\nLet’s begin!<\/p>\n
Prerequisites<\/h2>\n
- \n
- You have a Power BI account and you have basic Power BI skills.<\/li>\n
- An uploaded Power BI report to Power BI service.<\/li>\n
- A\u00a0Microsoft Azure<\/a>\u00a0subscription (there is a\u00a0free<\/a> option with a 200 USD credit).<\/li>\n
- Azure Active Directory<\/a> set up.<\/li>\n
- SSIS<\/abbr>\u00a0designer installed.\u00a0Sometimes it is referred to as\u00a0SSDT<\/abbr>\u00a0or BIDS<\/abbr>\u00a0(download it from the Microsoft site<\/a>).<\/li>\n
- Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
- ZappySys SSIS PowerPack<\/a>\u00a0is installed (download it<\/a>).<\/li>\n<\/ol>\n
Getting started<\/h2>\n
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:<\/p>\n
![\"Preparing](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/refresh-power-bi-dataset-using-rest-api-and-ssis-e1558968134828.png\")
Preparing to refresh the Power BI dataset using SSIS and Power BI REST API.<\/p><\/div>\n
To start, create an SSIS package and follow the steps below to accomplish the goal.<\/p>\n
Step By Step\u00a0– How to refresh a Power BI dataset with REST API using SSIS<\/h2>\n
Create an OAuth application<\/h3>\n
Create an Azure Active Directory OAuth application<\/h4>\r\n
- \r\n \t
- Firstly, go to Azure Active Directory, click App Registrations<\/a>, and then register a new application:\r\n
![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/20-register-azure-active-directory-application-to-use-in-ssis-or-odbc-wider.png\")
Select option Web<\/em> and enter this URL into the\u00a0Redirect URI<\/em> field:\r\nhttps:\/\/login.microsoftonline.com\/common\/oauth2\/nativeclient<\/code><\/li>\r\n \t- Once created, copy\/paste the Application (client) ID<\/em>\u00a0into a notepad, we will need it later:\r\n
![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/21-save-client-id-of-azure-active-directory-application-to-use-from-ssis-or-odbc-e1696948772287.png\")
<\/li>\r\n \t- Next, go to Certificates & secrets<\/em> and create a new client secret. Copy a new client secret, we will need it later:\r\n
![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/40-create-a-new-client-secret-in-azure-active-directory-oauth-application-to-use-from-ssis-or-odbc-e1696949525459.png\")
<\/li>\r\n \t- Then go to Overview\u00a0<\/i>and hit Endpoints.\u00a0<\/em>Proceed by copying the\u00a0Authorization<\/em> and Token<\/em> URLs and paste them into a notepad; we will use them later:\r\n
![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/90-copy-azure-active-directory-oauth-application-authorization-and-token-endpoints-2.png\")
<\/li>\r\n<\/ol><\/div>\nCreate OAuth Connection Manager in SSIS package<\/h3>\n
- \n
- Drag and drop REST API Task<\/em> from SSIS Toolbox, double click, select Url from connection,<\/strong> and then select <New ZS-OAUTH Connection><\/strong> item:\n
![\"Creating](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/110-create-ssis-oauth-connection-for-powerbi-rest-api-application-1-e1559316589109.png\")
Creating OAuth Connection Manager to connect to Power BI OAuth Application from SSIS.<\/p><\/div><\/li>\n
- Input Client ID<\/strong> from Step 2<\/em> when we were creating an Azure Active Directory OAuth Application.\n
<\/div>\n![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/120-configure-ssis-oauth-connection-manager-for-powerbi-rest-api-application-1.png\")
Configuring OAuth Connection Manager to authenticate to Power BI OAuth Application using SSIS.<\/p><\/div><\/li>\n
- Then proceed and input the\u00a0Client Secret<\/em> from Step 3, <\/em>Create an Azure Active Directory OAuth application<\/em> section.<\/li>\n
- Please go on, and fill in Authorization\u00a0Url <\/em>and Access Token Url <\/em>properties with values we copied in Step 4<\/em>, <\/em>Create an Azure Active Directory OAuth application<\/em> section.<\/li>\n
- In Scopes \/ Permissions<\/em>\u00a0field input these scopes:
\nhttps:\/\/analysis.windows.net\/powerbi\/api\/Dataset.ReadWrite.All
\noffline_access<\/code><\/li>\n- Then go to the\u00a0Advanced<\/em> tab and in the\u00a0Callback\/Return Url<\/em>\u00a0field input this value:
\nhttps:\/\/login.microsoftonline.com\/common\/oauth2\/nativeclient<\/code><\/p>\n![\"\"](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/130-configure-redirect-url-for-ssis-oauth-connection-manager-for-powerbi-rest-api-application-1.png\")
Setting Callback\/Return Url (Redirect URI) in OAuth Connection Manager to authenticate to Power BI OAuth Application using SSIS.<\/p><\/div><\/li>\n
- Then go back to the tab General <\/em>and click the\u00a0Generate Token<\/em> button.<\/li>\n
- Once asked for credentials, log in with your organization user; this will generate Refresh<\/em> and Access<\/em> tokens.<\/li>\n<\/ol>\n
Retrieve the ID of the Power BI dataset you want to refresh<\/h3>\n
- \n
- Go to Power BI datasets<\/a>.<\/li>\n
- Then click on Datasets<\/strong>.<\/li>\n
- Proceed and click the dataset you want to refresh.<\/li>\n
- Finally, copy the ID of the dataset from the URL, we will use it in the next section.<\/li>\n<\/ol>\n
![\"Getting](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/140-get-the-id-of-the-dataset-in-power-bi-to-refresh-it-using-rest-api-and-ssis-e1559663902796.png\")
Getting Power BI dataset ID to refresh it in an HTTP request to Power BI REST API.<\/p><\/div>\n
Configure REST API Task to refresh a Power BI dataset<\/h3>\n
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:<\/p>\n
- \n
- Go to the\u00a0SSIS package<\/strong> and open the\u00a0REST API Task<\/strong> for configuration.<\/li>\n
- Input URL<\/em> in such a format:
\nhttps:\/\/api.powerbi.com\/v1.0\/myorg\/datasets\/DATASET-ID\/refreshes<\/strong><\/code> Replace DATASET-ID<\/em>\u00a0with an actual ID of the dataset.\u00a0E.g.:
\nhttps:\/\/api.powerbi.com\/v1.0\/myorg\/datasets\/a38c78fc-8c87-45e3-b961-6dcd3ac1eada\/refreshes<\/code>NOTE<\/strong>: If your dataset resides in a Workspace, use this format instead:
\nhttps:\/\/api.powerbi.com\/v1.0\/WORKSPACE-ID\/datasets\/DATASET-ID\/refreshes<\/strong><\/code><\/div><\/div><\/li>\n- Then set POST<\/strong> as HTTP Request Method.<\/em><\/li>\n
- In the\u00a0Body<\/em> field, input this JSON:
\n{ \"notifyOption\": \"MailOnFailure\" }<\/strong><\/code><\/li>\n- Press the\u00a0Test Request\/Response<\/em> button to test the request and refresh the dataset at the same time.<\/li>\n<\/ol>\n
![\"Configuring](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/150-configure-REST-API-Task-to-refresh-power-bi-dataset-using-rest-api-and-ssis.png\")
Configuring a REST API Task to refresh a Power BI dataset using Power BI REST API and SSIS.<\/p><\/div>\n
Run the package<\/h3>\n
Hit F5<\/strong> and execute the package:<\/p>\n
![\"Executing](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/160-run-SSIS-package-to-refresh-power-bi-dataset-rest-api.png\")
Executing SSIS package and refreshing Power BI dataset.<\/p><\/div>\n
The Results<\/h2>\n
Now it’s time to check the results and see if the dataset was refreshed successfully:<\/p>\n
- \n
- Again, go to Power BI datasets<\/a>.<\/li>\n
- Then click on Datasets<\/strong>.<\/li>\n
- Click the dataset you refreshed.<\/li>\n
- Then click “Refresh history” to check the refresh history. We expect two manual refreshes to be there.<\/li>\n<\/ol>\n
![\"Results:](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/05\/170-checking-the-result-of-refreshing-power-bi-dataset-using-ssis-and-rest-api.png\")
Results: Successful Power BI dataset refresh using Power BI REST API and SSIS.<\/p><\/div>\n
Debugging HTTP requests<\/h2>\r\n
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.\u00a0<\/span>To test how things look behind the scenes we strongly suggest to use Fiddler<\/a>\u00a0- a popular web debugging tool.<\/p>\r\n
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):<\/p>\r\n\r\n
\r\n\r\n![\"Debugging](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/05\/ssis-rest-api-call-debug-via-fiddler.png\")
<\/a>\r\n\r\n<\/div><\/div>\nDeployment to Production<\/span><\/h2>\n
In SSIS package sensitive data such as tokens and passwords are by default encrypted by SSIS<\/a> with your Windows account which you use to create a package.\u00a0So 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<\/a>\u00a0or an HTTP Connection Manager<\/a> with credentials, etc.), consider using parameters\/variables to pass tokens\/passwords. In this way, you won\u2019t face authentication related errors when a package is deployed to a production server.<\/p>\r\n
Check our article on how to configure packages with sensitive data on your production or development server<\/a>.<\/p><\/div>\n
Conclusion<\/h2>\n
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”<\/em> method and successfully refreshed the dataset. If you need to make a call to another Power BI REST API<\/a>\u00a0method, then just configure the REST API Task according to the documentation (for getting data use JSON Source<\/a>\u00a0or Web API Destination<\/a> instead). Finally, if you make many requests to the Power BI REST API service, make sure you check its limitations<\/a>.<\/p>\n
References<\/h2>\n
https:\/\/powerbi.microsoft.com\/en-us\/<\/a><\/p>\n
https:\/\/docs.microsoft.com\/en-us\/rest\/api\/power-bi\/datasets\/refreshdataset<\/a><\/p>\n
https:\/\/docs.microsoft.com\/en-us\/rest\/api\/power-bi\/<\/a><\/p>\n
- Then click on Datasets<\/strong>.<\/li>\n
- Input URL<\/em> in such a format:
- Then click on Datasets<\/strong>.<\/li>\n
- Input Client ID<\/strong> from Step 2<\/em> when we were creating an Azure Active Directory OAuth Application.\n
- Once created, copy\/paste the Application (client) ID<\/em>\u00a0into a notepad, we will need it later:\r\n
- Azure Active Directory<\/a> set up.<\/li>\n