DWH job fails with duplicate keys on DWH_TME_ENTRY_FACTS after time slice rollover (Fixed in 15.9.1)

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse (DWH) job is failing with duplicate keys generated on DWH_TME_ENTRY_FACTS after Time Slice Rollover.  

 
STEPS TO REPRODUCE:

1. Wait for Time Slice to rollover
2. Run Data Warehouse job - Incremental load

Expected Results: Incremental to run successfully

Actual Results: It fails with error: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

ORA-06512: at "PPM_DWH.DWH_TME_ENTRY_FACTS_LOAD", line 76 

Full Error message:

ERROR 2020-10-02 20:21:46,486 [dwh_tme_entry_facts UUID: 1c244eec-ffc9-4431-bac6-347972b512a7] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :
Couldn't execute SQL: BEGIN
  DWH_TME_ENTRY_FACTS_LOAD(
    P_DBLINK => 'PPMDBLINK',
    P_LAST_LOAD_DATE => to_date('2020/09/30 22:47:56', 'yyyy/MM/dd HH24:mi:ss'),
    P_CURRENT_DIM_LOAD_DATE => to_date('2020/10/02 12:02:33', 'yyyy/mm/dd HH24:MI:SS'),
            P_ARRAY_SIZE => 50000
  );
END;

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_TME_ENTRY_FACTS. SQLERRM : ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INTERNAL_MD. SQLERRM : ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
ORA-06512: at "PPM_DWH.DWH_TME_ENTRY_FACTS_LOAD", line 76
ORA-06512: at line 2

Environment

Release : 15.8.1, 15.9.0

Component : CLARITY DATA WAREHOUSE

Cause

This is DE58253, affecting 15.8.1 and 15.9.0

Resolution

• Fixed in 15.8.1 for POSTGRES database
• Fixed in 15.9.1 for ORACLE database

Additional Information

A similar issue was logged as DE55383(DE54887, DE48982) and fixed in 15.8.1 (POSTGRES only) and 15.9.1 (ORACLE).