Performance of your Power BI reports is incredibly important.
Users are far less likely to engage with reports that are slow to load, or where the data is outdated.
This can result in a lot of effort being wasted as the your end report becomes underutilised.
To avoid this, we should always think of performance implications at each step of the way, and build datasets and reports that are prepared to scale.
This post will serve as a starting point for exploring the options available when optimising your reports.
It will link to several other posts that will go into further detail on specific points.
Measuring performance
Before jumping into specific performance issues, we should first cover how performance is measured for Power BI reports.
The main two metrics for Power BI Performance are:
- Dataset Refresh time
- Report Query response time
In other words, how long is my dataset taking to refresh, and how long does it take for the visuals to load in the reports.
These are both things that we want to minimise where possible.
Dataset Refresh Time
A performant dataset refresh can be crucial to delivering the right data at the right time.
If users are expecting to see data for the past day (or hour!), the data must reliably refresh often enough to service that need.
Refreshes that span over multiple hours not only block users from having an up-to-date view of their data, but may also be impacting other operations on the data source.
Report Response Time
Report performance is arguably even more critical.
As a report takes longer to load, its interactivity sharply declines.
Research has shown that page load times have a huge impact on content engagement across all tech platforms, and reports are no different. Users don’t want to wait around – slow applications are often ignored or abandoned. It’s important that your reports are minimising any latency and providing a smooth and interactive experience.
When considering performance in Power BI, we will target improvements for either (or both) of these metrics.
Some improvements are straight-forward whilst others may incur some form of tradeoff.
It is a good idea to have targets in mind for these metrics when beginning performance tweaks so that you can weigh up the impacts of any change more effectively.
Performance Optimisations
The advice provided below will generally apply to all models – however, if you do expect your model to be particularly large (for example, containing tables of 100M+ records), then do make sure to review the Large Models section, which explores some features specific to that use case.
Before Development
The topics in this section should be considered before you even open up Power BI Desktop.
They will be fundamental to the design of your reporting solution.
You should take some time here to consider the right approach for your situation.
Trying to change stance later on often leads to excessive rework and from experience is usually better starting again from scratch.
- Data Modelling (coming soon)
- Choosing Storage Modes (coming soon)
For large models also consider:
- Aggregation Tables (coming soon)
- Incremental Refresh (coming soon)
During Development
The following topics are typically dependent on the decisions made above, and can be addressed during development.
- Optimising Import Tables (coming soon)
- Optimising DirectQuery Tables (coming soon)
- Optimising PowerQuery Transformations (coming soon)
- Optimising Model Relationships (coming soon)
- Optimising DAX Objects (coming soon)
- Optimising Visuals (coming soon)
Tools
There are several tools required for effectively troubleshooting performance issues in Power BI.
Some are built into Power BI Desktop and others must be installed separately.
They will be frequently referenced throughout the posts.
Below you will find some basic overviews of the tools and their use cases.
- Performance Analyser (coming soon)
- DAX Studio (coming soon)
Footnote
Naturally this content will require frequent updating to make it complete and keep it up to date.
Therefore this page and subsequent pages will be a constant work in progress.
They will be updated as necessary and when possible. Feel free to stay tuned.