DWH - LDW incremental job failure while processing DWH_INV_TEAM_SUMMARY_FACTS

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Our DWH Incremental load job has failed at least 3 times in the month of July. Running the full DWH load corrects this issue, but we would like to figure out what is causing the failures and how we can fix them.

Job Started 7/11/22 12:23 AM
Message 7/11/22 12:23 AM
Executing ETL Job.
Error 7/11/22 12:49 AM
Load Facts PG - An error occurred executing this job entry :
Couldn't execute SQL: CALL DWH_INV_TEAM_SUM_FACTS_LOAD (P_LAST_LOAD_DATE => to_timestamp('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss')::timestamp, P_CURRENT_DIM_LOAD_DATE => to_timestamp('2022/07/11 00:23:36', 'yyyy/mm/dd HH24:MI:SS')::timestamp);

ERROR: ENCOUNTERED EXCEPTION WHILE CREATING INDEX. could not create unique index "dwh_inv_team_sum_facts_u1"
  Where: PL/pgSQL function dwh_savedrop_create_indexes_sp(text,text) line 70 at RAISE
SQL statement "CALL DWH_SAVEDROP_CREATE_INDEXES_SP('DWH_INV_TEAM_SUMMARY_FACTS','CREATE')"
PL/pgSQL function dwh_inv_team_sum_facts_load(timestamp without time zone,timestamp without time zone) line 56 at CALL
Error 7/11/22 12:49 AM
ETL Job Failed. Please see log bg-dwh.log for details.
Job Completed 7/11/22 12:49 AM
NJS-0401: Execution of job failed.

Environment

Release : 16.0.2

Component : Clarity Data Warehouse

Resolution

DE65996, Fixed in 16.0.3

Workaround: Run LDW in Full mode.