Attribute using Lookup CSK_RPT_ENTITY_FISCAL_YEAR Causes DWH Job Failure
search cancel

Attribute using Lookup CSK_RPT_ENTITY_FISCAL_YEAR Causes DWH Job Failure

book

Article ID: 256558

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

When creating a custom attribute using the lookup

CSK_RPT_ENTITY_FISCAL_YEAR the data warehouse full load will fail with the following message:

 

INFO  2022-12-20 13:50:31,661 [Thread-924100] dwh.event (none:none:none:none) Full load for the lookup DWH_LKP_CSK_RPT_ENTITY_F_00

 INFO  2022-12-20 13:50:31,662 [Thread-924100] dwh.event (none:none:none:none) dwh_lookup_load_pg - Starting entry [Full Insert]

ERROR 2022-12-20 13:50:31,719 [Thread-924100] dwh.event (none:none:none:none) Full Insert - 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

    BEGIN

                               INSERT INTO DWH_LKP_CSK_RPT_ENTITY_F_00 (CLARITY_UPDATED_DATE, CSK_RPT_ENTITY_F_00, CSK_RPT_ENTITY_F_00_KEY, LANGUAGE_CODE, LANGUAGE_CODE_KEY, dw_updated_date) SELECT SRC.LAST_UPDATED_DATE AS CLARITY_UPDATED_DATE, SRC.PNAME AS CSK_RPT_ENTITY_F_00, SRC.PID AS CSK_RPT_ENTITY_F_00_KEY, SRC.LANGUAGE_CODE AS LANGUAGE_CODE, SRC.LANGUAGE_ID AS LANGUAGE_CODE_KEY, TO_TIMESTAMP('20-dec-2022 13:50:31', 'dd-mon-yyyy hh24:mi:ss') AS dw_updated_date FROM DWH_LKP_CSK_RPT_ENTITY_F_00_V SRC WHERE language_code IN (SELECT language_code FROM cmn_languages WHERE is_dw_enabled = 1) AND SRC.LAST_UPDATED_DATE >= TO_TIMESTAMP('01-jan-1910 00:00:00', 'dd-mon-yyyy hh24:mi:ss');

    EXCEPTION WHEN OTHERS THEN

        V_SQLSTATE := SQLSTATE;

        V_SQLERRM := SQLERRM;

        ROLLBACK;

        BEGIN

        V_EXCEPTION := 'ENCOUNTERED EXCEPTION WHILE INSERTING IN DWH_LOOKUP_LOAD DWH_LKP_CSK_RPT_ENTITY_F_00. ' || V_SQLERRM;

        V_SQL_TEXT := 'INSERT INTO DWH_LKP_CSK_RPT_ENTITY_F_00 (CLARITY_UPDATED_DATE, CSK_RPT_ENTITY_F_00, CSK_RPT_ENTITY_F_00_KEY, LANGUAGE_CODE, LANGUAGE_CODE_KEY, dw_updated_date) SELECT SRC.LAST_UPDATED_DATE AS CLARITY_UPDATED_DATE, SRC.PNAME AS CSK_RPT_ENTITY_F_00, SRC.PID AS CSK_RPT_ENTITY_F_00_KEY, SRC.LANGUAGE_CODE AS LANGUAGE_CODE, SRC.LANGUAGE_ID AS LANGUAGE_CODE_KEY, TO_TIMESTAMP(**20-dec-2022 13:50:31**, **dd-mon-yyyy hh24:mi:ss**) AS dw_updated_date FROM DWH_LKP_CSK_RPT_ENTITY_F_00_V SRC WHERE language_code IN (SELECT language_code FROM cmn_languages WHERE is_dw_enabled = 1) AND SRC.LAST_UPDATED_DATE >= TO_TIMESTAMP(**01-jan-1910 00:00:00**, **dd-mon-yyyy hh24:mi:ss**)';

        INSERT INTO DWH_CMN_ERROR_MESSAGE VALUES ('DWH_LKP_CSK_RPT_ENTITY_F_00',V_EXCEPTION,V_SQLSTATE,REPLACE(V_SQL_TEXT,'**',''''));

        END;

        COMMIT;

        RAISE EXCEPTION '%', V_EXCEPTION;

    END;

    COMMIT;

END $$

LANGUAGE PLPGSQL;

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING IN DWH_LOOKUP_LOAD DWH_LKP_CSK_RPT_ENTITY_F_00. duplicate key value violates unique constraint "dwh_lkp_csk_rpt_entity_f_00_u1"

  Where: PL/pgSQL function inline_code_block line 20 at RAISE

Environment

16.0.3, 16.1

Cause

The view that feeds the DWH:  DWH_LKP_CSK_RPT_ENTITY_F_00_V

Does not contain a unique key.  It appears to be a bad join to one of the tables.

The view looks like this:

Where you can see the PID is not unique and the only difference is in the LNAME field.

Resolution

Reported as DE67806, fixed in 16.1.1