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.

Environment

Release : 15.8.1

Component : CLARITY DATA WAREHOUSE

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

Resolution

Ensure project fields are setup properly (including OBS)

Additional Information

See also: Load DWH job frequently reported issues