Trace Queries in Excel Power Pivot using SQL Server Profiler
This post is also available in:
German
Although most of the articles in this blog are dealing with Power BI, we should not forget that the technology of Power BI is also available in Excel. There it’s just called Power Pivot, but behind the different user interface, it’s more or less the same as Power BI.
Power Pivot is Power BI in Excel
So once in a while, I also work with Excel and Power Pivot models. I recently had that pleasure again and in this case one of the DAX Measures was causing some trouble. In order to find out what the measure was really doing, we had to find out what is really being executed.
In Power BI you have many more possibilities to find out what DAX is executing, in Power Pivot, the best chance is to take a look behind the curtain with a trace in SQL Server Profiler.
Trace has to be started in Power Pivot settings
When working with SQL Server Profiler, usually you can just add the address of the server and start the trace. I was actually looking for this option for quite a while, but it works differently with Power Pivot in Excel.
Here, you start the trace from within Excel. In order to start the trace, you have to go to the Power Pivot tab and go to the settings:
Then you have to start the trace with the “Client tracing is enabled” option, the trace will automatically be saved in the trace file location as *.trc file:
Open trc file in SQL Server profiler
Afterwards you can just open SQL Server Profiler and open our trace file:
The trace will be shows as usual:
If you want to reduce the displayed events to specific ones, what usually makes sense, you can change the displayed event by choosing file –> properties:
Or you can also use the properties icon directly:
Afterwards you can select the events that you want to see, like in the screenshot above only “Query Begin” and “Query End”.
Summary
Doing a trace is Power Pivot is very easy. You only have to know, that you have to start the trace from the Power Pivot settings within Excel. After that, the trace is done automatically and you can analyze in SQL Server Profiler as you would always do.
