Snapshot Reports II: SQL Server Management Studio Perspective

Friday Nov 30th 2007 by William Pearson
Share:

BI Architect Bill Pearson concludes his subseries surrounding Caching Options, examining the use of SQL Server Management Studio to configure / manage 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, 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.

Settings to Configure Snapshot Caching from SQL Server Management Studio

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 SQL Server Management Studio. 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

In Part I, we noted 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. In this article, we will examine the use of SQL Server Management Studio to achieve similar ends. Among many other management capabilities, SQL Server Management Studio affords us a straightforward means of flexibly performing administration of reports and related Reporting Services resources.

Let’s take a look at how we can work with Snapshot caching settings for a report within the SQL Server Management Studio. To do this, we will take the following steps:

1.  Start Microsoft SQL Server Management Studio.

2.  Select Reporting Services in the Server type selector of the Connect to Server dialog that appears, if necessary.

3.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

4.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears similar to that depicted in Illustration 3.


Illustration 3: Connecting to Reporting Services ...

5.  Click the Connect button to connect with the specified server.

The Microsoft SQL Server Management Studio opens. The Reporting Server to which we have connected appears atop the Object Explorer pane.

6.  In the Object Explorer pane, expand the Home folder (click the “+” sign to its immediate left), appearing underneath the server within which we are working.

7.  Navigate to, and expand, the subfolder containing a sample report with which to complete the steps of this section, as appropriate (my sample reports have been deployed within the AdventureWorks Sample Reports folder, and appear, with this step, as shown in Illustration 4.


Illustration 4: The Sample Reports Appear

8.  Right-click the Sales Reason Comparisons report (or another report of your choice).

9.  Select Properties from the context menu that appears next, as depicted in Illustration 5.


Illustration 5: Accessing Report Properties from the Object Explorer ...

The Report Properties dialog for the selected report opens.

10.  Click Execution in the Select a page pane on the left side of the dialog, 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.

The Execution properties page opens in the area to the right of the Select a page pane. Here 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.

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

12.  Select (by placing a checkmark in the checkbox to its left, if necessary) Create a snapshot on the following schedule.

13.  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 Create Custom Schedule page, where we can establish a schedule for Snapshot report generation.

14.  Within the Range of Recurrence section in the upper half of the page, to the right of the Begin running this schedule on, select January 1, 2008 (or another, more relevant, date for the time at which you are performing these procedures) using the calendar selector, as depicted in Illustration 7.


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

15.  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.

16.  Select Month as the Type, within the Recurrence Pattern section just beneath the Recurrence Range section.

17.  Within the scheduling details settings in the boxed section that next appears below, modify the Start time atop the section to read “12:00:00 AM.”

18.  Ensure that all months have been selected via the associated checkboxes.

19.  Select the radio button labeled On calendar day(s), at the bottom of the boxed section, and supply a “1” in the input box to the right of the label.

Our Create Custom Schedule page settings, which establish a schedule for Snapshot generation in the earliest minutes of the first day 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, as we found in our examination of performing parallel settings via Report Manager (in Part I), initiate Snapshot execution from either the individual report level (the foregoing procedure) or from a shared schedule.

20.  Click OK (at bottom left) to save settings and to return to the Execution page within the Report Properties dialog.

21.  Select the radio button labeled Create a snapshot of the report when this page is saved, if you wish to create a Snapshot report immediately.

Our settings on the Execution page, among which is included a summary of our newly established schedule for Snapshot generation, appears as depicted in Illustration 9.


Illustration 9: Execution Page Settings with Schedule Summary

NOTE: For details surrounding the Report Execution Timeout Defaults settings in the bottom section of the Execution page, see Report Execution Caching Parts I and II.

22.  Click OK to apply our settings (creating a Snapshot in the process, if we have directed the Report Server to do so using the Create a snapshot of the report when this page is saved setting we examined above), and to dismiss the Properties dialog for the report we have chosen.

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 (both in this article and in Part I), we can use History settings 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.  Right-click the Sales Reason Comparisons report (or the report with which you have chosen to complete our practice session), once again.

2.  Select Properties from the context menu that appears next, as we did earlier.

The Report Properties dialog for the selected report opens, as before.

3.  Click History in the Select a page pane on the left side of the dialog.

On the History page, we are greeted with 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:

4.  Place a check in the box atop the History page, labeled Allow history to be created manually, as shown in Illustration 10.


Illustration 10: Configuring for Manual Snapshot Creation ...

5.  Click OK in the lower right corner of the dialog, to apply our setting and to dismiss the dialog.

We will now transit to the report within the Report Manager, where we will simply execute it and so forth. We will not deal with report properties, at this level, in the sense that we did in Part I, where we performed the same steps we have been performing in this article from within the Report Manager, versus the SQL Server Management Studio.

We will leave SQL Server Management Studio open in its present position as we take the following steps.

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

7.  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 11.


Illustration 11: Accessing the Report Manager from the Web Browser

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

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


Illustration 12: Open the Sample Sales Reason Comparisons Report ...

The report briefly executes, and then opens.

10.  Click the History tab atop the upper left corner of the report body, as depicted in Illustration 13.


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

11.  On the History page, click the New Snapshot button (seen circled in Illustration 14) in the upper left corner, to use the newly enabled option to manually generate a Snapshot report.


Illustration 14: 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.

12.  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), similar to those shown in Illustration 15.


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

We can thus see the action that is enabled by our setting within the SQL Server Management Studio to allow for manual Snapshot creation.

As we noted in Part I, the History Log, which also allows us to delete Snapshots manually, works the same if the Snapshots are added manually or automatically. We saw in the previous article how we can provide for the automatic addition of Snapshots to the report history within Report Manager. We will provide this same capability from the SQL Server Management Studio by taking the following steps:

13.  Return to the open SQL Server Management Studio.

14.  Right-click the Sales Reason Comparisons report (or the report with which you have chosen to complete our practice session), yet again.

15.  Select Properties from the context menu that appears next, as we did earlier.

The Report Properties dialog for the selected report opens, as before.

16.  Click History in the Select a page pane on the left side of the dialog, as before.

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), 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 16.


Illustration 16: Options for Adding Snapshots to History

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

18.  Exit SQL Server Management Studio 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 the second part of 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 SQL Server Management Studio interface (we addressed similar settings from a Report Manager perspective in the first half of this article, Snapshot Reports I: 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