Data warehouse (DWH) truncates large string attributes to include only the first 1000 chars

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

For an attribute that has 3000 chars in the Clarity DB, only the first 1000 characters are in the Data Warehouse.

Steps to Reproduce:

1. Go to Administration > Objects > Project > Attributes > New 
2. Create a new Large String attribute and make sure that it is marked as "Include in the Data Warehouse" 
3. Add the attribute into Project Views so it can be filled. 
4. Create a new project, add a large string to this attribute of more than 1000 characters long (e.g.: 3000 chars). 
5. Execute the job "Load Data Warehouse". 
6. Compare attribute content from Clarity DB (ODF_CA_PROJECT) and DWH DB (DWH_INV_PROJECT).

Expected result: Clarity DB and DWH contain 3000 chars for that attribute

Actual result: The attribute has 3000 chars in Clarity DB and only the first 1000 chars are in the DWH

Cause

Storing 'CLOB' field in PPM as 'VARCHAR' field in DWH was by design.

Resolution

After review by Sustaining Engineering, it has been determined that this is not a defect but part of the original design, to accommodate multi-byte characters which usually take 3 bytes (some can take up to 4 bytes).

Additional Information

• Reason for truncation CLOB and storing first 1000 Characters
• Maximum length for varchar column is 4000 bytes in both SQL & Oracle with the PAS in 14.2, but we store only first 1000 characters.
• This was done to accommodate multi-byte characters which usually take 3 bytes, some can take up to 4 bytes. So, we store first 1000 characters (4000/4) = 1000.