Load Data Warehouse Incremental Job Fails Intermittently When Running Scheduled

Products

• Clarity PPM SaaS

Issue/Introduction

Load Data Warehouse Incremental Job Fails Intermittently When Running Scheduled

STEPS TO REPRODUCE:

1.Schedule and run the incremental Load DWH job to run every hour.

2.Schedule the full Load DWH to run once per night.

3.Review the Load DWH job runs and check if it completes or fails.

Actual: The incremental Load DWH job will fail intermittently with generic

BG DWH Log error:

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('2022/10/07 01:15:06', 'yyyy/MM/dd HH24:mi:ss')::timestamp, P_CURRENT_DIM_LOAD_DATE => to_timestamp('2022/10/07 07:15:05', 'yyyy/mm/dd HH24:MI:SS')::timestamp);

 

ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_INV_TEAM_SUMMARY_FACTS. there is no unique or exclusion constraint matching the ON CONFLICT specification

Where: PL/pgSQL function dwh_inv_team_sum_facts_load(timestamp without time zone,timestamp without time zone) line 80 at RAISE

Expected: The incremental job should process changes

Workaround: Run the full Load.

The job is supposed to take incremental changes such as in project teams.
The problem is that the job fails with a generic error and so I obtained the database when the job failed. 
What data issue or change is causing this error?

 

 

Environment

Release : 16.0.3. 16.1.0

Resolution

This is reported as DE67377.

Review if there exists an index on the DWH table: dwh_inv_team_summary_facts 

If not, have the index recreated:
CREATE UNIQUE INDEX dwh_inv_team_sum_facts_u1 ON [schema].dwh_inv_team_summary_facts USING btree (team_key)