24 November 2021
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:
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:
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.
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.
After you have identified the columns you want to delete. I suggest you go into the transform and think about the following:
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!