Load Data Warehouse Full fails with ORA-02298: cannot validate (PPM_DWH.DWH_INV_APPLICATION_FK1) - parent keys not found

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM On Premise

Issue/Introduction

When running Load Data Warehouse - Full after upgrade of APM and PMO, it's persistently failing with error:

Execute SQL script - org.pentaho.di.core.exception.KettleStepException: 
Error while running this step!

Couldn't execute SQL: ALTER TABLE DWH_INV_APPLICATION ENABLE VALIDATE CONSTRAINT DWH_INV_APPLICATION_FK1

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_INV_APPLICATION_FK1) - parent keys not found

Nothing else is running at the time of the execution. 

Environment

Release : All Clarity releases with DWH

Component : CA PPM DATA WAREHOUSE

Resolution

Try the following:
1. Connect to Data Warehouse database

2. Run the SQL query: 

delete from DWH_INV_APPLICATION where INVESTMENT_KEY not in
(select INVESTMENT_KEY from DWH_INV_INVESTMENT)

Commit

3. Now run a Load Data Warehouse - Full Load

If this does not help:

This issue can be due to orphan investment. To identify the investment, please run:
select * from DWH_INV_APPLICATION where INVESTMENT_KEY not in
(select INVESTMENT_KEY from DWH_INV_INVESTMENT)

By the ID try opening the investment in UI, if you have errors, that's a good indication there is a problem.

If the investment is corrupted we recommend marking it for deletion and running Delete Investments job.