Problem with "Refresh data when opening the file" in the odc, and Excel Services rendering thumbnails in PowerPivot gallery
I'm facing a problem with Excel Services, the PowerPivot gallery and the requirement for Refreshing the Workbook "on-open". The scenario is like this:
We run a set of servers with WFE, Application Server, DB Server, Analysis Services Server. Kerberos is configured and working fine.
In Sharepoint 2010, users are publishing Workbooks that are connected to Analysis Services (OLAP). The odc they use is located in a Sharepoint library (http://SP2010/Data%20Connections%20for%20PerformancePoint)
The library is added in "Trusted Data Connections Library"
When the Workbook is published to the PowerPivot Gallery, the thumnails are rendered fine (one pr. Sheet in the Workbook)
Inside Analysis Services roles are defined (users have access to certain countries in the Geography dimension) and since the person designing the Excel workbook layout and content has full access to all countries. When the Workbook is published to Sharepoint, some figures inside each of the sheets are shown reflecting that designers access rights in the cube. Now when an end user opens the Workbook in Excel Services the initial figures shown are still reflecting the desginers. In other words, the workbook does not refresh on-open reflecting the end users access rights.
Now there is this nice feature to check the mark in the Connection Property - "Data -> Connections ->Properties -> Refresh data when opening the file". When the workbook is then published to Sharepoint and an end user open the workbook, it initially refreshed the data on-open correctly reflecting their access rights.
But here is the problem since now the Thumbnails cannot be rendered inside the PowerPivot gallery. Every sheet that have the "Refresh data when opening the file" ticked, shows a padlock in the thumbnail with the error - "Snapshots are disabled because this file contains connections to data sources other than PowerPivot workbooks in the library". So if I remove the tickmark in "Refresh data when opening the file" again and re-publish, the thumbnails are rendered again.
I have tried looking for other ways to refresh on-open , but haven't found a solution that works.
Anyone knows of a solution for this?
- Moved by GreggPJ - MSFTModerator Monday, April 16, 2012 1:25 PM moving to correct forum (From:SharePoint 2010 - Using SharePoint Designer, Infopath, and other customization)
- Moved by Max MengMicrosoft contingent staff Thursday, April 19, 2012 9:15 AM Moving to a more appropriate forum (From:Excel IT Pro Discussions)