Clarity: Load Data Warehouse full job fails with duplicate key

Document ID : KB000125262
Last Modified Date : 14/02/2019
Show Technical Document Details
Issue:

When running the full load, the following error appears in the BG DWH logs:

--MSSQL
Error for table 'DWH_X_RES_AVAIL_PER_FACTS' - Error Number 1505 'The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'ppm_dwh.DWH_X_RES_AVAIL_PER_FACTS' and the index name 'DWH_X_RES_AVAIL_PER_FACTS_U1'. The duplicate key value is (5000259, 2006540).' ERROR 2019-01-24 15:06:46,211 [dwh_mssql_error_handling UUID: 5c1aed54-85e2-4629-bd1f-44c9a59a5685] dwh.event Abort job - Aborting job. INFO 2019-01-24 15:06:46,334 [Thread-18776] dwh.event null - Job execution finished

--Oracle
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_X_RES_AVAIL_PER_FACTS. SQLERRM : ORA-00001: unique constraint (PPM_DWH.DWH_X_RES_AVAIL_PER_FACTS_U1) violated 
ORA-06512: at "PPM_DWH.DWH_X_RES_AVAIL_PER_FACTS_LOAD", line 110 
 

Cause:

There is duplication of rows in the PRCALENDAR table. At this time we have no known steps to reproduce this problem.

Resolution:

1. Run the SQL to confirm:
select * from prcalendar
where PRRESOURCEID in
( select PRRESOURCEID from prcalendar
group by PRRESOURCEID
having count(PRRESOURCEID) > 1 )

2. If you see two records, note the PRID for each row.

3. Confirm which one is used in prj_resources for this resource:

select prcalendarid from prj_resources where prid ='<resource id from  step 1>

4. Backup the PRCALENDAR table

5. Delete one of the duplicate entries:
delete from prcalendar where prid = <id of the one we want to remove>

6. Connect to Clarity - Home - Resources - Click on Calendar tab for the affected resource

7. Make sure you see no error message there

8. Now run Load Data Warehouse - Full Load