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

Products

• Clarity PPM SaaS

Issue/Introduction

STEPS TO REPRODUCE:

1. After Time Slice rollover run Data Warehouse job for an 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 2019-05-01 04:29:26,565 [dwh_tme_entry_facts UUID: fb7d909d-efb7-4287-83e6-d5aeb8d7eeea] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :

Couldn't execute SQL: BEGIN

DWH_TME_ENTRY_FACTS_LOAD(

P_DBLINK => 'PFEDPPM',

P_LAST_LOAD_DATE => to_date('2019/04/30 21:17:51', 'yyyy/MM/dd HH24:mi:ss'),

P_CURRENT_DIM_LOAD_DATE => to_date('2019/05/01 04:03:02', '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

Resolution

This issue has been reported as DE55383(DE54887, DE48982), fixed in 15.8.1 for Postgres, Fixed in 15.9.1 for Oracle