Changes and Enhancements

Share This Topic
Help Videos
Client Support Center
Contact Mitratech

Zoom Window Out
Larger Text | Smaller Text
Hide Page Header
Show Expanding Text
Printable Version
Send Mail Feedback
Save Permalink URL

Navigation: Data Warehouse Help > Data Warehouse Release Notes

Data Warehouse 5.0 includes the following changes and enhancements. For more detail, please see the Data Warehouse FAQ and Data Warehouse Help guide.

For users accessing this guide online, click the headline for each enhancement to toggle additional details.

UTC timestamps. Data Warehouse now stores the times that jobs were run using universal (UTC) instead of local time. This change helps ensure that no data is lost if refresh jobs are switched to run from a server in a different time zone. Times stored in audit tables also use UTC

Stability improvements. ETL transformations have been re-sequenced to reduce the potential for SQL deadlocks.

Previous releases executed many SQL instructions in parallel, which sometimes caused delays, timeouts and deadlocks. The new release removes multiple copies from input steps and separates them, resulting in fewer potential conflicts. The default database commit sizes were increased, replacing batch sizes. In addition, the initial load job was streamlined to remove unnecessary steps it shared in common with the refresh job. The net result of these changes should be fewer job failures, and fewer incidents of jobs running far longer than expected.

Prevent refresh job collisions. A new refresh job will be prevented from launching if the previous one is still running.

Past releases did not check if an old refresh job was still running before launching a new one. It was possible to accidentally have two or more refresh jobs running simultaneously, risking lockups and database integrity. The new release creates lock files and also checks the operating system. If it finds that a refresh process is already running, it will skip the current refresh job and terminate. So long as each refresh job is launched from the same machine, the standard wrapper script (.bat or .sh) will ensure that only one job runs at a time.

Discrete time boundaries. Each refresh job collects only records created or modified between the last refresh and current refresh starting times. Boundaries don’t overlap, which improves auditability and avoids redundant data collection for records that haven’t changed.

As in earlier releases, the refresh job scans the modifiedOn timestamps of each TeamConnect record to see if the records are new or newly updated. However, the minimum and maximum time boundaries used to determine what’s “new” are computed differently in this release. Each job has boundaries adjacent to the previous successful job. These values are stored in the WH_LAST_REFRESH table. Only records whose modifiedOn times are greater than or equal to the lower boundary or less than the upper boundary are eligible. The lower boundary of the current job is simply the upper boundary of the previous successful refresh job. In other words, the current refresh picks up from where the previous refresh left off. This boundary is uniform across all entities.

The upper boundary is computed from two values. First, it uses the start time of the current job. Then the upper boundary is adjusted by subtracting an offset from the current start time. For example, consider a job scheduled to run at the top of each hour. When it starts at 15:00 with an offset of 300 seconds, it will only collect records modified through 14:55. The user can change the offset by editing the COMMIT_DELAY value in the kettle.properties configuration file.

The purpose of the offset is to ensure that records saved in TeamConnect shortly before the job start time aren’t lost even if they aren’t committed to the database until after. In this example, a record modified at 14:57 will not be updated in the warehouse during the 15:00 job run. However, it will be included in the 16:00 run. Because the upper boundary for each job becomes the lower boundary for the next, the offset changes the minimum as well as maximum timestamps that qualify for each refresh. The 15:00 job run would include records >= 13:55 and < 14:55; the 16:00 run would filter records between 14:55 and 15:55; and so on.

Refresh audit table. This new audit table maintains an automatic history. Each refresh job logs how many of each main entity were transferred, starting/ending times, and discrepancies between expected and actual counts.

Every time the ETL Refresh job runs, it adds information about to the AUDIT_TBL database table. A row is appended for each main entity: User, Group, Contact, Project, Involved, Account, Appointment, Document, Expense, History, Invoice and Task. An entry contains these columns:

•ENTITY: The name of the entity.

•RUN_NUMBER: An incremental integer assigned to each job, starting at 1.

•TC_TABLE: The TeamConnect database table that is is the primary data source for the transformation.

•DW_TABLE: The corresponding warehouse target table populated from the source table.

•TC_COUNT_UPDATE: The number of source records collected for this refresh that were updated in TeamConnect since the last refresh. These records will be updated in the warehouse.

•TC_COUNT_NEW: The number of source records that were new in TeamConnect. These records will be added to the warehouse target.

•TC_COUNT_DELETED. The number of source records that were deleted from TeamConnect. These records will be deleted from the warehouse target. The deleted count is obtained from the WH_REMOVED_RECORDS table.

•TC_DELTA. The net increase or decrease in the number of source records, calculated as TC_COUNT_NEW minus TC_COUNT_DELETED.

•DW_COUNT_BEFORE: The count of target records in the warehouse at the start of the refresh job.

•DW_COUNT_AFTER: The count of target records in the warehouse at the conclusion of the refresh job.

•DW_DELTA: The net increase or decrease in the number of target records, calculated as DW_COUNT_AFTER minus TC_COUNT_BEFORE.

•TC_DW_DIFF: The difference (discrepancy) between the net change in the number of TeamConnect records and the net change in the number of warehouse records (TC_DELTA minus DW_DELTA).

•ENTITY_START. The time that the Refresh job began collecting and populating the entity.

•ENTITY_COMPLETE. The time that the Refresh job finished collecting and populating the entity.

•RUN_TIME_MINUTES. The number of elapsed minutes for the entity to populate (ENTITY_COMPLETE minus ENTITY_START).

The refresh audit table will grow over time. The application does not delete or overwrite old records. If you wish, you may safely delete records from the table when the refresh job is not running.

Overall audit count comparison. The user can run a top-level audit at any time, comparing TeamConnect database to warehouse totals.

The user can compare counts at any time between the TeamConnect database and the warehouse, by running the audit.bat (Windows) or audit.sh (Linux) script. This command will collect the counts in the AUDIT_COUNT database table, and also export them to a text file. The table includes these columns:

•ENTITY_NAME: The name of the entity.

•TC_COUNT: The number of entity records in TeamConnect.

•TC_REMOVED_COUNT The number of entity records that were deleted from TeamConnect since the last fresh, obtained from the WH_REMOVED_RECORDS table.

•TC_TOTAL_COUNT: The adjusted TeamConnect entity count for comparison with the warehouse. Records tracked in WH_REMOVED_RECORDS have not yet been deleted from the warehouse. Therefore, this field equals TC_COUNT plus TC_REMOVED_COUNT, the expected warehouse count if the two databases are in balance.

•DW_COUNT: The actual number of entity records in the warehouse.

•DIFF_TC_MINUS_DW. The difference (discrepancy) between the adjusted TeamConnect and warehouse counts.

Notifications with logs. The log file is now included as an attachment to all e-mails.

The user will receive the log file as an e-mail attachment, along with any e-mail notification that the job has completed, provided that SMTP e-mail has been configured in the Kettle properties. The log will be included for either initial load or refresh, whether or not the job succeeds. The notification is generated as a separate Pentaho job following the completion of the ETL job. This ensures that the notification is sent so long as the ETL job returns an error code.

Resume initial load. If the initial load job fails or is interrupted before it finishes, it won’t start over at the beginning. When the job is re-launched, it will skip over entities that have already populated successfully.

If an initial load terminates before it completes -- for example, because of a network interruption -- it can be resumed from near the point of failure. Simply start the command again (TeamConnect_Warehouse_initial.sh or TeamConnect_Warehouse_initial.bat). The job will begin with the most recent main entity it was processing, skipping over entities that have already finished populating.

The job uses the IS_INITIAL_COMPLETE column in the WH_LAST_REFRESH table as a flag field to track which entities have already succeeded. The value is changed from zero to one once the entity is complete.

Resume refresh after failure. Likewise, a refresh that is partially complete can be restarted and will skip over entities that are already complete.

A refresh that fails can also be be restarted and will skip over entities that are already complete. Unlike initial load, the process is not automatic. The user must run a different script to resume the refresh: TeamConnect_Warehouse_Refresh_Resume.bat (Windows) or TeamConnect_Warehouse_Refresh_Resume.sh (Linux).

The refresh resume feature is provided as an optional command because the standard refresh cycles will eventually correct for failures. Any records that were not populated because of a failed refresh will be collected during the next refresh (the time boundaries only advance when a refresh succeeds). The resume script is helpful when the refresh is expected to take a long time, or the failed refresh was on the verge of completing.

The resume refresh job uses the IS_REFRESH_COMPLETE column in the WH_LAST_REFRESH table as a flag field to track which entities have already succeeded. The value is changed from zero to one once the entity is complete.

Contents
Index
Search