Advanced Power BI Performance Analyser by SmartPowerBI

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).

Analysis By Action
All Actions Summary
Action Gantt

You can download the solution files and document from the GitHub repository. This product comes “As Is” under MIT license. Please read the conditions for this type of license.

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:

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.

Page: Analysis by Action

One important detail is that such benchmarks not only drive the severity of the warnings and the recommendations, but also can be configured to meet each organisational needs.

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.

Page: All Actions Summary

In this page we can also see what the different thresholds are, as shown below.

Configurable Thresholds

Action Gantt.

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.

Page: Action Gantt

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.

Credits.

In some parts of this article or the downloadable files in Github repository, I have used and referenced articles or other resources authored by:

And special thanks to Steve Verschaeve, Senior Customer Engineer from Microsoft, and Chris Webb, for their thorough review of the documentation and great advice on the functionality.

7 comments

  1. This is a really interesting tool and very insightful. In our case. In our setting we serve our PBI content externally using PBI Embedded with RLS (with reports separate from datasets). Can you think of a way to obtain the performance analyser json, however with an RLS role enabled? As without RLS, it is not a true reflection of the full DAX execution (albeit in most cases likely an indicative measurement which is still better than none).

    Like

    1. Hi Tim,
      Very good question.

      Advanced Performance Analyser provides suggestions mainly for the content of your report. Yes, the JSON file contains all DAX query execution time and could make a more thorough analysis of those, but it only does for the longest one.

      The short answer is that, if you enable “view as” in Power BI Desktop, it should form queries that apply RLS, and you could modify the final report (Advanced Performance Analyser) to surface all those queries and their execution times.

      The long answer is that, when you want to make a more thorough analysis of query execution, there are two tools that will serve you better (and actually can record a live session – therefore recording your actual users usage).

      SQL Server Management Studio, beautifully explained by Chris Webb in his blog: https://blog.crossjoin.co.uk/2020/03/02/connecting-sql-server-profiler-to-power-bi-premium/

      DAX Studio , explained here: https://daxstudio.org/documentation/features/all-queries-trace/

      Once you know what visuals are having longer running queries, you can influence in their execution either, by changing the report (limited options) or by changing the model.

      For the latter, I would always check Tabular Editor’s Best Practices Analyser by Michael Kovalsky (as mentioned in the article), to get the most of your model.

      Hope this helps.

      Like

      1. Thank you for your robust response. Was thinking this was going to be the response. Shame as I like your way of joining the queries back to the actions done and collating it together. We use log analytics on our main workspace to capture all queries, but this alone I find it difficult to ascertain the exact action a user has done. For your short answer, this unfortunately isn’t possible on thin reports, which is a tad frustrating as it would be very useful to have (especially since some of my reports depend on rls to render a result). I started refactoring your power query, will share it back your way if I make sensible progress. Thanks, Tim.

        Like

  2. Hi, thank you for this post. This looks a very useful tool and I’m keen to implement this in my practices.
    I build reports on shared datasets, so report visualisations and the datamodel , DAX are separated. I would like to understand if the performance analyzer tool would still give an accurate measure on the model and DAX query in such scenario.

    Like

    1. Hi Anna, thanks for your comment. The JSON file produced by Power BI Desktop Performance Analyzer does contain time for all DAX queries captured, however that is not server time. Let me explain: Perf An. captures de round time from when the DAX query is sent and the results start arriving to PBI Desktop, but in that time you will still have some latency for Service communication (to and from). In a 20 seconds DAX query execution, that latency is negligible compared with the total, but in a 300ms DAX query execution, might be an important portion.

      Within this Advanced Performance Analiser, all DAX queries are stored in the relevant table and you can see your top N queries, so you can focus your improvement efforts in those long running queries, but for a fine grain DAX query execution analysis, I would use DAX Studio’s “Server Timings” option, to run specific queries, or the option “Load Perf Data” that can be used to analyse queries from a PBI Desktop Performance Analyzer JSON file.

      To do so, you will need your dataset to be hosted either in Premium per Capacity, Premium per User or Premium Embed, being PPU perhaps the quickest and cheapest option.

      What you will have from DAX Studio “Server Timings” is a more detailed explanation of your DAX query, starting from how much time is spent in Storage Engine and how much in Formula Engine.

      Hope this helps.

      Alex.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s