cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: ELI5: Automated Refresh of Power BI Dataset using Powershell

Hi @Sorphicle ,

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
Highlighted
Memorable Member
Memorable Member

Re: ELI5: Automated Refresh of Power BI Dataset using Powershell

Hi @Sorphicle ,

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...

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!

Highlighted
Community Support
Community Support

Re: ELI5: Automated Refresh of Power BI Dataset using Powershell

Hi @Sorphicle ,

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

Highlighted
Helper I
Helper I

Re: ELI5: Automated Refresh of Power BI Dataset using Powershell

@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
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors