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.)

A similar error may also be seen:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_LOOKUP_LOAD (DWH_LKP_TEST). SQLERRM : ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 23

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.)

Caused by CLRT-79090

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.

There are times where the full Load DWH job will need to run.

 

Clarity 14.2+

To resolve the issue, run the full Load DWH job. Run the full Load DWH if the following changes in Clarity occur:

1. Incremental Load DWH job fails
2. A new language is added in the data warehouse
3. Entity for fiscal time periods is changed in the data warehouse.
4. Time reporting periods have been added or made active/inactive
5. A data warehouse time slice is changed to include a larger time frame.
6. An attribute is deleted or unselected from the data warehouse via Administration > Studio.
7. An attribute data type is changed

Reference article Load DWH Full fails with FK constraint when scheduled at a certain time of the day only (Oracle) - due to a custom job or process accessing records and updating them whilst the Load Data Warehouse job is running.