DWH Incremental fails with duplicates in DWH_INV_TEAM_SUMMARY_FACTS when there aren't any

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

DWH Incremental intermittently fails with duplicates on DWH_INV_TEAM_SUMMARY_FACTS

STEPS TO REPRODUCE:

1. On a large database, schedule Load Data Warehouse Incremental

Expected Results: the job to run successfully all the time

Actual Results, the job fails intermittently (at least once per week) with error:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_INV_TEAM_SUMMARY_FACTS' and the index name 'DWH_INV_TEAM_SUM_FACTS_U1'. The duplicate key value is (10217112).

If we look in DWH_INV_TEAM_SUMMARY_FACTS there is only 1 record for 10217112

Environment

This applies to MSSQL, but also Oracle and PG

Clarity 16.0.x, 16.1.x, 16.2.0,16.2.1

Resolution

This is DE78970, fixed in 16.2.2