Incremental and Full Load DWH Fails on ToDos

Products

• Clarity PPM SaaS

Issue/Introduction

The Load DWH job has recently failed.

The BG-DWH log shows an issue with To Dos

ERROR: could not create unique index "dwh_cmn_todo_u1"
  Detail: Key (todo_key)=(5188559) is duplicated.
  Where: SQL statement "CREATE UNIQUE INDEX dwh_cmn_todo_u1 ON example.dwh_cmn_todo USING btree (todo_key)"
PL/pgSQL function dwh_cfg_pre_config_sp(character varying) line 53 at EXECUTE

Cause

The STR (Steps-To-Reproduce) are unknown as to how To Dos were able to be associated with multiple checklists.
To Dos are only able to belong to one checklist at at time.
It was also found that a checklist was deleted, but somehow the To-Do was still associated to the deleted checklist.

Resolution

To resolve the one-off situation, run the following queries, where the to-do ID is obtained from the BG-DWH log/error.

Example: If the todo key/id = 5188559

--1 
SELECT * FROM oba_todos
WHERE id = 5188559

--2 
select * from dwh_oba_todo_v
WHERE todo_key  = 5188559

Review associated checklist(s)

5046047    checklists
5072073    checklists
5073074    checklists <--missing

--3 get associated checklist
SELECT * FROM NMC_CHECKLISTS
where id in (5046047, 5072073, 5072074)

NOTE: Once the checklist assoicated with the ToDo is missing or disassociated, the view (dwh_oba_todo_v)  is no longer valid.
It is not possible to delete records in the view as it is tied to several tables and built dynamically.

The only way to resolve the issue to
1. Delete the completed ToDo and its associated checklist.

2. Truncate the DWH table: dwh_cmn_todo
because the job would just fail at the next todo, because it may already have the unique id/record created.