Execution Snapshots | MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

Report caching is a great tool for improving the performance of reports with long execution times, but one problem still exists. The first user who requests the report after the cached instance has expired must wait for the report to be created from the underlying data. It would be nice if there were a way to have cached report instances created automatically, so 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

You can enable the creation of execution snapshots using two methods. Let’s 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 appears.
Select Execution from the left side of the screen. The Execution Properties page appears.
Select the option Render This Report from a Report Execution Snapshot.
Check the Create a Report Snapshot When You Click the Apply Button on This Page check box.
Click Apply. Note the error message that appears next to the execution snapshot option. When an execution snapshot is created, it is done as a background process, so no one will be available to select a value for the report parameter. Because this parameter has no default value, the Report Server does not know what value to use for the report parameter when the report is run by the schedule. Let’s provide a default value for the parameter so we can proceed.
Select Parameters from the left side of the screen. The Parameters page appears.
Check the box in the Has Default column.
Enter the following in the Default Value drop-down edit area:

AFU

BLN

NOX

RKM

SLN

SRA

(We need to enter the values passed to the parameter, in this case, the planet abbreviations, not the planet names that are displayed in the drop-down list. Each value should be entered on a separate line.)
Click Apply to save the default value.
Let’s try to set up snapshot execution again. Select Execution from the left side of the screen. The Execution Properties page appears.
Select the option Render This Report from a Report Execution Snapshot.
Check the Create a Report Snapshot When You Click the Apply Button on This Page check box.
Click Apply. As soon as you click Apply, the Report Server executes the report and places an instance of the report in the report cache. Allow time for this process to complete.
Select the View tab.

The report is 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 Report Execution Snapshots check box.
Report-Specific Schedule is selected by default. Click Configure next to Report-Specific Schedule. The Schedule page appears.
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 five 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 five minutes past the hour. However, you probably don’t want to waste computer resources generating an execution snapshot of the Weather report hour after hour, day after day, so we 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 five minutes, the scheduled execution snapshot will create a cached instance of the report.
Select the View tab after five 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 runs and is 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 periodically (again, think of a data warehouse 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 Security

Not all users can change execution snapshots. 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.