Improving Power BI reports’ performance is not always easy and straight. Although Power BI Desktop provides native “Performance Analyzer”, the details given are not as rich as they could or should.
“Power BI Advanced Performance Analyzer by SmartPowerBI” is designed to take Performance Analyzer to the next level and provide clear insights and it is easy to use, for PBI Developers and BI Managers.
The use of “Advanced Performance Analyzer by SmartPowerBI” returns a full and clear report showing all performance affecting factors, recommendations specific to the magnitude of those factors and links to best practices and guidelines associated to such recommendations.
Power BI is a platform that can perform very well, and deliver reports that render fast, displaying complex insights over datasets of hundreds of millions of rows, if not more. However, there will be a few considerations and best practices to be applied so you can get the best performance that the combination of dataset, business requirements and technology can achieve.
This article is meant for Power BI Developers and BI Managers alike, so they can use a simple tool to gain immediate insights on what could be improved in their reports for better performance. In addition to this, it is also meant for Power BI Centres of Excellence or other IT teams that need to help their business to build faster solutions.
What is Power BI Advanced Performance Analyzer by SmartPowerBI?
It is a Power BI template that takes the .json file generated by Power BI Desktop native “Performance Analyzer”, and uses it to deliver clearer insights about different factors affecting reports’ performance – yes, that simple!
In other words, it is a Power BI Report that ingests, transforms, models, enriches and presents the .json file exported from a PBI Desktop native performance analyzer session.
But that’s not all, this tool also has a place to assign best practices and guidelines associated to specific recommendations, therefore this tool can:
- Produce immediately actionable insights.
- Ensure that best practices and guidelines are communicated so they can be applied…
There is a more important agenda to this tool though. As Head of Power BI Centre of Excellence, I face the challenge of supporting a business with thousands of PBI authors, and I cannot support them all directly. This tool has been conceived and created so I can help these authors to help themselves, through custom insights and best practices – this tool will make clear what factors are affecting their reports’ performance.
This tool has been built to help a very large community of Power BI Practitioners to build faster reports, and with this article and associated documentation, I am trying to make that community even larger.
For those eager to see the product in action, here is a link to a working live report (that I have used for most of the relevant screenshots in this article).
In this repository, you will find the following files:
- .Pbix file named “Advanced PBI Performance Analyser by SmartPowerBI”, that contains the connection to the .json file (and others), loads, transforms, and models the data within it and presents the results in several pages.
- Excel file named “Advanced Performance Analyser by SmartPowerBI Support File”, which contains the rationale, recommendations, and links to other resources.
- Excel file named “Advanced Performance Analyser by SmartPowerBI Actions Script” which you will use to capture all the actions taken on the report subject to analysis that help enriching the results report, as will contain information that is not present in the original json file.
A word of caution (disclaimer): this solution is provided “as is” and there is no warrantee that will work in untested scenarios or if Microsoft decides to change the format or data quality of the .json file. There is also a known bug in the .json file affecting visuals internal ids, which this solution solves by removing duplicates, at the cost of potential data loss.
Use cases are for this Advanced Performance Analyzer.
At the end, it is all the same: understanding reports’ performance affecting factors and knowing what to do to improve such performance. The big deal is that it can be beneficial in different cases and for different personas.
The personas that I have mainly in mind are 3:
- Power BI Developers, who work on the creation of data models, DAX calculations and report pages, all of it influencing end users’ experience.
- BI Managers or BI Leads, that can request their teams to run this tool to gain immediate and easy-to-understand insights on performance for those solutions that they are responsible for.
- IT Organisations, that need either, to enforce certain performance levels or to support large communities of Power BI authors.
The main use cases that I have in mind would be the following:
- Initial performance assessment when developing new solutions or introducing significant changes to existing ones (for developers).
- Continuous performance assessment and comparison between versions of solution via performance tracking (for developers and BI managers).
- BI managers’ review and guide: as not all BI managers know all the intricacies of PBI performance, this tool gives them a simplified and accurate view of what is going on in their reports.
- Analysis prior to go live and/or onboarding in Premium capacity shared with other solutions.
- Continuous assessment of solutions hosted in Premium.
- Supporting community of PBI practitioners, by IT organisations (i.e. CoEs) with a stake on performance.
- Enforcing certain thresholds regarding performance parameters, by IT organisations that want or need to do so.
- Communicating Best Practices and Guidelines, again by IT orgs with a bested interest.
What is native Power BI Desktop Performance Analyzer?
It is an out-of-the-box functionality existing in Power BI Desktop where you can view visuals’ loading times for one or more pages.
The Performance Analyzer pane in Power BI Desktop will show different timings for all visual elements rendered in your PBI report, triggered by either “Refresh visuals” button or actions taken on the report (filter change, data point selection,…).
DAX query and Visual display measure time taken for query resolution and visual render, respectively.
There is an “Other” element in the visual timings, which is very important as does affect the complete report render time and depends, amongst other things, on the parallelism when loading content in the report canvas. We will talk more about this in this article as this is very important.
If you wish to know more about this PBI Desktop feature, please refer to:
- Microsoft’s Performance Analyzer official documentation.
- GitHub’s documentation about the JSON file format (published in GH by Will Thompson).
- Not only one, but two videos from Marco Russo (SQLBI), alongside a myriad of related articles.
- Chris Webb’s related articles.
What do I get from Advanced Performance Analyzer by SmartPowerBI?
During a Power BI Desktop Performance Analyzer session, we can record several actions and the time taken for all the visual elements rendered within each action. The most common action recorded will be “page refresh”, which we will do by changing tabs, however we can also record actions such as drill down, change filter selection, or highlight a data point.
Each action will force refresh of different visual elements placed in the report canvas, and whilst full page refresh will trigger the update for all visuals in such page, changing filters or selecting data points, depending on how the interactions have been defined, will trigger actions in all or only some of them. Drilling up/down and expanding/collapsing hierarchies in a visual will only affect that visual.
This tool allows you to capture all those actions at once, and export in a single .json file and then analyse each action individually.
Analysis by Action.
The first page in the report is one that produces insights about whatever action we have recorded and selected in the only filter existing in this page.
Once a certain action has been selected, the page will display all affecting factors (that can be extracted from the .json file) and:
- Benchmarks all the results.
- Provide a rationale for how that factor can affect performance.
- Provide recommendations depending on the benchmarking result (Good, High,…)
- Provide links that will refer to documentation relevant to the factor and the recommendation.
More details about all sections of the report explained in the full document in GitHub repository.
All Actions Summary.
Provides the same insights from the “Analysis by Action” page but summarises all actions recorded in performance analyzer and provides recommendations for the worst-case scenario for each factor, or for an individual action if it has been selected in the main table.
Not all Power BI practitioners are aware of the influence of having a large number of visual elements in the report.
More specifically, visual elements, including those with static content (images, shapes, buttons,..) will render in the report canvas with certain parallelism, potentially pushing other more important visuals to be rendered at a later time.
Action Gantt page shows clearly what degree of parallelism is achieved in any given action and the dependencies when refreshing such large number of visual elements, including query preparation, query execution and wait time.
All visual elements rendered in the report page need to complete some of the following activities:
- Query Generation
- Query Pending
- Execute Semantic Query
- Parse Result
- Data View
- Final Render
And what is very important: waiting before any of these events, which will be affected, amongst other reasons, by limited parallelism of query and render actions.
As mentioned before, the complete documentation to understand how to use and interpret this tool can be downloaded from the GitHub repository.
You are ready now to take native Performance Analyzer to the next level and leverage its details for supporting large communities of Power BI authors creating faster solutions, whilst educating such community in best practices and enforcing performance limits. Do not hesitate to contribute with your questions, comments, and recommendations.
In some parts of this article or the downloadable files in Github repository, I have used and referenced articles or other resources authored by:
- Patrick Leblanc (Guy in a Cube (Power BI Training and More))
- Melissa Coates (Coates Data Strategies)
- Marco Russo (Home – SQLBI)
- Chris Webb (Chris Webb’s BI Blog)
- Reid Havens (Havens Consulting)
- Will Thompson (Github repository)
- Sam McKay (Enterprise DNA | Empowering Power BI Users to Change Their World)
- James Serra (James Serra’s Blog)
- Michael Kovalsky (ElegantBI).