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.

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.

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

The Microsoft SQL Server Management Studio opens.

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.

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.