Find out why having strong version control is fundamental to data governance, especially in a multi developer project.
One of the most important aspects of the software development life cycle is to have control over different versions of a solution, especially in a project where there is more than one developer involved in the implementation.
Just like when you normally create a project in visual studio and you commit the changes back to a source control system like GitHub or Azure DevOps, it’s advised to keep the history of different versions of your Power BI reports. What we expect from a source control solution is to keep tracking of all changes happening in the source code while developing a project. So you can easily roll back to a previous state if you like to.
The other benefit of having a source control process in place is when multiple developers are working on a single project. Every single one of them makes changes in the source code then they commit all the changes into the source control server without overwriting each others’ work.
With Power BI things are a bit different though. Power BI report files are PBIX files which are stored in binary format (well, PBIX is basically a zip file isn’t it?) which at the time of writing this post, there is no official way to enforce Power BI source control in any source control solutions like GitHub or Azure DevOps (YET).
Microsoft announced a fantastic feature last week (6/05/2020) named “Deployment Pipelines” which does exactly what we’re after, but it is currently a preview feature which is only available only to organisations with Power BI Premium. So it is out of the game for the majority of us.
Having said that, there is still a way to keep history of changes in the shape of different versions of PBIX files. This is called version control.
There are several ways you can enable version control over your PBIX files while developing the report. Regardless of the version control platform you need to think about having multiple environments and who can access them for doing what.
| Environment | Accessible to | Description |
|---|---|---|
| Development | Developers | Data modellers and report writers access this environment for development purposes. |
|
User Acceptance Testing |
Developers, SMEs, Technical Leads, Power BI Admins | After the development is finished the developers deploy the solution to the UAT environment. The solution will then be tested by SMEs (Subject Matter Experts) to make sure the business requirements are met. |
| Pre-prod (Optional but recommended) | Technical Leads, Power BI Admins | After the solution passed all UAT testing scenarios Technical Leads or Power BI Admins will deploy it to Pre-prod for final checks to make sure all data sources are correctly pointing to production data sources and all reports and dashboards are working as expected. |
| Production | Technical Leads, Power BI Admins, End Users | After pre-prod checks completed Technical Leads or Power BI Admins deploy the solution to the Production environment which is then available to the end users. |
If your organisation does not have a Premium capacity then “Deployment Pipelines” feature is not available to you. So you need to come up with a solution though. In this section I name some Version Control options available to you
In the absence of an official source control mechanism, the inherent source control capability of “One Drive for Business” can be used for version control. It would be good to create a project directory, then four sub-directories, one for each environment. We then copy files to each project directory in relevant environment directory. We then share the project directories and their sub-folders with relevant people.
Tips:
Restoring an older version is very simple. The user with the correct level of access needs to login to “OneDrive for Business” online, navigate to the corresponding directory, click on the ellipsis button on the right side of the file then click “View History”.
A “Version History” page shows a full history of changes in the file. You can now click on the ellipsis button of an older version then click “Restore”.
One of the coolest features of using OneDrive for Business in conjunction with Power BI Service is that you publish the report just one time, then Power BI Service keeps the reports undated directly from OneDrive for Business. This means that you really don’t need to republish the reports several times. You just make the changes to the file and save it. By default, OneDrive updates files every hour, you can select to keep the files up to date from the dataset settings.
Tip:
After publishing the file to Power BI Service navigate to Dataset settings and check that “OneDrive refresh” is enabled. This is enabled by default anyway, never the less, it is good to know where to find it.
Note:
It is important to know that the OneDrive refresh is different from Schedule refresh. When we connect to a Power BI report file (PBIX) stored in OneDrive for Business from Power BI Service, whenever we edit the PBIX file in Power BI Desktop and save the changes back to our OneDrive for Business, the connected dataset (data model) and report will be automatically refreshed if we setup the OneDrive refresh. So, if we added a new measure to the model, the change will be propagated to the connected dataset in the Service. This is DIFFERENT from the Schedule refresh setting or when we manually refresh the dataset from the Service. If we require the dataset to be refreshed automatically from the connected data source(s) then we need to setup the Schedule refresh. In this case the dataset from the Service will get data directly from the underlying data source(s) which means the data refresh process has nothing to do with the PBIX file stored in our OneDrive for Business.
While this method works okay in some scenarios, it is good to know about its limitations. So here are some pros and cons:
You can use either Microsoft Teams or SharePoint Online for version control.
Note:
If you enabled Block classic workspace creation setting within the Power BI Admin Portal, then you already prevented generating classic workspaces by creating Microsoft Teams team. Therefore, we need to create a team in Microsoft Teams separately, create a modern Workspace in Power BI then link our modern Workspace from Power BI to the Teams team. You can check if the Block classic workspace creation setting is enables as below:
)
Now let’s have a look at Teams. We now create a new Teams team per environment, add users to the new team and upload your PBIX files.
At last you publish the reports directly from Power BI Service.
The new Teams team appears as a “Classic Workspace” in Power BI Service, you can then upgrade the workspace to the new workspace experience to leverage new features and improved security. If you desire to do so:
Tip:
Do not forget to change the data sources in Power BI Desktop to point to the correct data source environment.
For more convenience and avoid downloading the files and uploading after you make changes you can simply synchronise your files so you always have a local copy of it in your hard drive. So you can open the PBIX files directly in Power BI Desktop to make some changes and save it back to your hard drive. Just like OneDrive, your changes will be written back to Teams.
When using Microsoft Teams you have the ability to check-out a file. To do so follow the steps below:
The file will mark as checked out. So this file is now only available to you.
You and now open the file in Power BI desktop to make the changes (through the synced directory) and save the file.
Now from Teams:
Currently you cannot restore an older version of the file directly from Microsoft Teams. However, you can do so from SharePoint.
There are some other features available from SharePoint Online. In this section we look at two of them which are more relevant to version control and governance.
You can setup alerts so you are notified as soon as a file is changed. In SharePoint Online:
If you already have some security and compliance policies in place you can apply some of those to your files in SharePoint Online. For instance, you can apply “Retention” policy to the files so if someone accidentally deletes a file you can later recover that file from “Recycle bin” folder. Or you may have a policy to totally prevent file deletion. The files that are protected by retention policies remain in SharePoint for a certain period of time. For instance in the below example the file retains for 7 years, after that time it must be audited by an admin before deletion. To set add retention labels to the files follow the steps below:
Read more about Retention Policies here.
While this method works really good for collaboration, but it currently has some limitations. Here are some pros and cons:
Tip:
In the above scenarios the process of promoting files from dev directory to UAT and from there to Production can be automated using a combination of tools such as Power Automate and Approval apps available in Office 365 which is out of scope of this post. It needs a dedicated blog post.
This post explains three best practices for Power BI governance: multiple environments, data source certification and documentation.
From data sources, to ETL and your semantic layer - learn how key BI components come together in Power BI.
Find out why using a shared dataset allows organisations to have a single source of truth with the help of thin reports.