Snapshot Reports II: SQL Server Management Studio 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 subseries, Black
Belt Administration: Caching Options:

Report Session Caching, Report Execution Caching Parts I and II, and Snapshot
Reports Part I
, we discussed the fact that a common
characteristic 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 access and management. Through
this means and others, Reporting Services provides multiple management
options to administrators.

In our
introductory comments within each of the articles of this subseries, we noted
that one of many capabilities that Reporting Services offers
administrators is caching. During report execution, we learned, 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).

We
noted in several instances in the previous articles that, because the Report
Server
can cache the intermediate format of the report, 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 is accessed frequently. As
we also observed, 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, present themselves as considerations when
we choose among the available types of caching.

We stated
that our objective, within the 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, the
second of two parts, we will conclude the exploration of caching options
we began with an introduction to Snapshot caching in Snapshot
Reports I: Report Manager Perspective
. As a part of our
examination of Snapshot caching, we will:

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

Snapshot Caching in Reporting Services 2005

As we noted in Snapshot
Reports I: Report Manager Perspective
,
Snapshot caching
is turned off by default. A significant difference
between both Report Session and Report Execution caching (the
subjects of earlier articles in this subseries) and Snapshot caching is that the first two
options afford us little “fine tuning” opportunity with regard to overall
control. We cannot, for instance, dictate the precise point in time at which a
given report expires because we cannot know in advance when the
countdown to expiration (the point at which a given information consumer
initially executes the report) begins.

As we pointed out in Snapshot Reports I,
business requirements often dictate the need to be able to maintain our reports
at specific points in time. A simple example might be the need to
maintain an income statement at the end of each month, to allow management to
compare the results presented in each against other months of the current year,
or even earlier years. Moreover, various balance sheet accounts, such as
inventory (the beginning and ending balances of which typically change
frequently), might present opportunities for such a comparison over periods. Snapshot
caching
provides the option for storing reports from differing periods, in
a manner that enables us to support the need in each of the foregoing examples
– a need to compare the values contained in the report under consideration as
they existed at differing dates.

In addition to allowing us to maintain various “snapshots
in time” for comparison purposes, Snapshot caching provides the
additional benefit of enhancing report performance (similarly to other caching
options), in that the report is served from a copy of the associated intermediate
format
that is stored within the Report Server database. Snapshot
caching differs from Report Execution caching (which we examined
in Report
Execution Caching Parts I
and
II)
in several ways, of course. First, in the case of Snapshot caching, the
associated intermediate format is stored within ReportServer DB,
the Report Server configuration database (the intermediate format
is stored in the ReportServer TempDB database for Report Execution
caching
.) Snapshots are cached within the SnapshotData table,
the layout of which is depicted in Illustration 1.



Illustration
1: Layout of the SnapshotData Table within the ReportServer DB

Snapshot caching offers us more flexibility, as we learned in Snapshot Reports I,
than Report Execution caching in some respects, and less in others. For
example, within our use of Snapshot caching, we can dictate the precise
time of cache refreshment – a specific time that we select, as opposed to the
absence of such control when we use Report Execution caching. And while
we typically generate our Snapshot reports based upon a specific time
(such as just prior to midnight on the last day of a given month, in the case of a
“month-end balances” snapshot), we also have the flexibility to specify ad hoc
execution at any time we wish through the Report Manager or
programmatically.

Examples of scenarios where Snapshot
caching
allows less flexibility than Execution caching include lack
of interactivity (as an illustration, Snapshot reports scheduled for a
specific execution time do not afford information consumers the capability to
interactively modify report parameters, where applicable) – in fact, when we
establish Snapshot caching with parameterized reports, we have to supply
default values for the parameters involved.

An Overview of the Purpose of Snapshot Caching, and Details of Its Operation

The primary objective of Snapshot
caching
, as we noted earlier, is to support our need to maintain snapshots
(or “histories”) of selected reports over time for comparison or other
purposes. Added benefits include enhanced reporting performance, particularly
in the case of more complex or larger reports, because the Snapshot reports
are generated from intermediate files stored in the Report Server database.

To restate what we have learned throughout our Caching Options
subseries, 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
(in the case of either of the Report Session or Report Execution
caching options). Caching the intermediate format, as we noted in
general, 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 Snapshot caching differs
significantly from the operation of Report Session or Report
Execution 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. We then noted,
in Report
Execution Caching Parts
I and
II, that, 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.

With Snapshot caching, a single intermediate
format
file “version” (recall that parameters are not a
consideration, as the required default parameter is used in generating
the report instance concerned) is stored in the ReportServerDB database
(instead of the ReportServerTemp database used by the Report Session or
Report Execution caching
options). Once a Snapshot-configured
report has been executed, and an instance of its intermediate format is
available in the SnapshotData table of the ReportServerDB database,
the cached instance of the report is shared among the first client
requesting the report (typically in unattended mode) and other clients
that later request the same report.

This characteristic of the operation of Snapshot
caching
is shown in Illustration 2.



Illustration
2: Snapshot Caching: Clients Share a Single Cached Intermediate Format

We learned in Caching Options: Report
Session Caching
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.) Moreover, as we learned in Report Execution Caching, Parts I and II, the primary objective of Report
Execution caching
is enhanced performance. By contrast, as we noted
earlier, the primary goal of Snapshot caching is to support our needs to
maintain Snapshots of selected reports over time for comparison or other
purposes (although enhanced reporting performance is, once again, typically an
added benefit).

As with other caching modes, if the Report Server
is called upon to execute a given report, and if the report instance does not
exist in cache, then the Report Server generates the report and,
based upon its Report Manager (or, as we shall see in this article, its SQL
Server Management Studio
) execution settings, performs caching
within the ReportServerDB
database, SnapshotData
table, as we have noted, if the settings for the report dictate Snapshot treatment
(and within the ReportServerTemp database otherwise). When we schedule
a report for Snapshot caching, information consumers can no longer make parameter
selections at runtime – parameters are disabled because Snapshots
are typically executed, as we noted earlier, in unattended mode, and we
are thus compelled by the Report Server to supply the default values
that are required for parameters at the scheduled execution times.

We got hands-on exposure in Snapshot Reports I to scheduling Snapshot
generation within Report Manager. We can accomplish scheduling from the
SQL Server Management Studio, too, as we shall see in the next section. As
we noted in Part I,
we can use the Store All Report Execution Snapshots in History setting
(by default, only a single instance of the Snapshot, representing its
most recent execution, is maintained in the ReportServerDB database), as we shall also see,
to populate the History table with reports at the scheduled Snapshot times,
to support consumer comparisons between the Snapshots over time. We can
conveniently view, or even remove, the various stored Snapshot instances
via the Report History tab at any time.

We will get some
hands-on exposure to enabling Snapshot caching in the section that
follows, just as we did in our earlier articles Caching Options: Report Session Caching and Report Execution Caching parts I and II, for the respective caching options. This time, we will gain some practice with Snapshot
caching
settings within SQL Server Management Studio, in parallel to
our exposure to the settings to accomplish the same ends from the Report Manager
interface in Part I.

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