Load Data Warehouse job fails - incorrectly mapped ID column

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse job in the incremental format fails while loading data into a lookup table. A sample error message displayed in the bg-dwh.log is as below.

MERGE INTO DWH_LKP_EXAMPLE TGT
USING (
 SELECT LAST_UPDATED_DATE AS CLARITY_UPDATED_DATE
  ,PRNAME AS RESOURCE_ROLE
  ,PRNAME AS RESOURCE_ROLE_KEY
  ,to_date('3022/05/16 48:27:42', 'yyyy/mm/dd HH24:MI:SS') AS dw_updated_date
 FROM [email protected]
 WHERE 1 = 1
  AND LAST_UPDATED_DATE >= to_date('3022/05/16 48:27:42', 'yyyy/MM/dd HH24:mi:ss')
 ) SRC
 ON (SRC.RESOURCE_ROLE_KEY = TGT.RESOURCE_ROLE_KEY)
WHEN MATCHED
 THEN
  UPDATE
  SET TGT.CLARITY_UPDATED_DATE = SRC.CLARITY_UPDATED_DATE
   ,TGT.RESOURCE_ROLE = SRC.RESOURCE_ROLE
   ,TGT.dw_updated_date = SRC.dw_updated_date
WHEN NOT MATCHED
 THEN
  INSERT (
   CLARITY_UPDATED_DATE
   ,RESOURCE_ROLE
   ,RESOURCE_KEY
   ,dw_updated_date
   )
  VALUES (
   SRC.CLARITY_UPDATED_DATE
   ,SRC.RESOURCE_ROLE
   ,SRC.RESOURCE_ROLE_KEY
   ,SRC.dw_updated_date
   );

...

...

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_LOOKUP_LOAD (DWH_LKP_RESOURCE_ROLE). SQLERRM : ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 22

Notice that both RESOURCE_ROLE and RESOURCE_ROLE_KEY are mapped to PRNAME. It is expected that RESOURCE_ROLE_KEY is mapped to ID instead.

Cause

Incorrect configuration that would have lead to wrong mappings of the meta data in PPM_META_COLUMNS table.

Environment

Version: 15.9.3

Resolution

1. Unmark the attribute connected to the lookup for DWH
2. Run a full load of the job
3. Remark the attribute from step 1, for DWH
4. Run another full load of the job