Automated Refresh of Power BI Dataset using Powershell

[Anonymous]

I hate Powershell. I hate it a lot. Every time I try to follow a tutorial online I get sucked down an error rabbit hole. Powershell makes me want to punch stuff.

But it seems like Powershell is a good way to trigger automated refreshes of a dataset in Power BI Premium, which is what I need to do. I'd much rather be able to do this by hitting the XMLA endpoint from my on-premise SSIS or SQL Agent, but that seems like a bigger pipe dream.

Is there a set of step by step instructions for setting up automated refresh of a Power BI dataset in Powershell for people who hate Powershell?  It seems like all the articles I've found assume a level of familiarity with Powershell that I don't know where to get.  I've been Googling this for days, and I think you can sense my level of frustration.

Thanks, I hope!

[v-yingjl · Accepted solution]

Hi @Anonymous ,

About using Powershell to refresh dataset, I think this blog could help you better to use it:

Using-the-power-bi-api-with-powershell-scripts-refreshing-your-dataset 

From SQL Agent to refresh, you can refer this blog:

USING SQL SERVER AGENT & POWERSHELL TO REFRESH POWER BI DATASET WITH REST API 

In addition, you can try PowerAutomate to trigger dataset refresh as @KBO  mentioned if you really hates Powershell and do not want to use it any more. You can follow this viedo to try: Power BI - Refresh Dataset by Button with Power Automate 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[KBO]

Hi @Anonymous ,

you hate Power Shell? I understand 😄 try instead Power Automate -> https://data-marc.com/2020/01/13/manage-all-your-power-bi-refreshes-in-one-place-with-power-automate-flow/#:~:text=Power%20Automate%2C%20to%20trigger%20the,the%20Power%20BI%20REST%20API.

Thats much easier and make me happy every time it works :).

Best,

Kathrin

If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!

[Anonymous]

@KBO - Thanks for the info... I should have added, I'd rather do this for no additional charge. $60,000/year for a premium license seems like it should let me do an automated refresh.

@v-yingjl Thanks very much!  I followed the tutorials and then went on a bit of an expedition to figure out how to use a service principal to automate my refresh perfectly. I used a client secret instead of a certificate to authenticate it.  I ended up with this script that I just pasted in SQL Agent.  Works great!!!  I don't hate PowerShell anymore!!!  Hooray!!

###########################################
###########################################
# This script uses the credentials of the [PBI Service Account] service principal in Azure Active Directory to execute dataset refresh
# MUST INSTALL THIS FIRST: Azure PowerShell cmdlets (https://aka.ms/webpi-azps)
# MUST RUN THIS ONE TIME TO INSTALL: "Install-Module -Name MicrosoftPowerBIMgmt" (https://www.powershellgallery.com/packages/MicrosoftPowerBIMgmt/1.0.867)
#
# Reference: https://alphabold.com/using-sql-server-agent-powershell-to-refresh-power-bi-dataset-with-rest-api/
# Reference: https://www.fourmoo.com/2018/06/05/using-the-power-bi-api-with-powershell-scripts-refreshing-your-dataset/
# Sample Script https://github.com/Azure-Samples/powerbi-powershell/blob/master/manageRefresh.ps1
###########################################
###########################################


#Supply the credentials - This is found/configured in "Azure AD > App Registrations > Certificates & Secrets"
$ApplicationClientID = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
$ClientSecret = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" | ConvertTo-SecureString -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $ApplicationClientID, $ClientSecret


#Enter the tenant ID - This is found in "Tenant Information" section of Azure AD main webpage
$TenantID = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"


#Establish connection to Power BI Service
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $Credential -TenantId $TenantID


#Execute dataset refresh, using GUIDs for the workspace (group) and dataset
Invoke-PowerBIRestMethod -Url 'groups/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/datasets/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/refreshes' -Method Post