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.

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:

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!