Load DWH error on DWH_LKP_TRANSLATION: "The MERGE statement attempted to UPDATE or DELETE the same row more than once."

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When running the Load Data Warehouse (DWH) job, it fails with the below error:
 
ERROR 2019-01-10 08:12:33,398 [Thread-4530072] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :
Couldn't execute SQL: MERGE INTO DWH_LKP_TRANSLATION TGT
USING
(
select LAST_UPDATED_DATE as CLARITY_UPDATED_DATE, IS_ACTIVE as IS_ACTIVE, LANGUAGE_CODE as LANGUAGE_CODE, LANGUAGE_ID as LANGUAGE_CODE_KEY, SORT_ORDER as SORT_ORDER, NAME as TRANSLATION, LOOKUP_CODE as TRANSLATION_KEY, CONVERT(DATETIME ,'2019-01-10 08:12:32') as dw_updated_date from [PPMDBLINK].niku.niku.DWH_LOOKUPS_V where 1=1 AND LAST_UPDATED_DATE >= CONVERT(DATETIME,'2018-11-06 10:07:11')
) SRC
ON
(
SRC.TRANSLATION_KEY = TGT.TRANSLATION_KEY
AND SRC.LANGUAGE_CODE = TGT.LANGUAGE_CODE
)
WHEN MATCHED THEN
UPDATE SET TGT.CLARITY_UPDATED_DATE = SRC.CLARITY_UPDATED_DATE, TGT.IS_ACTIVE = SRC.IS_ACTIVE, TGT.LANGUAGE_CODE_KEY = SRC.LANGUAGE_CODE_KEY, TGT.SORT_ORDER = SRC.SORT_ORDER, TGT.TRANSLATION = SRC.TRANSLATION, TGT.dw_updated_date = SRC.dw_updated_date
WHEN NOT MATCHED THEN
INSERT (CLARITY_UPDATED_DATE, IS_ACTIVE, LANGUAGE_CODE, LANGUAGE_CODE_KEY, SORT_ORDER, TRANSLATION, TRANSLATION_KEY, dw_updated_date)
VALUES (SRC.CLARITY_UPDATED_DATE, SRC.IS_ACTIVE, SRC.LANGUAGE_CODE, SRC.LANGUAGE_CODE_KEY, SRC.SORT_ORDER, SRC.TRANSLATION, SRC.TRANSLATION_KEY, SRC.dw_updated_date)
;
 
[CA Clarity][SQLServer JDBC Driver][SQLServer]The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. 

Environment

Release: All Supported
Component: Data Warehouse (DWH)

Cause

Caused by unresolved duplicates in DWH_LKP_TRANSLATION on DWH side

Resolution

1. On the ppm_dwh database, run the following:
   
   truncate table DWH_LKP_TRANSLATION
   
   
2. Run a Full Load of the Data Warehouse job

Additional Information

See also KB: Data Warehouse (DWH) known issues