Search code examples
sql-serverpowershellpowerbisql-agent

How to start Power BI Dataset refresh with SQL Agent Job

In a certain requirement I have to start Power BI Dataset refresh only after SQL Agent job finished. My plan is to add a step in the Agent Job so that it can call the Power BI Data refresh. Any one has any idea how to achieve that.

Thanks in advance.

Solution

  • One step to do this is with with PowerShell. Install Microsoft Power BI Management CmdLets and add a PowerShell step to execute the following script:

    Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile

$password = "xxxxx" | ConvertTo-SecureString -asPlainText -Force
$username = "[email protected]" 
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential

Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/refreshes' -Method Post

Disconnect-PowerBIServiceAccount

    It will call the Refresh Dataset In Group REST API to initiate a dataset refresh.

    Replace the x-es with the group/workspace ID and actual dataset ID. Also provide valid credentials. This is the simplest way, but you can also make it work with service principal for example.