Powershell script to automatically refresh scorecards

[Anonymous]

Hi all,

We have run into a well-known issue with scoreboards/metrics that they do not refresh as expected (see: https://community.fabric.microsoft.com/t5/Service/Power-BI-metrics-refresh-but-not-updating-values-o... and https://community.fabric.microsoft.com/t5/Service/PowerBi-Scorecard-Metrics-not-automatically-updati... ). One would expect scorecards to update everytime their underlying reports refresh, but that is not the case. For us this has been a deal-breaker with using metrics.

We found https://community.fabric.microsoft.com/t5/Community-Blog/Power-BI-Metrics-Pro-Tip-Refresh-all-metric... which gave a great starting script. It however was still quite manual, and just 'moves' the problem of manual refreshing from metric owners over to the script owner. It also doesn't work in 2FA environments. So we had to iterate on this idea, and we came up with the following Powershell script:

######################################
#Install module if it isn't already installed
if (!(Get-Module -ListAvailable -Name MicrosoftPowerBIMgmt)) {
    try {
        Install-Module -Scope CurrentUser -Name MicrosoftPowerBIMgmt -AllowClobber -Confirm:$False -Force  
    } catch [Exception] {
        $_.message 
        exit
    }
}
######################################

Import-Module "C:\Program Files\WindowsPowerShell\Modules\MicrosoftPowerBIMgmt"
Import-Module "C:\Program Files\WindowsPowerShell\Modules\MicrosoftPowerBIMgmt.Profile"
Import-Module "C:\Program Files\WindowsPowerShell\Modules\MicrosoftPowerBIMgmt.Admin"

#Use one of the two options below
#Option 1 requires a pre-existing service account, and is a great way to get around manual log ins
#Option 2 is easier to setup and test, but likely requires you to manually go through 2FA or other additional login screens
##### LOGIN OPTION 1: SERVICE USER AND PASS#############
$PbiUser = "your-user@your-org.onmicrosoft.com"
$PbiPassword = "your-password-here"
$PbiSecurePassword = ConvertTo-SecureString $PbiPassword -Force -AsPlainText
$PbiCredential = New-Object Management.Automation.PSCredential($PbiUser, $PbiSecurePassword)
Connect-PowerBIServiceAccount -Credential $PbiCredential
$token = (Get-PowerBIAccessToken)["Authorization"]
########################################################

##### LOGIN OPTION 2: PERSONAL USER AND PASS ###########
Login-PowerBI
$token = (Get-PowerBIAccessToken)["Authorization"]
########################################################

#Variables 
$api = "https://api.powerbi.com"

function GetWorkspaceList() {
    #Query scorecards from given workspace accessible by the user
    Write-Host "Getting a list of workspaces accessibly by $PbiUser"
    $workspaceIdList = @()
    foreach ($ws in Get-PowerBIWorkspace -All) {
        $workspaceIdList += $($ws.id)
    }    
    return $workspaceIdList
}

function GetScorecardList($workspace) {
    #Query scorecards from given workspace
    $workspaceId = "769e9f68-d6f7-42cd-a50d-64894278c467"
    Write-Host "Retrieving scorecard list: " -NoNewLine
    $response = Invoke-WebRequest `
        -Uri "$api/v1.0/myorg/groups/$workspaceId/scorecards" `
        -Headers @{ "Authorization"=$token }
    Write-Host -ForegroundColor Green OK

    #Convert to JSON and extract scorecard values as a list
    $scorecard = $response.Content | ConvertFrom-Json   
    $scorecardIdList = @()
    foreach ($value in $scorecard.value) {
        $scorecardIdList += $($value.id)
    }

    return $scorecardIdList
}

function GetScorecard($scorecardId) {
    Write-Host "Retrieving scorecard $scorecardId :" -NoNewLine

    $response = Invoke-WebRequest `
        -Uri "$api/v1.0/myorg/scorecards($scorecardId)?`$expand=goals" `
        -Headers @{ "Authorization"=$token }
    Write-Host -ForegroundColor Green OK
    $scorecard = $response.Content | ConvertFrom-Json
    return $scorecard
}

function RefreshGoalValueConnection($scorecardId, $goalId) {
    $response = Invoke-WebRequest `
        -Method Post `
        -Uri "$api/v1.0/myorg/scorecards($scorecardId)/goals($goalId)/RefreshGoalCurrentValue()" `
        -Headers @{ "Authorization"=$token }
}

function RefreshGoalTargetConnection($scorecardId, $goalId) {
    $response = Invoke-WebRequest `
        -Method Post `
        -Uri "$api/v1.0/myorg/scorecards($scorecardId)/goals($goalId)/RefreshGoalTargetValue()" `
        -Headers @{ "Authorization"=$token }
}

function update_scorecard($scorecardId) {
    $scorecard = GetScorecard -scorecardId $scorecardId

    $connectedGoals = @($scorecard.goals | where { $_.valueConnection -or $_.targetConnection })

    Write-Host "Found $($connectedGoals.Count) connected metric(s) in scorecard '$($scorecard.name)'."

    if ($connectedGoals.Count -gt 0) {
            foreach ($goal in $connectedGoals) {
                Write-Host "Refreshing connections for metric '$($goal.name)'"
                if ($goal.valueConnection) {
                    Write-Host -NoNewline "   Value connection:  "
                    RefreshGoalValueConnection -scorecardId $scorecardId -goalId $goal.id
                    Write-Host -ForegroundColor Green OK
                }
                if ($goal.targetConnection) {
                    Write-Host -NoNewline "   Target connection: "
                    RefreshGoalTargetConnection -scorecardId $scorecardId -goalId $goal.id
                    Write-Host -ForegroundColor Green OK
                }
            }
    }
}

function loop_over_scorecards_in_workspace($workspaceId) {
    $scorecardIdList = GetScorecardList -workspace $workspaceId
    foreach ($scorecardId in $scorecardIdList) {
        update_scorecard -scorecard $scorecardId
    }
}

function run_over_workspaces() {
    $workspaceIdList = GetWorkspaceList
    foreach ($workspaceId in $workspaceIdList) {
        loop_over_scorecards_in_workspace -workspaceId $workspaceId
    }
}

run_over_workspaces

It provides two login options, one for service accounts with elevated priveledges, requiring the service account to have access of the workspaces that you wish to refresh. The alternative is to use the PowerBI-Login method which may require you to manually go through login checks.

Once authenticated, the script will find all workspaces the user has access to, all scorecards within those workspaces, and then update all goals that are connected to data. The script has been written with the intent of running it in a task scheduler once per day without human intervention (pure automation!) as a bandaid solution until PowerBI Metrics update themselves as expected.

I hope this script is useful for anyone else trying to use PowerBI Metrics to implement company-wide KPIs!

Cheers,

Samuel