Managing the Report Content Using Report Manager: Caching


Introduction

In the Managing
the Report Content Using Report Manager: Part I

we saw how  to go about setting the security for the Report Manager report
items. In this article we will be focusing on caching SSRS reports.

Caching

Caching is when a copy of the processed report
after the execution is saved into into ReportServerTempDB for faster retrieval.
This is beneficial when a particular report takes a long time to create and is
accessed multiple times. In this scenario if the report is cached then the
execution time is drastically reduced as the report’s result set is already
stored in the cache and is simply returned.   

Since we will be using the ReportServerTempDB  for
caching, it is to look at the difference between the ReportServer and
ReportServerTempDB.

ReportServer Database Vs ReportServerTempDB

ReportServer:

1. Stores information about the report schema, report property, data
sources, parameters, stores the folder hierarchy, report Execution log.
2. Always exists until changes are made to the RDL Schema
3. Helps to access the structure.
4. Data always exists during SSRS service restarts

ReportServerTempDB:

1. Stores only the cached copy of the report data.
2. Expires based Expiry settings.
3. Helps to improve the performance of report execution as it is loading
data from cache.
4. Services restarts will clear the Temp Data

 Note: You need to have
a data source with stored credentials on the report server in order to implement
caching. If you have windows credentials then it will throw the below error:

Description: Stored Credential used for Caching

How
to Implement Caching in SSRS?

Implementing the caching in the SSRS is really very
simple. If you are using SSRS 2008 R2 then you need to just click on the report
on the dropdown for which the caching needs to be performed and click manage:

The following menu bar will be displayed on the
left pane from which we are going to focus on caching options.

Description: C:\Franco's data\SSP\Myarticles\part 2\S1.1.jpg

Click on
the processing option. It will show a list of options which are as follows:

Description: C:\Franco's data\SSP\Myarticles\part 2\S1.2.jpg


No Caching
Options

  • Do
    not cache temporary copies of the report.


Caching Options

  • Cache
    the temporary copy of report for limited amount of time  (only in mins).
  • You
    can schedule the cached report to be available in the system for a specific
    duration after which it will automatically expire
  • Render
    the report from the report history snapshot.


Report timeout
option

  • You
    may use the system’s default setting (which is defined in web.config file). This is
    applied to reports deployed on the report server. If you wish to change it you
    may edit the web.confg file.
  • Report
    may never timeout.
  • You
    may specify the report time out (only in mins) for this specific report only.

Click
apply to save your settings.

How to implement caching
in reporting services?

Consider
a production scenario where a particular MIS report takes a long time to process and may even  time out. Obviously there is a huge performance
issue with this kind of report. In this scenario you can either fine tune the
query or you can cache the report for faster retrieval. The below query takes more than 1 min for execution which
may be unacceptable in the live production scenario. I have purposely applied a
cross join to demonstrate the caching feature of ssrs.

 SELECT      pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName, p.EnglishProductName, frs.UnitPrice, frs.ProductStandardCost,

             frs.TotalProductCost, frs.SalesAmount

FROM         DimProductSubcategory AS psc inner  JOIN
                      DimProduct AS p on psc.ProductSubcategoryKey=p.ProductSubcategoryKey inner JOIN
                      DimProductCategory AS pc on pc.ProductCategoryKey=psc.ProductCategoryKey cross JOIN
                      FactReSellerSales
                      

In
order to perform caching for limited number of minutes follow the below steps:

1. Right
Click on the Product Sales Analysis  report. Go to Manage->Processing
options

2. I
have selected the default option of 30 mins but as you can see its a free text cell
and you can input any number of minutes you want the report to be cached
for. Click Apply.

3. At
first as you can see the the report takes the same amount of time for execution
as the default running time of the query as the reason being the report is also
being cached while its being processed.

4. When
you run the report for second time the report executes much faster than the
previous instance obivously because this time it is being rendered from the
cache.

5. As
you can see we have an option of scheduling the caching duration which will
expire after the specific amount of time. You can schedule the report anywhere from
hourly to monthly basis. Also the option of the ‘once’ is given if the report
needs to be processed and expired on a specific day.

6. You
also have an option to render the report from the snapshot option for which you
need to specfy the schedule as when this snapshot needs to be taken. A snapshot never expires but  is replaced with
newer version of the report on the next scheduled execution.

7. I
can schedule a cache refresh for my report on the schedule of hourly,daily
weekly or monthly basis. A cache refresh plan can help you preload the data on
the scheduled basis and ensure fresh data is being served.

8. Since
the cache refresh plan is a scheduling operation ensure that SQL
Server Agent is up and running.

9. In this demo I have kept the
default settings but you can customize it as per your requirements.You can also
have a shared schedule in place and use the same caching throughout the
process.

10. You can also
specify caching on parameters defined in you dataset and thus a new report will be cached
for different sets of parameters. Since I have not defined parameters the option
is unavailable for me.

11. After the cache
refresh plan has been added your screen will look something like this:

As you
can see you may have an option of creating the new plan from the exisiting one
in which all the existing plans’ settings will be made available to you
or you can always create a new cache refresh plan.

In the next article we will see how to
manage subscriptions and scheduling of the reports to be delivered into your
inbox.




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |