- Ciao friends.
I want to show you how you can use
Power BI Desktop in order to investigate on a slow report.
And there is a tool called Performance Analyzer
that can help you find the bottleneck in your reports.
Before showing you Performance Analyzer,
think about what happens when you open a report
or you interact with a report.
When I click on a new brand in this slicer,
as soon as I click the brand,
all the other visuals are updated.
If you think about what is required,
in order to refresh these visuals,
every visual has to request data from the database
in order to retrieve the numbers that are displayed,
for example in this table, in order to render this map
or in order to update this bar chart
with the numbers that correspond to the selected brand.
And every time I select a new brand,
all these queries have to run again
and all the visuals have to refresh the report.
Now Performance Analyzer is a tool that you can find here
in view in the button Performance Analyzer.
And when I click on Performance Analyzer,
a new pane is opened here.
As you see, the pane doesn't do anything,
there is an important button here that is Start Recording.
So what happens when I click Start Recording?
Performance Analyzer start to listen
to all the events generated by all
the user interface elements of your report
that can produce an update of the visuals.
So if I start recording at this point,
nothing happens until I do something on the report.
So let me show you here what happens
when I click on a slicer choosing a different brand.
You see that something happened here,
and when I click on another brand,
something else happened here.
So now we can focus on the content
of this Performance Analyzer and
we can try to understand what is going on.
As you see, we had two events,
changed the slicer, changed the slicer.
But we could have also the selection
of a bar chart or other.
You can see that there are four visualizations
and the time here that you see displayed says,
"okay, the slicer has been refreshed
"in 103 milliseconds".
The time is always in milliseconds,
then the Sales Amount by Country
has been refreshed in 271 milliseconds and so on.
Now, sometimes the name of the visual
cannot be easy to detect.
And it is useful to know that when I click on a visual
on a name here in the Performance Analyzer,
the corresponding visualization
is highlighted in the report.
This can help you finding the visual
when the name is not enough to understand
what is the visual involved.
Now, once I click this element,
there are other numbers here that are interesting.
You see that for a slicer, we have three elements.
But actually for every visual,
we have a detail of three voices, three items,
that tells me how the time has been spent.
We have DAX query, visual display and other.
Let me open something like the top 10 customers,
'cause we have more interesting numbers.
What I see here is the following.
The top 10 Customers table that you see here,
has been refreshed in 356 milliseconds.
Now what happens for every visual?
The visual has to request the data from the database,
and in order to do that, it generates a DAX query.
Then the result of this DAX query is
considered by the visual in order to update,
to, we say render, to draw the content of the visualization.
Sometimes this content could be a map,
sometimes the content could be a table or a chart.
No matter what is the visual,
the result of a query is just a table with numbers.
So the DAX query tells me how much time
the Power BI report waited for the database
to execute your DAX query.
Whereas visual display is the time
that Power BI spent reading this data
and rendering the report.
You can imagine that if you have a complex map
with thousands of points, the time required to render
the visual will be larger than the time required
to render just a couple of points in the same map.
But there is an interesting consideration
about the time that is called other,
as you can see my top 10 customers spent
just 56 milliseconds of the 356 milliseconds
for the entire visual.
So I could say even though I optimize the DAX query,
I will not recover much time.
If the visualization is slow,
it is not the DAX query that is slow in this case.
Is it the visual display?
Not much 'cause also the visual display
didn't take the larger amount
of the time for the visualization.
But what is this other?
Well this other time, which in this case,
is a very big number compared to top 10 customer,
we're talking about 70, 80% of the time,
236 milliseconds
have been spent waiting, waiting for other visualizations
or for other operations in Power BI Desktop.
Can you optimize the other time?
No, the only thing you can do,
you can just reduce the number of visualizations
in the report.
In this particular case, we should not be concerned
about this time, 'cause this time cannot be cumulated
with... the same other time of other reports.
You can imagine that you can sum the visual display time
or the DAX query time, but it doesn't make sense
to sum other because actually, this is waiting time,
and many different people wait for
the same amount of time blocked
by another visual for example.
So this is the way we can collect this data.
But what if we want to get
what if we want to make sure to analyze
all the visuals in our report?
Well, we have this button Refresh Visuals
that will require a complete refresh of all the visuals.
Sometimes when you click on a slicer,
you might update only a few visuals in the report
because others are not connected to that slicer.
So before doing this refresh visuals,
I can also clear my Performance Analyzer list
'cause I want to get rid of this list.
And now I want to create a single refresh,
so that I will have every visuals only once in this list.
And if I do that, what I get is that,
there is a new execution of the rendering
of each visual without the user doing any action.
And if you see the event name is refreshed visual.
Now I can continue analyzing the numbers this way,
but of course, in order to understand
why a particular DAX query or a particular visualization
is slow, it requires you to enter in more details
about the DAX query plan or how you build the custom visual,
which is the topic of other videos.
However, there is still a feature
that I want to show you about Performance Analyzer,
which is Export.
And of course Stop just stops the recording.
But Export enables me to save in a file in a JSON file,
the information about the recording
that I obtained since the last clear.
So if I save this file,
what I have is a text file is actually a JSON file
that contains all the details
of what I'm seeing here in Power BI.
This file will be useful from other tools
that can offer different visualizations,
of these numbers and maybe some time more details.
What is this file?
I can show you that this file is basically a text file,
you see that this JSON file that I saved now
is something that I can open with the Notepad
or Notepad ++ in this case,
just to show you that even though this file is a text file
is a JSON file, it has a number of detailed information
that could be overwhelming.
Maybe we want to use DAX Studio,
which is a tool that can open this file
and show these numbers in a different way,
providing further analytics in order
to investigate more in what happened in your slow report.
So Performance Analyzer is a tool
that can retrieve data that can help you understanding
where is the bottleneck in your report.
You can open this file with other tools
like DAX Studio that offers additional features
to investigate about these numbers.
Enjoy DAX!