none
Problem with "Refresh data when opening the file" in the odc, and Excel Services rendering thumbnails in PowerPivot gallery

    Question

  • 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? 

    \Plesner

    • 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)
    Friday, April 13, 2012 12:03 PM

All replies

  • Hi,

    Thank you for your question .I am trying to involve someone familiar with this topic to further look at this issue.

    Thanks

    Entan Ming

    TechNet Community Support

    Monday, April 16, 2012 10:10 AM
  • Thanks

    I'm really looking forward to some input

    \Christian Plesner


    • Edited by Jam281 Wednesday, April 18, 2012 7:11 AM
    Wednesday, April 18, 2012 7:11 AM
  • Hi Entan

    Any info on this issue yet ?

    Kind Regards

    Christian Plesner

    Wednesday, April 25, 2012 8:25 AM
  • Hi,

    Did you find any solution to this problem ? I'm facing teh same issue.

    Thanks,

    Cedric

    Monday, August 27, 2012 1:08 PM
  • Hi Christian,

    Could you please let me know if you had any solution for that.


    Best Regards, Paramesh.

    Wednesday, November 28, 2012 11:14 AM