Supporting multi-developer scenarios for Power BI using ALM Toolkit

This article is a follow up to a blog post I wrote a couple of months ago. It was on options for DevOps in Power BI and the benefits one can get from professionalizing the development and deployment process: 

DevOps for Power BI - What is it? Do you need it? And which options are available?

I also introduced a couple of tools that are needed to bring those benefits to Power BI.

In this post I want to take a closer look at one of those tools, the ALM Toolkit, to enable branching scenarios in your development.

Let's assume you work in a team of Power BI developers. Let's also assume that your team is responsible for several complex Power BI datasets, that are subject to frequent changes. Also, keep in mind that currently Power BI Desktop does not support concurrent work on your PowerQuery pipelines, data models or reports with other developers.

In this situation there are two options to design your development process:

The sequential approach enables one developer at a time to work on your .pbix-file. If you have a couple of features to implement in the same file, each developer needs to wait for his colleague to bring his changes to production in order to create his own features on top of the resulting master-file that represents the latest version of your Power BI project.

It is quite obvious that this process is not very efficient. Its sequential character makes it relatively slow and costly.

The second option enables several developers to work in parallel on the same project. Therefor each developer creates her own feature branch, a copy derived from the latest master-version of the project. The developers create and test their features in that specific branch before merging it back to the master-file for deployment to production.

Although this approach sounds very promissing in terms of efficiency and speed, it also rises some problems. Each branch represents a different version of the project, containing some features that are nowhere else. You need to have all those features combined into one consolidated version of the .pbix-file, the master-file. So you have to merge the branches in some way while avoiding the risk of overwriting the changes coming from other branches.

Of course that could simply be done by copying the changes of each branch to the master. But wouldn't that be a very inefficient, tedious and error-prone way to go? So how can we do it differently?

When it comes to branching, ALM Toolkit is one of the best options you have in Power BI. It provides the ability to perform incremental metadata deployments and merges for Power BI data models. In a multi developer scenario this means that each developer can deploy the new features from her individual branch to the master file without touching any other aspects of the master. You can download ALM Toolkit for free from its website.

It provides schema compare between a source and target model and the ability to choose which changes to deploy to the target and which not. Christian Wade provided a great demo walking through the main aspects of ALM Toolkit and its user interface on the PowerBI.tips YouTube channel (ALM ToolKit with Christian Wade).

The user interface is simple yet very powerfull:

• [1] Object Window - The main canvas of the tool showing all components of the source and the target model side by side. These components can be tables, columns, relationships, measures, roles, perspectives, calculation groups, PowerQuery queries, parameters, refresh policies and so on. It enables you to decide whether to skip, delete, update or create objects in the target based on what is in the source.

• [2] Code Window - Displays some details on the difference between objects in the source and target model.

• [3] Button "Compare" - Select the source and target and start metadata comparison. As source and target you can use Power BI Desktop files (-PBIX), bim-files, Power BI models hosted in the Power BI service or Analysis Services/Azure Analysis Services models.

• [4] Button "Select Actions" - Options to filter the objects displayed in the object window and to bulk select the action (skip, delete, update, create) performed on these objects.

• [5] Button "Validate Selection" -  After selecting the objects to merge and actions to perform, this button starts a check to ensure that all the changes will lead to a technical valid result in the target model.

• [6] Button "Update" - After validation was successful this button performs the deployment to the target model.

• [7] Button "Generate Script" - Creates a script file containing the changes selected. This script can be incorporated in your devops pipeline to perform automated deployment of the changes, reuse of definitions or rollback of single features.

• [8] Button "Options" - Provides options and settings to configure the behavior of ALM Toolkit in terms of object types to be included/excluded from schema comparison and refresh behavior of the target model after the update. 

Although this sounds great there are still some requirements and limitations to consider, when using ALM Toolkit. In a nutshell a Power BI solution consists of three layers: The PowerQuery ETL layer, the data model including the business logic and the visual layer providing the user interface. That said the following aspects must be taken into account:

• Visual Layer - ALM Toolkit is not capable of doing schema compare and metadata merging for the visual layer. It will not transfer changes made to your reports to a master file. This is another reason why it is a good practice to separate the visual layer (the report) and the model in different .PBIX-files following a front-end/back-end approach.

• Power BI Desktop - If you choose a .PBIX-file as your merge target keep in mind that currently it is not possible to merge your PowerQuery ETL changes to a Power BI Desktop file, as it will not accept them.

• Power BI Service - Choosing models that reside in the Power BI Service as a target will accept both your PowerQuery ETL changes and your other changes made to the data model. But in order to deploy your changes to that model it has to reside in a workspace backed by either Premium Capacity or Premium per User. You also need to have the XMLA read/write endpoint enabled. However, this is not without a cost. Currently, after performing changes using the XMLA endpoint, you will not be able to download the model from the Cloud-Service to a .PBIX-file anymore. If you still want to download the model you will have to rely on other tools than Power BI Desktop, tools like Tabular Editor, which enables you to locally save the model as .bim-file. 

My original blog post listed a number of benefits that DevOps can bring to your Power BI development process. Let's take a look at which of these are addressed by ALM Toolkit.

It is pretty obvious that Branching and CI/CD are the main purposes of ALM Toolkit as it provides the ability to merge incremental, even small changes from different branches to a master data model. In this way it is possible to increase the frequency of your deployments. Unfortunatelly it does not provide this for all the components of a Power BI solution (e.g. the visual layer)

Other aspects like Automation, support for Development-/Test- or Production Environments and Version Control are not that much in scope of this tool. Although the ability to script out your changes enables incorporation in automated processes, deployments are still mostly a manual process.

As you can see this tool does not aim at the full package of DevOps benefits. But depending on your situation, the number of developers, the number of data models and reports, their complexity and last but not least the frequency of changes to be done, you might not even need a complete DevOps solution. In that case ALM Toolkit can be just the right choice for you.

If you want to enjoy all the benefits of DevOps, there are other tools to consider. If you would like to have branching and CI/CD for all layers of your Power BI solution, including the visual layer, I recommend to have a closer look at "PBI-Tools". At the time of writing "PBI-Tools" is not officially supported, it is in beta and it is just a command-line-tool lacking any user interface. But it works and will definitively be enhanced in the future. Mathias Thierbach gives a very good introduction to his tool in this video on the Havens Consulting YouTube channel (Deploying Reports with PBI-Tools)

If you seek for a solution providing all the benefits described, you will have to go with a DevOps platform such as "Azure DevOps". These platforms are far more powerful than ALM Toolkit but are also even more complex to master.

I will keep aspects of "PBI-Tools" and "Azure DevOps" for upcoming posts and videos.

Hope you enjoyed this article. Looking forward to welcome you back for the upcoming posts on this topic. You can also visit my YouTube-Channel for more content on Power BI, SSAS, Power Pivot, Power Query, DAX und M!