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

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)

Additional Information

If you have upgraded and still run into the issue, make sure to schedule your financial jobs, Post Transactions to Financials and Post to WIP, for times that do not overlap the Load Data Warehouse job.