Clarity: Load Data Warehouse full job fails with duplicate key

Document ID : KB000125262
Last Modified Date : 12/04/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 
 

Environment:
Clarity PPM 15.x
Cause:

There is duplication of rows in the PRCALENDAR table. At this moment, there is 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 there are 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 which should be removed>

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

7. Make sure there is no error message.

8. Now, run Load Data Warehouse - Full Load

These steps above will work fine if DWH is not being used for that Clarity PPM environment. But, for an environment where DWH and Advanced Reporting is used and configured, the RPT_CALENDAR table needs to be cleaned as well. Because, when the Load DWH job gets executed, the data from the RPT_CALENDAR table gets populated to the PRCALENDAR table. So, the RPT_CALENDAR table cannot have any duplicate records in order to fix this problem. 

Hence, after step number 7, as mentioned above, follow the below steps: 

8. The Clarity PPM services need to be stopped again. 

9. Take a backup of the table RPT_CALENDAR before purging the data in it.

10. Truncate table RPT_CALENDAR;

11. Start all the services.

12. Run “Update Report Tables” job with “Update Reporting Calendar “checked and wait for job to complete.

13. Run the Load Data Warehouse job in full load.