Load DWH is failing with DWH_INV_PROJECT_FK1 parent keys not found

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The load data warehouse (DWH) job is regularly failing with "parent keys not found" error.

We have deleted the orphan record by executing the below query

delete from dwh_inv_project_ln where INVESTMENT_KEY not in
(select INVESTMENT_KEY from DWH_INV_INVESTMENT);

After the deletion its running for infinite time and if we cancel and run again getting parent keys not found.

Cause

This can happen if you change project data, such as update projects without OBS, which will make the projects invisible from the UI causingissues. 

2020/12/13 07:57:50 - Execute SQL script.0 - Couldn't execute SQL: ALTER TABLE DWH_INV_PROJECT ENABLE VALIDATE CONSTRAINT DWH_INV_PROJECT_FK1
2020/12/13 07:57:50 - Execute SQL script.0 - 
2020/12/13 07:57:50 - Execute SQL script.0 - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_INV_PROJECT_FK1) - parent keys not found
2020/12/13 07:57:50 - Execute SQL script.0 - 
2020/12/13 07:57:50 - Execute SQL script.0 - 
2020/12/13 07:57:50 - Execute SQL script.0 -  at org.pentaho.di.core.database.Database.execStatement(Database.java:1432)
2020/12/13 07:57:50 - Execute SQL script.0 -  at org.pentaho.di.core.database.Database.execStatements(Database.java:1538)
2020/12/13 07:57:50 - Execute SQL script.0 -  at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:225)
2020/12/13 07:57:50 - Execute SQL script.0 -  ... 2 more
2020/12/13 07:57:50 - Execute SQL script.0 - Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_INV_PROJECT_FK1) - parent keys not found

Environment

Release : 15.8.1

Component : CLARITY DATA WAREHOUSE

Resolution

Ensure project fields are setup properly (including OBS)

Additional Information

See also: Load DWH job frequently reported issues