Load Data to HD Process Misses Records Created between ROD and HD Start
Last updated on MARCH 08, 2017
Applies to:Oracle Fusion Transportation Intelligence - Version 6.2.0 and later
Information in this document applies to any platform.
***Checked for relevance on 23-Sep-2013***
Consider the following scenario as an example:
The ROD refresh time is scheduled @4:00 AM daily. The Load Data to HD process is scheduled @5:00 AM daily.
This is causing an issue with the query condition:
And ((LOCATION.INSERT_DATE>=to_date('#FTI_ETL.D_PREVIOUS_LOAD_TIME', '#FTI_ETL.V_TIME_STAMP_FORMAT') and
LOCATION.INSERT_DATE< TO_DATE('#FTI_ETL.D_LOAD_START_TIME', '#FTI_ETL.V_TIME_STAMP_FORMAT'))
Consider the Location: 'XXXXX. CUS-550194-403592100'
Location Insert Date: 11/19/2011 4:54:41 AM GMT
ROD Refresh Time :11/19/2011 4:00:00 AM GMT
Load Data to HD process timings as per E_LOAD_LOG table:
Load Time Previous Load Time Load_Start_Time
11/20/2011 5:00:08 AM 11/19/2011 5:00:05 AM 11/20/2011 5:03:09 AM
11/19/2011 5:00:05 AM 1/1/1970 11/19/2011 5:02:57 AM
When the ROD refresh happened @ 4 AM , the above mentioned location had been missed out since it got created @ 4:54AM in OTM.
The same record won't be considered during the next day's run either, because the Previous Load Time would be 11/19/2011 5:00:05 AM (Start time of the Load HD process on the previous day).
The 'Load data to HD process' run cannot be made dependent on the ROD refresh job.
Even after making the process dependent on the ROD refresh job, it hardly helps, because any new data inserted during a ROD refresh job would also be missed in the Load HD process runs.
ROD Refresh Start Time: 11/19/2011 4:00:00 AM
ROD Refresh End time: 11/19/2011 4:10:00 AM
Location Insert Date: 11/19/2011 4:05:41 AM GMT
Load HD Process Start time: 11/19/2011 4:11:00 AM GMT
In this case too, the record would be missed for the same reasons.
In short, the Load Data to HD Process misses out records created between the ROD start time and Load data to HD process start time.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms