Increment Load DWH - Refresh Matl View - DWH_FIN_PLAN_VIRT_NEW_MV
search cancel

Increment Load DWH - Refresh Matl View - DWH_FIN_PLAN_VIRT_NEW_MV

book

Article ID: 258589

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

The Incremental Load Data Warehouse job fails intermittently with the following exception

Executing ETL Job.
Error 1/19/23 2:30 AM
Refresh Matl View - An error occurred executing this job entry : Couldn't execute SQL: DO $$DECLARE V_COUNT BIGINT;        V_SQL_TEXT TEXT;		V_DELETE_IDX TEXT;		V_TABLENAME VARCHAR(30);		V_INDEXNAME VARCHAR(30);        V_EXCEPTION VARCHAR(4000);        V_SQLERRM VARCHAR(4000);        V_SQLSTATE VARCHAR(5);        cursor_1 REFCURSOR;BEGINIF 1 = 1 THEN        SELECT COUNT(1) INTO V_COUNT FROM PG_CATALOG.PG_CLASS C WHERE C.RELKIND = 'm' AND UPPER(C.RELNAME) = 'DWH_FIN_PLAN_VIRT_NEW_MV';    IF V_COUNT = 1 THEN		EXECUTE 'DROP INDEX IF EXISTS DWH_FIN_PLAN_VIRTUAL_MV_N1';		EXECUTE 'DROP INDEX IF EXISTS DWH_FIN_PLAN_VIRTUAL_MV_U1';		EXECUTE 'DROP INDEX IF EXISTS DWH_FIN_PLAN_VIRTUAL_SUM_MV_N1';		EXECUTE 'DROP INDEX IF EXISTS DWH_FIN_PLAN_VIRTUAL_SUM_MV_U1';		    	ALTER MATERIALIZED VIEW DWH_FIN_PLAN_VIRTUAL_MV RENAME TO DWH_FIN_PLAN_VIRT_OLD_MV;        ALTER MATERIALIZED VIEW DWH_FIN_PLAN_VIRTUAL_SUM_MV RENAME TO DWH_FIN_PLAN_VIRT_OLD_SUM_MV;        ALTER MATERIALIZED VIEW DWH_FIN_PLAN_VIRT_NE
Error 1/19/23 2:30 AM
ETL Job Failed. Please see log bg-dwh.log for details.
Job Completed 1/19/23 2:30 AM
NJS-0401: Execution of job failed.
 

Environment

Release : 16.1.0

Cause

The job cannot build the unique index because there are duplicate rows

ERROR: duplicate key value violates unique constraint "dwh_fin_plan_virtual_mv_u1"
  Detail: Key (plan_detail_key, period_key, language_code_key)=(5664215, 5027069, 1) already exists.
  Where: SQL statement "INSERT INTO ppm_dwh.dwh_fin_plan_virtual_mv 
  SELECT (diff.newdata).* FROM pg_temp_83.pg_temp_20779_2 diff WHERE tid IS NULL"
SQL statement "REFRESH MATERIALIZED VIEW CONCURRENTLY DWH_FIN_PLAN_VIRTUAL_MV"
PL/pgSQL function inline_code_block line 190 at SQL statement

Resolution

Follow the steps documented here to Enable Financial Plan Actuals