Pre-loader

Power BI Cleaning

24 November 2021

.Net Core

So… I’ve been looking into an issue for a client. The premise for the project is an embedded Power BI report in a Portal. The platform and report itself have been stable for many years, with plenty of feature enhancements and tweaks. The amount of data it reports on is quite large and the tables are quite wide. A mixture of “more” data since the product was launched and redundant transforms on creating columns and measures for previous enhancements had taken its toll on the refresh process. Essentially the server was running out of memory and not able to refresh the data.

Power BI, whilst immensely powerful, does not have tools that come out of the box to find unused columns and measures. I came across several articles, however one in particular I found proved particularly useful.

The beauty about this article(s) I found is that the author, Imke Feldmann, wrote another Power BI application “Power BI Cleaner tool”, that analyses what is used and what isn’t, which measures reference which columns, the cardinality when it comes to compression. Quite an impressive piece of kit.

Now, I said article(s) in plural as he actually wrote a few articles. I can see Imke’s journey enhancing his tool, my heart really goes out to how active the community is. Regardless, I had to work off an older version in the end. There is a known issue in the latest version that struggles with special characters in some structures in Power BI reports that the author is looking into. If you are looking to try the cleaner for yourself I suggest trying Power BI Cleaner Gen 2 first. If you have issues, use the Gen 1 version, it is still excellent. I’ll be continuing talking about my experience with Gen 1 below. However, here is a link to the two articles.

Gen 2:
Power BI Cleaner Gen 2

If the above fails, Gen 1:
Power BI Cleaner Gen 1

 

Cleaner tool Gen 1 crash course

As I mentioned I suggest trying Gen 2 first. If you are and it’s up and running, skip to the analysis and tips sections below. Otherwise follow me on the crash course to get started with Gen 1:

  1. Open your report
    1. Ignore Privacy Levels (File -> Options and Settings -> Options -> Current File -> Privacy -> Privacy Levels -> check “Ignore the Privacy Levels….”)
    2. Close report
  2. Download the cleaner file here: PowerBICleanerV11_upload.zip (After download, rename the xxx.zip to xxx.pbix to open the file in PBI Desktop)
  3. Install DAX studio. This will plug into Power BI. Ensure you have no instances of Power BI running!
  4. Open your Power BI report you want to work on:
    1. Save your report as a template file (with the extension .pbit). This file is required for the cleaner tool.
    1. Open DAX Studio using the external tools menu.
    1. Export Metrics from dax studio. This will give you a VPAX file.
  1. Open the Power BI cleaning tool we downloaded earlier “PowerBICleanerV11_2_p.pbit”.
    1. Put in the saved template file you saved from your report. Double check your using the pbit NOT the pbix.
    2. Put in the VPAX file exported from DAX studio.
    3. Both surrounded by quotes.

 

There were some occasional quirk getting Gen 1 running. Occasionally when Power BI cleaner is opening it will error due to a cyclic referencing error. My workaround was to try again.

 

The Analysis

After you have identified the columns you want to delete. I suggest you go into the transform and think about the following:

 

Set WhereUsed to blank.

Now you have the tools to identify quickly which columns and measures to delete.



Tips

 

After you have identified the columns you want to delete. I suggest you go into the transform and think about the following:

  1. Do I need to load the column at all? I.e. remove column on load.
  2. If the column is used as part of the transform, delete the column at the end of the transform.
  3. Don’t accidentally delete a column you might actually need.
    1. You might be looking at the point and thinking.. Well duh.. When I got into it I was deleting everything I could get my hands on. In Power BI embedded you can enable an option to export the underlying data. Clients might want access to these fields. I forgot about that minor detail at the time.

 

The results

After I was finished I managed to cut the size of the Power BI file by 40% and deleted about 30 unused measures. Maintenance on the project will be much easier without that “bulk”. The report refreshes as normal now. The report itself, the responsiveness feels faster, resulting in a more positive user experience.

I hope you found this article useful. If you need any help or have another tool you’d like to share with me, please let me know. Send me a message on LinkedIn, I’d be interested to hear from you.

Track Metrics are experts in discovering insights in your data and are geared to creating a scalable business intelligence platform for you. We are interested in helping you, please get in touch if you want some help.

Have a great day!