Cache Refresh in SSRS 2008 R2
Posted by decipherinfosys on June 4, 2010
We had discussed caching options in SSRS in one of our articles before – here. In today’s post, we are going to look into the cache refresh functionality that has been introduced in R2 version of SQL Server 2008. Prior to R2, if the cache expires and a request is made to render the report, the request has to wait for the report execution to complete. One way to get around this was to create a subscription that used a NULL delivery provider to populate the cache ahead of the request by the first user. In the R2 version, we can use cache refresh feature to establish a schedule to load the reports into the cache.
In addition to loading the reports into the cache, cache refresh also allows us to load shared datasets into the cache which would benefit all the reports that use those shared datasets. Navigate to your report, click on the drop-down and select “Manage”.
Click on the cache refresh options tab and then you can create a new cache refresh plan. One thing to remember/do before you create the cache refresh plan is to configure stored credentials for the data source. What this means is that you need to go to data sources and make sure that the “Connect using credentials stored securely in the report server” option is checked and you provide a valid username and password. Once you do that and you have the report set up for caching, you can go ahead and create a cache refresh plan: