Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Automated Refresh of Power BI Dataset using Powershell

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!

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

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
MVP

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
Not applicable

@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

 

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors