Clarity: Dataware house ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_TASK

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse job fails with the following error message:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_TASK). SQLERRM : ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INTERNAL_MD. SQLERRM : ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at line 31

Environment

Release : 15.6

Component : CA PPM DATA WAREHOUSE

Cause

Issue was caused by a duplicate Task in a Clarity PPM project.

Resolution

Information from DWH_CMN_ERROR_MESSAGE table indicated that there was duplicate task in a Project

Issue was caused by a duplicate Task in a Clarity PPM project. After clearing out duplicate Tasks, the job run successfully. 

Sql query used to identify duplicate task is: 

 select A.task_key, A.task_name from DWH_TASK_V A group by A.task_key, A.task_name having (count (task_key) > 1)