Execution caching allows you to save the data from a report execution, so that reports can be viewed more quickly, and with less stress to the database. The frequency and schedule of database updates is customizable for each report. Updates can be scheduled for off-hours in order to better balance system load. Caching can significantly reduce database calls for reports which are accessed frequently. Large or data-intensive reports, which can take a long time to run, especially benefit from caching.
Note: Execution caching is currently incompatible with the scheduler queue.
Enabling Execution Caching
Execution caching uses the report scheduling services and the scheduling user interface to cache the data and set the refresh intervals. When caching is enabled for a report, a caching schedule is created for that report which determines how often the data is refreshed. Scheduling services need to be enabled for the user or users who will be setting the caches; however, users who only need to view the cached reports do not need to have scheduling enabled.
To enable execution caching, set the following admin console setting to true:
( Scheduler Settings ) Enable Execution Cache
Cache Visibility Settings
The userId and companyId parameters must exist and have default values assigned to them in order to be able to enable execution caching. These parameters are used to assign visibility permissions to cached data.
The following administrative settings that affect access to some schedule recurrence options will also affect the recurrence options available in the execution caching window:
( Scheduler Settings ) Show Schedule No End Date Option
( Scheduler Settings ) Show Schedule Intraday Recurrence Option
The following administrative setting will affect the view level options that a user has when assigning permissions for an execution cache:
(pre-v2017.3) ( Scheduler Settings ) Scheduler Manager User View Level
(v2017.3+) ( Scheduler Settings ) User Cache Visibility Level
The cache data is stored in the scheduler temporary directory, so you should make sure that the
max_temp_file_age scheduler setting is high enough that caches will not be purged unpredictably. See Scheduler Configuration for details.
Setting Cached Reports and Refresh Intervals
To set up caching for an advanced report:
CAUTION: Role-based filters and column-based tenancy apply based on the user who creates the cache. Users who can view the cache will see data filtered by the creator's role and tenancy, not the viewer.
From the Report Options menu, click Options > Execution Caching.
Select the Enable execution caching check box.
Choose which users will be able to see the cached data:
Note: Not every option may be available.
User - Users will only see cached data that they have created. When another user runs the report, it will execute against the database instead of the cache.
Company - Users will see cached data that has been created by anyone with the same companyId parameter. When a user from outside the company runs the report, it will execute against the database instead of the cache.
- Global - The report has one data cache which is visible for all users.
Choose how often the cache is updated:
Enter a time of day in the Schedule Time field, or use the picker to select a time.
Optional: To update the cache multiple times per day, select the Repeat Every check box and enter a time for how often it should repeat.
Optional: To repeat until a certain time of day, enter that time in the until field, or use the picker to select a time.
Choose which days of the week the cache is updated. Select either:
To repeat every day or every number of days, click Every __ day(s) and enter a number of days for how often it should repeat.
To repeat every Mon, Tues, Wed, Thurs, and Fri, click Every weekday.
When execution caching is enabled for a report, the cache is not created immediately. To run the report, you can manually create the cache by clicking the refresh icon, or you can wait until the first scheduled update completes. The cache can always be updated manually by clicking this icon.
Cache schedules are visible in the schedule manager, with the label (cache). Caches can be edited or deleted using the schedule manager or the advanced report designer.
Viewing a report cache in the schedule manager
Editing cached reports
Editing a cached report invalidates its cache. It cannot be run until the cache is updated. A missing or invalid cache is indicated by the message Cache is invalid, both in the report description and in the report tree menu.
Valid and invalid report cache indicators
It is recommended that you fully design a report first, so that you can manually verify the output, before enabling the cache.