how to hide cached pivot table data in Excel

Question

I have an Excel 2010 workbook that contains a pivot table that connects to a cube. Authentication is per user. The workbook is hosted on Sharepoint 2013. It is important that no user should ever see cube data that they do not have access to. But users should be allowed to download the workbook to their desktop.

With the setting "Refresh data when opening the file" checked, users never see cached data via Excel Services. But when they download the workbook and open it they may have the chance to see the cached data of the last user who saved the file on Sharepoint. (say, after they open the workbook but before they click on "Enable Content"). How can this viewing of cached data in Excel be prevented?

Answer 1

One answer is to use filters on the document library views. This allows control over what workbooks users can see. You can set a filter to only display workbooks last modified by a system account. And create a SharePoint workflow to force the refresh of the workbook on save using a service account for refresh with limited data access. (The refresh can also be controlled manually or schedule using the Manage PowerPivot Data Refresh options). Only when the refresh is complete will the workbook be visible to users because the last modified by will meet the filter conditions. This means that the workbooks will effectively be empty templates and the data is secured.