Increment Load DWH - Refresh Matl View - DWH_FIN_PLAN_VIRT_NEW_MV

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_x.pg_temp_x_x 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

It is also recommended to run the job Update Financial Plan Actuals and Forecasts before you run the Load Data Warehouse