PASS BA Conference Speaker

PASS BA Conference Speaker
Register and use code BASF2O for $150 discount!

Wednesday, 21 December 2011

Sharepoint and PowerPivot: Connections fail to refresh in SQL Server 2008 Denali CTP3

Sharepoint requires a range of troubleshooting skills because it can involve the integration of various applications, which are necessarily resting on the application stack. I have the deepest respect for SharePoint specialists since i know that the troubleshooting skills can cut across applications and an understanding of the 'glue' that welds SharePoint together. Take, for example, the issue which I found recently whilst importing a PowerPivot into SharePoint. Everything looked absolutely fine: the PowerPivot loaded nicely into the PowerPivot gallery, and I could see my Excel spreadsheets too. The black and lime-green SharePoint theme was carried on throughout the Excel workbooks, and the whole thing was looking great. The Excel slicers were highly customised to match the SharePoint theming and customer branding, and the whole pieces, put together, looked fresh.

The PowerPivot was created on a Windows 2008 R2 Server 64 bit machine, which directly accessed the underlying SQL Server 2008 R2 database source. It is roughly 1.8Gb in size, which is just under the 2Gb limit for SharePoint files. I uploaded the PowerPivot from the original machine to the PowerPivot gallery as normal, on the target machine.

The issue came, however, when the user tried to filter the Excel spreadsheets or use the slicers in the Excel Services workbook, based on the PowerPivot. Even though the Excel workbook was part of the file uploaded to Sharepoint, the filters would not refresh. The file was uploaded with 'All' so it was not the case that any data had been filtered.  When a filter item was selected, the Excel spreadsheet froze, and then produced the following error message: 

Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data

This issue is covered by MSDN here. However, none of their resolutions worked for me. The way I resolved this issue was as follows:

My resolution: I recreated a new Excel workbook, which used the PowerPivot as the source. The issue was due to the fact that I had developed the PowerPivot on one machine, and then moved it to another. Otherwise, the initial PowerPivot worked fine, and a new test PowerPivot on the subsequent machine worked fine too. The second machine did not have access to the source data since the PowerPivot was generated from a static data source, so it did not actually need to be refreshed. 

The MSDN suggested resolutions were as follows:

Scenario 1: The cause might be that a domain controller is not available to validate the user identity - this was a test VM that was a full administrator, and this wouldn't explain why it worked for a PowerPivot that was 'home grown' on the server, and not a PowerPivot that had been moved.

Scenario 2: Differing versions of PowerPivot. I was very careful to ensure that the versions of PowerPivot were identical across machines

Scenario 3: IIS Reset in Sharepoint might work; no, I did try.

In the end, it was just easier and quicker to recreate the Excel workbook. The PowerPivot was otherwise unharmed during its movement from one server to another. I could see that the 'PowerPivot data' connection, when it was cracked open, pointed to a file that didn't exist on the target machine. To make sure everything ran smoothly, it was easier just to recreate the Excel workbook in a completely new Excel workbook, which then worked perfectly.

These scenarios show the multi-disciplinary elements of troubleshooting Sharepoint and PowerPivot, since it involved aspects of Sharepoint such as IIS, Windows Claims authentication, and so on. Despite this, the end result of using Sharepoint and PowerPivot together was an great solution and if you haven't tried it out yet, I suggest that you think about installing a Sharepoint and MS Business Intelligence Virtual Machine and try it out for yourself! Here is a link which might help you to build a VM for yourself.

Hope that helps someone else! 
Post a Comment