ORA-02298 FK error when running Load Data Warehouse - Full

Products

• Clarity PPM On Premise

Issue/Introduction

This is an issue with data that is updated in DWH_FIN_TRANSACTION_FACTS but not in DWH_FIN_TRANSACTION. So the foreign keys cannot connect to the parent key as they are missing.

 

Note: This may happen on other tables and dependent foreign keys

 

Example error on PostgreSQL:
ERROR 2021-01-26 06:06:51,422 [Dispatch Load Data Warehouse : bg@XXXX (tenant=clarity)] dwh.event ETL Job Failed. Log details below: 
2021/01/26 06:06:51 - Fin Transactions? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry : 
2021/01/26 06:06:51 - Fin Transactions? - Couldn't execute SQL: CALL DWH_ENABLE_CONSTRAINTS_SP(NULL,NULL);
2021/01/26 06:06:51 - Fin Transactions? - 
2021/01/26 06:06:51 - Fin Transactions? - ERROR: ENCOUNTERED EXCEPTION WHILE ENABLING dwh_inv_asset_fk1.  insert or update on table "dwh_inv_asset" violates foreign key constraint "dwh_inv_asset_fk1"
2021/01/26 06:06:51 - Fin Transactions? -   Where: PL/pgSQL function dwh_enable_constraints_sp(character varying,character varying) line 43 at RAISE

Environment

Release:
Component: ODDWH

Resolution

Manually remove the values with query:

delete from DWH_FIN_TRANSACTION_FACTS where TRANSACTION_KEY not in

(select TRANSACTION_KEY from DWH_FIN_TRANSACTION)

Ensure nothing else is running on the application-level that updates the records last_updated_date as per the article below

 

 

Additional Information

Load Data Warehouse job - Full Load fails with FK constraint when scheduled at a certain time of the day only (Oracle)