Load Data to HD Process Misses Records Created between ROD and HD Start (Doc ID 1430443.1)

Symptoms

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

Explanation:

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.

Example:

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.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.