Systems Engineering and RDBMS

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:

We can choose from a variety of schedule options:

Resources:

  1. Cache Refresh in SSRS 2008 R2 – TechNet article – here.
  2. TechNet article on caching of shared data sets – here.

3 Responses to “Cache Refresh in SSRS 2008 R2”

  1. Mahesh said

    Shared Datasets in SQL Server 2008 R2 was good

  2. Mahesh said

    The info is useful

  3. […] with the new features.  We had covered some of them in our posts before – Data Visualizations, Cache Refresh and Powerpivot – I and […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: