Report Session Caching in Reporting Services 2005

Black Belt Administration: Caching Options: Report Session Caching

About the Series …

This
article is a member of the series MSSQL Server Reporting Services.
The series is designed to introduce MSSQL Server Reporting Services (“Reporting
Services
”), presenting an overview of its features, with tips and
techniques for real-world use. For more information on the series in general,
please see my initial Database
Journal

article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools
needed to complete the hands-on portion of this article, see BlackBelt
Administration: Linked Reports in Report Manager
, another article within this
series.

Introduction

As we
have noted in other articles of this series, a common attribute of enterprise
reporting systems is their provision for single points of maintenance for various
aspects of system configuration. Reporting Services meets the general
need for centralized maintenance of reports and their constituent components by
housing them within a central “report catalog,” facilitating easier report
access and management. Reporting Services provides multiple management
options to administrators.

One of
many capabilities that Reporting Services offers administrators is caching.
During report execution, the three basic steps taken by the Report
Server
include:

  • Retrieval of data from the specified data
    source(s);
  • Merging of the retrieved data with
    the layout information specified by the report author;
  • Generation of the intermediate format
    of the report (which is next turned into the final report output within the rendering
    stage).

The Report
Server
can cache the intermediate format of the report, to shorten the time required to retrieve a
report. This accelerated retrieval can mean an improved user experience,
particularly in cases where the report is large or accessed frequently. Caching
is a performance-enhancement technique that is effective in many cases,
although cache content volatility (the tendency of content to change as
reports are added, replaced, or removed) as well as other
factors, can be a consideration when we choose among the available types of caching.

Within
the three-part Black Belt Administration: Caching Options
subseries, we will introduce and overview the three types of caching
that Reporting Services 2005 offers administrators. The three
caching
types are Report Session, Report Execution, and Snapshot.
In this article, we will explore Report Session caching. As a part of
our examination of Report Session caching, we will:

  • Discuss the
    general purpose of Report Session caching;
  • Review details
    about how Report Session caching operates in Reporting Services 2005;
  • Explore the
    settings involved in putting Report Session caching to work, including
    system defaults for those settings;
  • Include other
    information about Report Session caching that may prove useful in
    selecting or discarding this option for use within our own business
    environments.

Report Session Caching in Reporting Services 2005

The three caching options
within Reporting Services 2005 have numerous characteristics
in common. One way that Report Session caching differs, however, lies
in the fact that it cannot be “disabled” in the purest sense. Unless a report
is configured for “snapshot” treatment, its intermediate
format
is automatically cached
– at least for the duration of the report session, a parameter which
can, itself, be specified for the Report Server, as we shall see.

Purpose of Report Session Caching

We can begin discussing Report
Session caching
with a definition of a report session. Each report
session
is associated with a single, requesting consumer client for
a given report. The report session setting represents a modifiable
duration of time within which our Report Server is allowed to serve
report requests 1) from the same consumer client, and 2) from the same intermediate
format
that has been cached on the server. The purpose of report
session caching
is to support a consistent viewing experience during a
single browser session (a configurable “report session,” as we have
noted).

Because the cached copy of
the report (its intermediate format) is used by the consumer until the report session
expires, we obtain both enhanced performance (the retrieved dataset is
already stored and “waiting” for the consumer – it does not have to be
retrieved upon request) and consistent data. Consistency is
important, in most cases, in that the report data remains static during the
consumer report session – that is, if the source data underlying the
report changes during a report session, the data remains fixed within
the displayed report, or even when the same data is exported via the Report
Server
within the same report session.

Details of Report Session Caching Operation

Most of us are aware that Reporting Services stores
temporary files within an underlying database to support user sessions and
report processing. Each Report Server database uses a related temporary
database to store session and execution data, cached reports, and work
tables that are generated by the report server. Caching in general
occurs for internal use and to support a consistent viewing experience during a
single browser session, as we have noted to be so in the case of Report Session
caching
.

Anytime a report is requested from a given consumer client,
Reporting Services caches the intermediate format for the
report – not the ultimate report output – within the ReportServerTemp database.
Caching the intermediate format means that varied rendering
options can still be applied upon the cached data (the intermediate
format
) to offer the performance benefits of caching (primarily
speed and consistency, as we have noted) while still offering flexibility in
the appearance of multiple renderings.

As we see in the Illustration 1 below, each client
request entails the creation of a report session cache client-report
“version” of an intermediate format file. If two consumer clients request
the identical report, as is the case with Client A and Client B,
each of whom has requested Report 1, a
separate intermediate format is created and assigned a session
identifier. (Clients C and D in the illustration have requested
different reports.) For each client, for any requests that follow (within the
specified report session duration) with the same client / session
identifier combination, the intermediate format is cached within
the temporary database.



Illustration
1: Report Session Caching: Individual Client Intermediate Formats

To summarize the operation of Report Session caching,
when a client makes a report request, the retrieved data and
report layout are merged into intermediate format, and stored in
the session cache. The intermediate format within the cache can
be rendered multiple times, in multiple formats, within the duration of the report
session
. The report session is, itself, configurable
(we will discuss the settings involved in the next section), but we cannot
completely disable Report Session caching.

Settings to Configure Report Session Caching

While we cannot completely disable Report Session caching, we do have
options to adjust settings to adapt its operation to more closely meet our
needs. We can directly control two primary aspects of Report Session caching:
1) we can specify how a client requesting a report is
associated with a report session, and 2) we can set the expiration
interval
of the report session (and thus the duration of the session).

Let’s take a look at how we can adjust these settings
within our own environments. To do this, we will work from within the Microsoft SQL Server
Management Studio
.

1. 
Start Microsoft
SQL Server Management Studio
.

2. 
Select Database
Engine
in the Server type selector of the Connect to Server
dialog that appears.

3. 
Type / select
the server name (server name / instance, if appropriate) in the Server name
selector.

4. 
Supply
authentication information, as required in your own environment.

The Connect to Server
dialog appears similar to that depicted in Illustration 2.



Illustration 2:
Connecting to the Server …

5. 
Click the Connect
button to connect with the specified server.

The Microsoft SQL
Server Management Studio
opens.

6. 
In the Object
Explorer
pane (it appears by default on the left side of the Studio),
expand the Databases folder (click the “+” sign to its immediate left),
appearing underneath the server within which we are working.

7. 
Expand the ReportServer
database (click the “+” sign to the immediate left of its icon).

8. 
Expand the Tables
folder to expose the database tables.

9. 
Right-click
the ConfigurationInfo table.

10. 
Select Open
Table from the context menu that appears, as shown in Illustration 3.



Illustration
3: Opening the ConfigurationInfo Table

The ConfigurationInfo table
opens. Here we can make modifications to adjust the operation of Report
Session
caching. There are two controllable options for this type of caching.
First, we can adjust the UseSessionCookies setting, which allows us to
dictate how a requesting information consumer client is associated with
a report session. Default setting for this property is “true,”
although we have the option for setting this to “false,” and disallowing
the use of session cookies. The “false” alternative will direct the
Report Server to embed the session identification information into the URL
address
for the report involved.

The other configuration
option that is available to us with Report Session caching lies
in our ability to set the duration of the report session by
adjusting the expiration interval. We manage expiration interval
via the SessionTimeout setting. Default for the setting is ten (10) minutes
(or 600 seconds).

The default UseSessionCookies
and SessionTimeout settings appear as depicted in Illustration 4.



Illustration 4: ConfigurationInfo
Table – Report Server Caching Settings

When making changes to the UseSessionCookies and SessionTimeout
settings, we will, of course, want to consider such things as their impact
upon overall security and any risks involved in presenting stale data,
respectively. And while there are a couple of ways, from a technical
perspective, of “refreshing” the data at any time by clearing the current report
session
, consumers can also accomplish this by using the Refresh Report
button within the report toolbar, as appropriate.

Conclusion

In this
article, we introduced a three-part subseries surrounding caching
options in Reporting Services 2005. We began by overviewing
the three types of caching that Reporting Services 2005 offers:
Report Session, Report Execution, and Snapshot. We then
introduced the first of these three, and launched our overview of Report
Session caching
.

As a part
of our examination of Report Session caching, we discussed the general
purpose of this first caching type. We next reviewed details about how Report
Session
caching is accomplished in Reporting Services 2005.
Finally we explored the settings involved in putting Report Session caching
to work, including system defaults for those settings. Throughout the various
sections of the article, we discussed other information about Report Session
caching in an attempt to assist in selecting or discarding this option
for use within our own business environments.

»


See All Articles by Columnist
William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles