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:

Job Type: Load Data Warehouse - Job Properties

General Job Name Load Data Warehouse - Incremental Sun Through Fri Run ID 10523010 Job ID 5302737 Run Status Failed   Parameters Full Load     When Run Start Date 12/14/21 5:15 AM Run Finish Date 12/14/21 5:24 AM Duration (Minutes) 9:54 Log Entries Log Entry Date​ Message Job Started 12/14/21 5:15 AM   Message 12/14/21 5:15 AM Executing ETL Job. Error 12/14/21 5:24 AM 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 Error 12/14/21 5:24 AM ETL Job Failed. Please see log bg-dwh.log for details. Job Completed 12/14/21 5:24 AM NJS-0401: Execution of job failed.

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