- In the previous video,
we have seen how you can use information generated
by the Performance Analyzer inside DAX Studio.
But what if the client tool you're using is not Power BI?
So you don't have the option
of looking at the queries generated by Power BI.
Or what if you need to monitor an Analysis Services engine
and you don't have access to all the clients.
You just want to read all the queries
and then perform an analysis further.
If that is the case,
you can use the all queries functionalities.
All queries traces all the queries
which are executed by the server
to which DAX Studio is connected.
And it is useful when you have no better option to gather
the queries executed to perform a performance analysis.
Just be aware, it may generate a lot of queries
on a busy server, so you will need to pay attention
to the moment when you run it in order not
to be overwhelmed by the amount of data that it generates.
Let's see how it works together.
Now we were working on the query generated
by Performance Analyzer,
but we might not be all that lucky.
So this is the model to which we are connected
and let's suppose I cannot use the Performance Analyzer
or simply I'm using Excel to connect to this same model.
Let me get rid of it.
Excel now starts, and I have a pivot table
that is connected with the Power BI file
to which DAX Studio is connected.
So I have at the same moment both Excel
and DAX Studio connected with my Power BI file.
I'm not interested in Power BI,
we're mainly interested in Excel and DAX Studio.
This pivot table will run queries against the Power BI file.
And I want to try to trace and to read all those queries.
To do that, I need to enable the all queries button.
Let's do that.
If I click on all queries, you see that now at the bottom,
I have the all queries panel, which right now is empty
because Excel is not yet doing anything,
but I can take the sales amount and drop it.
I need to take the sales amount and drop it in the values.
In order to retrieve this number,
Excel had to run an MDX query.
And if I go to DAX Studio, you'll see that I have here,
the MDX query, which has been cached by DAX Studio.
And if I go on and work with Excel, generate another query
like slicing, putting the city on the rows
and maybe the, no let's put the occupation on the columns.
I now have generated a pivot table.
And if I go in DAX Studio,
you'll see that I have all the queries
that have been executed here.
As it happened earlier,
I can just double click on any of these queries
and the query will appear in my code.
I can look at the content of the query just
to check that it is MDX.
This is an MDX query which has been cached from Excel,
connected to the Power BI file.
Keep in mind, you are not forced to use only Power BI.
The same technique works if you're connected
to an Analysis Services engine.
In that case, you connect DAX Studio to Analysis Services,
Excel, or whatever is the client to Analysis Services,
you enable trace all queries,
and then you start gathering information
about all the queries that are being executed.
For each query you have information
which are extremely useful.
You have the start time, the type of the query,
the duration, and that is probably the most relevant part,
because if you're doing this job,
it is likely you are interested in analyzing performance
of the query so you search for the worst one.
Then you double click, you put it in your editor
and you start the process of optimizing it.
Now, optimizing it requires skills,
requires being able to read and understand
the query plan, server timings and all the information
that DAX Studio offers you,
but... trace all queries is one
of the most important features,
if you can not rely on Power BI
and its Performance Analyzer.