Report Execution Caching II: Report Manager Perspective

Friday Sep 28th 2007 by William Pearson
Share:

BI Architect Bill Pearson resumes his Caching Options subseries, examining the use of Report Manager to configure Report Execution 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 sub-series, Black Belt Administration: Caching Options: Report Session Caching and Report Execution Caching I, 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 report access and management. Through this means and others, Reporting Services provides multiple management options to administrators.

In our introductory comments within both articles, 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, can be a consideration when we choose among the available types of caching.

We stated that our objective, within the three-part 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, we will continue the exploration of Report Execution caching that we began in Report Execution Caching I, where our hands-on exercises centered upon an MSSQL Server Management Studio perspective. As a part of our continuing examination of Report Execution caching, we will:

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

Report Execution Caching in Reporting Services 2005

Report Execution caching, one of three caching options within Reporting Services 2005, is turned off by default, as we learned in Report Execution Caching I. Therefore, the most basic difference between Report Execution caching and Report Session caching (the subject the first article in this sub-series) is that the former can at least be disabled. (Report Session caching cannot be disabled in the purest sense, as we noted; Unless a report is configured for “snapshot” treatment, its intermediate format is automatically cached – at least for the duration of the report session, a parameter which can, itself, be specified for the Report Server).

A Review of the Purpose of Report Execution Caching, and Details of Its Operation

The primary objective of Report Execution caching, as we noted in Report Execution Caching I, is to provide enhanced reporting performance. This contrasts with the goal of Report Session caching, the purpose of which is to support a consistent viewing experience during a single browser session (a configurable “report session,” as we noted in Black Belt Administration: Caching Options: Report Session Caching).

As we noted in previous articles of our Caching Options sub-series, most of us are probably aware that Reporting Services stores temporary files within an underlying database to support user sessions and report processing. Each Report Server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the Report Server. Caching in general occurs for internal use, and to support a consistent viewing experience during a single browser session. Making this happen is the primary objective of Report Session caching.

As we have learned, 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. Caching the intermediate format 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 Report Execution caching differs significantly from the operation of Report Session 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. 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. This characteristic of the operation of Report Execution caching is depicted in Illustration 1.


Illustration 1: Report Execution Caching: Clients Share a Single Cached Intermediate Format

We learned 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.) By contrast, the primary objective of Report Execution caching is enhanced performance.

Administrators can specify settings for cache expiration. Expiration options include the capability to set a specific life for the cache (for example, we can direct that its life not exceed twelve hours), as well as the capability to specify a fixed schedule for cache expiration (for instance, we can direct that the cache is expired at midnight and at noon of each day). We thus have the flexibility to perform cache flushes based upon scheduled OLAP and OLTP updates, among other data-changing events, so as to provide the most current information to report consumers in a performance-enhanced manner. We can then even put into place processes to automatically run critical, popular reports “the first time,” after each cache flush, to re-instantiate the cache with the report’s updated intermediate format.

To “drill down” upon the general operation in a little more detail, we will note that the cached intermediate format is removed / swapped from the Execution cache under several conditions. The report is swapped, of course, when the cached report expires (expiration is dependent upon the expiration settings we have made). Moreover, restart of the Report Server application domain will also trigger a swap of the cached report. Further, the RS Web Service Flush-Cache method can be used to explicitly flush an existing intermediate format cache. Finally, changes to the execution settings, report definition file (.rdl), and other modifications, as well as other events, can result in the Report Server swapping out the cached intermediate format.

To use Report Execution caching, we have to ensure that a couple of credentials considerations are acceptable within the environment, and that we comply with these conditions. First, we must ensure that reports for which we wish to enable Report Execution caching do not prompt consumers for database login credentials at runtime. Second, candidate reports cannot employ Windows authentication – neither for establishing the underlying database connection (“Integrated Security”), nor within expressions contained in the reports.

We will get some hands-on exposure to enabling Report Execution caching in the section that follows, just as we did in the first half of this article, Report Execution Caching I. Instead of focusing on the configuration of Report Execution caching from within SQL Server Management Studio, however, we will turn our attention to an alternate approach. This time, we will gain some practice with Report Execution caching settings within the Report Manager interface.

Settings to Configure Report Execution Caching from Report Manager

As we have learned, the primary objective of Report Execution caching is to enhance report performance by caching temporarily, upon the initial consumer-client request, reports for which we enable such caching. We make the settings to enable this capability, as well as specify its operational behavior, within the report execution properties. Report execution properties control how a report is processed. Execution properties must be set for each report individually, although they can be set to reference pre-established defaults, as we shall see.

As we saw in our hands-on practice session in Report Execution Caching I, setting Report execution properties within the SQL Server Management Studio is both straightforward and intuitive. In the subsections that follow, we will examine these settings for a sample report from the perspective of another avenue, the Report Manager. We will do so, once again, 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 presented in the steps below. If you prefer to use a local report, the steps will be approximately the same for accessing the property settings.

Configure Report Execution Caching from the Report Manager

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 adjust Report Execution 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 appropriate.

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


Illustration 2: Accessing the Report Manager from the Web Browser

3.  Access the sample reports (mine are within the AdventureWorks Sample Reports directory shown in Illustration 2 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 depicted in Illustration 3.


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

The report briefly executes, and then opens.

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


Illustration 4: Accessing Report Properties ...

6.  Click the Execution tab that appears on the left side of the page, as depicted in Illustration 5.


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

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, and, among other activities, we can set these options to govern a report run during off-peak hours, and the like, for flexible resource scheduling within our own environments. As we have learned, one of the options that might be useful to us might be to temporarily cache copies of a frequently accessed report. The idea, of course, would be to eliminate wait time for multiple users attempting to access the same report, say, within minutes of each other.

7.  On the Execution page, click-select the radio button to the immediate left of Always run this report with the most recent data, if necessary.

Our intent is to configure the report to run “on demand,” from the cache, as we shall see with our next settings, where we schedule the expiration of the cached report, using each of two possible options.

To make the cached copy expire after a particular time period:

8.  Click Cache a temporary copy of the report. Expire copy of report after a number of minutes.

9.  Type the number “30” in the selector box to the immediate right.

Our settings on the Execution page for a half-hour expiration of the cached report appear as shown in Illustration 6.


Illustration 6: Settings for Expiration of Cached Report after a Specified Time Period

To make the cached copy expire on a schedule:

10.  Click Cache a temporary copy of the report. Expire copy of report on the following schedule.

11.  Click Configure.

The Create Custom Schedule page appears.

12.  Within the Schedule details section atop the page, to the left of the Daily Schedule box, ensure that the Day radio button is selected.

13.  Within the Daily Schedule box, click-select the radio button to the immediate left of Every weekday.

14.  Leave the Start time, in the setting at the bottom of the box, at 2:00 AM.

15.  Within the Start and end dates section of the page, type / select today’s date in the selector box that appears to the right of the label Begin running this schedule on.

The Create Custom Schedule page appears, with our settings, as depicted in Illustration 7.


Illustration 7: Create Custom Schedule Page with Our Settings

The settings above dictate a custom schedule for expiration of the cache of this specific report. Using such a fixed schedule is ideal in many scenarios, where we want to mandate Report Server processing at a specific time. An example might be setting the execution cache to expire at 2:00 AM every Monday morning, in a situation where, say, we update sales data and refresh our cubes by midnight each Sunday for reporting the following week. This means, of course, that the newly updated sales data is reflected when information consumers begin requesting reports after the opening of business on Monday morning.

16.  Click OK to accept our settings input, and to dismiss the Create Custom Schedule page.

We are returned to the Execution page, where a summary of our new settings appears as shown in Illustration 8.


Illustration 8: Execution Page Displaying Our Settings for Scheduled Cache Expiration ...

Finally, an additional setting, Report Execution Timeout, lies at the bottom of the Execution page. Here we can set a limit on the amount of time allowed for report processing – one of several “governor” properties we can set surrounding our reports to prevent “runaway conditions,” as well as perhaps to minimize the risk of overtaxing system resources in other ways. Our options for timeout default settings include:

  • Use the system default setting - directs the Report Server to use the default report execution timeout value as it is globally defined for the Report Server (which, as we learned in Report Execution Caching I, we specify within the Report Server properties, on the Server Properties dialog, Execution page, as depicted in Illustration 9).


Illustration 9: Execution Page of the Server Properties Dialog – Global Timeout Default

  • Do not timeout report execution - directs the Report Server to impose no limitations on report execution time.
  • Limit report execution to the following number of seconds - timeout value is defined as a specific number of seconds (with the defaulted selector value the same as the global default setting that is defined in the corresponding Server Properties setting, as noted in the Use the default setting option above).

17.  Click the View tab to depart the Execution page, leaving all settings at their original positions.

18.  Exit Report Manager when ready.

Conclusion

In this article, we continued a three-part subseries surrounding caching options in Reporting Services 2005. We began by briefly reviewing the three types of caching that Reporting Services 2005 offers. We then resumed our examination of the second of these three, Report Execution caching.

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