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:
Essentially, Power BI Performance Analyzer will provide you with a list of how long it took for each visual to update, measured in milliseconds (ms). You can then drill down even further to see how much of that time was due to the visual update versus the time it took for the related DAX query to run. You can even copy the DAX query to better understand it and potentially optimize it as well.
Once you are done interacting with the report and have accumulated a decent amount of metadata, you can click on “Stop recording.” From this point onwards, you can export the metadata in JSON format to investigate further. If your resulting JSON is too complex, you could also write some custom Python code to parse and sort the data according to your liking. The JSON file is stored locally on your PC for your analysis, as shown below:
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 have had a look at the Performance Analyzer data and figured out what visuals or data are causing problems, your next step would be to figure out how to resolve it. Depending upon your use case and the problems you discover, there could be lots of potential solutions. These could be as follows:
- 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
As we have explained in our blog, Performance Analyzer in Microsoft Power BI is a nifty utility that developers can use to figure out what is causing performance detriments before they decide to roll it out to stakeholders. It provides you with information about performance at a very granular level, so you can consider it as an essential utility in investigation activities.
That said, there is definitely room for improvement when it comes to Performance Analyzer. For one, the lowest granularity level of metrics that it offers is visuals. This means that if your visual is being slowed down only by a couple of custom columns stored in it, you will not be able to identify this just by looking at the output of Performance Analyzer. The tool will inform you as to which visual is causing the slow-down. But it will not let you drill down to a columnar level, the lowest level of granularity is the visual itself. It does offer you the query it is using to refresh the visual, but investigating that is a cumbersome and tedious process.
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), which is considerably more user-friendly in terms of readability.
Irrespective of these drawbacks, there is no doubt that Performance Analyzer in Microsoft Power BI Desktop is a very handy utility that you can leverage if you are unsure why your report is not as fast as you would like it to be. The fact that it is a free tool that is built-in in Power BI is just icing on the cake.