Report Session Caching in Reporting Services 2005

Tuesday Aug 28th 2007 by William Pearson
Share:

BI Architect Bill Pearson begins a three-part sub-series on Caching Options within Reporting Services 2005. In this article, we focus upon Report Session Caching.

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.

MSSQL Server Reporting Services
- Introducing the Tablix Data Region: Basic Grouping Concepts
- Introducing the Tablix Data Region in Reporting Services 2008
- 100% Stacked Column Chart for Analysis Services Data
- XY (Scatter) Chart for Analysis Services Data
- Simple Doughnut Chart for Analysis Services Data
- Exploded Pie Chart for Analysis Services Data
- Stacked Bar Chart for Analysis Services Data
- Line Chart for Analysis Services Data
- Stacked Column Chart for Analysis Services Data
- A More Advanced Pie Chart for Analysis Services Data
- Simple Pie Chart for Analysis Services Data
- Simple Bar Chart for Analysis Services Data
- Simple Column Chart for Analysis Services Data
- Introducing Reporting Services Charts for Analysis Services
- Mastering OLAP Reports: Parameterized Grouping
- Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
- Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
- Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
- Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
- Support Parameterization from Analysis Services - Parameter Defaults
- Parameterization from Analysis Services - Cascading Picklists
- Support Parameterization from Analysis Services
- Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
- Reporting Services: Customize Automatically Created Parameter Support Objects
- Snapshot Reports II: SQL Server Management Studio Perspective
- Snapshot Reports I: Report Manager Perspective
- Report Execution Caching II: Report Manager Perspective
- Report Execution Caching I: SQL Server Management Studio Perspective
- Report Session Caching in Reporting Services 2005
- Black Belt Administration: Reporting Services Configuration Manager
- Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
- Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
- Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
- Mastering OLAP Reports: Extend Reporting Services with Custom Code
- Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
- Black Belt Components: Support Simple Navigation with a Document Map
- Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
- Black Belt Components: Interactive Sorts within a Matrix Data Region
- BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
- Mastering OLAP Reporting: Prototype KPIs in Reporting Services
- BlackBelt Administration: Linked Reports in SQL Server Management Studio
- BlackBelt Administration: Linked Reports in Report Manager
- Mastering OLAP Reporting: Reporting with Analysis Services KPIs
- Report Builder: Creating a Report Model
- Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
- Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
- Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
- Interactive Sorting Within Reporting Services
- MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
- MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
- MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
- MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
- MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
- MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
- MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
- MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
- MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
- MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
- MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
- MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
- MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
- MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
- MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
- MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
- MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
- MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
- MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
- MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
- MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
- MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
- MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved