Reading your #PowerPivot Profiler trace

To help troubleshoot your PowerPivot for Excel workbook, you can click on Settings within the PowerPivot selection in the Office Ribbon which will bring up the Support & Diagnostics tab within the PowerPivot Options & Diagnostics dialog.  Click on the checkbox next to “Client tracing is enabled” and after opening the PowerPivot window, you will have a trace recorded on your desktop.

image

Figure 1: Click the checkbox to enable client tracing

 

In order to view this trace, you will use SQL Server Profiler to view it.  Yet, if you do not have a SQL Server 2008 R2 client components installed on your box, you will get the following error:

image

Figure 2: SQL Server Profiler cannot open the trace

 

The error message says:

Unable to location trace definition file Microsoft Analysis Services TraceDefinition 10.50.0.xml for open trace file …

 

In many cases, you will not since your business analysts are using Excel as their primary tool.  If they have SQL Server client components installed, commonly it is the SQL Server 2008 components as opposed to the SQL Server 2008 R2 components.

 

So how can your SQL Server 2008 Profiler read this trace so that way you do not have to uninstall / install the SQL Server 2008 R2 client components?  You have two options:

  1. Connect your SQL Server 2008 Profiler to a SQL Server 2008 R2 Analysis Services server.  The profiler caches these tracedefinition files within the \Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Tracedefinitions folder.  So when you connect to a SQL Server 2008 R2 Analysis Services server, the profiler will obtain from the server the trace definition of that server if it does not already have it.
  2. Copy the trace definition file to your profiler directory.  Since the trace definitions are located in the \Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\TraceDefinitions, just go to this folder onto a server that has R2 running on it.  Copy the Microsoft Analysis Services TraceDefinition 10.50.0 file to your own box.

Thanks to Mike Vovchik from the Analysis Services team for helping me solve the problem as well.

Enjoy!

3 Comments

  1. […] Lee described how to open a trace file in PowerPivot for Excel. It is something that is very important because the SSAS engine which runs PowerPivot runs inside […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s