ReportService.Caching

<< Click to Display Table of Contents >>

Navigation:  Configuration > ST_CONFIG Entries > EarthSoft.Reports >

ReportService.Caching

Report Cache Hours

Report Cache Location

 

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 with only CONFIG_SECTION, CONFIG_KEY and STRING_VALUE populated. Report caching cannot be disabled.  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 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 this configuration entry exists in ST_CONFIG. Because EQuIS can not 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. Care should be taken if the same storage container is used for multiple EQuIS databases as unexpected behavior is likely to occur.

 

ST_CONFIG

VALUE

REMARK

VALID VALUES

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');