Snapshot Reports I: 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 subseries, Black
Belt Administration: Caching Options: Report Session
Caching
, and Report Execution Caching parts I
and II,
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 access and
management. Through this means and others, Reporting Services provides
multiple management options to administrators.

In our
introductory comments within the first three articles of this subseries, 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,
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, together with its sequel, we will conclude our exploration of caching
options
with an introduction to Snapshot caching. 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 Report Manager, 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

Snapshot caching, one of three caching options within Reporting
Services
2005, 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.

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 at differing
dates.

In addition to allowing us to maintain various “snapshots
in time” for comparison purposes, Snapshot caching also 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,
an example of 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 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 in earlier articles of 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, once the first client
requests the report (typically in unattended mode), among it 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 in this article, 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 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
required for parameters at the scheduled execution times.

We can specify scheduling for Snapshot generation
within Report Manager, as we shall see in the next section. Moreover, 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. We will gain some practice with Snapshot caching
settings within the Report Manager interface in this article, and
examine similar settings from the SQL Server Management Studio in Part II.

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