Full Load DWH Job Fails With Duplicate Key Unique Constraint Violation Inserting Into Table DWH_CMN_PERIOD

book

Article ID: 125262

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

When running the full load, one of the following error 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

Cause

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

Environment

Any

Resolution

Resolution A
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:




If so, have the DBA truncate the table.

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
1. Once you complete the DWH Time Slices setup run Time Slicing job to completion

2. Ensure Update Report Tables job is paused

3. Truncate table RPT_CALENDAR

4. Run Update Report Tables job with all options

5. 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 is 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. Connect to Clarity - Home - Resources - Click on Calendar tab for the affected resource
    6. Make sure there is no error message. Now continue with 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 “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

Attachments