Overview: How to produce a trace file for the IT or BI Support team to review when troubleshooting an error within PowerPivot running on an individual user machine.

Let’s say you’ve been working with PowerPivot for a while and suddenly an error starts to occur. Perhaps this error has popped up when you try to refresh the data. In the following screen shot, the error message states “We couldn’t refresh the connection. Please go to existing connections and verify they connect to the file or server.”

In the above screen shot, the Work Item of “AutoSales” is the friendly name given in the PowerPivot model for the first table it’s trying to process – it’s not the actual name of the data connection. So, the friendly table name may or may not be descriptive enough to infer what the actual data source is. In this situation, you will probably be able to resolve this issue by going to Existing Connections on the Home tab > select the appropriate PowerPivot Data Connection and choose Edit > then Test Connection. The test will almost certainly fail if the data refresh failed.

The screen shot above which displays the Server and Database name is excellent information to provide to the IT/BI team for troubleshooting connectivity.

But…what if it’s not so simple? What if you need more information to figure out what the workbook is actually doing? Enter the trace file…

Creating a PowerPivot Trace File

PowerPivot has an option to create a trace (*.trc) file which will describe, in excruciating detail, each step that is happening behind the scenes. In certain situations, having all of this detail this can be tremendously helpful.

To generate a trace file, go to the PowerPivot menu in the Excel window (not the PowerPivot window) > select the “Client tracing is enabled” checkbox.

This will enable tracing for this one workbook, while it remains open - i.e., for this session only. This behavior is nice because you don’t have to remember to turn the tracing off; it terminates when the workbook is closed.

Finding Information in the Trace File

In our situation, we can quickly scroll down and spot the error in the trace file. The descriptive message appears at the bottom of the window for each line.

The full error is a mouthful, but the description from the trace does give more info than the initial PowerPivot screen did: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [53]. ; 08001. A connection could not be made to the data source with the DataSourceID of 'b3c4d1db-0d27-477c-bff6-8a020d0841c4', Name of 'SqlServer SQLIMAGE2012 AutoSalesSourceDW'. An error occurred while processing table 'AutoSales'. The current operation was cancelled because another operation in the transaction failed.

If you scroll up to earlier entries in the trace file, we can find the actual query being executed against the source:

It also captures information such as how calculated measures are being created:

There’s can be a lot of volume to scroll through, but there is lots of excellent information captured by a trace file. In some cases, sending the trace file to the IT/BI Support Team will provide enough details to figure out what’s gone wrong.

Reading a Trace File

An application which is able to read a *.trc file is required. Common applications like Notepad or Excel cannot read all the contents of a trace file:

To read *.trc files on your machine, you’ll need the SQL Server Profiler which comes as part of a SQL Server installation. If you are a business user who uses PowerPivot but not SQL Server, installing the Developer Edition of SQL Server is probably overkill, but it is an option if you don’t have IT support to help.

Finding More Information

PowerPivot-info-.com – How can I see what internal commands PowerPivot executes in its engine?

Denny Lee’s blog – Reading Your PowerPivot Profiler Trace

TechNet – PowerPivot Options & Diagnostics Box