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

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

When running Load Data Warehouse (DWH) job - Full after upgrade of CA Application Portfolio Management (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 : CLARITY DATA WAREHOUSE

Resolution

Step 1

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)

     3. Commit

     4. Now run a Load Data Warehouse - Full Load

Step 2

If the above does not help, this issue can be due to orphan investment.

1. To identify the investment, please run:
   select * from DWH_INV_APPLICATION where INVESTMENT_KEY not in
   (select INVESTMENT_KEY from DWH_INV_INVESTMENT)
2. 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. 

Additional Information

Reference: Load DWH job frequently reported issues