Trace Queries in Excel Power Pivot using SQL Server Profiler

December 29, 2023
Denis Selimovic
Power Pivotcomment 3Created with Sketch Beta.0 comments

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:

Power Pivot settings within Excel

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:

image 1

Open trc file in SQL Server profiler

Afterwards you can just open SQL Server Profiler and open our trace file:

Open trace file in SQL Server Profiler

The trace will be shows as usual:

image 3

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:

image 4

Or you can also use the properties icon directly:

image 5

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.

Share:
0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments