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.

Power BI REST API | Test Dataset

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

Register an application

  • Copy the Application ID and save it for later use

Application ID

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.

App Workspace

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

new temp url

  • 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”

Dataset ID

  • Under the Datasets section, click on your dataset

Datasets section

  • Now, take a look at the URL again

url change

  • 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

PowerShell ISE

  • 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

SQL Server Agent

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

General tab

 

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

Type and Command

  • 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.

Configure a Trigger

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.

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.

Refresh History

  • Status changes to Completed upon a successful refresh.

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

3 thoughts on “Using SQL Server Agent & PowerShell to Refresh Power BI Dataset with REST API”

  1. Thanks for the script. I added the three variables but I get this error. Any idea?

    Import-Module : The specified module ‘AzureRm.Profile’ was not loaded because no valid module file was found in any module directory.
    At line:39 char:10
    + Import-Module AzureRm.Profile
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ResourceUnavailable: (AzureRm.Profile:String) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

    New-Object : Cannot find type [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]: verify that the assembly containing this type is loaded.
    At line:48 char:23
    + … thContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirecto …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

    You cannot call a method on a null-valued expression.
    At line:50 char:8
    + $authResult = $authContext.AcquireToken($resourceAppIdURI, $cl …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    You cannot call a method on a null-valued expression.
    At line:59 char:1
    + $authHeader = @{
    + ~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    VERBOSE: POST https://api.powerbi.com/v1.0/myorg/datasets/127066e1-e1dd-4588-aa90-2b8756412c3e/refreshes with 0-byte payload
    Invoke-RestMethod : The remote server returned an error: (403) Forbidden.
    At line:74 char:1
    + Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST –Verbos …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

    VERBOSE: GET https://api.powerbi.com/v1.0/myorg/datasets/127066e1-e1dd-4588-aa90-2b8756412c3e/refreshes with 0-byte payload
    Invoke-RestMethod : The remote server returned an error: (403) Forbidden.
    At line:78 char:1
    + Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET –Verbose
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

  2. Hi dear,

    Thank you for the explanation,

    I do not think this is applicable in Azure enviroment 🙁 I cannot find SQL Agent.

    Thanks,
    Juli

  3. Hi Juli,

    There could be two possibilities:

    1) If you’re using the SQL Server Expression edition, you won’t have the functionality to use SQL Agent. You could do that using Developer or Enterprise edition.

    2) Make sure your user is a member of a role that has permission to see/use SQL Agent.

    Regards,
    BOLDEnthusiasts

Comments are closed.