Full Load DWH job fails with Duplicate Key Unique Constraint Violation inserting into table DWH_CMN_PERIOD

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

When running the full load, one of the following errors appears in the BG DWH log:

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 (xxxxxx, xxxxxxx).'

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

PostgreSQL

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_CMN_PERIOD. duplicate key value violates unique constraint "dwh_cmn_period_u1"
  Where: PL/pgSQL function inline_code_block line 23 at RAISE

Environment

Any

Cause

1. There is a duplication of rows in the PRCALENDAR table. At this moment, there is no known steps to reproduce this problem.
   
   or
   
   
2. The DWH periods are set up wrong, which leads to duplication in DWH_CMN_PERIOD table

Resolution

Resolution 1

1. Check if the weekly or monthly periods are not all starting on the same day on the RPT_CALENDAR table.
   
   Example: The following shows overlap/duplication:
   
   
2. If so, have the DBA truncate the table.
3. If you note the periods are set up wrong, you have to set up the DWH time slices by the Best practices on DWH Time Slices
4. Once you complete the DWH Time Slices setup run Time Slicing job to completion
5. Ensure Update Report Tables job is paused
6. Truncate table RPT_CALENDAR
7. Run Update Report Tables job with all options
8. Run Load Data Warehouse - Full Load

Resolution B:

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 no duplicates, skip to step 3. If there are two records:
   1. Note the PRID for each row.
   2. Confirm which one is used in prj_resources for this resource:
      
      select prcalendarid from prj_resources where prid ='<resource id from  step 1>
      
      
   3. Backup the PRCALENDAR table
   4. Delete one of the duplicate entries:
      
      delete from prcalendar where prid = <id of the one which should be removed>
      
      
   5. In Clarity, go to Home -> Resources
   6. Click on the Calendar tab for the affected resource
   7. Make sure there is no error message. Now continue with the steps below
3. Make sure the Update Reports Tables job is paused
4. Take a backup of the table RPT_CALENDAR
5. Truncate table RPT_CALENDAR
6. Run the “Update Report Tables” job with all options checked and wait for the job to complete. Ensure you give it enough time (5-10 min.) even if it states Completed.
7. Run the Load Data Warehouse job - Full Load

Additional Information

Reference also:

• Load DWH job failures - troubleshooting
• Data Warehouse (DWH) known issues
• Load DWH Full - When to run to prevent failures