Create Power BI deployment pipelines automatically

In part 4 of this series, I introduced a standard pattern for organising report files and pipelines, with a standard process for creating new reports. Repeatable patterns and processes are great candidates for automation, and in this post I'll build a report creation process that automatically configures a new report and creates its deployment pipeline 😃.

This video (5m31s) shows you the process in action, from creating a new report to seeing it deployed automatically to Power BI:

At a high level, here's how it works:

1. A developer runs a script to create a new report. The script:
   • creates the report by copying a template report folder
   • modifies files in the copied folder to correspond to the new report

   • creates a new Git feature branch for the report, then commits and pushes the new files to the central Git repository.

2. In the background, pushing the feature branch to the central Git repo triggers a special DevOps pipeline called the Report Pipeline Manager. This pipeline:
   • inspects the repository for report pipeline configuration YAML files (reports/*/pipeline.yaml)
   • for each identified pipeline configuration file, checks for the existence of a corresponding Azure DevOps pipeline

   • if no corresponding report deployment pipeline is found, it creates a new one.

     At this point, everything needed to deploy the report automatically has been set up! 🚀

3. Later, the developer takes an action required to trigger a deployment pipeline – for example, they open a PR to add an updated report to a release candidate. By now, the pipeline manager has created the necessary deployment pipeline – so the report is automatically deployed to Power BI.

Let's dig into some of the detail 😊!

The report setup script set-up-new-report.cmd is a small DOS batch script which launches a PowerShell script, tools/New-PbiReport.ps1:

@echo off
echo:
echo Enter folder name for new report (no spaces!)
powershell.exe -command "& '..\tools\New-PbiReport.ps1'"
pause

The final pause is there just to give me a chance to look at any output on the console.

Here's the PowerShell script called by the batch file, New-PbiReport.ps1:

param(
  [Parameter(Mandatory = $true)]
  [ValidateNotNullOrEmpty()]
  [string] $FolderName,
 
  [Parameter(Mandatory = $False)]
  [bool] $WithGitActions = $True
)
 
$ErrorActionPreference = 'Stop'
 
$toolsFolder = $MyInvocation.MyCommand.Path | Split-Path
Import-Module $toolsFolder\PbiDeployment\PbiDeployment.psm1 -Force
Set-Location $toolsFolder
 
$reportFolder = "$toolsFolder\..\reports\$FolderName"
if(Test-Path -Path "$reportFolder") {
  throw "$reportFolder already exists. Delete the existing folder or create a new one."
}
 
# create new report folder
Copy-Item `
  -Path "$toolsFolder\ReportTemplate" `
  -Destination "$reportFolder" `
  -Recurse
 
Write-Host "Created report folder $reportFolder"
 
# configure pipeline YAML files
foreach($file in @('pipeline.yaml', 'metadata.yaml')) {
  $content = Get-Content -Path "$reportFolder\$file"
  $content = $content -Replace "_ReportTemplate_", "$FolderName"
  Set-Content -Path "$reportFolder\$file" -Value $content  
}
 
# create & push feature branch
if($WithGitActions -eq $true) {
  New-ReportBranch -FolderPath $reportFolder
}

The script prompts for a FolderName value, then checks that no folder of that name already exists (lines 17-19). If the folder doesn't exist yet, it's created by copying the folder tools/ReportTemplate (along with all its contents) and renaming it (lines 21-25).

There are three files copied from the report template folder: a blank Report.pbix file, and the two YAML files pipeline.yaml and metadata.yaml. The two YAML files are edited to replace a placeholder value (“_ReportTemplate_”) with the name of the new folder (lines 28-34) – this makes the file pipeline.yaml ready for use, and starts the customisation of metadata.yaml.

The remainder of the file (lines 36-39) creates and pushes a Git feature branch for the new report by calling the custom function New-ReportBranch.

The New-ReportBranch function called at the end of script New-PbiReport.ps1 is defined in the module PbiDeployment\PbiDeployment.psm1:

# Creates a new feature branch, adds a specified folder, pushes to origin.
function New-ReportBranch ([string]$FolderPath) {
    $ErrorActionPreference = 'Stop'
    $FolderName = Split-Path $FolderPath -Leaf
    $BranchName = "create-$FolderName"
 
    Invoke-Utility git checkout main
    Invoke-Utility git pull
    Invoke-Utility git checkout -b $BranchName
    Invoke-Utility git add "$FolderPath"
    Invoke-Utility git commit -m "Initialised report folder $FolderName"
    Invoke-Utility git push --set-upstream origin $BranchName
 
    Write-Host "`n------------------------------------------------`n"
    Invoke-Utility git status
    Write-Host ""
}

The function takes the path of the new report folder as a parameter, and uses it to define the name of a new feature branch. The rest of the function invokes a series of git commands to:

• sync my local repository with the central copy
• create the new branch
• commit the new files to the branch
• push the branch to the Git server (in my case, GitHub).

Pushing the new feature branch to GitHub (or Azure DevOps) means that the central repository now has a copy of the pipeline configuration file pipeline.yaml – but no corresponding Azure DevOps pipeline “wrapper” exists yet. The pipeline manager is a special DevOps pipeline which ensures that every report's pipeline.yaml file has a corresponding wrapper pipeline in Azure DevOps.

This is the YAML definition for the pipeline manager:

trigger:
  branches:
    include:
    - '*'
    exclude:
    - main
    - rc/*
  paths:
    include:
    - /powerbi-pro-devex-series/06-ReportCreation/reports
pr: none

variables:
- group: DeploymentSecrets
- name: folderPath
  value: $(System.DefaultWorkingDirectory)/powerbi-pro-devex-series/06-ReportCreation

pool:
  vmImage: ubuntu-latest

steps:
- task: PowerShell@2
  displayName: Manage report pipelines
  inputs:
    targetType: filePath
    filePath: $(folderPath)/tools/Sync-AzureDevOps.ps1
    arguments: >
      -AzureDevOpsOrganization "https://dev.azure.com/richardswinbank"
      -AzureDevOpsProject "PowerBiProDev"
      -RepositoryLocalPath "$(Build.Repository.LocalPath)"
      -BranchName "$(Build.SourceBranchName)"
    failOnStderr: true
  env:
    AZURE_DEVOPS_EXT_PAT: $(System.AccessToken)
    AZURE_DEVOPS_EXT_GITHUB_PAT: $(GitHubPersonalAccessToken)
    GITHUB_SERVICE_CONNECTION_ID: $(GitHubServiceConnectionId)

The trigger section indicates that the pipeline is triggered only on pushes to branches (not by any pull requests), and only when the target branch is not main or a release candidate (rc/). This means that the pipeline is triggered by every push to a feature branch – this may seem unnecessary, but it offers additional flexibility (described below).

The pattern of the pipeline body is familiar: it calls a PowerShell script 😊 – in this case, Sync-AzureDevOps.ps1. Notice however that three additional environment variables are being set:

• AZURE_DEVOPS_EXT_PAT is set to the Azure DevOps system variable System.AccessToken – this provides an Azure DevOps personal access token, specific to the build agent executing the pipeline.
• AZURE_DEVOPS_EXT_GITHUB_PAT is set to the pipeline variable GitHubPersonalAccessToken, a variable I've stored the value securely with other secret variables. It contains a GitHub personal access token I have created to allow access to my GitHub account.
• GITHUB_SERVICE_CONNECTION_ID is set to the pipeline variable GitHubServiceConnectionId, another variable I've added to the deployment variable group. We'll see where its value comes from – and what it's for – in a moment.

The two access tokens, passed into environment variables, will be used to authenticate the pipeline manager when creating the new report's deployment pipeline.

Part of PowerShell script Sync-AzureDevOps.ps1 is shown below. (The full version is available in the code files that accompany this post, available on GitHub).

# get existing Azure DevOps pipelines in target folder
$pipelines = az pipelines list `
  --organization "$AzureDevOpsOrganization" `
  --project "$AzureDevOpsProject" | ConvertFrom-Json
 
# check YAML pipeline definitions for corresponding Azure DevOps pipelines
foreach($folderPath in Get-ChildItem -Path $reportsFolder) {
  if(Test-Path -Path "$folderPath/pipeline.yaml") {
    $pipelineFolder = "\06-ReportCreation\Reports"
    $pipelineName = "Deploy $(Split-Path -Leaf $folderPath) report"
 
    if(Test-DevOpsPipeline -Folder $pipelineFolder -Name $pipelineName -Existing $pipelines) {
      Write-Host "FOUND $pipelineFolder\$pipelineName (already exists)"
      continue
    }
 
    $yamlPath = "$folderPath/pipeline.yaml" -Replace $RepositoryLocalPath,''  
 
    az pipelines create `
      --organization "$AzureDevOpsOrganization" --project "$AzureDevOpsProject" `
      --name "$pipelineName" --folder-path "$pipelineFolder" `
      --yaml-path "$yamlPath" --skip-first-run `
      --service-connection "$Env:GITHUB_SERVICE_CONNECTION_ID" --branch "$BranchName" `

Lines 36-38 use the Azure CLI command az pipelines list to list existing Azure DevOps pipelines in the specified Azure DevOps project. I haven't explicitly signed in here, because I don't need to – presenting an access token via the AZURE_DEVOPS_EXT_PAT environment variable takes care of that for me.

Lines 41 onward iterate through the list of folders in the reports directory. For each folder, the script:

• constructs the name of the corresponding DevOps pipeline (line 44)
• checks if it exists (line 46-49)…
• …and if it doesn't, creates it using the Azure CLI az pipelines create command.

Although AZURE_DEVOPS_EXT_PAT simplifies logging in, I still need to allow build agents to access Azure DevOps pipelines. I also need to enable access to my GitHub repository, so the pipeline manager can access the new report's pipeline.yaml file.

So far, the only thing I've needed is grant access to is Power BI. I did that in the first post of this series, using a specially created service principal which I allowed to access Power BI workspaces. Now I need to allow the report manager pipeline to manage other Azure DevOps pipelines.

All my Azure DevOps pipelines are executed by the project's build service, a principal created automatically when I created my Azure DevOps project. I allow the build service to edit pipelines like this:

1. Use the ellipsis button in the top right of the Pipelines screen to find and select the “Manage security” option:

   

2. On the project permissions page, select the project build service, then set the “Edit build pipeline” option to “Allow”:

   

Now the build service can modify the Azure DevOps pipeline, but remember that this pipeline is just a “wrapper” – an Azure DevOps object that encapsulates the underlying YAML pipeline definition. All my code is in a GitHub repo, so for the pipeline manager to be able to encapsulate the YAML file in a new pipeline, it needs access to that repo.

In fact, I've already allowed Azure DevOps to access GitHub – when I created my first pipeline, I signed in to GitHub when prompted by Azure DevOps. This created an Azure DevOps service connection, which you can find via the “Service connections” page of your Azure DevOps project settings:

Because I created the service connection, I'm allowed to use it personally – but the pipeline manager isn't. To grant access, I access the service connections security settings using the ellipsis button in the top right (indicated in the screenshot above) then under “User permissions” I add the project build service to the project's User role:

Finally, I'm going to need to be able to refer to it in the pipeline – for that I'll need the GUID that identifies the service connection. It's displayed in the page URL as the resource_id parameter. I've indicated its position in the address bar on the screenshot, but obfuscated its value.

Look again at line 57 of the PowerShell script and you can see where the value of the GitHub service connection ID is needed. I can reference it as an environment variable because I passed it into the environment on the last line of the YAML pipeline definition.

Finally! All the code is ready, all the permissions granted – the only thing left to do is to create the pipeline manager. This is a simple Azure DevOps pipeline, using the YAML file I've already defined. It needs no additional configuration – all the necessary information is stored in the PowerShell script, or the YAML pipeline, or the variable group it references.

In this post I showed you a scripted process for creating a new Power BI report and setting up its deployment pipeline.

• Using a report template makes it easy to ensure that all the components you want for a report – configuration information, and perhaps a standard look and feel – are set up correctly from the start.
• Using the report setup script customises the report template, performing initial setup and adding the report to centralised version control.
• Using a pipeline manager means that, when the report is added to centralised version control, its deployment pipeline is created automatically, ready to support your team's development workflow.