SSRS and report caching, dynamic refresh for Data Warehouses

I have a Data Warehouse, in which the data is loaded once a day and during the rest of the time the data does not change. There are several heavy reports in SSRS used to cater data to the users, and it really saves the day to cache them. The performance improvement is great, but there is one problem. I do not know exactly when the data is coming, and I need to refresh the chached report dynamically.

In SSRS there are several options for re-loading the chache of the cached reports:

– Either after N minutes, or by a schedule.

As you can see, none of these options work for me, so I looked a bit in detail in what options I have to refresh the cache manually. How do I do that?:

– There is a stored procedure in the Report Server database called:

dbo.SetChacheOptions which takes 4 parameters.

CREATE PROCEDURE dbo.SetCacheOptions    

@Path             AS NVARCHAR(425),    
@CacheReport      AS BIT,    
@ExpirationFlags  AS INT,    
@CacheExpiration  AS INT=NULL

– The @Path is the path to the report. I.e. /TestFolder/Report1 .
– The @CacheReport – is used to flag whether the report is cached or not.
– The @ExpirationFlags – is the option which points out the expiration type. 1 means that the Report expiration is set to N minutes (defined by the user) and 2 means that the report will expire according to a schedule (also created by a user).
– The @CacheExpiration – is used if the @ExpirationFlags are set to 1, i.e. to set the time in which the report will expire. The value is in minutes.

So, this said, let’s give some examples:

— Clear Cache EXEC dbo.SetCacheOptions ‘/TestFolder/Report1’,0,1
— Recreate the report cache EXEC dbo.SetCacheOptions ‘/TestFolder/Report1’,1,1,1440

In the first example, I just force the report to discard the cache. In the second example I am setting the chache to expire in 24 hours. Here is the question, though:

– how do I refresh the cache only after the data has been loaded in my database, given that there is a 6 hour window in which the data is loaded and the times are different every day?

This is relatively easy: first of all, we do not have to flush the cache before reloading it. Every time we execute the caching of the report, it will refresh the cache automatically and recreate it. So, in my SSIS routine which I use to load the data, I have an extra step after the data is loaded to just create the report cache like this:

— Recreate the report cache EXEC dbo.SetCacheOptions ‘/TestFolder/Report1’,1,1,1560

I am setting the expiration to 26 hours, just to give some tolerance for the next data load. But this does not really matter, because during the next data load I will execute the cache refresh procedure again.

/Feodor Georgiev