History Tracking with SQL query option

*This feature added in ROB-EX Scheduler version 7.4

In order to support Business Intelligence on historic transactional data, the ROB-EX Scheduler Multiuser Server will automatically, once every hour (configurable), copy data for the following transactional entities into dedicated history tables of the ROB-EX Scheduler GanttMultiuser database.

• Project
• Production order
  • Including Routing information
• Operation
  • Including Resource id and name

The purpose of these tables is to provide end users with a “simple” way to access historic data, without having to rely or know about the otherwise complex relationship between data in the ROB-EX Scheduler database.

The only way to access the history data is by SQL lookup, so create a SQL user/role with read-only access and use that whenever the need for Business Intelligence is needed on ROB-EX Scheduler data. Talk to your IT department in order to set this user up.

The data is copied by a global cross-site logic, i.e. all sites on the instance is copied into history all at the same time and using the same rules. This also means that any configuration related to history tracking is global and will affect all sites of the instance.

The logic that copies data to history tables copies based on the modification calendar on entities – if the modification calendar of an entity is newer than last time data was copied out, the history archive will be updated.

Once a Project or Production order is deleted

Once a Project or Production order is deleted the status of Project and Production order is no longer copied to the history archive. Also as the modification dates will typically no longer be updated, this effectively means that what is left in the history data is a snapshot of how data looked liked at the point in time the entity was deleted.

*An implication of the “Deleted” logic, is that a Project or Production order should be left with status Complete at least 2 hours (if the batch job is configured to run on a 1 hour schedule) after it is completed and before it is deleted, to make sure the history archive will record it as completed.

When are entities moved to the history tables

Project

Projects are copied into the archive as soon as they are created and receives a status higher than the limit state specified

Project entity data is stored in table HProjectHistory.

Production orders

Production orders are copied into the archive as soon as they are created and receives a status higher than the production order limit state specified.

Production order entity data is stored in table HProductionOrderHistory and includes, besides production order data, also data from the Product and Route (OperationSequence) entities.

Operation

Operations are copied into the archive as soon as they are created and the production order they belong to receives a status higher than the production order limit state specified

Operation entity data is stored in table HOperationHistory and includes, besides operation data, also data from the Resource entity (resource id and name).

Configuration options

The history tracking feature can be completely disabled and it is also configurable what status a project or production order must have before it is copied to the history tables. The configuration is carried out by editing the ganttServerG2/application.yml file.

Configure when and how often the history tracking batch job runs

The frequency of the batch job is controlled via a cron expression. The default is that the job runs at minute 55 of each hour.

robex:
   historytracking:
      interval: 0 55 * * * *  # Sec(0-59) Minute(0-59) Hour(0-23) DayOfMonth(1-31) Month(1-12) DayOfWeek(0-6=Sun-Sat)

Disable history tracking

To disable use the cron expression “-”. Add the following to the application.yml file in the robex section

robex:
   historytracking:
      interval: "-"

Configure minimum status of Project and Production order

To specify a minimum status required before respectively a Project or a Production order is copied into the history tables, use these two settings (the values shown are the default values):

robex:
   historytracking:
      minimumProjectStatus: 10 # new
      minimumOrderStatus: 20 # planned

SQL Datamodel

The tables involved, and the relations between them, are shown on the following drawing.

The following is a sample SQL query loading project, production order and operation information. The siteId must also in all queries be part of the join, even though it for clarity is not shown on the above drawing.

USE GanttMultiuser

SELECT prjh.Id as projectId
    ,prjh.AlternateName as projectNumber
	,prjh.Name as projectName
    ,poh.Id as orderId
    ,poh.Name as orderNumber
    ,poh.State as orderStatus
    ,poh.ProductName as itemText
    ,oprh.Label as oprNum
    ,oprh.Name as oprName
    ,oprh.SelectedResourceId as selResId
    ,oprh.*, poh.* FROM [dbo].[HOperationHistory] oprh
 INNER JOIN [dbo].[HProductionOrderHistory] poh on (oprh.siteId=poh.siteId AND oprh.ProductionOrderId=poh.Id)
 LEFT OUTER JOIN [dbo].[HProjectHistory] prjh on (oprh.siteId=prjh.siteId AND poh.projectId=prjh.Id)
WHERE oprh.siteId='gear'
AND poh.State >= 20 -- 10=New 15=Forecast 20=Planned 30=Released 40=Start possible 50=Started 60=Paused 70=Cancelled 80=Completed 90=Deleted
AND poh.Id = '1000'

Pre-prepared views incorporating Time Tracker registrations

A common way to use the history data, is to analyze planned vs actuals for different groups of data. A pre-made SQL view will aggregate the individual time tracker registrations (setup time, workload time, quantity etc.) and pair it up with the data stored in the history tables (e.g. what operation, what item, what resource, etc.)

The view is available out of the box. The picture below shows example SQL for calling the view – and example output:

Troubleshooting the History Tracking batch job

Whenever the batch job runs, log entries similar to these are logged in the ganttServerG2/logs/rbx-srv-log.txt file. The timing numbers provided may help to determine how “expensive” the batch job is, and help to decide if the frequency should be decreased.

10-26 09:55:00.026 INFO  [   scheduling-1] [                    ] g.s.h.s.i.HistoryTrackingServiceImpl : starting history tracking.
10-26 09:55:01.526 INFO  [   scheduling-1] [                    ] g.s.h.s.i.HistoryTrackingServiceImpl : 0 history operations updated.
10-26 09:55:01.541 INFO  [   scheduling-1] [                    ] gantt.utils.timing.Timing : TIMED: update history operations :: 1.117 s
10-26 09:55:02.916 INFO  [   scheduling-1] [                    ] g.s.h.s.i.HistoryTrackingServiceImpl : 0 history operations added.
10-26 09:55:02.932 INFO  [   scheduling-1] [                    ] gantt.utils.timing.Timing : TIMED: insert history operations :: 1.387 s
10-26 09:55:03.213 INFO  [   scheduling-1] [                    ] g.s.h.s.i.HistoryTrackingServiceImpl : 0 history orders updated.
10-26 09:55:03.213 INFO  [   scheduling-1] [                    ] gantt.utils.timing.Timing : TIMED: update history orders :: 110.3 ms
10-26 09:55:03.635 INFO  [   scheduling-1] [                    ] g.s.h.s.i.HistoryTrackingServiceImpl : 1060 history orders added.
10-26 09:55:03.635 INFO  [   scheduling-1] [                    ] gantt.utils.timing.Timing : TIMED: inserting history orders :: 417.1 ms
10-26 09:55:03.744 INFO  [   scheduling-1] [                    ] g.s.h.s.i.HistoryTrackingServiceImpl : 0 history projects updated.
10-26 09:55:03.744 INFO  [   scheduling-1] [                    ] gantt.utils.timing.Timing : TIMED: Update history projecs :: 44.23 ms
10-26 09:55:04.151 INFO  [   scheduling-1] [                    ] g.s.h.s.i.HistoryTrackingServiceImpl : 27 history projects inserted.
10-26 09:55:04.151 INFO  [   scheduling-1] [                    ] gantt.utils.timing.Timing : TIMED: Insert history projects :: 404.1 ms
10-26 09:55:04.151 INFO  [   scheduling-1] [                    ] g.s.h.s.i.HistoryTrackingServiceImpl : history tracking completed.

*As there is no cleanup of the history tables, make sure your database server has sufficient storage capacity to hold the data amounts these tables over the years may end up containing