Load DWH Incremental fails with "ORA-02298 - PPM_DWH.DWH_FIN_TRANSACTION_LN_FK1

Article Id: 144503
Status: Published
Updated On: 07-07-2020 10:52
Products:
Clarity PPM On Premise
Issue/Introduction:

We run Load Data Warehouse - Incremental, and it fails with  [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_FIN_TRANSACTION_LN_FK1) - parent keys not found

Full error below:

ERROR 2020-01-28 12:42:23,328 [dwh_etl_interface_dim UUID: 654c8d3e-0847-4311-beb5-1c63b96ee922] dwh.event dwh_enable_constraints_oracle - Errors detected!
INFO  2020-01-28 12:42:23,333 [Thread-516738] dwh.event null - Job execution finished
ERROR 2020-01-28 12:42:23,415 [Dispatch Load Data Warehouse : [email protected] (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
2020/01/28 12:42:23 - Execute SQL script.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Unexpected error
2020/01/28 12:42:23 - Execute SQL script.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
2020/01/28 12:42:23 - Execute SQL script.0 - Error while running this step!
2020/01/28 12:42:23 - Execute SQL script.0 -
2020/01/28 12:42:23 - Execute SQL script.0 - Couldn't execute SQL: ALTER TABLE DWH_FIN_TRANSACTION_LN ENABLE VALIDATE CONSTRAINT DWH_FIN_TRANSACTION_LN_FK1
2020/01/28 12:42:23 - Execute SQL script.0 -
2020/01/28 12:42:23 - Execute SQL script.0 - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_FIN_TRANSACTION_LN_FK1) - parent keys not found
2020/01/28 12:42:23 - Execute SQL script.0 

Environment:

Release : 15.5.1, 15.6, 15.6.1, 15.7, 15.7.1
Component : CA PPM DATA WAREHOUSE

Resolution:

This is a known defect: DE52941, fixed in 15.7.1.2 and 15.8

STEPS TO REPRODUCE: 
1. Login to Clarity PPM.
2. Schedule Load Data Warehouse - Incremental to run every hour.
3. Adjust around 10K transactions through WIP adjustment.
4. Post to WIP those adjustments while the Load Data Warehouse job is executing.

Expected Results :- Load Data Warehouse - Incremental shouldn't have any issues.
Actual Results :- Load Data Warehouse - Incremental fails with error ORA-02298: cannot validate (PPM_DWH.DWH_FIN_TRANSACTION_LN_FK1) - parent keys not found when transactions are made whilst the job is running

Workaround: Avoid adjusting WIP transactions when the Incremental Load is running.

Solution: Upgrade to 15.7.1.2 or 15.8 (and higher)