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 Nov 30, 2007

Snapshot Reports II: SQL Server Management Studio Perspective

By William Pearson

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.



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


















Thanks for your registration, follow us on our social networks to keep up-to-date