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
 

Environment

Release: All Clarity releases with DWH
Component: PPMDWH

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.
 

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