Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 28, 2007

Report Session Caching in Reporting Services 2005

By William Pearson

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM