My Oracle Support Banner

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 11.1.1.9.1 and later
Information in this document applies to any platform.

Symptoms

On Oracle Business Intelligence Applications OBIA: 11.1.1.10.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.

UPDATE W_ETL_LOAD_DATES SET LAST_MAX_DATE=LAST_MAX_DATE+(1)*#PRUNE_DAYS WHERE DATASOURCE_NUM_ID=:DSN

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.

Changes

 NA

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.