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).' ERROR 2019-01-24 15:06:46,211 [dwh_mssql_error_handling UUID: xxxx] 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 

--PostgreSQL

ERROR 2020-08-31 18:51:32,565 [Thread-2004] dwh.event postgres? - An error occurred executing this job entry : 
Couldn't execute SQL: DO $$
DECLARE
    V_SQL_TEXT text;
    V_EXCEPTION varchar(4000);
    V_SQLERRM varchar(4000);
    V_SQLSTATE varchar(5);
BEGIN
    CALL DWH_DISABLE_CONSTRAINTS_SP('DWH_CMN_PERIOD','FK');
    TRUNCATE TABLE DWH_CMN_PERIOD CASCADE;
  CALL DWH_SAVEDROP_CREATE_INDEXES_SP('DWH_CMN_PERIOD','SAVE_DROP');
 V_SQL_TEXT := 'INSERT INTO DWH_CMN_PERIOD (PERIOD_KEY, CLARITY_PERIOD_KEY, PERIOD_TYPE_KEY, PERIOD_START_DATE, PERIOD_END_DATE, MONTH_KEY, MONTH_START_DATE, MONTH_END_DATE, QUARTER_KEY, QUARTER_START_DATE, QUARTER_END_DATE, YEAR_KEY, YEAR_START_DATE, YEAR_END_DATE, ENTITY_KEY, ENTITY_ID, ENTITY, PERIOD_FTE, CLARITY_CREATED_DATE, CLARITY_UPDATED_DATE, dw_updated_date) select PERIOD_KEY, CLARITY_PERIOD_KEY, PERIOD_TYPE_KEY, PERIOD_START_DATE, PERIOD_END_DATE, MONTH_KEY, MONTH_START_DATE, MONTH_END_DATE, QUARTER_KEY, QUARTER_START_DATE, QUARTER_END_DATE, YEAR_KEY, YEAR_START_DATE, YEAR_END_DATE, ENTITY_KEY, ENTITY_ID, ENTITY, PERIOD_FTE, CLARITY_CREATED_DATE, LAST_UPDATED_DATE, to_timestamp(''2020/08/31 18:51:26'', ''yyyy/mm/dd HH24:MI:SS'') as dw_updated_date  from DWH_CMN_PERIOD_V A where LAST_UPDATED_DATE >= to_timestamp(''1910/01/01 00:00:00'', ''yyyy/MM/dd HH24:mi:ss'') AND LAST_UPDATED_DATE <= (SELECT DISTINCT DWH_DIM_START_DATE FROM DWH_CFG_SETTINGS) AND A.LANGUAGE_CODE =''en''';
    BEGIN
    EXECUTE V_SQL_TEXT;
    EXCEPTION WHEN OTHERS THEN 
        V_SQLSTATE := SQLSTATE;
        V_SQLERRM := SQLERRM;
        ROLLBACK;
        BEGIN
        V_EXCEPTION := 'ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_CMN_PERIOD. ' || V_SQLERRM;
        INSERT INTO DWH_CMN_ERROR_MESSAGE VALUES ('DWH_CMN_PERIOD',V_EXCEPTION,V_SQLSTATE,V_SQL_TEXT);
        END;
        COMMIT;
        RAISE EXCEPTION '%', V_EXCEPTION; 
    END;
    COMMIT;
        CALL DWH_SAVEDROP_CREATE_INDEXES_SP('DWH_CMN_PERIOD','CREATE');
        CALL DWH_GATHER_TABLE_STATS_SP('DWH_CMN_PERIOD');
END $$
LANGUAGE PLPGSQL;

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.

Environment

Clarity PPM 15.x

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.

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

     9. The Clarity services need to be stopped again. 

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

    11. Truncate table RPT_CALENDAR;

    12. Start all the services.

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

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

Attachments