Custom skills MVL attribute causing the DWH load job to fail post upgrade to 16.2.3

Products

• Clarity PPM SaaS

Issue/Introduction

If a Custom Skills MVL attribute is created in the system and if you upgrade to 16.2.3 the Load Data Warehouse job will fail to process the MVL load and will end up corrupting the custom skills MVL.

Steps to Reproduce:

1. Setup a Clarity 16.2.2 system
    
2. Create a Lookup: TEST
   Lookup id: TEST    
   Source: static Dependent list and Create some Levels and Values
    
3. Create a Multi-valued attribute with attribute ID = skills
   Attribute name: Skills
   Attribute id: skills
   Associate to the lookup created in the previous step
   API attribute id: r_skills
   Include in the Data warehouse to be checked
   Observe that the attribute would be of MVL string
    
4. Run an instance of the load data warehouse job in FULL mode and it should be successful and DWH metadata would reflect the custom skills attribute.
    
5. Upgrade the clarity system to 16.2.3
    
6. Post upgrade run an instance of the Data Warehouse job in FULL mode

Expected result: The load data Warehouse job post 16.2.3 upgrade is successful and the custom skills MVL string attribute will hold its properties

Actual result: The load data Warehouse job fails post 16.2.3 upgrade and the custom skills MVL attribute is now the number MVL associated with the newly introduced Skills Lookup

 

DWH job log from PG DB environment:

MVL Load PG - An error occurred executing this job entry : 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);ERROR: column lkp.tech_skills_key does not exist  Where: PL/pgSQL function dwh_cmn_mvl_load(timestamp without time zone,text) line 76 at EXECUTE

DWH job log from MSSQL:

DB envionment:ERROR ,508 [Dispatch Load Data Warehouse : bg@machine (tenant=clarity)] dwh.event (clarity:a:Load Data Warehouse) () ETL Job Failed. Log details below: 2024/10/04 21:22:17 - MVL Processing - Starting MVL Processing - MVL Load MSSQL - EXEC DWH_CMN_MVL_LOAD '[TESTDBLINK].niku.niku', 'CONVERT(DATETIME,'1910-01-01 00:00:00')', 'N' 2024/10/04 21:22:21 - Abort job - ERROR (version 9.3.0.0-428, build 9.3.0.0-428 from 2022-04-12 04.56.25 by buildguy) : Aborting job.

Cause

DE154680

Resolution

DE154680, in review by engineering.