Power BI Project (PBIP) and Azure DevOps CI performance tests

Reading Time: 6 minutes

In this post I want to cover some Power BI Project (PBIP) and Azure DevOps CI performance tests that I recently performed.

Based on the Power BI Project (PBIP) and Azure DevOps build pipelines for continuous integration guide by Microsoft that is now available. Which you can use with Microsoft Fabric Git integration.

It covers how you can automate performing tests on your Power BI project by using a pipeline in Azure DevOps. Including automated tests for against your Power BI deployments using both Tabular Editor 2 and PBI-Inspector.

I was curious to see what was recommended for various reasons. Including the fact that a while back I shared one way you can potentially work with Microsoft Fabric Git integration and multiple workspaces.

One key point to note is that Microsoft Fabric is now generally available. You can read more about this in detail in the official post by Ryan Majidimehr.

If you need help with any jargon used in this post, then I recommend that you read one my other posts. Which is a Microsoft Fabric Git integration jargon guide for Fabricators.

Power BI Project (PBIP) and Azure DevOps CI guide baseline test

I decided to test the guide with the Power BI report that I showed in my post about work with Microsoft Fabric Git integration and multiple workspaces.

So, I went through the guide and was pleasantly surprised that it showed how to do it with a YAML pipeline in Azure Pipelines. Which I must admit I prefer for reasons that I covered why in a previous post about disabling classic pipelines in Azure DevOps.

Below is an overview of what the guide shows:

1. How to setup Microsoft Fabric Git integration with a repository in Azure Repos.
2. After the repository is populated, create a YAML pipeline based on its contents in Azure Pipelines within Azure DevOps.
3. Configure a branch policy so that the pipeline must run every time want to update main branch.
4. Test by performing a pull request.

Anyway, I went through the guide with my existing repository, it worked as expected. In fact, a bit too well as it turns out my report violated some rules.

Which I was able to resolve easily enough in the Data Model.

After resolving the issue within model, I ran the pipeline again which then completed.

However, after scrolling down to the end, I noticed that it passed even though it had multiple warnings.

I will not go into too much details about the warnings. Suffice to say I should have disabled the automatic date tables. You can tweak the tests performed if you are not comfortable with it passing with warnings.

Anyway, once completed I was able to update the report in through the source control section in my Microsoft Fabric workspace.

Improving the performance

In the guide the example shows it working with a Microsoft-hosted agent. Which on average took well over a minute.

Which is fine for testing, but what about in an enterprise environment which contains a more complex repository?

In an enterprise environment you can look to improve the performance of Microsoft-hosted agents by adding more parallel jobs.

I decided to test with my laptop acting as a self-hosted agent instead. With the software preinstalled.

I did this by running PowerShell to install Tabular Editor and PBI-Inspector in ‘c:\temp’. I did this by running a modified version of the PowerShell provided in the guide locally. Like in the below example for Tabular Editor.

#Installing Tabulator editor locally
$path = "C:\Temp"                       
$tempPath = "$path\_temp"
$toolPath = "$path\_tools\TE"
New-Item -ItemType Directory -Path $tempPath -ErrorAction SilentlyContinue | Out-Null              
              
Write-Host "##[debug]Downloading Tabular Editor binaries"
$downloadUrl = "https://github.com/TabularEditor/TabularEditor/releases/latest/download/TabularEditor.Portable.zip"
$zipFile = "$tempPath\TabularEditor.zip"
Invoke-WebRequest -Uri $downloadUrl -OutFile $zipFile
Expand-Archive -Path $zipFile -DestinationPath $toolPath -Force            

Write-Host "##[debug]Downloading Dataset default rules"
$downloadUrl = "https://raw.githubusercontent.com/microsoft/Analysis-Services/master/BestPracticeRules/BPARules.json"
Invoke-WebRequest -Uri $downloadUrl -OutFile "$tempPath\Rules-Dataset.json"  

Once done I commented out the download tasks in the pipeline as below.

        # - task: PowerShell@2
        #   displayName: 'Download Tabular Editor and Default Rules'
        #   inputs:
        #     targetType: inline
        #     script: |     
        #       $path = "$(Build.SourcesDirectory)"                       
        #       $tempPath = "$path\_temp"
        #       $toolPath = "$path\_tools\TE"
        #       New-Item -ItemType Directory -Path $tempPath -ErrorAction SilentlyContinue | Out-Null              
              
        #       Write-Host "##[debug]Downloading Tabular Editor binaries"
        #       $downloadUrl = "https://github.com/TabularEditor/TabularEditor/releases/latest/download/TabularEditor.Portable.zip"
        #       $zipFile = "$tempPath\TabularEditor.zip"
        #       Invoke-WebRequest -Uri $downloadUrl -OutFile $zipFile
        #       Expand-Archive -Path $zipFile -DestinationPath $toolPath -Force            

        #       Write-Host "##[debug]Downloading Dataset default rules"
        #       $downloadUrl = "https://raw.githubusercontent.com/microsoft/Analysis-Services/master/BestPracticeRules/BPARules.json"
        #       Invoke-WebRequest -Uri $downloadUrl -OutFile "$tempPath\Rules-Dataset.json"    

You can comment everything out easily if you are working in the pipeline in Azure DevOps by highlighting all the text and pressing CTRL & / together.

I then modified the two outstanding PowerShell tasks to read the binaries from the new locations and still use the original sources. Like in the below example.

        - task: PowerShell@2
          displayName: 'Run Dataset Rules'
          inputs:
            targetType: inline
            script: |
              $sourcepath = "$(Build.SourcesDirectory)"
              $path = "C:\Temp"
              $tempPath = "$path\_temp"
              $toolPath = "$path\_Tools\TE\TabularEditor.exe"
              $rulesPath = "$path\Rules-Dataset.json"

              if (!(Test-Path $rulesPath))
              {
                  Write-Host "Running downloaded rules"
                  $rulesPath = "$tempPath\Rules-Dataset.json"
              }

              $itemsFolders = Get-ChildItem  -Path $sourcepath -recurse -include *.pbidataset

              foreach($itemFolder in $itemsFolders)
              {	
                  $itemPath = "$($itemFolder.Directory.FullName)\Model.bim"

                  Write-Host "##[group]Running rules for: '$itemPath'"

                  Start-Process -FilePath "$toolPath" -ArgumentList """$itemPath"" -A ""$rulesPath"" -V" -NoNewWindow -Wait

                  Write-Host "##[endgroup]"
              }

Running the tests locally with the applications already installed was faster. From taking well over a minute to thirty seconds. As you can see below.

Parallel jobs performance test

I then decided to add another user account to my organization that has a Visual Studio Enterprise subscription. So that I could take advantage of a second parallel job. Which is explained in the configure and pay for parallel jobs documentation.

Note that doing this is not an immediate process. It can take Azure DevOps some time to update your parallel job limits.

Anyway, with an additional parallel job the pipeline took on average around fifteen seconds.

I did not perform parallel Microsoft-Hosted agents for various reasons.

I am certain they perform better then a single agent. However, I suspect the self-hosted agent is still faster considering when I tested using parallelism with two self-hosted agents the test completed in fifteen seconds.

Shifting left

Before I finish this post, I want to highlight another key point. In this post I followed along with the instructions in the guide by adding the pipeline to run after updates to my ‘Dev’ branch. Which is configured to use Microsoft Fabric Git integration.

However, you might want to consider shifting left if people are working with Power BI projects locally in Power BI Desktop.

In other words, get the pipeline to run whenever somebody attempts to do a pull request for the Git repository connected to your development workspace. So that the development workspace is only updated if it passes all the tests.

Of course, this all depends on your way of working.

Final words about my Power BI Project (PBIP) and Azure DevOps CI guide performance tests

I hope sharing my initial results for my Power BI Project (PBIP) and Azure DevOps CI guide performance tests is useful for some of you.

Clearly if you run a self-hosted agent on a decent computer, it will perform better. However, significant gains can be achieved when you use parallel jobs.

Of course, if you have any comments or queries about this post feel free to reach out to me.