How to Refresh a Cached Excel Services Report

This post doesn’t so much describe how to do something as why you shouldn’t worry about something you may see. It can be unnerving to make a change in a SharePoint object, then not see the change when you check for it. Here is a case where there is an explanation for that behavior. Here is a case where caching, which helps with rendering speed and performance, may impact how you specify an Excel Services data source. It’s not a big inconvenience, but I wanted to point it out nonetheless.

Assume you have an Excel Services workbook uploaded to a SharePoint document library.

Now, create an Excel Services data source for PerformancePoint Services, using Dashboard Designer. The Item Name pull-down shows the sole, named region.

Here comes the change: we’re going to modify the workbook itself and see how that affects the existing PerformancePoint data source. Open the workbook using Excel, add a new table or named range, set the Publish Options to include the new range, and save the document back to the SharePoint document library (same name).

Back in Dashboard Designer, in the Data Connection dialog, with the Connection Settings unchanged, open the Item Name drop-down.

The list hasn’t changed! What happened to the new item? As I said, this can be somewhat unnerving.

The explanation has to do with the product’s behavior “under the hood.” To improve responsiveness and efficiency, Dashboard Designer caches recently used items, so fewer data-intense transfers (between the client and its server) are needed. In this one situation, the signal to Dashboard Designer that there is a change invalidating its cache does not get through, so it shows you the unchanged version of the Item Name list.

The Fix

One way to make Dashboard Designer get a fresh copy of the data source information is to make a change to the external details of the workbook. For example, if you changed the filename or a property, Dashboard Designer would discard the cache entry for the data source and fetch a fresh copy from the server.

Here, we’ve changed the name of the file on SharePoint, and when accessing the newly named file in Dashboard Designer, the Item Name pull-down shows the added range name.

The other way to get the updated contents reflected in Dashboard Designer is to wait for the cache to time out. This is a site-selectable value, and defaults to 10 minutes.

We hope this helps, should you ever see this issue.
Thanks for checking in!

Paul Thrasher
Test Engineer, Office BI