Incremental Load DWH is failing with error DWH_FIN_TRANSACTION_FACTS_FK3 in Oracle

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Incremental Load Data Warehouse (DWH) is failing when transactions are getting updated with an Oracle database.

STEPS TO REPRODUCE: 

Note: To reproduce this defect you will need to have a large volume of transactions (50+ Million) in the system

1. Schedule to run the load data warehouse inclemently every 4 hours
2. During the run, create a process to perform the below steps 
   
   
   • Run the below jobs using the process
     • Post Transactions to Financial
     • Post to WIP
     • Import Financial Actuals

• • Once those jobs are completed: Create negative transactions via xog to zero out actuals in the cost plan. For example, if actuals of cost plan for a month is 500 EUR then a negative transaction is created of -500 EUR which is xogged to zero actuals.
• 
  
  • Run the below jobs again
    • Post Transactions to Financial
    • Post to WIP
    • Import Financial Actuals

Expected Results: The load data warehouse job should finish successfully 

Actual Results: The load data warehouse fails with the below error 

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ERROR ENABLING CONSTRAINT. SQLERRM : ORA-02298: cannot validate (CLARITYDWHP.DWH_FIN_TRANSACTION_FACTS_FK3) - parent keys not found
ORA-06512: at "CLARITYDWHP.CMN_ENABLE_CONSTRAINTS_SP", line 27
ORA-06512: at line 2

Environment

Release :  15.8, 15.8.1,15.9,15.9.1,15.9.2 

Component : CLARITY DATA WAREHOUSE

Cause

This is logged as defect DE61541

Resolution

This defect is fixed in 15.9.3 

Workaround:

• Set the process to be run via job as incompatible to load data warehouse job 
• After failure, ensure you run the full load data warehouse job
• Replace the attached stored procedure DWH_FIN_TRAN_FACTS_LOAD which has the fix for version 15.8 and above for Oracle Database only.  

Additional Information

Reference also:

• Load DWH job failures - troubleshooting
• Data Warehouse (DWH) known issues
• Load DWH Full - When to run to prevent failures