Insert or update on table "dwh_x_res_avail_per_facts" violates foreign key constraint "dwh_x_res_avail_per_facts_fk1"

Products

• Clarity PPM SaaS

Issue/Introduction

Steps to Reproduce:

1. Incremental DWH Load
2. Resource updates?

Expected Results:

• Job runs without a problem.

Actual Results:

Error :

Load Facts PG - An error occurred executing this job entry : Couldn't execute SQL: CALL DWH_X_RES_AVAIL_PER_FACTS_LOAD(P_LAST_LOAD_DATE => to_timestamp('2021/12/14 03:15:13', 'yyyy/MM/dd HH24:mi:ss')::timestamp,P_CURRENT_DIM_LOAD_DATE => to_timestamp('2021/12/14 04:15:12', 'yyyy/mm/dd HH24:MI:SS')::timestamp);ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_X_RES_AVAIL_PER_FACTS. insert or update on table "dwh_x_res_avail_per_facts" violates foreign key constraint "dwh_x_res_avail_per_facts_fk1"  Where: PL/pgSQL function dwh_x_res_avail_per_facts_load(timestamp without time zone,timestamp without time zone) line 89 at RAISE

Environment

Release : 16.0.0

Component : CLARITY DATA WAREHOUSE

Resolution

The issue was reported as defect DE63537.

Defect monitored further in clarity SaaS environments and could not be reproduced from 16.0.2. If the issue is noticed please log a case with Broadcom support as additional diagnostics from the test case needs to be captured to debug further. 

The following SQL query in particular addition to the bg-dwh.log

select * from dwh_cmn_error_message where table_name='DWH_X_RES_AVAIL_PER_FACTS'; 

select * from dwh_cmn_error_msg_history where table_name='DWH_X_RES_AVAIL_PER_FACTS