(GCP) Load Data Warehouse fails on DWH_X_PLAN_ACT_PER_FACTS with duplicate key value violates unique constraint dwh_x_plan_act_per_facts_u3

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

STEPS TO REPRODUCE

1. Schedule the Incremental job to run few times per day, wait

Expected results: The job to run consistently fine

Actual Results: One of the instances failed with error:

ENCOUNTERED EXCEPTION WHILE INSERTING FROM DWH_X_PLAN_ACT_PER_FACTS. duplicate key value violates unique constraint "dwh_x_plan_act_per_facts_u3"

INSERT INTO DWH_X_PLAN_ACT_PER_FACTS SELECT A.PLAN_KEY, A.PLAN_DETAIL_KEY, A.INVESTMENT_KEY, A.PERIOD_KEY, A.COST_TYPE_KEY, A.TRANSACTION_CLASS_KEY, A.CHARGE_CODE_KEY, A.DEPARTMENT_KEY, A.INPUT_TYPE_CODE_KEY, A.LOCATION_KEY, A.USER_VALUE1_KEY, A.USER_VALUE2_KEY, A.RESOURCE_CLASS_KEY, A.RESOURCE_KEY, A.ROLE_KEY, A.ACTUAL_UNITS, A.ACTUAL_COST, A.ACTUAL_REVENUE, A.CLARITY_UPDATED_DATE, TO_TIMESTAMP('18-dec-2020 12:09:47', 'dd-mon-yyyy hh24:mi:ss') FROM DWH_X_PLAN_ACT_PER_FACTS_V A

Index details:

CREATE UNIQUE INDEX dwh_x_plan_act_per_facts_u3 ON dwh_x_plan_act_per_facts USING btree (plan_detail_key, plan_key, investment_key, period_key, cost_type_key, transaction_class_key, charge_code_key, department_key, input_type_code_key, location_key, user_value1_key, user_value2_key, resource_class_key, resource_key, role_key)

When we run a Full Load after that there is no problem. The issue is intermittent

Environment

Release : 15.9 - 15.9.2

Resolution

This is logged as DE59230, addressed in 15.9.3 by using the Financial Period Plan toggle as described here