Skip to content

How to Work on a Power BI Report With Multiple Developers

Technology

Aug 3, 2022 - 8 minute read

2588 Powerbi Multiple Developers Blog 416X300[138907]
Injae Park BI Tech Lead

A Swedish-Korean Business Intelligence Technical Lead specialising in Power BI and Azure. Recently honoured with the Microsoft MVP recognition, Injae is an avid contributor of Power BI content on platforms like YouTube and LinkedIn. 

See all Injae's posts

2988 HC Digital Transformation 476X381

Share

With the highest execution ability and completeness of vision, Power BI is celebrated as one of the leaders in the business intelligence (BI) field according to Gartner’s 2022 analysis of available BI solutions — for the 4th year running. It has a long list of strengths and represents more than a third of the market share in a growing market. Yet, despite the recognition and wide-scale adoption, there are still some aspects where Power BI is lacking — the most impactful of which is the difficulty of working with other Power BI developers on a single report.

This isn’t something you face every day. In one-developer projects, the problem is moot, whereas in larger projects, with multiple reports and team members, assigning a report per developer could be the best approach. There are actually great Power BI-native features which can support collaboration, including workspaces and Microsoft Teams conversations. However, difficulty exists in projects between these two scenarios, where multiple developers need to work together on a single PBIX file. Whether it’s due to an approaching deadline, or the report itself is too large for one person, there will be projects where it just makes sense to have multiple developers share the work.

So, what’s the problem? Simply put, it’s a matter of overwriting each other’s work. In software development projects, version control systems such as GIT or Azure DevOps are used to overcome this. With the prevalence of graph structure in such software, teams can create “branches” of development that can be merged into the main software once tested and accepted. This works very well in programming, as merging usually involves text comparison of the merged branch, which allows for clear oversight of when and where changes occurred. However, things aren’t so simple in Power BI — even though this is a feature sought by the Power BI community since 2015, as of now, there’s no native functionality to achieve source control. In this article, I’ll check why this is the case and explore different approaches to working together as Power BI developers.

Why Is Working Together So Difficult?

Let’s consider a project in Power BI with two full-time developers on the job. They have an assignment to develop a single-page report, and they cooperate in ingesting data and creating a data model in one report. They each take one copy of the report and develop functionalities with the intent to bring them together after finishing their respective tasks. When it’s time to merge the reports, the activity takes longer than the work they put into their individual shares.

What went wrong? There are 3 main aspects to Power BI:

  1. Backend Data Model
  2. Calculations (DAX)  Measures
  3. Frontend  –  User interface

Different issues occurred in each of these aspects. In the backend, one developer decided to modify the data model, normalising some dimensions, whereas the other decided to aggregate some values to improve performance. Due to the model difference, DAX measures from each copy of the report didn’t work in the other, which lead directly to issues in the frontend where measures wouldn’t show data. While this part could be addressed with some form of source control, that wouldn’t take into account frontend features such as interactions, hidden filters, and bookmarks. In the end, these developers had to get together and make their changes on one report step by step.

How to Successfully Collaborate in Power BI

So how can you avoid issues in cooperation? I will outline two different approaches which I’m calling the “check-in” and the “functional split”. These different ways of working have different pros and cons, and choosing the best way to work together really comes down to selecting the best fit for the project. It’s important to remember that the key to making cooperation work is communication.

The Check-In Approach

The check-in approach is where only one person is working on a report at a time. This is done by “checking out” a report when it’s being worked on, and “checking in” when it’s not, like a library book. This essentially takes the ambiguity out of the development, and there’s no chance of overwriting another developer’s work. While this is logically sound, it might not be appropriate for a local team where two developers are working full time in the same hours — what is the other to do while the report is checked out? However, if the developers are in different time zones and work at different hours, or if they aren’t working on making changes to the report full time, this approach could present a solid method of building a report together.

This can be implemented in a number of ways. As long as there’s a centralised location where files are stored and communication between the team members is open, developers could informally check in and check out the report, indicating to the team that they’re making changes to the existing report. There are also check-in and check-out options natively available on both SharePoint and Teams, which also have visibility of version history and can be used to add some structure to this approach. PowerBI.Tips have a great solution created in PowerApps, which also provides the check-in, check-out functionality.

The Functional Split Approach

Another way of cooperation is to divide the areas of work. One developer could focus on the backend by means of PowerQuery and data modelling, while another could focus on the frontend, creating a clear split in the division of labour. Alternatively, the work could be divided by specific pages within the report.

There are a couple of ways this can work, but success relies on very strong communication within the team. In the scenario that one developer is working on the data model and the other on the frontend, a published dataset could be used, where the updated model is stored and loaded into the frontend version once pushed to the Power BI service. This allows the PBI service to act as an interim between the different report versions, and it can also be used if different developers are creating separate tabs. The developer working on the backend would simply need to make changes and inform the frontend developer to refresh their data model.

If using a published dataset isn’t possible, there are third-party tools that can act as some form of source control. The ALM toolkit is a third-party tool that has the ability to merge two completely separate PBIX files. It works on checking the BIM files, which are JSON text files that outline the data model and DAX measures. This can be done for two PBIX desktop files for free, but a premium license is needed to deploy to the service as it requires an XMLA endpoint. A drawback to this method of merging, however, is that this doesn’t account for merging the frontend of the report.

Source: Home Page - ALM Toolkit (alm-toolkit.com)

All is not lost on the frontend, however, as visuals with the same data model can be copied from one report and pasted into another. Items not visible may not be copied, so the best practice when copying an entire page of one PBIX file to another is to open up the selection pane, select all the objects and paste them into the other file — as simple as Ctrl + C, Ctrl + V. As mentioned earlier, edited interactions, bookmarks, and some other things that don’t get copied will need to be recreated manually.

In the same vein, PowerQuery items can also be copied and pasted from one report to another, which could be a less controlled alternative to using the ALM toolkit. It may not copy the security credentials, but selecting items in the Query Pane, and using Ctrl + C, Ctrl + V can be an easy way to replicate all required steps. Otherwise, copying the M Query using the Advanced Editor functionality might be the way to align the backend.

The Tabular Model

There are some considerations for when the data isn’t in import mode — specifically if the data is connected to the report using a tabular model (Azure Analysis Services). This is a way of connecting very large data sets directly to the report, and it’s usually chosen as the step between the cost of Power BI Pro and Power BI Premium. A semantic model is built using Azure resources which include DAX calculations. As such, any backend or measure changes that are committed to the tabular model become instantly live for all other reports connected to that model.

There are quite a few options you can choose in this approach, such as using Visual Studio and the BISM Normalizer Extension. The latter acts like the ALM toolkit for version control, while also applying deployment functionalities in Visual Studio. The tabular model could be affected by Tabular Editor working in direct connection to the model or through any local BIM files. The tabular model can directly integrate with GIT or Azure DevOps, and rigid version control can be maintained this way. This can also be integrated into deployment pipelines.

However, this approach isn’t just limited to using Azure Analysis Services. You can actually extract the BIM file using Tabular Editor. There is no direct integration with Power BI Desktop, so you might have to extract the BIM file and find another programme to check the merge and control the changes. Still, if your project really needs version control without AAS, this could be the way to go.

What Could Go Wrong?

Even when taking special care, problems can still occur while multiple people work together on a single report. What can be done? In my experience, there are a couple of best practices which reduce the risk. Having a defined naming schema and folder structure for measures, backups of previous versions (whether it’s the PBIX, a record of measures extracted using the VertiPaq Analyzer, or the BIM file), and good documentation of the development process (to identify when something was changed) will be good work habits in any project.

However, the biggest headaches come from changes in modelling or naming conventions. In cases where DAX measures are no longer working due to a large number of changes, it could be worthwhile to use the ALM toolkit or the advanced scripting functionality of Tabular Editor to copy or edit the measures. Advanced scripting allows you to programmatically make changes to your DAX code, so if the problem is that a column or table name has changed, it can be quickly fixed with the code running through all measures and while making text replacements.

Final Thoughts

Working together in Power BI should be simpler, and hopefully, soon it will be. It really boils down to what makes sense in terms of dividing the work and managing time in the most efficient way. The best tools available are the external ones I mentioned in this post: the ALM toolkit and Tabular Editor, which should be considered in any project for the best results. Finally, once the project has been completed and you want to distribute the report, you can choose from a few efficient ways of sharing Power BI reports with end users.

2988 HC Digital Transformation 476X381
Injae Park BI Tech Lead

A Swedish-Korean Business Intelligence Technical Lead specialising in Power BI and Azure. Recently honoured with the Microsoft MVP recognition, Injae is an avid contributor of Power BI content on platforms like YouTube and LinkedIn. 

See all Injae's posts

Related posts

You might be also interested in

Contact

Start your project with Objectivity

CTA Pattern - Contact - Middle