BUSINESS IMPACT

Jun 09, 2020

How to Improve Power BI Performance - Part II

Dan Szepesi Posted by Dan Szepesi

In part one of my Power BI Performance series, I talked about the importance of being able to understand the overall architecture of Power BI so that you could better understand where your performance problem may lie.  In this post, it's much more simple because we are going to focus on the performance of your visuals.  This is going to get a lot more detailed but that is to be expected because we are rolling up our sleeves and really digging into the material now.

In all honesty, I probably should have lead with this section because this is usually how you are first going to experience performance problems occur in Power BI.  Let's face it, Power BI is all about visualizing data so that you can gain new insights about your data.  If your users are staring at spinning dots instead of visuals, they aren't learning anything and their razor thin attention spans are at risk of being grabbed by something else.  Websites, apps, games - it's all about how fast you can get information to the user to grab their attention and hold it.

power-bi-performance-part-two

How fast is fast?  I spent a good chunk of my early career in managed hosting (the cloud before the cloud) and the rule of thumb we lived by was an eight second load time.  Eight seconds is a lifetime now and if you are still using that as your benchmark, you are frustrating your users.  It's much more reasonable to expect users to start complaining if they are routinely experiencing load times over three seconds.

Anyone who has ever worked in IT is familiar with the problem here though, one user's slow is another user's normal so how do we make this conversation about speed more objective than subjective?

Enter the Power BI Performance Analyzer View.  This view in the Power BI Desktop tool was released in May 2019 and it provides the definitive tool for understanding how your visuals are performing. 

To access this view while in the report view of Power BI Desktop, first open a blank page in Power BI Desktop, then select 'View' from the ribbon and choose the Performance Analyzer icon from the ribbon.

This will open a new section in Power BI Desktop shown below:

Opening a blank page first will allow you to have control over what visuals you want to capture performance data from.  Click on 'Start Recording' and you are ready to start testing load times for your visuals.  Select the page that you want to analyze and the tool will collect information on how long your visuals took to render. 

As an example, I am going to go through in detail how to use the results from the Performance Analyzer to understand the performance of your visuals.  I downloaded the sample PBIX from the Power BI Documentation at Microsoft.com - https://docs.microsoft.com/en-us/power-bi/create-reports/sample-datasets and I will use the visuals from the Net Sales report in the screenshots that follow.

I am going to walk through how I would approach looking at the performance of this visuals on this report and show what we can learn from the data that the Performance Analyzer gives me.

With the PBIX file open, I created a blank page and put it next to the Net Sales report in the bottom page navigator so that I could save the PBIX, close it, then open it again and have it start on the blank page.  Doing this lets me get the Performance Analyzer started and recording without having loaded any visuals into memory.  

So, with the PBIX reopened and on the blank report page, I start the Performance Analyzer and select 'Start Recording'.  I browse to the Net Sales report page and it captures the timings for the visuals. Information overload right?  The first thing I do is click the arrow next to Duration (ms), and select the options to Sort By - Total Time and leave the default sort type to Ascending.

This gives me the output in a way that I can easily see the slowest visual and how long that visual is taking to render.

 

Let's take a look at the last visual, 'Category Breakdown' and see what we can learn about this visual that took the longest to run (794ms).  If I click on 'Category Breakdown', it will highlight the visual on the Power BI Desktop canvas so that I can easily determine what visual's metrics are highlighted.  Clicking on the plus sign will expand the entry (as shown in screenshot) and show details on how long the DAX took to run, how long the visual took to render, and a mysterious heading named 'Other'.  If you hold your mouse over 'Other', it will explain that the 'Other' category captures time that was spent waiting in a queue or waiting for the system to finish with other visuals.  

Clicking on 'Copy Query' will allow you to copy the DAX query for that visual to your clipboard.  This means that you can then copy the DAX code to DAX Studio and execute it there as well.  DAX Studio has built in tools that will let you dissect the execution of your DAX code so you can really drill in on what is running slow. More on this in Part three of the series.

Selecting 'Refresh Visuals' will allow you to refresh the visuals on the page to create another capture of the times it took to execute.  Note that Power BI Desktop will cache content so after the first time you load the visuals so when you select 'Refresh Visuals', you may notice that its a bit faster after the first load.  To get around this, stop the performance analyzer and start the recording again, then select 'Refresh Visuals'.  To go expert mode, attach DAX Studio to your Power BI instance and use the 'Clear Cache' setting in DAX Studio to clear the cache between refreshes.

Knowing how to use the Performance Analyzer view will really help you start to quantify what is running slow and what 'slow' means.  Performance analysis is often an art form and there are no easy answers as to how to improve performance but getting a benchmark is a great start.

Let me leave you with some tips and tricks that I have collected for visualization performance.

1. Be smart about the grain that you choose for your visuals.  Use a design pattern that shows aggregated, or rolled up data at a high level on your more popular reports.  Use the 'Drill Through' feature to allow those few users who really need to dig down into the data to do so on less commonly used report pages.

2. Remember that more visuals means more DAX Queries analyzed.  If you have a slow page, you can see this as you will see visuals sometimes render one at a time.

3. Resist the urge to duplicate Excel Spreadsheets in the Table or matrix visual.  Just because you can, doesn't mean that we should.  Again, aggregate, roll-up and leave the high grain, spreadsheet visuals to drill through pages.

4. Pay attention to visual interactions.  If you have a lot of visuals and don't need them to interact or cross filter each other, disable the interactions between them.

5.  The capability to sync slicers across pages is cool, but understand that there can be performance implications if you are syncing a large amount of slicers across multiple pages.  Try disabling the synchronization to see if performance improves.

6. DAX has some behind the scenes optimizations that it will do to try and reduce the number of round-trips that have to be made to retrieve data from the storage engine.  Just like SQL has the query optimizer, DAX is always watching queries to see if it can combine them to get the data in less requests.  If you use a bunch of visuals like cards on your page, the DAX engine won't be able to combine those requests because each card is a single request.  If you combine your cards into a single visual, perhaps using the multi-row card or the Table/Matrix visuals, you give Power BI a chance to combine requests.  You can see how Power BI performance can be complicated: in step 3 I tell you not to use the Table or Matrix visual but here I mention that its use could improve performance.  To learn more about this internal engine optimization, go check out Phil Seamarks blog post on DAX Fusion at https://dax.tips/

Armed with the Performance Analyzer view, you can really start to get an idea about what the slow visuals are that are slowing down your pages.  If it's not one of these issues above causing the slowness, then what?  Odds are you are now in the unenviable position of having to optimize your custom DAX measures that are driving those visuals.  Optimizing DAX truly is an art form but if you have followed the tips from the first article in this series and the tips that I have included above aren't helping either then you need to look for part three in my Power BI Performance series where I start to grind down into the details of optimizing your DAX code.

New call-to-action
Dan Szepesi

About The Author

Dan Szepesi

Since 2000, Dan has been designing and implementing insightful information platforms, he specializes in enterprise-level business intelligence collaborations. He enjoys applying forward-thinking resolutions to complex BI challenges, and using his deep knowledge of Microsoft SQL Server, Azure, and Power BI to support clients. Dan’s expertise includes the retail and energy industries.

Latest Posts

New call-to-action