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