Report Execution Caching II: Report Manager Perspective

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

In the
previous articles of this sub-series, Black
Belt Administration: Caching Options: Report Session
Caching
and Report
Execution Caching I
, we discussed the fact that a common
attribute of enterprise reporting systems is their provision for unified points
of maintenance of various aspects of system configuration. We also noted that 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. Through this means and
others, Reporting Services provides multiple management options to
administrators.

In our
introductory comments within both articles, we noted that 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).

Because
the Report Server can cache the intermediate format of the report, we learned, the
time required to retrieve a report can be shortened. 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.

We stated
that our objective, within the three-part Black Belt Administration:
Caching Options
sub-series, is to 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 continue the
exploration of Report Execution caching that we began in Report Execution
Caching I
, where our hands-on exercises centered upon an MSSQL
Server Management Studio
perspective. As
a part of our continuing examination of Report Execution caching, we
will:

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

Report Execution Caching in Reporting Services 2005

Report Execution caching, one of three caching options within Reporting
Services
2005, is turned off by default, as we learned in Report Execution
Caching I
. Therefore, the
most basic difference between Report Execution caching and Report
Session
caching (the subject the first article in this sub-series) is that the former can at least be disabled.
(Report Session caching cannot be disabled in the purest sense, as we
noted; 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
).

A Review of the Purpose of
Report Execution Caching, and Details of Its Operation

The primary objective of Report
Execution caching
, as we noted in Report Execution Caching I, is to provide enhanced reporting performance. This
contrasts with the goal of Report
Session caching
, the purpose of which
is to support a consistent viewing
experience during a single browser session (a configurable “report session,”
as we noted in Black Belt Administration: Caching Options: Report Session Caching).

As we noted in previous articles of our Caching Options sub-series, most of us are probably 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.
Making this happen is the primary objective of Report Session caching.

As we have learned, 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.

The operation of Report Execution caching differs
significantly from the operation of Report Session caching. As we
learned in Black Belt Administration:
Caching Options:
Report Session
Caching
, each consumer-client’s request for a report entails
the creation of a separate report session cached, client-report
“version” of an intermediate format file. Although Report Execution
caching
is similar in its employment of the intermediate format that
is cached within the ReportServerTemp database, it has little
else in common with Report Session caching. With Report Execution
caching
, a single intermediate format file “version” (at least for
each report with the same, or no, parameters) is shared, once the first client
requests the report, among it and other clients that later request the
same report. This characteristic of the operation of Report Execution
caching
is depicted in Illustration 1.



Illustration
1: Report Execution Caching: Clients Share a Single Cached Intermediate Format

We learned that the primary objective upon which Report
Session caching
is based is the support of a consistent viewing experience
during a single browser session – a configurable “report session,” as we
noted. (Performance enhancement is, as we also discovered, a benefit we
obtain. This is a natural result of the use of a cached copy of the
report’s intermediate format by the consumer-client; the retrieved
dataset is already stored and “waiting” for the consumer – it does not have to
be retrieved upon request.) By contrast, the primary objective of Report
Execution caching
is enhanced performance.

Administrators
can specify settings for cache expiration. Expiration options
include the capability to set a specific life for the cache (for
example, we can direct that its life not exceed twelve hours), as well as the
capability to specify a fixed schedule for cache expiration (for
instance, we can direct that the cache is expired at midnight and at noon of each day). We thus have the
flexibility to perform cache flushes based upon scheduled OLAP and OLTP
updates, among other data-changing events, so as to provide the most current
information to report consumers in a performance-enhanced manner. We can then
even put into place processes to automatically run critical, popular reports
“the first time,” after each cache flush, to re-instantiate the cache
with the report’s
updated intermediate
format
.

To “drill down” upon
the general operation in a little more detail, we will note that the cached intermediate format is removed / swapped from the Execution
cache
under several conditions. The report is swapped, of course, when the
cached report expires (expiration is dependent upon the expiration settings we have made). Moreover,
restart of the Report Server application domain will also trigger a swap of the cached report. Further, the RS Web
Service Flush-Cache
method can be used to explicitly flush an existing intermediate
format
cache. Finally, changes to the execution settings, report definition file
(.rdl), and other modifications, as well as other events, can result in the Report Server swapping out the cached intermediate
format
.

To use Report
Execution caching
, we have to ensure that a couple of credentials
considerations are acceptable within the environment, and that we comply with
these conditions. First, we must ensure that reports for which we wish to
enable Report Execution caching do not prompt consumers for database login
credentials
at runtime. Second, candidate reports cannot employ Windows
authentication
– neither for establishing the underlying database
connection (“Integrated Security”), nor within expressions contained in
the reports.

We will get some
hands-on exposure to enabling Report Execution caching in the section
that follows, just as we did in the first half of this article, Report Execution Caching I. Instead of focusing on the
configuration of Report
Execution caching

from within SQL Server Management Studio, however, we will turn our
attention to an alternate approach. This time, we will gain some practice with
Report Execution caching settings within the Report Manager
interface.

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