MVL with many values causes DWH job failure

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Using a Multi Valued Lookup (MVL) with many values could cause the Load Data Warehouse job to fail (with PostgreSQL database).

Steps to Reproduce:

1. Using a Clarity PostgreSQL environment, create a new MVL attribute based on a lookup (e.g.: Investment browse if the environment has a lot of investments, which will be Multi valued Lookup - Number).
2. Include the attribute in the Data Warehouse.
3. For any instance that has the attribute, fill in with many values. For the earlier example, as it will use 8 characters for each instance, choose 501 or more values.
4. Run the full Load Data Warehouse job.

Expected Results: Job compltes successfully.

Actual Results: Job fails. Error shown:

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 00:00:00', 'yyyy/MM/dd HH24:mi:ss')::timestamp, P_FULL_RELOAD => 'Y'::text);

ERROR: ENCOUNTERED EXCEPTION WHILE UPDATING DWH_INV_PROJECT.value too long for type character varying(4000)
  Where: PL/pgSQL function dwh_cmn_mvl_load(timestamp without time zone,text) line 141 at RAISE

Environment

Release : 16.0.1

Component : Clarity Data Warehouse

Cause

This is caused by DE64917 (Postgress) and DE69611 (MSSQL)

Resolution

This issue is fixed in Clarity 16.0.3 for Postgress databases (DE64917)

As there are some external limitations on why there could be no more than 4000 characters, the fix will allow the DWH to continue working, but only the records that fit into these limits will be copied to the DWH.

In MSSQL there is a separate defect DE69611 that is targeted to be fixed in 16.1.3 and backported to 16.0.3.5 and 16.1.2.1