Source Control for Power BI Background
I follow an amazing person in the BI community, Eugene Meidinger. Eugene has a weekly newsletter called “The Weekly BI Five” that includes 5 links to blogs, software, communities and anything in-between that he came across recently that he though would interest us subscribers (if you’re interested in checking out the newsletter, check out his website here: https://www.sqlgene.com). In his recent newsletter for March 29th, he mentions a tool built by Mathias Thierbach.
The tool is called pbi-tools and is a command-line tool that enables source/version control for Power BI reports and datasets. It’s not a perfect tool as it seems to be in its infancy stages, however from my tests it shows a lot of potential and I thought I’d walk you through what makes it so powerful and also demo it using an example. I will also check-in the tutorial into my GitHub page if you want to take a peek at the repository.
Git Commit
First off I would like to point out that you will need to download the fully-featured version of pbi-tools vs the light weight version to follow along with me. I made the mistake of downloading the light weight version and only realized down-the-line that it doesn’t include the extract feature (which we will use soon).
Tool Actions
Pbi-tools has a series of tool actions that makeup the toolkit. Without going into detail for all of them, I’ll touch on the main actions that I find particularly interesting and then demo them.
Extract
The extract tool action provides you the capability to extract the contents of a PBIX/PBIT file into a folder structure that is optimized for source control. The output that I get after running the extract on a report will look something like the screenshot below for a PBIX file.
Compile
The compile tool action generates the PBIT and PBIX files from the file sources. Something to note, PBIX files are supported for report-only, whereas PBIT support files that contain a data-model.
Export-data
The export-data action exports all tables to CSV for offline PBIX files or a running Power BI Desktop session.
And more…
You can read about the remaining 7 action tools here.
Prerequisites
- Windows 64-bit
- Power BI Desktop 64-bit from the Windows Store or via the installer from the website
- .Net Framework Runtime 4.7.2 (this should already be installed as this comes with Power BI Desktop)
Console.Log(“Logs are for cool kids”)
You can also control the log outputs using the environment variable PBITOOLS_LogLevel. From my research digging into the source code (and also noticing the familiar log levels) I realized that this project uses Serilog (a well known logger library) for its logging. Below are the log levels and the descriptions for each from Serilog’s documentation.
Level Usage
| Level | Usage |
| Verbose | Verbose is the noisiest level, rarely (if ever) enabled for a production app. |
| Debug | Debug is used for internal system events that are not necessarily observable from the outside, but useful when determining how something happened. |
| Information | Information events describe things happening in the system that correspond to its responsibilities and functions. Generally these are the observable actions the system can perform. |
| Warning | When service is degraded, endangered, or may be behaving outside of its expected parameters, Warning level events are used. |
| Error | When functionality is unavailable or expectations broken, an Error event is used. |
| Fatal | The most critical level, Fatal events demand immediate attention. |
Lets implement
Here’s the situation, you have a team of report developers that are constantly building and adding new features to your companies internal and client-facing reports. In our case, the “company” report we are going to track is Adventure Works DW 2020 sample model provided by Mathias in his GitHub repository.
First, we want to compile the sample model into a PBIT file to be able to view and modify in Power BI Desktop. To do this, run the following command against the sample.
.\pbi-tools.exe compile -folder [SOURCE_FOLDER]\pbix -format PBIT -outPath [OUTPUT_FOLDER]\AdvWorksDW2020.pbit
Once we have opened our PBIT file, we will start making modifications to it to be able to view how the changes are tracked when diffing the source and final output extract files. I then also save the PBIT template as a PBIX file. Below, I’ve made an overview of the changes I made to my final PBIX file.
Report View
Page 1: Introduction
- Modified text visual to add “Modify” string
- Added card based on Date.Date
Page 2: New (added)
- Added custom visual (Advance Card) to page linked to Date.Month
Data View
– Date.Date => Formatted from *March 14, 2001 (Long Date) to 3-14-01 (m/dd/yy)
Data Model View
- Disable Sales and Date relation (Sales.OrderDateKey – Date.DateKey)
- Unhide ResellerKey in Reseller table
- Add new table called Random, two columns called Hi and Whats-New with the following rows:
| Hi | Whats-New |
| Text | 1.0 |
| Sell | 1415.00 |
Once my changes were completed, I saved the file as a PBIX file and ran the extract action on the PBIX file.
.\pbi-tools.exe extract [SOURCE_FOLDER]\AdvWorksDW2020.pbix
The final step is to compare the original source to the extracted output files. The aim here is to view what changes occurred and if I can make out if the changes I made in the final PBIX reflect in the extracted files. From my examination (and I encourage you to examine and test these findings yourself), I was able to see all my changes that I made to the Power BI report, which is certainly promising for an initial test.
Pondering…
We have barely touched the surface with this tool. And there are a number of questions that come to mind when it comes to pbi-tools and using it for as a source control tool for Power BI. Below are some questions that I still need to answer/test before I feel comfortable using this tool with clients and in production.
- How complicated is it to merge changes and/or conflicts?
- Are there any scenarios (apart from sensitivity labels) that aren’t captured when extracting with pbi-tools?
- How does pbi-tools handle larger PBIX and PBIT files?
- Are there any sources that won’t be captured when extracting the PBIX/PBIT file?
- Can you rely on support from version-to-version of the tool? So for example, will you run into an issue where you used v1.0 of the tool to extract a Power BI report and want to compile the report files using newer iterations of the tool such as v2.0 or vX.0, etc.?
- What happens if you corrupt the extracted files and how easy is it to fix? I’m thinking of cases where someone manually edits files (this can be easier to catch due to versioning) or due to file encoding issues.
Wrap it up!
This list could go on-and-on, but these are just a few questions that come to mind for me right now. Let me know what you think.
How do you feel using a tool like pbi-tools for Power BI source control? Do you think there is a need for source control for Power BI? Are you currently using some type of source control for the Power BI reports in your company?
Until next time! ✌️