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

Picture of alphabold

alphabold

Introduction

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.

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.

Infographic show the Power BI Dataset REST API

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
Infographic show the Power BI Dataset Register an application
  • Copy the Application ID and save it for later use
Infographic show the Application ID - Power BI Dataset

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.
Infographic show the App Workspace - Power BI Dataset
  • As soon as you click on that workspace, take a look at the URL
  • URL looks something like this
Infographic show the new temp of URL -Power BI Dataset
  • 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”
Infographic show the Dataset ID - Power BI Dataset
  • Under the Datasets section, click on your dataset
Infographic show the Datasets section - Power BI Dataset
  • Now, take a look at the URL again
Infographic show the code of url change - Power BI Dataset
  • Copy the ID as shown in the text box above and save it for later use

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

Read More about SQL Server Integration Services Azure Data Factory VS SSIS

Infographic show the PowerShell ISE
  • Save the script

Optimize Your Power BI Workflow with AlphaBOLD!

Looking to enhance your Power BI refresh strategy? Leverage AlphaBOLD's Power Platform services for SQL Server Agent and PowerShell integration. Empower your dataset management.

Request a Demo

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
Infographic show the SQL Server Agent - Power BI Dataset
  • A new window opens. In the General tab, specify the Name, Owner, Category and Description (optional). After specifying the fields, click OK.
Infographic show the General tab
  • Go to Steps tab, select Specify Step name, Type, and Command as shown. Click OK to close the window.
Infographic show the steps of tab 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.
Infographic show the 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.
Infographic show the Power BI Dataset 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.
Infographic show the Refresh History - Power BI Dataset
  • Status changes to Completed upon a successful refresh.

Read More about Power BI Support: Microsoft’s Support for Power BI: What Can you Except?

Infographic show the successful refresh - Power BI Dataset

Revolutionize Power BI Dataset Management!

Implementing SQL Server Agent and PowerShell for Power BI dataset refresh? Trust AlphaBOLD's Power Platform Solution for a smooth setup and enhanced automation. Unlock the full potential of your dataset management.

Request a Demo

Conclusion

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

Explore Recent Blog Posts

Infographics show the 2021 MSUS Partner Award winner

Related Posts

Receive Updates on Youtube

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.