You can’t deny the fact that Microsoft Power BI is an extremely powerful tool that goes beyond simple visualizations. It allows you to transform and manage data, perform complex calculations, and even build custom visualizations via third-party integration and JavaScript libraries. It also connects with a variety of heterogeneous data sources and empowers you to build robust data models.
Apart from these relatively traditional use-cases, the tool also offers tons of other capabilities that most developers and organizations never end up utilizing either because they do not know how to use them or they are simply unaware of their existence.
In this blog, we will walk you through one of these features, namely Performance Analyzer.
Why Is It Critical to Optimize Power BI Reports?
When a Power BI report runs slowly, it stresses memory consumption. Valuable resources are being consumed, and these are not available to other users and reports. As more users consume the report, more non-optimized performance will affect your users and organization.
When you are developing reports in Power BI Desktop, good performance is quite crucial in terms of load time for ensuring user adoption and gaining trust with stakeholders. Poor user experience and long load times deter users from adopting solutions as part of their daily processes.
What Makes Power BI Reports to Slow Down?
If you are building complex reports with lots of visuals, multi-million records, custom columns, and measures, there is a chance that you might experience performance detriments. For example, your visuals may update slowly or may take a long time to respond to slicer changes.
When building a detailed report containing the aforementioned complexities, many developers are unable to figure out exactly what is causing the report to slow down. They either tend to convey to stakeholders that this is an unsolvable problem or that it is just a limitation of the tool and that there is nothing they can do about it.
However, as Xavor has learned with experience with multiple clients and use-cases, this is rarely the case. It is mostly just a matter of report developers being unable to pinpoint the exact visual that is causing the negative performance impact and then subsequently having a look at its corresponding data. Fortunately, Microsoft Power BI offers a built-in tool called Performance Analyzer that you can use to aid your investigation.
How Does Performance Analyzer Help to Optimize Power BI Reports?
When you want to test the performance of a complex report, simply open the .pbix file in Power BI Desktop. Click on “View” in the top ribbon, and then on “Performance analyzer.” This will open a panel on the right side of your report. This can be seen in the screenshot below:
Click on “Start recording” and then start interacting with your report, change slicer values. Click on individual report elements, and so on. You will notice that the Performance Analyzer window will populate with some values, as shown in the screenshot below:
Power BI Performance Analyzer shows how long each visual takes to update, in milliseconds. You can drill down to see whether the time was due to the visual update or the DAX query execution, and even copy the DAX query for review and optimization.
After interacting with the report and gathering enough metadata, click “Stop recording.” You can then export the metadata as JSON to analyze further. If the JSON is complex, write custom Python code to parse and sort it. The file is stored locally on your PC.
You can also use this JSON as an input file for a Power BI report that visualizes your report’s performance, as described in this blog which builds on the public GitHub repository here.
Once you’ve reviewed the Performance Analyzer data and identified problematic visuals or data, your next step is to resolve it. Depending on your case and issues, there could be many solutions, such as:
- Optimizing the Data Model: Data reduction, DirectQuery optimization, hybrid model optimization
- Visualization Optimization: Understanding row-level security (RLS) and cache refresh, limiting visuals, optimizing custom visuals, applying restrictive filters
- Optimizing the Environment: Network latency checks, gateway sizing, capacity configurations
Some or all of these workarounds may be applicable to your report. Fortunately, Microsoft offers dedicated guidance for all these topics that you can check out here.
Conclusion
Performance Analyzer in Power BI helps identify performance issues at a granular level but only up to visuals, not individual columns. It exports data in JSON, which isn’t user-friendly. Despite limitations, it remains a valuable, free tool for troubleshooting report speed.
There is room for improvement in Performance Analyzer. It only provides metrics at the visual level, so if a visual is slowed by specific custom columns, you can’t identify this just by its output. The tool shows which visual causes slowdown but doesn’t allow drill-down to the column level, as the lowest granularity is the visual itself.
Finally, the results of Performance Analyzer can only be exported in JSON format, which not everyone is familiar with. It would be useful to have the ability to export this data as a CSV (Comma-Separated Values) or XLSX (Microsoft Excel) file, which is considerably more user-friendly in terms of readability.
Despite some drawbacks, Performance Analyzer in Power BI Desktop is a useful, free, built-in tool for diagnosing report speed issues.