Power BI has no inbuilt mechanisms for promoting Power BI Reports and Shared Datasets from one environment to another. This process is very manual. I was challenged by a colleague of mine to create CI/CD process to deploy Power BI objects.

I assume that reader:

  • Can configure basic DevOps CI/CD pipelines and have basic understand about CI/CD flow. (you can learn more from here)
  • Can add solution to source control (you can learn more from here)

Prerequisites:

  • PowerBI Web Workspace are created and security configured on the workspace level

Problem Definition

Manual Deployment of Power BI object has lots of small challenges like:

  • Configuring dataset connection to gateway is manual – requires file modification
  • Configuring reports connection to shared dataset – requires file modification
  • Tracking of what components version of the report has been deployed to each environment
  • Tracking compatibility between deployed database and Power BI Shared Dataset and Reports

Harnessing GIT Source Control together with DevOps Pipelines could remove majority of the pain associated with manual updates and let you release your Power BI objects quicker just like the rest of your software.

In later part of the blog we are going to cover how to implement this way of Power BI enrolment.

Power BI Architecture

Deployment process would vary depending on the Power BI architecture used. My Power BI architecture looks like below. This configuration will be the same for all environments

Solution

You will require to create single DevOps pipeline that would deploy both Shared Dataset and all the reports. Deployment scripts and Power BI files must be stored in DevOps GIT source control

CI/CD process can be visualised by below graph:

Implementation

To setup DevOps CI/CD pipelines for Power BI we need to:

  1. Solution Setup and configuring source control
  2. Creating and configuring Build Definition
  3. Creating Environment Variable Groups and set values for each environment
  4. Creating and configuring Release Definition

Solution Setup

Your solution should be organised as below Power BI Folder:

  • Scripts – Folder that stores all Power-Shell deployment scripts
  • Data – folder to stored pbix for Shared Data Set
  • Grouping folders e.g. Data Quality that would allow them to group reports that will be deployed to the same workspace.

Creating Build Definition (CI)

Build Definition – define how to snapshot all the files that are required for successful deployment – that is reports, shared dataset pbix files and Power-Shell scripts that are required for the deployment.

This build definition was created in classic pipelines and can be imported directly to your DevOps. There is some minor configuration that is required (Build Agent Pools and Variable Groups references as they do not load in and need to be configured manually)

Build Definition has four actions:

  1. Stage Power BI (PBIX) files – Stages all Power BI files from /PowerBI/Dev folder in source control on the build agent
  2. Publish Power BI (PBIX) files as Artefact – This step publishes staged artefacts (PBIX files) to release pipeline
  3. Stage the PowerShell (PS1) scripts –This stages  the deployment Power-Shell scripts from /Power BI/Scripts folder in the source control on the build agent
  4. Publish the PowerShell (PS1) scripts as Artefacts – This step publishes staged artefacts (Scripts) to release pipeline

Creating Library variable groups

The release definition is going to require the variable groups to be created beforehand.

The variables are going to be stored in DevOps/Library/Variable Groups. Variable group for Dev, Test and Prod would contain the same set of variables. All environment variables will be linked with corresponding Release Stage. Environment specific values will be injected into Power-Shell scripts for each environment during the release phase. Example of Power BI variable group for DEV environment is provided below

Pipeline requires all variables to be created as below:

BuildAgent.Name – this is built agent name that is set as an admin on the Power BI Servcie. It requires permissions to list and modify workspaces
BuildAgent.Password – this is password for the build agent
Gateway.Name [Optional]– If we use gateway for the connection, this defines name of the gateway that we connect our dataset to. It does not create gateway if it does not exist
Reports.ConfigurationJson – this is a JSON array that defines configuration for the reports deployment - only reports listed in the json will be deployed (but all reports that has dependencies will be re-binded into new dataset)
[{ "Workspace":"<Workspace.Name>", "Folder":"<Visual.Studio.Report.Root.Folder>", "ReportName":"<Report.Name>" }]
SharedDataset.Folder – this defines folder that Dataset pbix file is present in
SharedDataset.Name – this defines name of the shared Dataset
SharedDataset.RefreshSchedule.Json - defines refresh schedule in form of json object e.g.
{"enabled": true, "days": ["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"],"times": ["06:00"],"localTimeZoneId": "UTC", "notifyOption": "mailNotification"}
SharedDataset.Workspace.Name – this defines Workspace for shared dataset
SqlServer.Database.Name – This defines database gateway connects to
SqlServer.Name – This defines server name that gateway connects to

Creating Release Definition (CD)

Release definition –  defines all the steps that need to be done in order to deploy to Power BI reports and datasets to each of the environment. It also binds environment specific variables groups with environment specific Release Stage.

Definition would look like the one below.

Release Activities in each Release Stage will be the same and it would look like this

  1. Deploy Shared Dataset
    Arguments:

    -PowerBIFilePath "$(System.DefaultWorkingDirectory)/_PowerBI-CI/PowerBICode/$(SharedDataset.Folder)/$(SharedDataset.Name).pbix" -WorkspaceName "$(SharedDataSet.Workspace.Name)" -SharedDatasetName "$(SharedDataset.Name)" -BuildAgentLogin "$(BuildAgent.Name)" -BuildAgentPassword "$(BuildAgent.Password)" -SourceSqlServer "$(SqlServer.Name)" -SourceDatabase "$(SqlServer.Database.Name)" -GatewayName "$(Gateway.Name)" -InstallLibraries "True"
  2. Deploy Reports
    Arguments:

    -PowerBIDirectory "$(System.DefaultWorkingDirectory)/_PowerBI-CI/PowerBICode" -DatasetWorkspaceName "$(SharedDataSet.Workspace.Name)" -DatasetName "$(SharedDataset.Name)" -BuildAgentLogin "$(BuildAgent.Name)" -BuildAgentPassword "$(BuildAgent.Password)" -ReportsToDeploy '$(Reports.ConfigurationJson)'

     

Remarks:

DEV Environment cannot deploy SharedDataSet into DEV environment so this step in dev need to be disabled (right click on the Deploy Shared Dataset activity – disable)

Scripts

There are 2 scripts that serves deployment process. There is no need to modify script or update parameters in the script. All the values will be injected from Pipelines.

Deployment of the Shared Dataset

param(
    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
  [string] $PowerBIDirectory,

    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
  [string] $DatasetWorkspaceName,

    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
  [string] $DatasetName,

    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $BuildAgentLogin,

    [Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $BuildAgentPassword,

    [Parameter(Mandatory = $True)]
  [ValidateNotNullOrEmpty()]
  [string] $ReportsToDeploy = '[
        {"Workspace":"Workspace1","Folder":"Folder1","ReportName":"Report1"},
        {"Workspace":"Workspace2","Folder":"Folder2","ReportName":"Report2"}
    ]',

  [Parameter(Mandatory = $False)]
    [string] $InstallModules = "True"
)


###EXECUTION
cls;

Write-Host "### Script Started.";

try
{
    ### INSTALL MODULES AND LOGIN TO POWER ###
  if($InstallModules -eq "True")
  {
    install-PackageProvider `
      -Name "Nuget" `
      -Force `
      -Scope CurrentUser;

    install-module `
      -Name "MicrosoftPowerBIMgmt" `
      -AllowClobber `
      -Force `
      -Scope CurrentUser;
  }

    $secureBuildAgentPassword = ConvertTo-SecureString $BuildAgentPassword `
        -AsPlainText `
        -Force;
    
    $creds = New-Object PSCredential($BuildAgentLogin,$secureBuildAgentPassword);
    Login-PowerBIServiceAccount -Credential $creds;




    ### REDEPLOY REPORTS ###
    $reports = $ReportsToDeploy | ConvertFrom-Json ;
    Write-Host "ReportsCount: $($reports.Count)"
    foreach($report in $reports)
    {
        Write-Host "Workspace: $($report.Workspace)";
        Write-Host "Report: $($report.ReportName)";


        ## GET WORKSPACE ID
        Write-Host "Getting workspaces."

        $reportWorkspace = Get-PowerBIWorkspace -Name $report.Workspace;

        if($reportWorkspace -eq $null)
        {
            throw "Can not find $($report.Workspace) Workspace in available workspaces.";
        }

        ## GET REPORT ID
        Write-Host "Getting Report"
        $existingreport = Get-PowerBIReport -WorkspaceId $reportWorkspace.Id -Name $report.ReportName;
        $PowerBIFilePath = "$($PowerBIDirectory)\$($report.Folder)\$($report.ReportName).pbix";

        ## DEPLOY POWER BI REPORT
        if($existingreport -eq $null)
        {
            #CREATE NEW POWER BI REPORT
            Write-Host "Creating New PowerBI Report...";
            New-PowerBIReport `
              -Path $PowerBIFilePath `
              -Name $report.ReportName `
              -WorkspaceId $reportWorkspace.Id `
                -Timeout 3600 `
                -ErrorAction Stop;

            Write-Host "Created New PowerBI Report" -ForegroundColor Green;
        }
        else
        {
            ## UPDATE REPORT WHEN EXISTS (DROP AND RECREATE)
            Write-Host "Report Exists. Updating PowerBI Report..." -ForegroundColor Yellow;
            Remove-PowerBIReport `
                -WorkspaceId $reportWorkspace.Id `
                -Id $existingreport.Id `
                -ErrorAction Stop;;

            New-PowerBIReport `
              -Path $PowerBIFilePath `
              -Name $report.ReportName `
              -WorkspaceId $reportWorkspace.Id `
                -ErrorAction Stop;
        
            Write-Host "Updated PowerBI Report" -ForegroundColor Green;
        }

    


        ### REBIND NEW REPORT TO DATASET (DEV, TEST, PROD) ETC. ###
        ## GET NEW REPORT INFORMATION
        Write-Host "Getting New Report Information";
        $newReport = Get-PowerBIReport -WorkspaceId $reportWorkspace.Id.Guid -Name $report.ReportName;

        ## GET DATASET INFORMATION
        Write-Host "Getting Dataset Workspace Information";
        $datasetWorkspace = Get-PowerBIWorkspace -Name $DatasetWorkspaceName;
     
        Write-Host "Getting Dataset Information";
        $dataset = Get-PowerBIDataset -WorkspaceId $datasetWorkspace.Id.Guid -Name $DatasetName -ErrorAction Stop;

        ## SEND REQUEST 
        $requestBody = @{datasetId = $dataset.Id.Guid};
        $requestBodyJson = $requestBody | ConvertTo-Json -Compress;  
          
        $headers = Get-PowerBIAccessToken;
        $result = Invoke-RestMethod `
            -Headers $headers `
            -Method "Post" `
            -ContentType "application/json" `
            -Uri "https://api.powerbi.com/v1.0/myorg/groups/$($reportWorkspace.Id.Guid)/reports/$($newReport.Id.Guid)/Rebind" `
            -Body $requestBodyJson `
            -Timeout 3600 `
            -ErrorAction Stop;

        Write-Host "Rebinded";
        Write-Host "";

        Write-Host "Deployed and Rebinded Succesfully" -ForegroundColor Green;
        Write-Host "----------------";
    }
    Write-Host "### Script Finished Succesfully." -ForegroundColor Green;
} 
catch
{
    Write-Host "### Script Failed." -ForegroundColor Red;
    throw;
}

Script Explanation:

This script uses both Power BI Power-Shell Modules and also Power BI REST API calls. Script deploys shared dataset file to target workspace. This script drop and re-create the dataset. All reports are automatically rebound to a newly created shared dataset. All parameters and gateway connection are updated automatically by the script.

Deployment of the Power BI Reports

param(
    #[Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
  [string] $PowerBIFilePath = "C:\...\SharedDataSet.pbix",

    #[Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
  [string] $WorkspaceName = "SharedDatasetWorkspaceName",

    #[Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
  [string] $SharedDatasetName = "SharedDataSetName",

    #[Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $BuildAgentLogin,

    #[Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [String] $BuildAgentPassword,

    #[Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $SourceSqlServer,
    
    #[Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $SourceDatabase,
        
    [Parameter(Mandatory = $false)]
    [string] $GatewayName,

    [Parameter(Mandatory = $false)]
    [string] $ScheduleJson = '{"value":{"enabled":true,"days":["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"],"times":["06:00"],"localTimeZoneId":"UTC"}}',

    #[Parameter(Mandatory = $true)]
    [ValidateNotNullOrEmpty()]
    [string] $InstallLibraries = "True"
)

cls;

Write-Host "### Script Started.";

try
{
    ## INSTALL MODULES AND LOGIN TO POWER 
    if($InstallLibraries -eq "True")
    {
        install-PackageProvider `
            -Name "Nuget" `
            -Force `
            -Scope CurrentUser;

        install-module `
            -Name "MicrosoftPowerBIMgmt" `
            -AllowClobber `
            -Force `
            -Scope CurrentUser
    }
    $secureBuildAgentPassword = ConvertTo-SecureString $BuildAgentPassword `
        -AsPlainText `
        -Force;

    $creds = New-Object PSCredential($BuildAgentLogin,$secureBuildAgentPassword);
    Login-PowerBIServiceAccount -Credential $creds;

    ## GET WORKSPACE ID
    Write-Host "Getting workspaces..."

    $workspace = Get-PowerBIWorkspace -All | Where-Object { $_.Name -eq $WorkspaceName };

    if($null -eq $workspace)
    {
        throw "Can not find $($WorkspaceName) Workspace in available workspaces.";
    }

    ## GET REPORT ID
    Write-Host "Trying to obtain existing report..."
    $existingReport = Get-PowerBIReport -WorkspaceId $workspace.Id | Where-Object { $_.Name -eq $SharedDatasetName };

    #CREATE NEW POWER BI REPORT
    Write-Host "## Creating New PowerBI Report...";
    New-PowerBIReport `
      -Path $PowerBIFilePath `
      -Name $SharedDatasetName `
      -WorkspaceId $workspace.Id.Guid `
        -ErrorAction Stop `
        -Timeout 3600;

    $newDataset = Get-PowerBIDataset -WorkspaceId $workspace.Id.Guid -Name $SharedDatasetName | Where-Object {$_.Id -ne $existingReport.DatasetId};

    ## DEPLOY POWER BI REPORT
    if($null -eq $existingReport)
    {
        Write-Host "Created New PowerBI Report" -ForegroundColor "Green";
    }
    else
    {       
        Write-Host "Created Updated PowerBI Report for" -ForegroundColor "Green";
    
        ## REBIND REPORTS TO NEW DATASET
        Write-Host "Checking dependant reports...";
        $reportWorkspaces = Get-PowerBIWorkspace  | Where-Object {$_.Name -ne $WorkspaceName};
        foreach($reportWorkspace in $reportWorkspaces)
        {
            $reportsToRebind = (Get-PowerBIReport -WorkspaceId $reportWorkspace.Id.Guid)  | Where-Object { $_.DatasetId -eq $existingReport.DatasetId -and $_.Name -ne $SharedDatasetName};
    
            if($null -ne $reportsToRebind)
            {
                Write-Host ""
                Write-Host "Workspace reports To Rebind: $($reportWorkspace.Name)"
                Write-Host "Reports To Rebind Count: $($reportsToRebind.Count)"
                $requestBody = @{datasetId = $newDataset.Id.Guid};
                $requestBodyJson = $requestBody | ConvertTo-Json -Compress;
                foreach($reportToRebind in $reportsToRebind)
                {
                    $headers = Get-PowerBIAccessToken;
                    Invoke-RestMethod `
                        -Headers $headers `
                        -Method "Post" `
                        -ContentType "application/json" `
                        -Uri "https://api.powerbi.com/v1.0/myorg/groups/$($reportWorkspace.Id.Guid)/reports/$($reportToRebind.Id.Guid)/Rebind" `
                        -Body $requestBodyJson `
                        -ErrorAction Stop;
    
                    Write-Host "Rebinded Report: $($reportToRebind.Name)";
                    Write-Host "------";
                }
            }
        }

        ## REMOVE OLD REPORT
        Write-Host "Removing old report";
        Remove-PowerBIReport `
            -WorkspaceId $workspace.Id `
            -Id $existingReport.Id.Guid `
            -ErrorAction Stop;
    
        ## REMOVE OLD DATASET
        Write-Host "Removing old Dataset"
        $headers = Get-PowerBIAccessToken;
        Invoke-RestMethod `
            -Headers $headers `
            -Method "Delete" `
            -Uri "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id.Guid)/datasets/$($existingReport.DatasetId)" `
            -ErrorAction Stop; 

    }


    ## UPDATE DATASET PARAMETERS
    ## SEND REQUEST
    Write-Host "Updating DataSet Parameters...";
    $Parameters = @{
        "updateDetails"= @(
            @{
                "name"="SqlServer";
                "newValue"="$($SourceSqlServer)";
             },
            @{
                "name"="Database";
                "newValue"="$($SourceDatabase)";
             }
          )
    };

    $ParametersJson = $Parameters | ConvertTo-Json -Compress;

    $headers = Get-PowerBIAccessToken;
    Invoke-RestMethod `
        -Headers $headers `
        -Method "Post" `
        -ContentType "application/json" `
        -Uri "https://api.powerbi.com/v1.0/myorg/datasets/$($newDataset.Id.Guid)/Default.UpdateParameters" `
        -Body $ParametersJson `
        -ErrorAction Stop;

    Write-Host "Updated DataSet Parameters" -ForegroundColor Green;


    ## GET GATEWAY AND CONNECTIONS
    if($GatewayName -ne $null)
    {
        Write-Host "Connecting to gateway";
        $headers = Get-PowerBIAccessToken;
        $gatewaysResponse = Invoke-RestMethod `
            -Headers $headers `
            -Method "Get" `
            -Uri "https://api.powerbi.com/v1.0/myorg/gateways" `
            -ErrorAction Stop;

        $gateway = $gatewaysResponse.value | Where-Object {$_.name -like $GatewayName};

        ## GET GATEWAY DATA SOURCES
        $headers = Get-PowerBIAccessToken;
        Invoke-RestMethod `
            -Headers $headers `
            -Method "Get" `
            -Uri "https://api.powerbi.com/v1.0/myorg/gateways/$($gateway.Id)/datasources";

        ## CONNECT TO GATEWAY
        Write-Host "Binding to gateway";
        $requestBody = @{
          "gatewayObjectId"= $gateway.id;
        }

        $requestBodyJson = $requestBody | ConvertTo-Json -Compress;
        $headers = Get-PowerBIAccessToken;
        Invoke-RestMethod `
            -Headers $headers `
            -Method "Post" `
            -ContentType "application/json" `
            -Uri "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id.Guid)/datasets/$($newDataset.Id.Guid)/Default.BindToGateway" `
            -Body $requestBodyJson `
            -ErrorAction Stop;

        Write-Host "Report Binded to Gateway" -ForegroundColor Green;
    }

    ### Creating Refresh Schedule if provided
    if($ScheduleJson -ne $null)
    {
        Write-Host "Creating Refresh Schedule...";
      $headers = Get-PowerBIAccessToken;
        Invoke-RestMethod `
            -Headers $headers `
            -Method "Patch" `
            -ContentType "application/json" `
            -Uri "https://api.powerbi.com/v1.0/myorg/groups/$($Workspace.Id.Guid)/datasets/$($newDataset.Id.Guid)/refreshSchedule" `
            -Body $ScheduleJson `
            -ErrorAction Stop;

      Write-Host "Created Refresh Schedule" -ForegroundColor Green;
    }
    Write-Host "### Script Finished Succesfully.";
} 
catch
{
    Write-Host "### Script Failed." -ForegroundColor Red;
    throw;
}

Script Explanation

Scripts iterates over list of report configuration to deploy defined as a JSON parameter. Update happens through drop and re-create.

Summary

We have performed an exercise to deliver CI/CD for Power BI reports. Process is not perfect, but it provides nearly fully automatic way of deployment to multiple environment.

Positives:

  • We store only single file per report for each report and shared dataset (using one drive for source control would require)
  • Takes pain of copying the file and changing the connections
  • Works really well if multiple reports are to deliver (>10)
  • Eliminates need of tracking what version is deployed
  • One click deployment (Automatic deployment and promotion if required)
  • Easy Rollback to previous version
  • Ties database change with Power BI change e.g. adding field to abstraction views in SQL and Power BI model modification to accommodate that change could be single GIT PR

Negatives:

  • pipeline runs slightly slow when a lot of reports to deploy. If we have hundreds of reports to deploy, process might be slightly slow, but should not exceed 20 minutes. It will be quicker than deploying reports manually and changing the connections for each file.
  • Your source control can grow in size due to binary files being committed to repository
  • Requires dropping of the shared dataset/reports and recreating it

Limitations:

  • current script can be run only for the configuration of shared dataset accessing Azure SQL Server database and reports in the dataset
  • current script can not run if there are reports that has been referenced by dashboards
  • rest API has some limitations that are described in each section here. – you may not be able to make some changes to the power BI if certain configuration is used e.g. SAP source systems etc.
  • the current scripts works only with the solution described on the beginning of the scrip