ANALYTICS UNLEASHED: Releasing Power BI reports across environments with Powershell and Azure Devops

Why the need for DevOps pipelines?

As Power BI grows in popularity and becomes the preferred technology for data visualization for an increasing number of projects of all sizes, there is an ever-growing need to integrate Power BI provisioning and publishing tasks with the automatic delivery mechanism used for traditional software development frameworks.

Since June 2020, Power BI premium ships with the new Deployment Pipeline feature which aims to close this gap with two significant limitations, however. First, this functionality is only available for Premium users, the top tier in Power BI subscription model, which is only a subset of all users. Second, there are clear advantages to having a central location from which to manage releases for different layers of a project stack rather than multiple.

Azure DevOps is an online collaboration platform designed to be a one-stop-shop for every aspect of your software development project, from agile project management tasks to source control and release pipelines. It offers a plethora of functionalities, including the ability to create CI/CD (Continuous Improvement, Continuous Delivery) pipelines to automate a range of tasks from architecture provisioning to releasing code across development and production environments.

The Power BI REST API

REST Apis are programming interfaces to software systems and databases and allow users to perform actions they would do manually with code i.e. programmatically. In the case of Power BI, such actions include publishing and refreshing reports, sharing with users, and changing source databases for example.

The Power BI REST API can be accessed with the language of your choice by making REST calls which essentially consists of “dialing” a URL corresponding to the action you want to perform, with a specific verb (Post, Get, Put, Patch). So for instance, the API call for a report refresh will be something like

POST https://api.powerbi.com/v1.0/myReportId/refresh

So provided we can perform programmatically via the API, all the actions we usually perform manually to publish a report to a certain environment, then we should be able to write a program making these API calls, then put it on DevOps so that the instructions are executed on-demand in a serverless way, with input parameters to determine which environments to release to – in other words, we should be able to create a release pipeline for our report

Release steps

Let’s imagine we are working on a local version of a report in Power Bi desktop, connected to our DEV SQL server. Our task is to release the report to the System Test (SIT) environment. The task can be broken down into the following steps :

  • Log into Power BI as ourselves
  • Publish the report to the SIT workspace
  • Switch the data source to use the SIT SQL Server instead of DEV
  • Enter SIT server credentials
  • Refresh the report

All the above operations can be handled via the REST API, good news! Now, if we want to automate the above process, we need to use a specific identity rather than “ourselves” – in other words, we need the program to log into Power BI as “something” that is not an individual’s account and perform the above tasks – this “something” is called a service principal. Also, our aim is for the program to publish the report not from our personal PC but from source control. This means that we want to keep the report as light/small as possible and perhaps above all, we also want to hold as little data as possible in the source control – ideally, we would like to hold to data at all, ie store the report’s metadata, however, the REST API does not support publishing reports from metadata files. As a workaround, we can use a Power Query parameter that will limit the tables to a single row when set to Yes, or pull the full dataset when set to No. We can then save the file with limited rows in source control, and change the parameter to No just before refreshing. The release steps, therefore, have become this :

  • Log into Power BI as the service principal
  • Publish the report to the SIT workspace
  • Take Ownership of the dataset
  • Switch the data source to use the SIT SQL Server instead of DEV
  • Enter SIT server credentials
  • Update parameter to remove row limit
  • Refresh the report

Now that we have got a good idea of what we want to achieve in our pipelines, let’s first ensure the service principal can actually perform these actions

Enable management by Service Principal

To allow the Service Principal to perform management actions via the API, you will need to activate a specific setting in the admin portal for your tenant – you need to be tenant administrator for it. Go to the Admin Portal > Tenant Settings, then search for Service Principal and on the below toggle :

Allow service principal to use the REST API

Please note that the way this setting works is by authorizing a set of security groups a.k.a Azure Active Directory Groups – this means you will first have to create your service principal and then add it to a security group, which you will then reference here.

Setting up the DevOps Pipeline

Our end goal is to create the following pipeline :

Three Stages

The artifact is the content that will be released across environments: it is the result of a Build Pipeline that runs on a specific branch and create the artifact to be used as the source of the release.

The SIT and UAT Stages will perform the list of tasks mentioned above for their respective environment, the DEV Stage will operate a reduced task set in that it does not need to switch data source and update credentials.

Let’s now take a closer look at the SIT stage: it consists of two steps that run a PowerShell script :

  • Install the Power Bi management module
  • Make the sequence of API calls to perform the desired actions

Remember that DevOps operates in a serverless way: that means that every time the pipeline is triggered, its Powershell code will run on a different Agent, which is essentially a computer in a Microsoft Data Center, with a clean configuration so any specific modules such as the Power BI content management modules need to be installed as an initial step.

Step 1 : Install the Management Modules

And now, the second stage where the magic happens!

Step 2 : Make the calls

The picture below shows two things :

  • The PowerShell script is called from a source provided by the artifact rather than inline: the script is maintained in source control and invoked by the pipeline
  • A number of variables are fed to the pipeline, including the target workspace ID, the target server credentials, the service principal credentials: these variables are pulled from DevOps variable groups which are themselves hooked up to Azure KeyVault for the secrets and password. The beauty of this is that the actual secrets live in the Keyvault and are passed in encrypted form to the agent during the execution without ever the need or ability to display or access the actual secret value.

Another thing to note on the picture above, in the specific formula to use to feed parameters to the script: -Name1 $(Name2) where Name1 is the parameter that will be used in the script and Name2 is the parameter name as specified in the variable group the value is sourced from – this can be confusing the first time so I thought I would clear it up!

Now that we’ve established the DevOps skeleton of the pipeline, the last remaining step is the PowerShell script itself!

PowerShell automation

The first bit of the script consists of sourcing the parameter values that will be used in the script execution: this includes the credentials needed to authenticate into the REST API, the target workspace Id where the report(s) will be released, the name of the server to redirect the source to, and the server credentials.

[CmdletBinding()]
param (
    [Parameter(Mandatory = $true)]
    [String]$DeploymentPrincipalAppId ,
    [Parameter(Mandatory = $true)]
    [String]$DeploymentPrincipalAppSecret ,
    [Parameter(Mandatory = $true)]
    [String]$powerBiTenantId ,
    [Parameter(Mandatory = $true)]
    [String]$workspaceId ,
    [Parameter(Mandatory = $true)]
    [String]$newServerName ,
    [Parameter(Mandatory = $true)]
    [String]$existingDbName,
    [Parameter(Mandatory = $true)]
    [String]$sqlServerAdminUsername,
    [Parameter(Mandatory = $true)]
    [String]$sqlServerAdminPassword,
    [Parameter(Mandatory = $true)]
    [String]$reportsBasePath
)

The next step is to authenticate into the REST API as the service account, using the credentials sourced from the variable group via the params fed to the script.

# Connect to service as Service Principal
$credentials = New-Object System.Management.Automation.PSCredential ($DeploymentPrincipalAppId, (convertto-securestring $DeploymentPrincipalAppSecret -asplaintext -force))
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credentials -Tenant $powerBiTenantId 

try {
    $token = Get-PowerBIAccessToken 
} 
catch [System.Exception] {
    Connect-PowerBIServiceAccount
    $token = Get-PowerBIAccessToken 
}

$accessToken = $token.Values -replace "Bearer ", ""

$apiHeaders = @{
    'Content-Type'  = 'application/json'
    'Accept'        = 'application/json'
    'Authorization' = "Bearer $AccessToken"
}

The above section also contains a few lines of code to form an object named apiHeaders. This is useful further down in the script when we need to use PowerShell default’s Invoke-RestMethod instead of the Power BI API’s specific: unlike the latter, the former requires API headers to be specified explicitly on top of the other parameters passed to the REST call. Now, the reason we may want to use the native PowerShell command rather than the Power Bi cmdlet is that Invoke-RestMethod provides better debugging capabilities, and more specifically can provide more precise error details beyond the 404 error code.

The next step is to create JSON payloads to pass to the API to update the data source credentials and set the value of the parameter that controls the number of rows we are pulling into the report to “NO” :

# Create JSON to update parameter
$updateParamPayload = [PSCustomObject]@{"updateDetails" = @(@{ "name" = "limitRows"; "newValue" = "NO" }) } | ConvertTo-Json -Depth 10

# Create JSON to update credentials
$UpdateCredentialPayload = @{
    credentialDetails = @{
        credentialType      = "Basic"
        credentials         = "{`"credentialData`":[{`"name`":`"username`", `"value`":`"$sqlServerAdminUsername`"},{`"name`":`"password`", `"value`":`'$sqlServerAdminPassword`'}]}";
        encryptedConnection = "Encrypted"
        encryptionAlgorithm = "None"
        privacyLevel        = "None"
    }
} | ConvertTo-Json -Depth 10

We are now ready to start making API calls and push content to the Power Bi service: the first step if to quite simply publish the reports located in the source folder located at $myPath to the target environment, like so :

# Publish local pbix files to service
# List files in folder
$Files = Get-ChildItem -Path  $myPath
# Import files to workspace
foreach ($File in $Files) {		
    $extn = [IO.Path]::GetExtension($File)
    $reportName = [IO.Path]::GetFileNameWithoutExtension($File)
    $sourceFilePath = "$myPath\$File"
    if ($extn -eq ".pbix" ) {
        Write-Host "Publishing file $File as $reportName from $sourceFilePath"
        New-PowerBIReport -WorkspaceId $workspaceId -Path $sourceFilePath -Name $reportName -ConflictAction CreateOrOverwrite
    }
    
}

Finally, once the reports have been published, we can loop through the datasets in our workspace(s) and perform the steps listed above, that is take ownership, switch source, input new credentials, and update parameter value. The example below shows a case where we are updating reports in two separate workspaces, you can see how we are simply following along the process listed above for each dataset to achieve the desired result :

  • Grabbing the dataset Id
  • Obtaining the current data source details JSON object using a REST call with getDataSourceURL, then updating the object with the new data source details
  • Preparing the URLs for taking ownership (TakeOwnershipURL), updating the data source (SubmitUrl), and Updating the param value (UpdateParamUrl) – then making these calls
  • Finally, build the UpdateCredentialUrl using the result from a REST call returning the gateway id corresponding to the current dataset id
    • This is a fairly confusing part as API gateways are entities internal to the API, not to be confused with Power Bi gateways
  • And as the last step, using this URL to update the credentials
$workspaceList = $WorkspaceId1, $WorkspaceId2
foreach ($workspaceId in $workspaceList) {
    # GetDataSets in workspace
    $DatasetResponse = Invoke-PowerBIRestMethod -Url "groups/$($workspaceId)/datasets" -Method Get | ConvertFrom-Json 
    $datasets = $DatasetResponse.value 
 
    foreach ($dataset in $datasets) {

        ## Looping through all datasets in workspace
        $datasetId = $dataset.id; # Get dataset id
        Write-Host " Processing Dataset : $datasetid "

        # Create JSON to Update data source
        # Obtain current server value
        $GetDatasourceURL = "https://api.powerbi.com/v1.0/myorg/groups/$($workspaceId)/datasets/$($datasetId)/datasources"
        $dataSourceDetails = Invoke-PowerBIRestMethod -Url $GetDatasourceURL -Method Get | ConvertFrom-Json
        $existingServerName = $dataSourceDetails.value[0].connectionDetails.server
        $obj = [PSCustomObject]@{"updateDetails" = @(@{"datasourceSelector" = @{"datasourceType" = "Sql"; "connectionDetails" = @{"server" = $($existingServerName); "database" = $($existingDbName) } }; "connectionDetails" = @{"server" = $($newServerName); "database" = $($existingDbName) } }) } 
        $updateDatasourcePayload = $obj | ConvertTo-JSON -Depth 10
        ## Prepare URLs
        $TakeOwnershipURL = "https://api.powerbi.com/v1.0/myorg/groups/$($workspaceId)/datasets/$($datasetId)/Default.TakeOver"
        $SubmitURL = "https://api.powerbi.com/v1.0/myorg/groups/$($workspaceId)/datasets/$($datasetId)/Default.UpdateDatasources"
        $UpdateParamURL = "https://api.powerbi.com/v1.0/myorg/groups/$($workspaceId)/datasets/$($datasetId)/Default.UpdateParameters"
        #$RefreshUrl = "https://api.powerbi.com/v1.0/myorg/groups/$($validationWorkspaceId)/datasets/$($datasetId)/refreshes"
   
   
        # Show URLS in console output, for debugging
        #Write-Host "Takeown URL to be Used: $TakeOwnershipURL"
        #Write-Host "Submit URL to be Used: $SubmitURL"
        #Write-Host "Get URL to be Used: $GetURL"

        # Make API calls
        Write-Host "Taking Ownership of dataset $datasetId"
        Invoke-PowerBIRestMethod -Url $TakeOwnershipURL-Method Post
        Write-Host "Updating datasource for dataset $datasetId"
        Invoke-RestMethod -Uri $SubmitURL -Headers $apiHeaders -Method Post -Body $updateDatasourcePayload
        Write-Host "Updating parameter for dataset $datasetId"
        Invoke-PowerBIRestMethod -Url $UpdateParamURL -Method POST -Body $updateParamPayload

        # Get gateway details - required to build the update credentials URL
        $BoundGateway = Invoke-PowerBIRestMethod -Url $GetDatasourceURL -Method GET | ConvertFrom-Json 

        # Prepare URL
        $UpdateCredentialsURL = "https://api.powerbi.com/v1.0/myorg/gateways/$($BoundGateway.value.gatewayId)/datasources/$($BoundGateway.value.datasourceId)"
        # Make API call
        Write-Host "Updating credentials for datasource $BoundGateway.value.datasourceId"
        Invoke-RestMethod -Uri $UpdateCredentialsURL -Headers $apiHeaders -Method Patch -Body $UpdateCredentialPayload

        # Refresh datasets
        #Invoke-PowerBIRestMethod -Url $RefreshUrl -Method POST

    
    } 

}

So in the end, we have it, a way to programmatically release our reports across environments, using PowerShell and Devops release agents.

In the future we can hope that the API will accept working with metadata power bi files to avoid the workaround with limiting rows via a parameter – we have seen promising developments recently with the addition of external tools to the Power BI service and the ability work with BIM model files in the same was as Tabular Editor – let’s hope all these good things make their way into the API sometime soon!

Related Post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.