Performance Analyzer: Power BI’s Chief Diagnostic Tool
Building Reports in Power is as Easy as 1, 2, 3. But Optimizing Them is Another Game:
The brand new availability of the once DAX-Studio-only Performance Analyzer in Power BI gives users even more… power.
Once you’ve gotten the hang of building and customizing visuals in Power BI, the next step is making sure they running efficiently. The first step here is knowing which visuals are bogging your report down. This is where the performance analyzer comes in.
The Performance Analyzer allows you to experiment with your report, while recording in real time the duration each visual on your canvas is taking to Query, Render, and Load.
Lets have a look at how to use the Performance Analyzer in Power BI:
Using the Performance Analyzer:
Step 1: Enable the Performance Analyzer
The first step is to check the performance analyzer from the view tab in the ribbon.
Navigate: View -> Performance Analyzer
Once you do this you’ll see a new pane appear on the right hand side of your canvas.
This is where you’ll start the analyzer and view the results of your exploration.
Step 2: Start Recording
Next you start recording!
Click the start recording button in the Performance Analyzer Pane.
Once you’ve done this click the Refresh visuals button in the Performance Analyzer Pane.
Right away, you should see a list of every visual on your canvas and the time Power BI took to get its data and render to completion. Our time measurements, labeled as duration, are recorded in milliseconds as shown.
Step 3: Explore and Review Results
Once you’ve gotten started you can explore your report taking note of specific actions that result in longer rendering times.
For instance, cross filtering on visuals is an action that requires all of your visuals to re-render on your canvas.
It is note-worthy that "undoing” a cross filter will often result in a shorter duration for each visual since some of what Power BI needs for those queries are pre-cached.
In the picture to the left, the first cross highlight is a new selection, and the second section of cross highlight duration measurements is from returning to the default state.
Explore cross filtering. You might run into a visual that is particularly troublesome.
Step 4: Understand the Duration Details
When you click the “+” icon to the left of the visual name you are looking at you will see separate times in three different categories, additionally, you will see a “Copy query” button.
These three categories help you to dig into the cause of rendering times, we will go into a little bit more detail about these categories in the next section.
Every time a visual is rendered, Power BI creates an evaluate statement based on your measures, fields, and sort options. The Copy query button copies that entire evaluate statement to your clipboard for you to manually test for problems.
Here is an example of the statement generated the “Project Profitability Compared to Budget” visual:
// DAX Query
DEFINE VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Project'[Project])),
NOT('Project'[Project] IN {"Project 6",
"Project 7"})
)
EVALUATE
SUMMARIZECOLUMNS(
__DS0FilterTable,
"Expected_Profits", IGNORE('Project'[Expected Profits]),
"Total_Project_Value", IGNORE('Project'[Total Project Value])
Now that we’ve seen some examples, it is important to learn the meanings behind the three categories.
Three Categories:
DAX Query:
As mentioned above, every visual creates at least one evaluate statements to be sent to your model for computation. This category measures the time taken between the statement being sent to the model and the first row of data being received. This time spent can be executed in parallel to requests from other visuals.
Visual Display:
This is the time Power BI takes to combine statement results with the visual formatting on each visual to render your results. This process is done one visual at a time going in layer order. There is a key component here to Power BI efficiency; and that is that every visual is slowed down by the total number of visuals on your canvas. Keep the number of visuals down at all costs!
Other:
The other measurements is the time required by the visual for preparing queries, waiting for other visuals to complete, or performing other background processing. You can most often think of this as synchronization time between visuals. This is not a category that should cause a bottleneck.
Additional Note:
One other important thing to keep in mind when trouble shooting performance issues is that custom visuals will almost always be less efficient than default Power BI visuals. While there are a lot of options available, you will sometimes run into a situation where a default visual will drastically improve performance over a custom one.
Conclusion:
The Performance Analyzer in Power BI is an awesome tool when it comes to pin-pointing slow visuals and understanding your report performance.
The key point to take away from this is that diagnosing basic report performance is no longer a mystery. You can easily tell exactly what visuals your stress points are coming from.
Now, there are inevitably going to be other areas that will affect your overall reporting performance that the Performance Analyzer will not diagnose for you. This is not a one tool fix all; but it is a tool that will help you solve report specific problems faster than ever.
Our Latest Blogs
About FreshBI
Operating throughout USA and Canada, FreshBI is a boutique Microsoft Partner using Power BI to unlock the value trapped in your data so that your business can succeed.