Data warehouse (DWH) truncates large string attributes to include only the first 1000 chars
book
Article ID: 44673
calendar_today
Updated On:
Products
Clarity PPM On PremiseClarity 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:
Go to Administration > Objects > Project > Attributes > New
Create a new Large String attribute and make sure that it is marked as "Include in the Data Warehouse"
Add the attribute into Project Views so it can be filled.
Create a new project, add a large string to this attribute of more than 1000 characters long (e.g.: 3000 chars).
Execute the job "Load Data Warehouse".
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.