ReportService.Caching

Report Cache Hours

Report Caching is a performance feature of the EQuIS Enterprise Web Application. It allows the web application to save the output of a report, and then return that data when another request is made within the cache limit. This can improve the responsiveness of the web application tremendously. For example, if a dashboard references the same user report in four (4) different widgets and report caching is disabled, the web application has to run the same report 4 times before the dashboard can be displayed. Report Caching allows that report to be run once and referenced 4 times.

EQuIS Version: 7+

Namespace: EarthSoft.Server

Products Affected: Enterprise

Required Modules: Enterprise Web Application

ST_CONFIG	VALUE	REMARK	VALID VALUES
CONFIG_SECTION	ReportService	Required
CONFIG_KEY	CacheHours	Required
OBJECT_TYPE	user_id	Optional	string literal 'user_id'
OBJECT_VALUE	<user_id>	Optional
STRING_VALUE	positive integer value	Required	Integer value >= 0 where 0 means no caching should be performed.

Remarks:

No default setting is defined. Caching must be enabled by each user through their profile or an EQuIS administrator by adding a record in the ST_CONFIG table manually. An EQuIS Administrator may set a default cache time for all users that have not specified one for themselves by creating an entry in ST_CONFIG with only CONFIG_SECTION, CONFIG_KEY and STRING_VALUE populated. Report caching cannot be completely turned off. The minimum report cache will always be 1/10 of an hour (i.e., 6 minutes), even if a zero (0) value is entered.

Report caching works by creating a checksum of all report parameters at run time. If a report parameter is set to a macro value like CURRENT DATE, the checksum is computed with the actual date at the time the report was run.

A cached report is identified based on three factors: (1) the underlying report (REPORT_ID), (2) the user who executed the report, and (3) the checksum of all parameter values when the report is run. User A will not be sent the output of User B's report, even though the same parameter values are used. It does not matter where the report is referenced. If User A referenced the same user report on multiple widgets spread across multiple dashboards, so long as the cached report has not expired, it will be used for all references.

Example: Set the default cache to four (4) hours. The user with user_id=12345 has set the minimum report cache of 6 minutes by entering a STRING_VALUE of 0 through their EQuIS Enterprise Web Application user profile.

CONFIG_SECTION	CONFIG_KEY	OBJECT_TYPE	OBJECT_VALUE	STRING_VALUE
ReportService	CacheHours			4
ReportService	CacheHours	user_id	12345	0

Report Cache Location

As of EQuIS Build 7.0.0.19300, Enterprise Report Cache uses the new RemoteBlob feature to store cached reports (see the RemoteBlob AzureBlobService and RemoteBlob NtfsService articles). Previously, reports were cached in the folder identified in ST_CONFIG as CONFIG_SECTION='efdManagementConfiguration' and CONFIG_KEY='workfolder'. Build 7.0.0.19300 disabled report caching if this configuration entry exists in ST_CONFIG. Because EQuIS cannot access organizations' Azure account settings, it was not possible to migrate this setting as part of the update scripts.

The example below defines an Azure Block Blob container named 'azurereportcacheblobcontainer'. Note that the pseudo protocol 'blob://' prefixes the container name. The container name may also be suffixed with any path as desired to point to an item in the container. For example: blob://azurereportcacheblobcontainer/production/bigclientdb/

This setting applies to all users in the EQuIS database and may not be overridden for individual users. If using multiple EQuIS databases, a separate storage container should be created for each database.

ST_CONFIG	VALUE	REMARK
CONFIG_SECTION	ReportService	Required
CONFIG_KEY	ReportCacheLocation	Required
OBJECT_TYPE		NULL
OBJECT_VALUE		NULL
STRING_VALUE	blob://azurereportcacheblobcontainer	Name of an EQuIS BlobStore container

The following SQL script will configure an EQuIS database to continue to use the NTFS folder designated as the WorkFolder for efdManagementConfiguration as the report cache location. This configuration is only appropriate for on-premise deployments.

declare @workfolder varchar(1024);

select @workfolder = coalesce(string_value, 'default')
   from st_config where lower(config_section) = 'efdmanagementconfiguration'
   and lower(config_key) = 'workfolder' and object_type is null and object_value is null;

Update st_config set string_value = 'File:///' + replace(@workfolder,'\','/')
   where lower(config_section) = 'common.remoteblob' and lower(config_key) = 'legacyreportcachestore'
   and object_type = 'CONNECTIONSTRING' and object_value = 'NTFS';
   if @@rowcount = 0
   Insert into st_config(config_section, config_key, object_type, object_value, string_value)
   Values('Common.RemoteBlob', 'legacyreportcachestore', 'CONNECTIONSTRING',
      'NTFS', 'File:///' + replace(@workfolder,'\','/') );

Update st_config set string_value = 'blob://legacyreportcachestore'
   where lower(config_section) = 'reportservice' and lower(config_key) = 'reportcachelocation'
   and object_type is null and object_value is null;
   if @@rowcount = 0
   Insert into st_config(config_section, config_key, string_value)
   Values('ReportService', 'ReportCacheLocation', 'blob://legacyreportcachestore');