One of the best features of Reporting Services is the fact that the data is requeried each time the report is executed. This is shown in Figure 11-1. The user is not viewing information from a static web page that is weeks or months old. Reporting Services reports include data that is accurate up to the second that 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.
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 day. 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 for reports on the Report Server. It is turned on individually for each report. 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
Upon 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, the subsequent times
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 will exist for 30 minutes and then it will be deleted. Alternatively, the expiration date can be determined by a set schedule. For example, the cached instance of the report will be 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 that is 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.
In order 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 that the subsequent users are receiving data in the report that was 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 that was 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.
You can see in Figure 11-2 that 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 that is ready for rendering.
Because it is the intermediate format that 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.
Let’s try enabling caching for one of our deployed reports. We actually 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 that are 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 will work just fine if it is retrieved from the cache, as long as we expire the cached instance fairly often, say every 45 minutes.
Let’s try enabling caching for the Weather report.
Open the Report Manager and navigate to the Chapter 08 folder.
Click Show Details.
Click the icon in the Edit column for the Weather report. The Properties page for the Weather report will appear.
Select Execution from the left side of the screen. The Execution Properties page will appear, as shown in Figure 11-4.
Figure 11-4: The Execution Properties page
Select the option “Cache a Temporary Copy of the Report. Expire Copy of Report After a Number of Minutes.”
Set the number of minutes to 45.
Select the View tab. The Weather report will run.
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.
Now let’s run the report. Because there is a cached copy of the report which has not expired, the report will be rendered from the cached copy.
Click the Refresh Report button in the toolbar. The report will appear almost immediately. That happened so fast, I bet you don’t even believe that it retrieved the report. Let’s try it again another way.
Click the Chapter 08 link at the top of the page.
Click the WeatherReport link in the Name column to run this report.
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?
Select “Acrobat (PDF) file” from the Select a Format drop-down list.
If a File Download dialog box appears, click Open.
Close the Adobe Acrobat Reader when you are finished viewing the report.
There will be a brief delay as the PDF document is created and your Acrobat Reader is opened. 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 will again be 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.
You have just learned that the weather web service we are using for our Weather report is updated every hour on the hour. It makes sense that we 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 has been updated.
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 “Cache a Temporary Copy of the Report. Expire Copy of Report on the Following Schedule.”
Report-Specific Schedule will be selected by default. Click Configure next to Report-Specific Schedule. The Schedule page will appear, as shown in Figure 11-5.
Figure 11-5: The Schedule page
You can specify hourly, daily, weekly, monthly, or one-time schedules. Select Hour.
Leave the Hourly Schedule set to run every 1 hours 00 minutes. Set Start Time to 5 minutes after the next hour. (If it is 2:30 P.M. now, set Start Time to 3:05 P.M.)
Select today’s date 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.)
Begin Running This Schedule On defaults to tomorrow’s date. If you want a schedule to start today, you need to change this from the default setting.
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 report cache settings.
Select the View tab. The Weather report will run.
Again, the report will take longer to execute the first time as the intermediate format is created and put into the report cache. This cached instance of the report will remain there until 5 minutes past the hour.
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 will cause a cached report instance to be removed from the report cache. If a new copy of a report is deployed from Visual Studio or uploaded using the Report Manager, any cached instances of that report are removed from the report cache.
Report caching works just fine with reports that are the same each time they are run—that is, reports that do not have any parameters. What about reports whose content changes based on user parameters? Suppose we have a report that requires the user to enter a month as a parameter. One user runs the report for March and creates a cached instance containing the March data. Now a second user runs the report selecting May for the report parameter. Because there is a nonexpired instance of the report in the report cache, it would seem that the report should come from the report cache. If this were to happen, though, the second user would receive the March data instead of the May 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 that were 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
Not all users can change report caching properties. In order 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.