Snapshot Reports I: Report Manager Perspective

Monday Oct 29th 2007 by William Pearson
Share:

BI Architect Bill Pearson continues his subseries surrounding Caching Options, examining the use of Report Manager to configure Snapshot 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

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.

Settings to Configure Snapshot Caching from Report Manager

As we have learned, the primary objective of Snapshot caching is to support the maintenance of snapshots (or “histories”) of selected reports over time for comparison or other purposes. “Fringe benefits” include enhanced general reporting performance, especially for more complex or larger reports, because the Snapshot reports are generated from cached intermediate files in the Report Server database.

In a manner similar to that of setting other caching options in Reporting Services, we make the required settings within the Report execution properties for the respective report. In the subsections that follow, we will examine these settings for a sample report from the perspective of the Report Manager. We will do so, as we have in other articles of this subseries, using a report from among the samples that are available to anyone installing Reporting Services 2005.

If you have not installed the samples, you’ll need to install them to use the sample report with which we perform the steps below. If you prefer to use a local report, the steps will be approximately the same for accessing the property settings for your report of choice.

Configure Snapshot Caching

Most of us are aware that, among the graphical user, command-line and programmatic interfaces that Reporting Services makes available to us for performing management activities, Report Manager is perhaps the most user-friendly. This web-based management application interfaces with the Report Server via the Web Service API, and is easily accessed using a web browser. Report Manager has many uses, and affords us a straightforward means of flexibly managing access to all reports and related resources.

Let’s take a look at how we can make Snapshot caching settings for a report within the Report Manager. To do this, we will take the following steps:

1.  Open an instance of the web browser on the PC.

2.  Point the browser to the following URL:

http://<Server_Name>/Reports

NOTE: Replace <Server_Name> in the above with the name of the server hosting Report Manager in the local environment. Also, replace the default Reports directory with the appropriate virtual directory established in the local environment, if yours is different.

Report Manager’s home page next appears, somewhat similar to that depicted in Illustration 3.


Illustration 3: Accessing the Report Manager from the Web Browser

3.  Access the sample reports (mine are within the AdventureWorks Sample Reports directory shown in Illustration 3 above), by navigating into their containing folder(s) from the home page.

4.  Click-select the Sales Reason Comparisons report from among the sample reports listed, as shown in Illustration 4.


Illustration 4: Opening the Sample Sales Reason Comparisons Report ...

The report briefly executes, and then opens.

5.  Click the Properties tab atop the report, as depicted in Illustration 5.


Illustration 5: Accessing Report Properties ...

6.  Click the Execution tab that appears on the left panel of the page, as shown in Illustration 6.


Illustration 6: Accessing the Execution Settings for the Report ...

Note: Credentials must be stored, and default parameters must be in place, for any report for which we wish to configure Snapshot caching.

We arrive at the Execution properties page, where we can set execution properties for the currently selected report only. These options determine when report processing occurs, as we have seen in other articles of this series. Among other activities, we can make settings to govern a report run during off-peak hours, and the like, for flexible resource scheduling within our own environments.

7.  On the Execution page, click-select the radio button to the immediate left of Render this report from a report execution snapshot.

8.  Select Use the following schedule to create execution snapshots.

9.  Leaving the radio button to the immediate left of Report-specific schedule selected, click the Configure button to its immediate right.

We arrive at the schedule details page, where we can establish a schedule for Snapshot report generation.

10.  Within the Schedule Details section in the upper half of the page, select the radio button labeled Month on the left side of the section.

11.  Within the Monthly Schedule box in the Schedule Details section, ensure that all months have been selected via the associated checkboxes.

12.  Select the radio button labeled On calendar day(s) within the same Monthly Schedule box, supplying a “1” in the input box to the right of the label. (Clear any residual settings in the On day of week checkboxes just above.)

13.  Modify the Start time at the bottom of the box to read “12:00 AM.”

14.  In the Start and end dates section in the bottom portion of the page, click the calendar icon to the right of the input box labeled Begin running this schedule on.

15.  Select November 1 (or other, more relevant date for the time you are performing these procedures) using the calendar selector, as depicted in Illustration 7.


Illustration 7: Selecting a Date to Begin Snapshot Execution ...

16.  In like manner, select a date exactly a year away in the input box labeled Stop this schedule on, ensuring that the selection is enabled via a checkmark in the checkbox to the left of the label.

Our settings, which establish a schedule for Snapshot generation at midnight on the first of every month, to continue through one annual cycle, appear as shown in Illustration 8.


Illustration 8: Schedule Details, with Our Input

It is useful to consider that we can, of course, initiate Snapshot execution from either the individual report level (the foregoing procedure) or from a shared schedule.

17.  Click OK (at bottom left) to save settings and to return to the Execution page.

18.  Select the radio button labeled Create a report snapshot when you click the Apply button on this page, if you wish to create a Snapshot report immediately.

Our settings on the Execution page, together with a summary of our newly established schedule for Snapshot generation, appear as depicted in Illustration 9.


Illustration 9: Execution Page Settings with Schedule Summary

19.  Click Apply.

Unless we dictate differently, a single Snapshot will be maintained with the ReportServerDB database. By default, whenever a new Snapshot report is created, it will be swapped with the single Snapshot already in place. To maintain historical Snapshots within the database, we need to make the appropriate settings on the History tab, as we shall see in the next section.

Manage Snapshot History

As we mentioned in our earlier overview of Snapshot caching, we can use settings on the History tab to manage the population of the History table with reports at the scheduled Snapshot times – primarily to support comparisons between the Snapshots over time. As we also noted, we can conveniently view, or even remove, the various stored Snapshot instances via the History tab at any time, as well. Let’s take a look at the steps involved in configuring Snapshots from the perspective of report history.

1.  Click the History tab within the left margin of the Execution page, as shown in Illustration 10.


Illustration 10: Moving to the History Settings from the Execution Page ...

On the History page, we are greeted with an initial pair of options for adding Snapshots to report history. We can check the box labeled Allow history to be created manually – which will allow us to navigate to the report involved from the Report Manager at any time, and create a new Snapshot manually, simply by clicking a button that appears after clicking the History tab atop the report viewer. We can simulate this action with the following simple steps:

2.  Place a check in the box atop the History page, labeled Allow history to be created manually, as depicted in Illustration 11.


Illustration 11: Configuring for Manual Snapshot Creation ...

3.  Click Apply in the lower left corner of the page.

4.  Click the View tab in the upper left corner of the page.

5.  Click the History tab atop the upper left corner of the report body, as shown in Illustration 12.


Illustration 12: Click the History Tab atop the Report ...

6.  On the History page, click the New Snapshot button (as depicted in Illustration 13) in the upper left corner, to use the newly enabled option to manually generate a Snapshot report.


Illustration 13: Click the New Snapshot Button to Manually Generate a Snapshot

The History Log is enabled, and presents an entry for the Snapshot we have created.

7.  Click the New Snapshot button two more times.

The three Snapshots we have generated, along with their respective execution times and size details, appear in the History Log (where we can view any of them simply by clicking the linked “When Run” information for the chosen Snapshot), as shown in Illustration 14.


Illustration 14: The Manually Generated Snapshots Appear in the History Log

The History Log, which also allows us to delete Snapshots manually, works the same if the Snapshots are added manually or automatically. We can provide for the automatic addition of Snapshots to the report history by taking the following steps:

8.  Click the Properties tab atop the History page to return to the Properties page for the report.

9.  Click the History tab, once again, in the left pane of the Properties page.

From the Properties – History page, with the appropriate selections, we can:

  • Automatically store all report execution Snapshots in history
  • Automatically add Snapshots to report history based upon a schedule:
    • Selecting a report-specific schedule (by filling in the schedule details, and selecting the start and end dates for the schedule, as we saw earlier), or
    • Selecting a shared schedule (by selecting a pre-existing schedule, as available, from the list).

We can also make settings to allow unlimited Snapshots in report history, as well as to limit their number to a preset quantity, in the lower section of the Properties page.

Our options on the Properties page appear as depicted in Illustration 15.


Illustration 15: Options for Adding Snapshots to History

10.  Be sure to turn off Snapshot generation as appropriate within the local environment, when ready.

11.  Exit Report Manager when finished...

Conclusion

In this article, we continued our subseries surrounding caching options in Reporting Services 2005. We began by naming the three types of caching that Reporting Services 2005 offers, referencing the articles within which we explore each. We then began our examination of the last of these three, Snapshot caching.

As a part of our examination of Snapshot caching, we reviewed the general purpose of this third caching option. We next reviewed details about how Snapshot caching is accomplished in Reporting Services 2005. Finally we explored the settings involved in putting Snapshot caching to work within the Report Manager interface (having noted that we address similar settings from a SQL Server Management Studio perspective in the second half of this article, Snapshot Reports II: Report Manager Perspective), including system defaults for those settings. Throughout the various sections of the article, we discussed other information about Snapshot 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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved