Version Control / Quality Control Best Practices

[mpgoggin]

Hello Power People,

What's the best practices around version control for PowerBI? In particular:

- How can i verify what items (e.g., tables, measures, columns, pages, visualizations, calculated measure/column definitions) are in one file versus another? Essentially, I'm looking for a diff tool/process.

- How can I verify where a table/measure/column is used (i.e., what report pages)? Or if the item is unused?

I'd like to remove unused/obsolete calculated measures/columns. Also, it would be nice to know what measures/columns I can hide.

Thank you in advance for your sage advice,

Michael

[TomMartens · Accepted solution]

Hey @mpgoggin ,

unfortunately, there is no simple answer. Until now, there is no built-in solution for versioning. If your pbix files are thin reports of course, you can use tools like Azure DevOps or git for the versioning. But as a pbix is binary, you can not use these tools for comparison. Depending on your needs, you can also consider storing your pbix inside a Sharepoint library and use the check-in/check-out mechanisms, but of course, versioning requires some kind of discipline. Also, you can use onedrive for business, as it also provides versioning of a file, meaning - if you break something, you can restore a pbix. This depends of the settings made by your organization, depending on the size of the artifact, the number of versions can be limited.

Comparing artifacts is also not built-in. Nevertheless, comparing datasets is quite simple by using the ALM Toolkit: Home Page - ALM Toolkit (alm-toolkit.com).
Comparing content, meaning comparing data visualizations is really hard. I started out with using the c# script from here: Export Power BI Report Objects (elegantbi.com) The script extracts data and provides information about what visualization are used, what data objects (columns and measures) are used inside the visualizations, and a lot of more things.

There are also  tools available like the

• Power BI Documenter: Power BI Model Documenter v2 – Data – Marc (data-marc.com)
• The Power BI Helper: Power BI Helper – A Power BI Tool That Helps!
• The Power BI cleaner: Measure Killer | Brunner BI

The order of the above tools does not reflect my preference, currently I/we are using none of them, maybe this will change or we creating our own solution.

Hopefully, this provides some ideas and gets you started.

Regards,

Tom

[mpgoggin]

Thank you for recommendations! I'll have a look soon. I definitely need a strategy / process to manage and control between versions and even just to better understand what is in a particular version. I appreciate your time spent answering this question.

Regards,

MG

[Anonymous · Accepted solution]

The Power BI Admin API has data about which datasets are using which queries and DAX measures, but I don't think it includes calculated columns. If you have the engineer resources, you can ETL it into your data warehouse and report on it. It has the full Power Query text, so you can search that for specific things. For example, we use this to check to see if/which reports are using specific database tables. It's hit or miss for columns, though, because depending on how the query was written, the column name may not appear in the actual query text (e.g. if you pull all columns and then remove specific ones, the columns you're using from the source table won't actually be referenced).

I don't believe it'll give you specific info about the tabs of reports or what visuals are being used... it's better than nothing though. 

Microsoft Purview is another option if you're looking for lineage info at the tenant level. 

[mpgoggin]

Thank you! Yes, ETL / Report might be an option.

Thank you for your advice and I hope to have a process soon for this that meets most of my needs. 

Of course, I would imagine that Microsoft should be on this for a native tool that helps a user understand what information is stored, how it is used, and how it DIFFers between versions. 

Regards,

MG

[TomMartens · Accepted solution]

Hey @mpgoggin ,

unfortunately, there is no simple answer. Until now, there is no built-in solution for versioning. If your pbix files are thin reports of course, you can use tools like Azure DevOps or git for the versioning. But as a pbix is binary, you can not use these tools for comparison. Depending on your needs, you can also consider storing your pbix inside a Sharepoint library and use the check-in/check-out mechanisms, but of course, versioning requires some kind of discipline. Also, you can use onedrive for business, as it also provides versioning of a file, meaning - if you break something, you can restore a pbix. This depends of the settings made by your organization, depending on the size of the artifact, the number of versions can be limited.

Comparing artifacts is also not built-in. Nevertheless, comparing datasets is quite simple by using the ALM Toolkit: Home Page - ALM Toolkit (alm-toolkit.com).
Comparing content, meaning comparing data visualizations is really hard. I started out with using the c# script from here: Export Power BI Report Objects (elegantbi.com) The script extracts data and provides information about what visualization are used, what data objects (columns and measures) are used inside the visualizations, and a lot of more things.

There are also  tools available like the

• Power BI Documenter: Power BI Model Documenter v2 – Data – Marc (data-marc.com)
• The Power BI Helper: Power BI Helper – A Power BI Tool That Helps!
• The Power BI cleaner: Measure Killer | Brunner BI

The order of the above tools does not reflect my preference, currently I/we are using none of them, maybe this will change or we creating our own solution.

Hopefully, this provides some ideas and gets you started.

Regards,

Tom

[csquared]

Hi Tom. You recommended ALM Toolkit in your post. I'm working with thin reports. I don't think I can compare differences in DAX measures across two different thin reports with ALM Toolkit, but please let me know if that is a wrong assumption. Bashing my head against the wall over here trying to identify differences in previous and current Power BI Report (thin) while code reviewing consultants work.

[TrevorC]

CSquared, no the ALM Toolkit only works on the semantic model. Comparing layouts and configuration of the UX component is not in scope for it. This has to be done manually.

Edit: the ALM Toolkit which was mentioned above does a great job comparing the semantic models of two files.
As for comparing the layouts, you can save a copy of your PBI file and change the extension to .zip. Then you can browse the contents. You may be able to do a file compare at this level for layouts but have never tried but seems possible. Finding the file that deals with layouts and visual config will be the trick.