When debugging server-side issues, you need to look at the various ways that the PowerPivot for SharePoint allows you to diagnose problems. Here are some tips and tricks for troubleshooting server-side issues:
A. Make sure that Excel Services works
Virtually everything in PowerPivot for SharePoint uses Excel Services.
- It is the basis for our primary rendering of workbooks, i.e. the thin-client browser-based viewing of workbooks with embedded PowerPivot data.
- Excel Services is the way that we generate thumbnails for the PowerPivot Gallery. Behind the scenes, there is an event handler that detects a changed workbook (either the uploading of a new one; or a revised one). The event handler then runs the GetSnapshot.exe program that generates and web browser control and renders the workbook in Excel Services – then taking a snapshot of the resulting web browser control (a *.png image file).
- Obviously when you click on the Gallery Silverlight control, we use Excel Services to bring up the workbook.
- And lastly, the PowerPivot Mgmt Dashboard makes extensive use of the Excel Services web part to show reports and allow the user to interact with the management reports.
Thus it should be your #1 job, as you start to investigate issues on SharePoint, that you first verify that Excel Services is working and rendering workbooks as expected. We strongly recommend that you don’t start any other troubleshooting without doing this first.
Unfortunately your first impression of Excel Services is likely misleading. You probably start it up and just click on a workbook to see if it renders. If it does, you might then bring up a PowerPivot workbook and ensure that it renders. Thus all is working and you can move on. Right? Well . . . Unfortunately, No. Just rendering a workbook does not exercise any of the PowerPivot midtier infrastructure. It just verifies that core Excel Services is working; not that the interfaces between Excel Services and PowerPivot is working; nor if PowerPivot itself is working.
To fully ensure that all of the PowerPivot ‘infrastructure’ is working, either:
- Click on a slicer if available in your workbook/model; or
- Click on Data and then either: (1) Refresh All Connections, or (2) select the pivot table with the PowerPivot data source and then Refresh Selected Connection
By doing so, you will exercise all of the midtier core components. You will ensure that Excel Services can communicate with external data sources (the PowerPivot OLEDB provider) and that credentials can be sent across properly. The PowerPivot midtier services will on-demand, locate and load the embedded workbook data (this will exercise both the Engine instance as well as the load balancing pieces of the service proxy. The connection will be exercised between the midtier System service and the Engine service for executing queries and getting results back through the OLEDB provider back to Excel Services for it to render the results.
As we’ve said above, but bears repeating again, DO THIS FIRST before you troubleshoot further, in particular if this is a new farm.
Recent Comments