Performance Analyzer: Power BI’s Chief Diagnostic Tool

 
AR DT 0.1Artboard 1.png
 

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:

sc1.jpg

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.

sc5.jpg
sc7.jpg

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.

sc5.jpg

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:

li2Asset 1.png

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.

Blog SSAS w Artboards-04.png

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!

li3Artboard 1.png

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.


 
FreshBIChris_02.png

Author: Chris Bradford

OCCUPATION:
BI Apprentice - Software Development and Report Design.

EDUCATION:
Honors in theoretical mathematics/minor in computer science.

EXPERIENCE:
Extensive problem solving and abstraction. Software development and design.

COMPANY:
FreshBI

Team FreshBI is an innovation machine and the blogs that Chris writes mostly emanate from the innovations that FreshBI does for its customers

 

Our Latest Blogs

About FreshBI

Based in Canada, South Africa and in the United Kingdom, we have helped hundreds of businesses achieve excellence & success through business intelligence.

Power BI runs in our blood, and we are ready to take your business to next level.

Chris BradfordPower BI