Speeding up your Power BI report
Data Power BIThe problem
Power BI reports can be revolutionary, with easy access to data, a high level of interactivity and up to date information.
Except, like any application or report, users can dislike Power BI. One common reason is slow report loading times. A UX rule of thumb states 10 seconds is the maximum delay before a user’s attention drifts. But even delays of a few seconds are an unpleasant experience.
As the Power BI report loads, users switch their attention from what information they were interested in, to the act of waiting for information. At this point Power BI does not feel user-friendly and responsive – users feel like they don’t have control over the system.
For these reasons, it is vital that your Power BI report has visual loading times averaging 0-3 seconds.
Power BI can cope with millions of rows and still perform fast calculations. So, the good news is that 99% of slow reports can be fixed.
The fix
1. Optimise inefficient DAX
For a recent client, I reduced visual load time from 25 seconds to 3 seconds. An 88% reduction in load time.
The culprit in that case was inefficient DAX. For smaller amounts of data, DAX is still fast even when not optimised. As the amount of data grows to millions of rows, the inefficient DAX leads to slower and slower load times.
2. Refine your visuals
Inefficient DAX is one of several reasons why your Power BI report may be slow. Another common reason is too many visuals on your report page.
You may feel each of the visuals in your report is essential for the user to understand the data. It’s hard to remove a visual you’ve carefully designed! But I can guarantee if you have over 8 visuals some are unnecessary noise slowing down your report. This is the point at which a user experience (UX) expert can really make a difference.
A UX designer will help refine your visuals to tell a concise story with additional details available through simple one-click navigations. And the second major benefit – your report page will load significantly faster with fewer visuals on the page.
3. Optimise your data model
A third reason for slow report load times is one of the hardest to fix – the data model.
Power BI is optimised for a star schema model, with many dimension tables explaining a fact table. Easier said than done – you may have multiple fact tables, dimensions and facts combined on one table. To build an optimal star schema in Power BI with complex data requires training and hands-on experience working with different models.
4. Reduce the size of your model
Are you using all your columns in your data model, or are some there just in case? What level of granularity is your model, and how often do you look at that level vs a more summarised version?
The answer to these questions can help guide you in reducing the size of your data model, because at the end of the day less data means faster visual load times. Deciding what data to keep or summarise is a difficult decision. An experienced analyst can help map out your end-user requirements.
Conclusion
Slow report load times are frustrating. The good news is they can be improved. I recommend starting with the easier fixes first – refining your visuals and cutting extraneous data from your model. Then try and optimise your DAX and data model. Once your report is faster, your users will start to appreciate the interactivity and intuitiveness of Power BI.
