Using SQL Server Agent & PowerShell to Refresh Power BI Dataset with REST API

Let’s suppose that you have a data warehouse and you’ve built a report based on that data. Let’s say you want your report to be refreshed only when new records are inserted in your data model (ETL) and you don’t want the dataset to be refreshed constantly. This blog will show you how we can do that.

There are two main tasks to execute this process successfully.

Task 1: Creating PowerShell script for Dataset Refresh

Power BI REST API lets you utilize many powerful functions. You can work on Admin functionalities, manage gateways, dataflows, datasets, and even user permissions. Microsoft has documented Power BI Rest API extensively over here. If you don’t know what a REST API is or what it does, I strongly suggest you read some of the documentation.

Find out more about AlphaBOLD's BI Services

Learn More

Let’s get started on the task at hand. We’ll be refreshing a dataset on Power BI Service using Power BI REST API and PowerShell script. I’ll be using “Test Dataset” for demonstration purposes.

First, download a sample PowerShell script from here. It is a sample script with all the goodies inside and it only needs to be updated on the credentials. Once we have all the credentials to authenticate to Power BI, we’ll be able to run this script successfully. Perform the following steps to get credentials.

Register an application

You have to register for an Application ID (Client ID).

• Go to https://dev.powerbi.com/apps
• Sign in using your Power BI account
• Fill in the necessary text boxes and choose “Read and write all datasets”
• Click Register

• Copy the Application ID and save it for later use

Group ID

A group is now called an App Workspace in Power BI. To get the group ID, perform these steps:

• Go to the workspace your dataset resides in
• In the current demonstration, I have my dataset in the “New Temp” workspace.

• As soon as you click on that workspace, take a look at the URL
• URL looks something like this

• Copy the group ID as shown in the textbox above
• Save it for later use

 

Dataset ID

To get Dataset ID, perform these steps:

• Click on the Settings icon and select “Settings”

• Under the Datasets section, click on your dataset

• Now, take a look at the URL again

• Copy the ID as shown in the text box above and save it for later use

 

Explore Our Business Intelligence Services

Learn More

Update the PowerShell Script

Now, we need to update the script using all the credentials we have saved so far.

• Open the script in PowerShell ISE
• Fill in the Group ID, Dataset ID and Application ID (client ID) fields

• Save the script

 

Task 2: Creating SQL Server Job to link with PowerShell Script

Now, we need to create a Job in SQL Server Management Studio. Here’s how we can do that.

Note: SQL Server Express Edition does not support SQL Server Agent. However, all the other editions including Developer, Enterprise, etc. have functional SQL Server Agent.

• Open the SQL Server Management Studio
• Connect to the server instance and navigate to “SQL Server Agent” under the instance name
• Right click on SQL Server Agent-> New -> Job

• A new window opens. In the General tab, specify the Name, Owner, Category and Description (optional). After specifying the fields, click OK.

 

• Go to Steps tab, select Specify Step name, Type, and Command as shown. Click OK to close the window.

• You can schedule this job in the Schedule tab but we’ll skip it in this case because we’ll only be refreshing the dataset whenever data is inserted in our tables. So, no need for it.

 

Configure a Trigger for Scheduling the PowerShell Script

Now, we need a SQL trigger so that we can link the job with PowerShell script.

• You can use this piece of SQL code for such a trigger. This trigger monitors the column “SalesAmountQuota” of the table “FactSalesQuota.”
• “EXEC dbo.sp_start_job (Job Name)” calls a stored procedure ‘sp_start_job’ to immediately run the specified job.

As soon as you run it, it will start monitoring the specific column. After insertion, it will trigger the associated script to perform a refresh for us.

 

Update the table and check Refresh History

• As a test, insert a record into the table.
• As soon as the new record arrives, the trigger fires, executing the PowerShell script. A pop-up asks to choose the Power BI account.

• After doing so, go to Power BI service. Navigate to the Dataset and check for Refresh History. Status shows that the refresh is in progress.

• Status changes to Completed upon a successful refresh.

 

There you have it! No more constant refreshes! Your dataset will only refresh when new records are inserted in your data warehouse! I hope that this helps you will your PowerShell endeavors. If you have any questions or insights, please leave a comment below! To get in touch with our BOLDEnthusiasts, click here!

References

• https://blog.netwrix.com/2018/07/03/how-to-automate-PowerShell-scripts-with-task-scheduler/
• https://github.com/Azure-Samples/powerbi-PowerShell/blob/master/manageRefresh.ps1
• https://www.fourmoo.com/2018/06/05/using-the-power-bi-api-with-PowerShell-scripts-refreshing-your-dataset/
• https://www.sqlservercentral.com/articles/scheduling-a-PowerShell-script-using-sql-server-agent