ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

MVL with many values causes DWH job failure

book

Article ID: 240186

calendar_today

Updated On:

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

Cause

This issue has been reported to Engineering as DE64917.

Environment

Release : 16.0.1

Component : Clarity Data Warehouse

Resolution

This issue is being reviewed by Engineering.