Field Size Mismatch for Description in Data Warehouse Hierarchies

Products

• Clarity PPM SaaS
• Clarity FedRAMP
• Clarity PPM On Premise

Issue/Introduction

The Description field in Clarity is defined with a data type size of 1333 characters, whereas in the Data Warehouse (DWH) the same field is limited to 760 characters. This mismatch in field size leads to Load Data warehouse Job failure.

Steps to Reproduce:

1. Create a hierarchy instance
2. Add the Description column to the grid and populate it with values exceeding 760 characters.
3. Run the Load data warehouse job

Expected Results: Load Data warehouse job should be successful

Actual Results: Load Datawarehouse job fails with error "ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_CMN_HIERARCHY. value too long for type character varying(760)" 

Environment

Clarity 16.3.2, 16.3.3, 16.4.0, 16.4.1 

Cause

DE183526

Resolution

Fixed in

• Clarity 16.4.2
  
  

Workaround: Limit the Description field value to 760 characters or fewer by running the query provdied below

 

Additional Information

Diagnostic Query: Run the following on the transactional schema (Clarity) database to identify affected records 

• Oracle / PostgreSQL

  SELECT
      ID,
      CODE,
      NAME,
      LENGTH(DESCRIPTION)        AS DESC_LENGTH,
      SUBSTR(DESCRIPTION, 1, 100) AS DESC_PREVIEW
  FROM ODF_HIERARCHIES
  WHERE ODF_OBJECT_CODE = 'odf_hierarchy'
    AND LENGTH(DESCRIPTION) > 760
  ORDER BY DESC_LENGTH DESC;

• MS SQL Server 

  SELECT
      ID,
      CODE,
      NAME,
      LEN(DESCRIPTION)           AS DESC_LENGTH,
      LEFT(DESCRIPTION, 100)     AS DESC_PREVIEW
  FROM ODF_HIERARCHIES
  WHERE ODF_OBJECT_CODE = 'odf_hierarchy'
    AND LEN(DESCRIPTION) > 760
  ORDER BY DESC_LENGTH DESC;