Forum Discussion
Automated Refresh of Power BI Dataset using Powershell
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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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!
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