Azure Power BI Automation - DataSets

by Madan Thota

Azure Power BI Automation - DataSets

This is the fifth post in KPI's Power BI blog series. In this blog, we provide a high-level overview of dataset refresh and other dataset operations from PowerShell. You can read the other posts in the series here:

1. Visual Interactivity in a Power BI Report
2. Dynamic Selection of Measures in Power BI
3. Security and Collaboration in Power BI
4. What is PowerShell and Connect to Power BI Service

Dataset refresh and other dataset operations from PowerShell

Article 4 covered the basics, about what is powershell, how to install Power BI cmdlets, and connect to Power BI service using powershell. This article more focused on Dataset operations and other use cases. The prerequisite for this article is you already have a dataset in your workspace.

One of the important tasks of Power BI Admins is to make sure dataset refresh is done, lets cover automate this through powershell.

Copy below code to any text editor and save the file as DataSetRefreshTest.ps1

#--------------- Establish connectivity Power BI service

$User = "username@yourcompany.com"

$PW = "yyyyy"

$SecPasswd = ConvertTo-SecureString $PW -AsPlainText -Force

$myCred = New-Object System.Management.Automation.PSCredential($User,$SecPasswd)

Connect-PowerBIServiceAccount -Credential $myCred

#--------------

Update Power BI credentials in the first two lines of PowerShell Script:

The powershell uses credentials for connecting Power BI cloud. This we are testing with Personal creds if you have any service principal created for CLI jobs you can use that.

Now let's gather information about dataset id, Every Power BI reports internally uses Dataset which will maintain a unique id.   If you login to Power BI service, one of the ways to see identify the id is through URL.

Login into Power BI Service, Navigate to the workspace, Go through your datasets, and open Settings of your dataset.

Now take a look at URL in the browser , you do see something like below.

https://app.powerbi.com/groups/ddfd2c56-5369-23c5-bb02-7a4a2c83b792/settings/datasets/7f64ed0b-d11b-4f85-a081-00d037ceb5e5?ctid=5ae1af62-9505-4097-a69a-c1553ef7840e

The unique ID generated for this dataset by Power BI service is after datasets/ in the URL, Copy the ID of the dataset, and update and add below code your script with your dataset id.  

-----------------------------

$headers = Get-PowerBIAccessToken

$datasetID = "7f64ed0b-d11b-4f85-a081-00d037ceb5e5" # the ID of the dataset that hosts the dataset

#dataset Refresh code

$uri = "https://api.powerbi.com/v1.0/myorg/datasets/$datasetID/refreshes"

$success = Invoke-RestMethod -Headers $headers -Uri $uri -Method POST

Write-Host "Dataset Refresh Submitted"

-----------------------------

In the above code, we are calling PowerBI REST API to trigger dataset refresh.   REST API URL needs credentials to be pass as part of the Headers and we are using Get-PowerBIAccessToken to get the header information.

Save the File and Run the script from PowerShell.  This will trigger the connection to Power BI service and log in with credentials

.\ DataSetRefreshTest.ps1

By executing this, you are opening a session and a successful connection provides session details like below.

Now you can log in to Power BI service, can check the refresh has submitted or not. It will show up as an on-demand refresh.

Other use cases you can do with data sets using Cmdlets:

1. List out datasets in the workspace for list out and run the operations in order with for loop in PowerShell script.

             Sample Code:   Get-PowerBIDataset -Workspace workspace name

2. Refresh PowerBI dataset after ETL(Data) load is done:   Many ETL tools allow us to call additional shell scripts/batch scripts as a post-load task: PowerShell executable is available in Windows, macOS and Unix environments. 

Sudo logic for this process when you have an ETL tool to call PowerShell :

Sudo logic for this process when Powershell to connect your database and verify ETL load is done:

3. Change the Dataset storage mode to Premium.  

Many other dataset operations can be done with Powershell, Power BI Cmdlets, and REST API combination. I will try to cover more in the next articles of this series. 

Check out our case studies to see how we've helped our clients:
Masterlock
Williams Sonama

Do you enjoy the solving technology problems and helping people meet their data analytics challenges? Maybe you would be a good fit for our team. See our job openings.

Madan Thota is a Director of Technology at KPI Partners and leads project delivery of  Big Data, Cloud and Analytics applications.  His professional focus is in the world of pre sales, enterprise decision support, business intelligence, and data architecture. Check out Madan's blog at KPIPartners.com.