9 Lessons I Learnt About Managing Power BI Performance

Introduction

I am the lead Power BI developer for a large reporting project for a global organisation. The project started back in October 2020, went live after 6 months, and continued to grow from then on. To date the team has delivered a single Power BI dataset with over 1bn rows of data, across 100 tables, 700 columns, 3000 measures, 200 partitions, around 90 mostly tabular reports, to a user base of 700. 

Managing Power BI performance has been a massive challenge, with a growing business appetite to load more data and to serve more users. I'd like to share some of the lessons the team and I learnt along the way. 

Lesson 1: Power BI performance is a set of small incremental changes that work together to reduce model refresh time and increase report performance.

As a general rule, no single change will makes multiple performance issues disappear. It requires a concerted effort to tackle the data model and reports from many different and varied angles. Many of the techniques are completely independent of each other, but when combined become a powerful weapon to defeat performance issues.

A Power BI performance improvement strategy should be treated as an ongoing part of the development process and not as a one off task. There is no finite end point while the model and reports are still being developed or enhanced; changes can be major, for example an upgrade to the Power BI service, or minor, a single change to a single measure, but any change can negatively impact performance.

Lesson 2: There is no silver bullet

It is possible but very unlikely that a single change will remove multiple performance issues, though a single change may fix a single issue. Performance issues tend to emanate from a collection of sometimes unrelated problems, from sub-optimal model / report design, to a lack of understanding of how Power BI and the underlying engine works, and often poor quality DAX statements that may work but don’t necessarily perform well.

Lesson 3: There is no silver bullet (unless you find a werewolf)

To contradict my previous statements, there are times where a single change has improved overall performance, for example: 

• The shift from Power BI Premium Gen 1 to Gen 2 was a step change; the change from having a fixed pool of memory for all models hosted on a single capacity to each model getting its own allocation of the full amount of memory (and other related architectural changes), provided an instant performance boost; reports that previously would not even render, suddenly came to life. 
• Limiting large dimension tables to only the required values, to match the facts, tended to have a positive impact across multiple reports where the dimension was a commonly used e.g. Product.

 However as I previously said, these instances are few and far between.

Lesson 4: Maintain a balance between report and refresh performance

Try to avoid prioritising report performance over refresh performance, it is important to have a balance between the two. If you prioritise report performance you could end up with dedicated flat tables for every scenario and a model that takes hours to refresh, which would generally be considered impractical.

Conversely a super-fast refresh might not include the extra aggregated versions of the data, required to ensure reports and calculations perform to user expectations.

Lesson 5: Large tables (tens and hundreds of millions of rows) are unforgiving

In the case of a small report with low data volumes, you can get away with poor design, in many cases no one will know the difference. Large data volumes are a different ball game and you will be severely punished, for the wrong design, with terrible report / model performance.

A single poorly built report can max out CPU (capacity threshold) on even the largest Premium Capacity available, where the model contains millions / billions of rows of data; this overload can lead to an additional 20 second delay for users that Power BI imposes, resulting in pure pain for everyone involved.

Lesson 6: Be prepared to argue the case for changes you believe in

There are many times I've put forward the case to use new features, sometimes while still in preview, to solve critical performance challenges, (Gen2 Premium and even the recent Field Parameters functionality are examples) or argued against proposed changes to the solution, that might be detrimental to performance.

I am firm believer that being passionate about your work, is a good thing; harness that energy to achieve the desired outcomes when building the case for change, especially when protecting performance gains.

Lesson 7: Performance management is a series of big and small wins, against a continuous flow of challenges.

In a large project, change requests / bug fixes are constant. Couple this with aggressive deadlines and performance is bound to reduce at some point, along with a build up of technical debt. It is important to monitor performance regularly, and clear down that technical debt; it's like the gunk that builds up inside a engine that stops it operating at optimum efficiency, it has to be taken apart and cleaned from time to time and maintained regularly to ensure smooth operation.

Lesson 8: The next major problem is always one single change away.

It is important to understand that in a large complex model with many dependencies, a single change can break the model / reports, or cause performance issue across multiple measures, and therefore multiple reports.

Changes should never be made in a silo, there has to be an awareness of the impact of that change on other components within the model and reports, as well as on performance. Developers often look at changes in the limited context of their own sprint goals, and not at how the changes sits in the overall data model and the effect those changes might have on other reports they are not working on.

Lesson 9: Be bold and take risks – never accept the norm

Power BI is still an evolving product. Each month presents new opportunities to re-evaluate how existing reporting solutions are built and how newly released features can be applied to fix existing issues, or simply improve the solution. It does require a level of risk embracement and self-motivation to move solutions forward, to keep them working optimally and reap the benefits that change can bring. Having a strong advocate for Power BI and for change is critical to making all of these things happen.

-----

I hope this article goes some small way to helping understand the challenges enterprise Power BI solutions present and the mindset required to keep them performant.