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 Test (UAT) | 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. |
Version Control Options
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
- OneDrive for Business
- Microsoft Teams/SharePoint Online
Version Control with One Drive for Business
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:
- It is recommended to have separate Workspaces in Power BI Service for each environment. After copying PBIX files to the next environment, the reports must be published to the relevant Workspace environment in Power BI Service.
- Data connections must to be switched to the correct data source when deploying from Dev to UAT.
Restoring an Older Version
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”.
Keep the Reports Updated After Publishing to Power BI Service
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:
- DO NOT publish the reports to Power BI Service from Power BI Desktop. Instead, get the report directly from Power BI Service from your OneDrive for Business. Otherwise you lose the automatic file updates feature in Power BI Service.
Publishing the Reports From Power BI Service
- In Power BI Service navigate to a workspace
- Click “New” then click “Dataset” (you can also click “Get Data” at the button left of the page)
- Click “Files”
- Click “OneDrive for Business”
- Click a Power BI report file (PBIX)
- Click “Connect”
Setting Auto Update on the Dataset
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.
- Click the workspace
- Hover over the desired dataset
- Click “Schedule refresh”
- Expand “OneDrive refresh” to see if automatic refresh is enabled
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.
Pros & Cons
While this method works okay in some scenarios, it is good to know about its limitations. So here are some pros and cons:
Pros
- It is easy to use
- Easy to restore older versions
- Leveraging dataset automatic refresh from OneDrive
Cons
- It is not a proper source control supporting code merge and branching strategies
- Commentary is NOT currently available, so you cannot put comments on each version to explain what have changed
- The sharing strategy is controlled by the user who created the file not by the organisation
- Lack of check-out/check-in
- Lack of alerting
Version Control with Microsoft Teams/SharePoint Online
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:
- After logging into Power BI Service with a Power BI Admin account
- Click the Settings button from top right (
- Click Admin Portal
- Click Teant Settings
- Under Workspace settings section expand the Block classic workspace creation and see if this is enabled for the entire organisation
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.
Sync with your Local Drive
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.
Check-out/Check-in
When using Microsoft Teams you have the ability to check-out a file. To do so follow the steps below:
- Navigate to a desired Teams team
- Click “Files”
- Hover-over the file and click ellipsis button
- Click “More”
- Click “Check out”
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:
- Hover-over the file
- Click ellipsis button -> More -> Check in
- To check-in the changes you need to put in some comments about the changes which is really helpful
Restoring an Older Version
Currently you cannot restore an older version of the file directly from Microsoft Teams. However, you can do so from SharePoint.
- In Microsoft Teams click “Open in SharePoint”
- Over-over a desired file -> click ellipsis button -> Version history
- select an older version of the file and click “Restore”
Other SharePoint Online Features
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.
Alert me
You can setup alerts so you are notified as soon as a file is changed. In SharePoint Online:
- Hover-over a file
- Click “Alert me”
- Setup an alert
Compliance details
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:
- Open the folder in SharePoint Online
- Hover-over a desired file
- Click ellipsis button -> More -> Compliance details
- Click “Label Status” hyperlink
- Apply a label from the dropdown list
- Click Save
Read more about Retention Policies here.
Pros & Cons
While this method works really good for collaboration, but it currently has some limitations. Here are some pros and cons:
Pros
- It is easy to use
- Commentary is available in this method
- While it is integrated with SharePoint Online sharing strategies can be governed by SharePoint
- Check-out/check-in functionality
- Setting up alerts
- Applying retention labels
- Leveraging dataset automatic refresh from OneDrive
Cons
- It is not a proper source control supporting code merge and branching strategies
- Not all features directly supported within Microsoft Teams. It is sometimes quite confusing to switch to SharePoint Online to do some activities like restoring an older version
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.
Have you ever used any of the above methods? Are you using another approach? Please share your thoughts with us in the comments section below.
