Client-side issues

When debugging client-side issues, you need to look at the various ways that the PowerPivot for Excel add-in allows you to diagnose problems. Here are some tips and tricks for troubleshooting client-side issues:

A. Generating a trace file directly within the client

This is extremely useful to understand exactly what the communication is between the PowerPivot add-in and the in-process Engine. Since we are using a modified version of the Analysis Services engine, we added a mechanism to generate the trace file (not unlike when you send an XMLA Command to start a server-side trace). To get a trace file is very easy.

1. Go to the Gemini ribbon and click on Options & Diagnostics:

2. Check "Enable Tracing" under Diagnostic Options.

3. If you already have the Client Window open, tracing will start immediately; otherwise tracing will begin as soon as you launch the Client Window.

4. The trace file is placed on your desktop (the info bar under Diagnostic Options shows the file path).

5. You can now use Profiler to load up the file and look at the trace events.

B. Getting a snapshot of the state of the client

The client add-in also has the capability to send a series of DISCOVERS to the in-process Engine to identify the state of the Engine.

1. Go to the Gemini ribbon and click on Options & Diagnostics: (see above)

2. Check "Enable Tracing" under Diagnostic Options. If do not already have the Client Window open, tracing will not start immediately and the "Take Snapshot" button will be grayed out.

3. After you launch the Client Window, you can go back any time to the Options & Diagnostics dialog and click the "Take Snapshot" button. This will send out a set of server state Discovers and capture the output in the trace you started earlier.

3. The trace file is placed on your desktop (the info bar under Diagnostic Options shows the file path).

5. You can now use Profiler to load up the file and look at the trace events, including the server state Discover responses for memory/object usage and so forth.

C. How much memory is being taken by a table

Sometimes it is useful to see how much memory is being taken by various PowerPivot tables in your workbook. While there is no built-in way to do this, there is an interesting trick that you can use to get a ballpark figure. You can use this to tailor your datasets to minimize the size of the resulting workbook. Smaller workbooks are easier and faster to upload and they render quicker on the server. The trick is that the in-process Engine keeps a on-disk “reflection” of its memory state. This “reflection” is used to ensure that transactional integrity is preserved if errors occur. The ”reflection” folder structure is located at:

C:\Users\<username>\AppData\Local\Temp\1\IMBI_<guid>\

For those geeks knowledgeable of SSAS, this looks remarkably like an instance data folder – and sure enough, that is EXACTLY what it is. The “reflection” folder is actually a data folder for the in-process Engine – each Engine is its own instance. Now if you drill down further into the single database folder and then into the “Sandbox” cube, you will see a folder for each dimension. When the PowerPivot add-in uses a dimension as the UDM equivalent of the table that you loaded in your model. If you look at the folder properties for each dimension/table, you will see roughly how much memory is being taken by each table in your model. The on-disk space taken by the dimension is roughly the amount of virtual memory allocated for that structure within the Excel process space. Cool, huh!

For those of you who want a ‘cheat sheet’ for how to do this:

1. Open the xlsx file & launch PowerPivot so that all data’s loaded

2. Go over to IMBI_* under %temp% in explorer. if there’re many, look for the latest folder.

3. Add a Size column to the detailed view

4. Type in “*.*” in the search box

5. Sort by Size, descending

The largest files will be at the top. Ignore the version information. The file name also contains the table and column name. If the columns are unnecessary or unexpectantly large, remove them from the PowerPivot model.

D. Saving mini-dumps when the client crashes

We have added functionality on the client to generate "enhanced" mini-dumps using SQLDumper when the in-proc engine crashes (hopefully rare), mirroring the current behavior on the server. In order to comply with legal requirements we need to bring up UI to get the user’s consent each time there’s a crash, and we need to remove the dump file (regardless of whether the user chooses to upload the data or not). The mini-dump would be very useful to add to bug reports so you will need to make sure you save the mini-dump file to an alternate location before you dismiss Watson dialog.

This is what you’ll see (you can copy the mini-dump file path from the dialog and copy the file to another location):

If you wish to save copies of the mini-dumps, either for your own purposes or to submit them with a bug report, make sure you do so before you click on Send information or Cancel. Once you this dialog box goes away, the files are deleted.