A single master cron job controls the entire reporting process from data migration to report calculations, to data cleanup. The following workflow steps are performed based on the cron job execution:

1. Activity tables are optimized and unnecessary data is removed.

2. Data used for the reports are migrated from the Application database to the Reports database and persisted to staging tables.

3. Aggregation and calculations are performed from the staging tables and the results persist in calculation tables that are used when rendering the reports.

4. The final data cleanup occurs on the Transact application tables and the staging tables used in the Reports database.

This entire sequence is executed by default every 15 minutes. This is all controlled by a cron job in the reporting property file.

See the following image for an example of these cron jobs in the dcma-reporting.properties file and the default settings.

• dcma.report.dashboard.serverStatus.cronExpression

  # Cron expression for execution frequency of reporting jobs

  By default, this is set to 15 minutes.

• dcma.report.reporting.cronExpression

  # Cron expression for archiving report data to the archive database

  • This operation should be configured to execute during off-hours (weekends, late night).

  • By default, this is set to 11:35 pm, Sunday night.

  # Cron expression for archiving report data to an archive database.

  • This property defines the number of days of data to leave in the reporting database when archiving or purging occurs.

  • Changing the setting to execute more frequently may impact system performance.

• dcma.report.reportData.duration

  # Cron expression that defines the number of days of data to leave in the Reporting database, at the time of executing the archival or purging job.

  • By default, this is set to 30 days. Any setting reflects the number of days reporting data is retained in the Reports database before the data is migrated to the Report Archive database.

  • Setting this to a higher value may impact system performance based on how much volume in terms of throughput is being processed. Customers processing over 3000 batches per day should use the default.

• dcma.report.enable.reportingServer

  # Cron expression for enabling or disabling this server to run ETL jobs

  • If true, this server is enabled to run reporting ETL jobs.

  • If false, ETL jobs are disabled for this server.

  • In a multi-server environment, one of the servers should have this setting as true.

  • In a single-server environment, this setting should always be true.

  • Customers may wish to disable ETL reporting for all servers. Set all servers to false.

• dcma.report.enable.reporting

  # Cron expression for cleanup and reports calculation, and specifies whether reporting is executed on this server, or not

  • This setting is useful for configuring reports in a multi-server environment. This setting can prevent report calculations from being performed on all servers. Instead, calculations can be performed on just one server.

  • If true, reporting data on this server will be collected and migrated to the Reports database.

  • If false, data cleanup is executed in the Applications database and no reports are calculated.

• dcma.report.cleanupBatchInstance

  # Cron expression enables or disables batch instance cleanup

  • If true, the reporting cleanup cron job also cleans the batch instance and associated tables for the finished and deleted batch instance entries.

  • If false, the user is responsible to clean up batch instance data and associated tables.

Data migration occurs from the following Transact tables to the Reports database:

• batch_instance

• act_hi_procinst

• batch_class

• batch_class_module

• rp_throughput_module_data

  This new table persists the number of documents and pages at the end of every workflow module in the batch workflow.

• rp_data_corrections

  This new table improves the correction reports as a part of Advanced Reporting. This new table stores JSON objects for more efficient mapping to the jobs that perform the calculations.

The default frequency of this migration cron job is every 15 minutes. After Transact migrates the tables successfully, Transact then runs the Advanced and Dashboard report jobs.

The batch.xml data that is required for reporting now persists to the Application database. The data migration job can leverage the necessary data strictly from the Application database. This makes data migration more efficient and simpler.

Transact performs two separate calculation jobs:

• Dashboard

• Advanced Reports

Activiti has the following four history tables:

• ACT_HI_PROCINST

  This table is used in reporting.

• ACT_HI_ACTINST

• ACT_HI_VARINST

• ACT_GE_BYETARRAY

Transact includes data cleanup with the following factors in design:

• The cleanup process is broken down into two separate jobs after the Advanced and Dashboard jobs are complete:

  • Cleanup tables in the Application database

  • Cleanup staging tables in the Reports database

These cleanup jobs run every 15 minutes. Once the batch instance is finished, the application deletes the history records.

• Transact stores only relevant fields from the batch.xml file that are required for correction reports in these tables. These fields are stored as JSON objects.

• The ApplicationcCleanup job is responsible for cleaning up Ephesoft Transact tables with batch instance tables.

  The ReportCleanup job cleans the staging tables that are used during migration.

• The jobs are executed in sequential order:

  • Cleanup Procedure: The database contains a procedure that cleans the tables in the database.

  • Activiti Workflow Engine: When executing a batch instance, Transact uses an Activiti workflow engine. Activiti creates history tables. Three history tables are not needed in Transact, so the reporting method removes them.

This topic provides recommendations for running reports in Transact:

• It is advisable to run the Reporting Service on a UI Server.

  Running the Reporting Service on an executing server may cause delays in the Report ETL Job execution due to high CPU utilization by batches.

• Dashboard and Standard report scripts and cron jobs have been merged into a single entity. This means both Dashboard and Throughput Reports will be updated at the same time in the UI. The Cron for this common ETL Script (Dashboard) should be frequent - every 15 to 20 minutes (the default setting is every 15 minutes).

• The clean-up algorithm will delete data for FINISHED batches for which all reports have been successfully run (as per the license).

  Along with this, XML files from SharedFolders/report-data and Activiti tables for the same batch instances will also be cleared.

• We recommend that all cron jobs should be configured so that minimal collisions occur.

  For example, the cleanup cron could be set at the 45th/50th minute of every "n" hours to ensure that the chances of another cron being fired at the same instance are less.

• ETL scripts are memory intensive due to the frequent sorting and aggregation of data. Hence, as per the load of batches, the JVM heap memory should be configured accordingly.

Transact allows administrators to adjust system-level configurations for reports, as follows:

• Properties, .lgx and script files

• Report database creation - report_create.sql

• Data management folders

The following views are leveraged to perform on the fly calculations as the source of the corresponding reports:

The following are applicable for Transact 2020.1 or above: