Friday, December 19, 2014

Session 19 :Caching SSRS Reports
AnandFriday, December 19, 2014 0 comments

            Session 19 :Caching SSRS Reports


Caching SSRS Reports

In this article, I will show you how to use caching for SSRS reports. But before that go through below lines which will help to understand the concept of caching in sql server reporting services.
A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period,
A report server can cache a copy of a processed report and return that copy when a user opens the report. To a user, the only evidence available to indicate the report is a cached copy is the date and time that the report ran. If the date or time is not current and the report is not a snapshot, the report was retrieved from cache.
Caching can shorten the time required to retrieve a report if the report is large or accessed frequently. If the server is rebooted, all cached instances are reinstated when the Report Server Web service comes back online.
Caching is a performance-enhancement technique. The contents of the cache are volatile and can change as reports are added, replaced, or removed.
To enable caching for a report, go through below steps :
1. First of all open Internet Explorer and go to Report Manager URL which is something like below:

Your internet explorer tab looks like below :
1-Caching SSRS Reports
2. Click on your SSRS project. In my case it is Start SSRS. So now it will show you list of reports which are deployed on your report server.
2-Caching SSRS Reports
3. Now click on down arrow on the report which you want to subscribe and select Manage as shown in below screen shot.
3-Caching SSRS Reports
4. Then select Processing Options from left pane and you will see screen shown in below screen shot. In that screen, you have three different choice.
Either you can set time in minutes
4-Caching SSRS Reports
or you can defined a schedule as shown below.
4-Caching SSRS Reports-1

5. Then click on Apply button. So we have done with Caching SSRS Reports.
Main purpose of Caching SSRS Reports is to improve the performance. To see the effect of Caching, execute following query.
?
1
2
3
SELECT ITEMPATH,USERNAME,PARAMETERS,TIMESTART,TIMEEND,TIMEDATARETRIEVAL,
TIMEPROCESSING,TIMERENDERING,SOURCE,BYTECOUNT,[ROWCOUNT]
FROM EXECUTIONLOG3
5-Caching SSRS Reports
Here you can see that it will not take even 1 second to retrieve data. Similarly you can see time difference in TIMESTART & TIMEEND.
6. You can also create Cache Refresh Plan which create a schedule for preloading the cache with temporary copies of data for a report. A refresh plan includes a schedule and the option to specify or override values for parameters.
To create a Cache Refresh Plan go to Cache Refresh Options and click on New Cache Refresh Plan.
6-Caching SSRS Reports
7. In that screen give details as shown in below screen. Then click on OK button.
7-Caching SSRS Reports
you can also check the history of Cache Refresh Plan.
7-Caching SSRS Reports-1
Congratulations! We successfully completed use of Caching SSRS Reports
In Category :
About The Author Anand Anand is a Microsoft Certified MCITP (Business Intelligence Infrastructure Using Microsoft SQL Server 2008), MCTS (SQL Server 2008, Business Intelligence Development and Maintenance) with 8 + years of experience in the Finance , Education, Healthcare, Banking and Insurance, Telecom domain focused on delivering software design, development, and data migrations from diversified data sources using Business Intelligence analysis tools..

0 comments

Post a Comment