Load Data Warehouse fails after Fiscal Rollover on the 1st of the month
search cancel

Load Data Warehouse fails after Fiscal Rollover on the 1st of the month

book

Article ID: 235716

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Load Data Warehouse fails with ORA- 01452: cannot CREATE UNIQUE INDEX; duplicate keys found on the 1st of the month during the 1st run only


STEPS TO REPRODUCE:

  1. Set up Clarity to run Full and Incremental DWH loads
  2. On the 1st of the month, Full Load runs successfully
  3. The Incremental after that starts running

Expected Results: The job to run well all the time

Actual Results: On the 1st of the month, Incremental fails once with error:

Executing ETL Job. NJS-0401: Execution of job failed. ETL Job Failed. Please see log bg-dwh.log for details. Load Facts Oracle - An error occurred executing this job entry : Couldn't execute SQL: BEGIN DWH_INV_TASK_PERIOD_FACTS_LOAD (P_ARRAY_SIZE => 50000, P_DBLINK => 'PPMDBLINK, P_CURRENT_DIM_LOAD_DATE => to_date('2022/02/01 04:43:10', 'yyyy/mm/dd HH24:MI:SS')); END; [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INV_TASK_PERIOD_FACTS_LOAD. 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_INV_TASK_PERIOD_FACTS_LOAD", line 76 ORA-06512: at line 2

Environment

Release : 16.0, 16.0.1, 16.0.2

Resolution

This is DE64227, fixed in 16.0.3

Workaround: Run another job after that and it will go through successfully

Schedule to Run a Full Load every 1st of the month

Powered by Wolken Software