In one of my previous posts I mentioned two techniques for improving the performance of reports hosted in SharePoint … Dataset caching and Report caching, but how do you decide you which one to apply?
Report Caching when enabled will stored a copy of the data required to run the report (this is not to be confused with snapshotting of reports where a complete rendered copy of the report stored). The cached data is only accessible to the report that cached it and can have specific refresh and expiration configuration settings that suit the requirements of the report. This is particularly appropriate for reports that contain information that is periodic in nature (e.g. daily/weekly/monthly status reports) but the information isn’t referenced in other reports. It should be noted that if data shown in a cached report changes this will not be updated in the report until the cache is and as a result you may find any other reports that reference that data directly will show different, more current, results
Shared Dataset Caching enables the storage of the data on a per dataset basis. The cached data is accessible to any report that depends on the Shared Dataset and all reports gain the performance advantage from the cached data. This is particularly appropriate where the rate of change of the various data sources is understood and can be taken into account when configuring the caching of the various shared datasets you have available. Configuring each Shared Dataset’s cache properties to take into account the churn rate on the source data will enable you ensure that the data stored is maintained at optimum freshness whilst still delivering the improved performance benefit to your reports of not having to query all the way back to the SQL Server for the data.
If you are combining both approaches, which is technically feasible, careful consideration should be given to when the cache at each point is expired/refreshed, how often the source data is expected to change and what the requirements of the consumers of the reports are regarding the currency of results displayed in reports, for example do they require the figures correct as of 12pm each day or the most up-to-date figures possible?