OBIA 11g: PRUNE_DAYS Nullify Issues In Incremental Load Oracle Fusion Application R12 Using Oracle Cloud Storage (UCM)
(Doc ID 2344504.1)
Last updated on AUGUST 14, 2018
Applies to:Business Intelligence Applications Consumer - Version 18.104.22.168.1 and later
Information in this document applies to any platform.
On Oracle Business Intelligence Applications OBIA: 22.214.171.124.2 version, DataWarehouse
OBIA is on premise and the source is installed on cloud.
OBIA 11g integrated with Oracle Fusion Application R12 using Oracle Cloud Storage (UCM). Therefore, SDS is deployed (IS_SDS_DEPLOYED = 'Y'). ETL runs 'Adjust ETL Load Dates' procedure to run following DML.
As a result of this LAST_EXTRACT_DATE is always set to LAST ETL RUN DATETIME instead of (SYSDATE -#PRUNE_DAYS).
This is causing major issues and missing updates from Fusion Application:
In cases where scheduled nightly Cloud Extract from Fusion application fails, but OBIA ETL scheduled load executes successfully. LAST_EXTRACT_DATE is set to LAST_ETL_RUN DATE TIME. When cloud extract from fusion completes successfully, this data only loads in SDS schema. But data is not loaded further into Staging tables or Dimension/Facts, because ETL LAST_EXTRACT_DATE is later than LastuUdateDate on source tables in SDS tables.
Ex: User makes updates on 23/11/2017 10:23:00AM. Nightly cloud extract from Fusion Application fails. Nightly scheduled 24/11/17 4:00AM OBIA load plan executes successfully without processing new data and set LAST_EXTRACT_DATE (LAST_MAX_DATE) to 24/11/2017 5:27:00AM. On morning of 24/11/2017, rerun cloud extract from fusion application and complete is successfully. But subsequent OBIA ETL run do not pick changes from SDS because LAST_EXTRACT_DATE date is later than LastUpdateDate (23/11/2017 10:23:00AM) of SDS table.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!