Load DWH fails on DWH_CMN_MVL_LOAD after upgrade to 16.0

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Load DWH fails on DWH_CMN_MVL_LOAD after upgrade to 16.0 on custom multivalued dynamic lookup attributes in DWH_CMN_MV_LOOKUP. The error referenced is one of the following:

column lkp.language_code does not exist
query string argument of EXECUTE is null

STEPS TO REPRODUCE:

Have few dynamic lookup attributes enabled on investment extension and other objects with which Load DWH job runs successfully
Upgrade to 16.0
Run the Load Data Warehouse job - Full Load

Expected Results: The job to complete correctly

Actual Results: The job fails with an error message on the DWH_CMN_MV_LOOKUP update:

2021/11/24 12:02:58 - MVL Load PG - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : An error occurred executing this job entry : 2021/11/24 12:02:58 - MVL Load PG - Couldn't execute SQL: CALL DWH_CMN_MVL_LOAD(P_LAST_LOAD_DATE => to_timestamp('1910/01/01 12:00:00', 'yyyy/MM/dd HH24:mi:ss')::timestamp, P_FULL_RELOAD => 'N'::text);2021/11/24 12:02:58 - MVL Load PG - 2021/11/24 12:02:58 - MVL Load PG - ERROR: query string argument of EXECUTE is null
2021/11/24 12:02:58 - MVL Load PG - Where: PL/pgSQL function dwh_cmn_mvl_load(timestamp without time zone,text) line 66 at EXECUTE

or

2021/11/22 18:10:26 - MVL Load PG - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : An error occurred executing this job entry :
2021/11/22 18:10:26 - MVL Load PG - Couldn't execute SQL: CALL DWH_CMN_MVL_LOAD(P_LAST_LOAD_DATE => to_timestamp('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss')::timestamp, P_FULL_RELOAD => 'Y'::text);
2021/11/22 18:10:26 - MVL Load PG - 2021/11/22 18:10:26 - MVL Load PG - ERROR: column lkp.language_code does not exist

Environment

Release : 16.0.0

Component : CLARITY DATA WAREHOUSE

Resolution

This is DE63266, in review by Engineering

Workaround:

For PostgreSQL run the query on the DWH database:

SELECT DISTINCT c.object_code, c.attribute_code, c.dwh_table dwh_base_table_name, c.dwh_column dwh_base_column_name,
CASE WHEN c.object_code = 'COSTPLAN' THEN 'PLAN_KEY' WHEN c.object_code = 'BENEFITPLAN' THEN 'BENEFITPLAN_KEY'
ELSE t.dwh_key_col END dwh_base_key_column, c1.dwh_table dwh_disp_table_name, c1.dwh_column dwh_disp_column_name,
CASE WHEN c.dwh_table = c1.dwh_table THEN 0 ELSE 1 END multi_lang, t.has_extension_table,
CASE WHEN c2.dim_key IS NULL THEN t1.dwh_key_col ELSE c2.dim_key END lkp_key_column_name,
CASE WHEN c2.dim_key IS NULL THEN t1.dwh_display_col ELSE c2.dim_display END lkp_disp_column_name, c1.lookup_table
FROM dwh_meta_column c INNER JOIN dwh_meta_column c1 ON c.object_code = c1.object_code
AND c.attribute_code || '_CAPTION' = c1.attribute_code INNER JOIN dwh_meta_table t ON c.dwh_table = t.dwh_table
INNER JOIN dwh_meta_column c2 ON c.object_code = c2.object_code AND c2.attribute_code = c.attribute_code || '_CAPTION'
LEFT OUTER JOIN dwh_meta_table t1 ON c2.lookup_table = t1.dwh_table WHERE c.is_multivalued = 1

For Oracle run the query on the DWH database:

SELECT DISTINCT c.object_code, c.attribute_code, c.dwh_table dwh_base_table_name, c.dwh_column dwh_base_column_name,
CASE WHEN c.object_code = 'COSTPLAN' THEN 'PLAN_KEY' WHEN c.object_code = 'BENEFITPLAN' THEN 'BENEFITPLAN_KEY'
ELSE t.dwh_key_col END dwh_base_key_column, c1.dwh_table dwh_disp_table_name, c1.dwh_column dwh_disp_column_name,
CASE WHEN c.dwh_table = c1.dwh_table THEN 0 ELSE 1 END multi_lang, t.has_extension_table,
CASE WHEN c2.dim_key IS NULL THEN t1.dwh_key_col ELSE c2.dim_key END lkp_key_column_name,
CASE WHEN c2.dim_key IS NULL THEN t1.dwh_display_col ELSE c2.dim_display END lkp_disp_column_name, c1.lookup_table
FROM dwh_meta_columns c INNER JOIN dwh_meta_columns c1 ON c.object_code = c1.object_code
AND c.attribute_code || '_CAPTION' = c1.attribute_code INNER JOIN dwh_meta_tables t ON c.dwh_table = t.dwh_table
INNER JOIN dwh_meta_columns c2 ON c.object_code = c2.object_code AND c2.attribute_code = c.attribute_code || '_CAPTION'
LEFT OUTER JOIN dwh_meta_tables t1 ON c2.lookup_table = t1.dwh_table WHERE c.is_multivalued = 1;
Identify the custom MVL lookups, and then object and attribute
Log in to Clarity and uncheck the lookups from the Data Warehouse
When all custom MVL lookups are unchecked from DWH, run a Full Load

Additional Information

Reference also: