ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

DWH job fails with duplicate keys on DWH_TME_ENTRY_FACTS after time slice rollover

book

Article ID: 206016

calendar_today

Updated On:

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

Cause

This is DE58253, affecting 15.8.1 and 15.9.0

Environment

Release : 15.8.1, 15.9.0

Component : CLARITY DATA WAREHOUSE

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

See also: Load DWH job frequently reported issues