Load Data Warehouse - Incremental fails intermittently with ORA-02298: cannot validate (PPM_DWH.DWH_RDM_ITEM_POSITION_FK2) - parent keys not found

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

On a large database of customer who heavily uses roadmaps, schedule the Load Data Warehouse - Incremental to run every hour
 
Expected results: The job to run fine
Actual Results: Once in a while the job will fail with error:
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_RDM_ITEM_POSITION_FK2) - parent keys not found
at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
Couldn't execute SQL: ALTER TABLE DWH_RDM_ITEM_POSITION ENABLE VALIDATE CONSTRAINT DWH_RDM_ITEM_POSITION_FK2
 

Cause

The issue is with the order the tables are loaded. If an item gets updated between DWH_RDM_ROADMAP_ITEM and DWH_RDM_ITEM_POSITION tables loading, it would cause this issue.
 

Environment

Release: All Clarity releases with DWH
Component: PPMDWH

Resolution

Part of this issue was addressed in 15.5.1 as fix for DE43623
It may still occur on 15.5.1 and require substantial design change -  logged as DE49361/DE58448, Fixed in 15.9.2