Problem with "Refresh data when opening the file" in the odc, and Excel Services rendering thumbnails in PowerPivot gallery
Friday, April 13, 2012 12:03 PM
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)
Monday, April 16, 2012 10:10 AMModerator
Wednesday, April 18, 2012 7:11 AM
I'm really looking forward to some input
- Edited by Plesner Wednesday, April 18, 2012 7:11 AM
Wednesday, April 25, 2012 8:25 AM
Any info on this issue yet ?
Monday, August 27, 2012 1:08 PM
Did you find any solution to this problem ? I'm facing teh same issue.
Wednesday, November 28, 2012 11:14 AM
Could you please let me know if you had any solution for that.
Best Regards, Paramesh.