Tuesday, December 16, 2014

SSRS Reports: Caching Reports
AnandTuesday, December 16, 2014 7 comments

SSRS Reports: Caching 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:
http://bhushan-pc/Reports2012
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 selectManage 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..

7 comments:

  1. Thank you for sharing this knowledge in a blogpost.Really simple and even more effective and this worked great, very useful tips
    MSBI Training In Hyderabad

    ReplyDelete
  2. Hello,
    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.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This idea is mind blowing. I think everyone should know such information like you have described on this post. Thank you for sharing this explanation. Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site..
    Salesforce Training in Chennai

    Salesforce Online Training in Chennai

    Salesforce Training in Bangalore

    Salesforce Training in Hyderabad

    Salesforce training in ameerpet

    Salesforce Training in Pune

    Salesforce Online Training

    Salesforce Training

    ReplyDelete
  5. Thank you for your valuable information.
    Keep Updating...

    MSBI Online Training

    ReplyDelete
  6. nice information thanks for sharing this article very useful for me ................!
    msbi course training

    ReplyDelete