Execution Snapshots | Microsoft SQL Server 2000 Reporting Services Step by Step (Pro-Step by Step Developer)

Report caching is a great tool for improving the performance of reports with long execution times. However, there is still one problem. The first user who requests the report after the cached instance has expired has to wait for the report to be created from the underlying data. It would be nice if there was a way to have cached report instances created automatically so that no user has to endure these wait times. Fortunately, Reporting Services can do this as well.

An execution snapshot is another way to create a cached report instance. Up to this point, we have discussed situations where cached report instances are created as the result of a user action. A user requests a report, and a copy of that report’s intermediate format is placed in the report cache. With execution snapshots, a cached report instance is created automatically.

Execution snapshots can create cached report instances on a scheduled basis, or they can be created as soon as this feature is turned on for a particular report. If a schedule is used, each time the schedule is run, it replaces the current cached instance with a new one. Cached report instances created by an execution snapshot are used to satisfy user report requests the same as any other cached report instance.

Enabling Execution Snapshots

There are two methods for enabling the creation of execution snapshots. We will look at the manual method first.

Manually Creating an Execution Snapshot

Let’s try enabling execution snapshots for the Weather report:

Navigate to the Weather report in the Report Manager, if you are not already there.
Select the Properties tab. The Properties page will appear.
Select Execution from the left side of the screen. The Execution Properties page will appear.
Select the option Render This Report from an Execution Snapshot.
Check the Create a Snapshot of the Report When the Apply Button Is Selected check box.
Click Apply. As soon as you click Apply, the Report Server will execute the report and place an instance of the report in the report cache. Allow time for this process to complete.
Select the View tab.

The report will be rendered from the cached report instance created by the execution snapshot.

Creating Execution Snapshots on a Schedule

Now let’s try the scheduled approach to creating execution snapshots:

Select the Properties tab. The Execution Properties page should appear. If not, select Execution from the left side of the page.
Check the Use the Following Schedule to Create Execution Snapshots check box.
Report-Specific Schedule will be selected by default. Click Configure next to Report-Specific Schedule. The Schedule page will appear.
You can specify hourly, daily, weekly, monthly, or one-time schedules. The Day option should be selected by default. Leave this option selected.
Select On the Following Days.
Uncheck all the days except for today. (If you are reading this on Monday, for example, leave only Monday checked.)
Set the start time to 5 minutes from now.
Select today’s date for Begin Running This Schedule On.

Check the box Stop This Schedule On and then select tomorrow’s date.

Note

I know this schedule does not fit the stated business requirements of refreshing the report at 5 minutes past the hour. However, you probably don’t want to waste computer resources generating an execution snapshot of the Weather report, so we will use this schedule for the demonstration.

Click OK to return to the Execution Properties page. Note the description of the schedule you just created under Report-Specific Schedule.
Click Apply to save your changes to the execution snapshot settings. After 5 minutes, the scheduled execution snapshot will create a cached instance of the report.
Select the View tab after 5 minutes. (Go grab some caffeine while you are waiting. You wouldn’t want to fall asleep while you are working through all this good stuff!) The Weather report will run and will be rendered from the cached report instance created by your scheduled execution snapshot.

This type of execution snapshot schedule would be appropriate for a report whose underlying data is changed only once per week (again, think of a data warehouse that is updated from a transactional system). The execution snapshot would be scheduled to create a new cached instance of the report right after the new data is available in the warehouse.

Execution Snapshots and Report Parameters

Execution snapshots are created either as soon as the feature is turned on or on a scheduled basis. Because this happens as a background process with no user interface, there is no opportunity to specify parameter values at the time that the execution snapshot creates the cached instance of the report. This fact makes using execution snapshots a little more of a challenge with reports that include parameters.

The way to get around this problem is to specify default values for all the report parameters. When the execution snapshot creates the cached report instance, it will use the default values for each of the report parameters. A side effect of this is that these default values actually become locked in and you can no longer change them.

Execution Snapshots and Security

Not all users can change execution snapshots. In order to change the execution snapshot properties for a report, you must have rights to the Manage Reports task. Of the four predefined security roles, the Content Manager, My Reports, and Publisher roles have rights to this task.