Use Power BI Rest API to deploy Power BI files

During the development of BI-solutions most of the sources are well maintained: central source location, version control, tracking changes, and automated deployment. Yes, not everything. Most of the time data pipelines, database(s), and analysis service model(s) are controlled with a service like Azure DevOps. Power BI models and reports are often not controlled centrally. There are many reasons why and I did it too. But since I learned how easy it is to integrate your Power BI development process with Azure DevOps, I never create Power BI reports without Azure DevOps anymore.

Power BI Premium licenses give you some extra features like deployment pipelines. That’s great, but for many organizations, a premium license is like buying a Ferrari when you need a bicycle. So, in this blog series of 4 posts, I’ll show you how you can improve your Power BI development process big time with just some tiny steps using Azure DevOps. And you only need a Power BI pro license.

In this second blog post, we’ll look at the PowerShell script you can use to deploy Power BI files. You need some extra configuration to make it work, like a service principal. Check out this blog to add the service principal.

We focus on Azure DevOps but you can use this script in any other way too. We’ll look at all parts of the script one by one. At the bottom, you can copy the whole script at once.

We have 3 parameters:

1. $pbixPath: The full path of the Power BI file in Azure DevOps
2. $datasetRefresh: Either ‘yes’ or ‘no’ depending on your data refresh needs of the particular Power BI file.
3. $stageOrder: ‘0’ –> Deploy to test environment, ‘1’ –> Deploy to production environment

param
(   
    [string] $pbixPath,
    [string] $datasetRefresh,
    [string] $stageOrder
)

You need the Tenant Id, App Id, and App Password for the connection to the Power BI Rest API. For more information, please check this blog. The names of the workspace must correspond with the names you see in Power BI Service.

#Fill in these values before starting the scirpt
    $tenantId = "[your tenant id]"
    $appId = "[app id of your service principal]"
    $appPassword = "[password of your service principal]"
    $workspaceDev = "[development workspace name]"
    $workspaceProd = "[production workspace name]"
    $DatamodelFilename = "[Power BI filename of the data model]"

#You need this module!!
    Install-Module MicrosoftPowerBIMgmt -force

#Determine which workspace to deploy to
    $DeployToWorkspace = ""
    if ($stageOrder -eq 0)
    {
        $DeployToWorkspace = $workspaceDev
    }
    elseif ($stageOrder -eq 1)
    {
        $DeployToWorkspace = $workspaceProd
    }

#Get name of PBIX-file
    Write-Host "Start Get report file info"
    $report = Get-ChildItem  $pbixPath
    $reportFileToPublish = $report.name
    Write-Host "End Get report file info"

#Login to Power BI
    Write-Host "Start login"
    $applicationId = $appId;
    $securePassword = $appPassword | ConvertTo-SecureString -AsPlainText -Force
    $credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $applicationId, $securePassword
    Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId $tenantId
    Write-Host "End Login"

#Get workspace id based on Workspace name
    Write-Host "Start - Get workspace info"
    $workspaceObject = ( Get-PowerBIWorkspace -Name $DeployToWorkspace )
    $groupid=$workspaceObject.id
    Write-Host "End - Get workspace info"

#Publish the PBIX-file to the workspace
    Write-Host "Start Publish report"
    $result = New-PowerBIReport -Path $pbixPath -Name $reportFileToPublish -Workspace $workspaceObject -ConflictAction CreateOrOverwrite
    $reportid = $result.id
    Write-Host "End Publish report: " $reportid

This means, when your Power BI file is connected to development and you deploy to production, you need to change the values of these parameters. This is done with the section below.

#update data source settings when deploying to production
if ($stageOrder -eq "1" -and $reportFileToPublish -eq $DatamodelFilename){

    Write-Host "Start Update data source on production"

#Take over dataset to have full control over the dataset
    $TakeOverUrl = "groups/$groupid/datasets/$datasetId/Default.TakeOver"
    Invoke-PowerBIRestMethod -Url $TakeOverUrl -Method Post

#Update parameters
    $ParametersUrl = "groups/$groupid/datasets/$datasetId/Default.UpdateParameters"
    $parameterName = "[name of the parameter]"
    $newParameterValue = "[new value of the parameter]"
    $Body = "{updateDetails:[{name:'$parameterName', newValue:'$newParameterValue'}]}"
    Invoke-PowerBIRestMethod -Url $ParametersUrl -Method Post -Body $Body `
                                -ContentType 'application/json'
    Write-Host "End Update data source on production"
}

#Refresh dataset
    $dataset = Invoke-PowerBIRestMethod -Url "groups/$groupid/datasets" -Method Get | ConvertFrom-Json
    $datasetid = $dataset.value[0].id

    $urlbase = "groups/$groupid/datasets/$datasetid/"

    if ($datasetRefresh -eq "yes") { 
      Write-Host "Start Dataset refresh"

      $url=$urlbase + "refreshes"
      $body = @"
      {
        "notifyOption": "NoNotification"
      }
"@
        Invoke-PowerBIRestMethod -Url $url -Method Post -Body $body
        Write-Host "End Dataset refresh"

      Write-Host "Dataset refresh succeeded"
    }

#Distconnect from Power BI
    Disconnect-PowerBIServiceAccount

param
(   
    [string] $pbixPath,
    [string] $datasetRefresh,
    [string] $stageOrder
)

#Fill in these values before starting the scirpt
    $tenantId = "[your tenant id]"
    $appId = "[app id of your service principal]"
    $appPassword = "[password of your service principal]"
    $workspaceDev = "[development workspace name]"
    $workspaceProd = "[production workspace name]"
    $DatamodelFilename = "[Power BI filename of the data model]"


#You need this module!!
    Install-Module MicrosoftPowerBIMgmt -force

#Determine which workspace to deploy to
    $DeployToWorkspace = ""
    if ($stageOrder -eq 0)
    {
        $DeployToWorkspace = $workspaceDev
    }
    elseif ($stageOrder -eq 1)
    {
        $DeployToWorkspace = $workspaceProd
    }

#Get name of PBIX-file
    Write-Host "Start Get report file info"
    $report = Get-ChildItem  $pbixPath
    $reportFileToPublish = $report.name
    Write-Host "End Get report file info"

#Login to Power BI
    Write-Host "Start login"
    $applicationId = $appId;
    $securePassword = $appPassword | ConvertTo-SecureString -AsPlainText -Force
    $credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $applicationId, $securePassword
    Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId $tenantId
    Write-Host "End Login"

#Get workspace id based on Workspace name
    Write-Host "Start - Get workspace info"
    $workspaceObject = ( Get-PowerBIWorkspace -Name $DeployToWorkspace )
    $groupid=$workspaceObject.id
    Write-Host "End - Get workspace info"

#Publish the PBIX-file to the workspace
    Write-Host "Start Publish report"
    $result = New-PowerBIReport -Path $pbixPath -Name $reportFileToPublish -Workspace $workspaceObject -ConflictAction CreateOrOverwrite
    $reportid = $result.id
    Write-Host "End Publish report: " $reportid

#update data source settings when deploying to production
if ($stageOrder -eq "1" -and $reportFileToPublish -eq $DatamodelFilename){

    Write-Host "Start Update data source on production"

#Take over dataset to have full control over the dataset
    $TakeOverUrl = "groups/$groupid/datasets/$datasetId/Default.TakeOver"
    Invoke-PowerBIRestMethod -Url $TakeOverUrl -Method Post

#Update parameters
    $ParametersUrl = "groups/$groupid/datasets/$datasetId/Default.UpdateParameters"
    $parameterName = "[name of the parameter]"
    $newParameterValue = "[new value of the parameter]"
    $Body = "{updateDetails:[{name:'$parameterName', newValue:'$newParameterValue'}]}"
    Invoke-PowerBIRestMethod -Url $ParametersUrl -Method Post -Body $Body `
                                -ContentType 'application/json'
    Write-Host "End Update data source on production"
}

#Refresh dataset
    $dataset = Invoke-PowerBIRestMethod -Url "groups/$groupid/datasets" -Method Get | ConvertFrom-Json
    $datasetid = $dataset.value[0].id

    $urlbase = "groups/$groupid/datasets/$datasetid/"

    if ($datasetRefresh -eq "yes") { 
      Write-Host "Start Dataset refresh"

      $url=$urlbase + "refreshes"
      $body = @"
      {
        "notifyOption": "NoNotification"
      }
"@
        Invoke-PowerBIRestMethod -Url $url -Method Post -Body $body
        Write-Host "End Dataset refresh"

      Write-Host "Dataset refresh succeeded"
    }

#Distconnect from Power BI
    Disconnect-PowerBIServiceAccount