Chapter 11: Delivering the GoodsReport Delivery

In the previous chapter, we moved from the development environment to the Report Server. The Report Server enables us to make our reports available to end users. We reviewed the various ways reports and their supporting resources can be moved from the development environment to the Report Server. We also reviewed the security features the Report Server provides.

In addition to all this, we looked at the Report Manager interface, which provides users with one method of accessing reports on the Report Server. In this chapter, you learn about additional ways to take reports from the Report Server to the users. You also learn ways to manage how and when reports are executed. These features can be used to level out server load and to increase user response time.

Caching In

One of the best features of Reporting Services is that the data is requeried each time the report is executed. This is shown in Figure 111. The user is not viewing information from a static web page that is weeks or months old. Reporting Services reports include data accurate up to the second the report was run.

Figure 11–1: Serving a report without caching

This feature can also be the source of one of the drawbacks of Reporting Services. The user is required to wait for the data to be requeried each time a report is run. If your query or stored procedure runs quickly, this may not be a problem. However, even fairly quick queries can slow down a server if enough of them are running at the same time.

Fortunately, Reporting Services has a solution to this problem. The solution is report caching.

Report Caching

With many reports, it is not essential to have up-to-the-second data. You may be reporting from a data source that is only updated once or twice a day. The business needs of your users may only require data that is accurate as of the end of the previous business period, perhaps a month or a quarter. In these types of situations, it does not make sense to have the data requeried every time a user requests a report. Report caching is the answer.

Report caching is an option that can be turned on individually for each report on the Report Server. When this option is turned on, the Report Server saves a copy, or instance, of the report in a temporary location the first time the report is executed, as shown in Figure 11–2.

Figure 11–2: Serving a report with caching, the first time

On subsequent executions, with the same parameter values chosen, the Report Server pulls the information necessary to render the report from the report cache, rather than requerying data from the database, as shown in Figure 11–3. Because these subsequent executions do not need to requery data, they are, in most cases, faster than the report execution without caching.

Figure 11–3: Serving a report with caching, subsequent times

Cached Report Expiration

Once an instance of the report is stored in the report cache, it is assigned an expiration date and time. The expiration date and time can be calculated in one of two ways. The expiration date can be calculated based on a certain number of minutes after the creation of the cached instance. For example, the cached instance of the report exists for 30 minutes, and then it is deleted. Or, the expiration date can be determined by a set schedule. For example, the cached instance of the report is deleted at 2:00 A.M. every Sunday morning.

The first type of expiration calculation is appropriate for a report that requires a large amount of database resources and is run often, but does not require up-to-the-second data. We can decrease the workload on the database server by fulfilling most of the requests for the report from the report cache. Every 30 minutes, we throw the cached report away. The next person who requests the report causes a new instance of the report, with updated data, to be placed in the report cache.

The second type of expiration calculation is appropriate for reports run against data that changes on a scheduled basis. Perhaps you have a report being run from your data warehouse. The data warehouse is updated from your transactional database each Sunday at 12:30 A.M. The data in the warehouse remains static in between these loads. The cached report is scheduled to expire right after the data load is completed. The next time the user requests the report after the expiration, a new instance of the report, with the updated data, is placed in the cache. This cached report contains up-to-date data until the next data load.

Cached Reports and Data Source Credentials

To create a cached instance of a report, the report must be using stored credentials. These can be credentials for either a Windows logon or a database logon, but they must be stored with the data source. If you think about this from a security standpoint, this is how it has to be.

Suppose for a minute that Reporting Services allowed a cached report to be created with Windows Integrated Security. The Windows credentials of the first person to run the report would be used to create a cached instance of the report. Subsequent users who request this report would receive this cached instance. However, this would mean the subsequent users are receiving data in the report created using the credentials from another user.

If the results of the database query or stored procedure that populates this report vary based on the rights of the database login, we have the potential for a big problem. If the vice president of sales is the first person to run the report and create the cached instance, all subsequent users would receive information meant only for the VP! Conversely, if a sales representative is the first person to run the report and create the cached instance, when the VP comes along later and requests the report, he will not receive all the information he needs.

The same problem exists if the report prompts for credentials. The first person who runs the report and creates the cached instance is the one who supplies the credentials. Everyone who views the cached instance is essentially using someone else’s logon to see this data.

The only way that caching works without creating the potential for a security problem is with credentials stored with the report. In this situation, the same credentials are used to access the database—whether it is the VP or a lowly sales representative running the report. There is no risk that the cached instance of the report will create a breach in database security.

Caching and Report Formats

As you can see in Figure 11–2, the intermediate format of the report, and not the final format of the report, is stored in the report cache. The intermediate format is a combination of the report definition and the data from the datasets. It is not formatted as an HTML page, a PDF document, or other type of rendering format. It is an internal format ready for rendering.

Because the intermediate format is stored in the report cache, the cached report can be delivered in any rendering format. The user who first requested the report and, thus, caused the cache instance to be created, may have received the report as an HTML document. The next user may receive the cached instance of the report and export it to a PDF document. A third user may receive the cached instance of the report and export it to an Excel file. Caching the intermediate format gives the report cache the maximum amount of flexibility.

Enabling Report Caching

Let’s try enabling caching for one of our deployed reports. We have a report that is a good candidate for caching. The Weather report takes a long time to execute because of the calls to the web service. Also, the weather conditions returned by the web service are not going to change from minute-to-minute, so it is not essential to retrieve new information every time the report is executed. The Weather report works just fine if it is retrieved from the cache, as long as we expire the cached instance fairly often, say, every 45 minutes.

Enabling Report Caching for the Weather Report

Let’s try enabling caching for the Weather report.

1. Open the Report Manager and navigate to the Chapter 08 folder.

2. Click Show Details.

3. Click the icon in the Edit column for the Weather report. The Properties page for the Weather report appears.

4. Select Execution from the left side of the screen. The Execution Properties page appears, as shown in Figure 11–4.

   
   Figure 11–4: The Execution Properties page

5. Select the option “Cache a temporary copy of the report. Expire copy of report after a number of minutes.”

6. Set the number of minutes to 45.

7. Click Apply.

8. Select the View tab. Check (Select All) in the Select Planets drop-down list and click View Report. The Weather report runs.

The first time the Weather report runs after caching is turned on, the report needs to perform its regular execution process to gather the data for the intermediate format. This intermediate format is then copied to the report cache before it is rendered for you in the browser. Because the report goes through its regular execution process, it still takes a while to appear.

Viewing the Report from the Report Cache

Now let’s run the report again. Because a cached copy of the report has not expired, the report is rendered from the cached copy.

1. Click the Refresh Report button in the toolbar. The report appears almost immediately. That happened so fast, I bet you don’t even believe it retrieved the report. Let’s try it again another way.

2. Click the Chapter 08 link at the top of the page.

3. Click the WeatherReport link in the Name column to run this report.

4. Check (Select All) in the Select Planets drop-down list and click View Report.

Note 

Be sure to make the same parameter selection each time you run this test. We discuss how report parameters affect caching in the section “Report Caching and Report Parameters.”

Pretty slick! The Report Server doesn’t need to retrieve any data, execute any expressions, call any assemblies, or create the intermediate format. All it needs to do is convert the intermediate format into the rendered format (in this case, HTML).

What happens if we ask for a different rendering format?

1. Select Acrobat (PDF) file from the Select a Format drop-down list.

2. Click Export.

3. If a File Download dialog box appears, click Open.

4. Close the Adobe Acrobat Reader when you finish viewing the report.

A brief delay occurs as the PDF document is created and your Acrobat Reader is opened, but there is no delay to retrieve the information using the web service. Instead, the intermediate format comes from the report cache and is rendered into a PDF document.

If you wait 45 minutes, the cached copy will have expired and the report is again executed to create the intermediate format. If you want to try this, you can put the book down, go have lunch, and then come back and run the report. It’s okay. You go right ahead. I’ll be here waiting when you get back.

Cache Expiration on a Schedule

You have just learned the weather web service we are using for our Weather report is updated every hour on the hour. It makes sense for us to set our cached copy of this report to expire on this same schedule. The cached copy should expire at five minutes past the hour, so a new copy of the weather information shows up the next time the report is run after the web service information is updated.

1. Navigate to the Weather report in the Report Manager, if you are not already there.

2. Select the Properties tab. The Properties page appears.

3. Select Execution from the left side of the screen. The Execution Properties page appears.

4. Select “Cache a temporary copy of the report. Expire copy of report on the following schedule.”

5. Report-Specific Schedule is selected by default. Click Configure next to Report-Specific Schedule. The Schedule page appears, as shown in Figure 11–5.

   
   Figure 11–5: The Schedule page

6. You can specify hourly, daily, weekly, monthly, or one-time schedules. Select Hour.

7. Leave the Hourly Schedule set to run every 1 hours 00 minutes. Set Start Time to five minutes after the next hour. (If it is 2:30 P.M. now, set Start Time to 3:05 P.M.)

8. Today’s date should be selected for Begin Running This Schedule On. Leave the field Stop This Schedule On blank. (You change these dates by clicking the calendar icon to the right of the entry area. You cannot type in the date directly.)

9. Click OK to return to the Execution Properties page. Note the description of the schedule you just created under Report-Specific Schedule.

10. Click Apply to save your changes to the report cache settings.

11. Select the View tab. Check (Select All) in the Select Planets drop-down list and click View Report. The Weather report runs.

Again, the report takes longer to execute the first time as the intermediate format is created and put into the report cache. This cached instance of the report remains there until five minutes past the hour.

Report Cache and Deploying

When a cached report instance expires, either because of a schedule or because it has existed for its maximum length of time, it is removed from the report cache. One other circumstance can cause a cached report instance to be removed from the report cache. If a new copy of a report is deployed from the Report Designer or uploaded using the Report Manager, any cached instances of that report are removed from the report cache.

Report Caching and Report Parameters

What happens with our report caching if different users enter different parameters when the report is executed? Suppose one user runs the Weather Report and only selects Borlaron from the Select Planets drop-down list. The Weather Report is cached with only the Borlaron information. Now a second user runs the report selecting only Stilation. Because a nonexpired instance of this report is in the report cache, it seems the report should come from the report cache. If this were to happen, though, the second user would receive the Borlaron data instead of the Stilation data.

Fortunately, the Report Server is smart enough to handle this situation. As part of the instance of the report in the report cache, the Report Server stores any parameter values used to create that cached instance, as shown in Figure 11–6. The cached instance is used to satisfy requests made by a subsequent user only if all the parameters used to create the cached instance match the parameters entered by the subsequent user.

Figure 11–6: Report caching with parameters

Report Caching and Security

Not all users can change report-caching properties. To change the report caching 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.