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
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
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
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
Hi dear,
Thank you for the explanation,
I do not think this is applicable in Azure enviroment 🙁 I cannot find SQL Agent.
Thanks,
Juli
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