Load DWH job fails - _FK errors - ORA-02298 Parent Keys Not Found

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Load Data Warehouse (DWH) job - Full load fails with FK errors after another Full Load DWH was Cancelled or Failed on any table

Error referenced: ORA-02298: cannot validate (***************_FK1) - parent keys not found (It can be on any FK.)

 

Steps to Reproduce:

1. Connect to Clarity with Data Warehouse set up
2.  Ensure the Load DWH job completes successfully
3. Go to Home, 'Reports and Jobs' or 'Jobs' menu link
4. Run a new Load DWH job with Full Load option selected
5. Wait for a minute, then Cancel the job
6. Now go and schedule another instance of Load DWH Job
7. Note that it's now in Waiting mode, until you delete the Cancelled instance
8. After the deletion, it's Processing, wait for the results

Expected Result:  Load DWH job to complete successfully as it truncates the tables before inserting new data.

Actual Result:  The job fails with ORA-02298: cannot validate (***************_FK1) - parent keys not found (It can be on any FK.)

Cause

This can be due to synchronization issues with the data between the Clarity PPM and DWH databases. When making particular changes to the PPM database, the views have not been made to the DWH database by making sure to run the full Load DWH job daily.

 

Resolution

1. Ensure the Load Data Warehouse is not running (pause any instances)
2. Make sure the DBA kills any queries running on DWH database
3. Run a Full Load of Data Warehouse