Data Warehouse : Attribute with Data Type URL will always get added to DWH with default length = 255, which fails Load DWH job if value is >255

book

Article ID: 35824

calendar_today

Updated On:

Products

CLARITY PPM FOR ITG CLARITY PPM FEDERAL Clarity PPM SaaS - Application Clarity PPM On Premise

Issue/Introduction

Issue:

When trying to sync attribute with data type URL to Data Warehouse (DWH) it will always get added to DWH with default length = 255 and fail if actual data exceeds that size.

Steps to Reproduce:

  1. Go to Administration, Studio: Objects, 'Project', Attributes tab
  2. Create a new URL attribute called Test_url, set the Maximum Size to 512 
  3. Check Include in the Data Warehouse to be enabled 
  4. Now 'Save and Return' 
  5. Go to Reports and Jobs and run 'Load Data Warehouse' job - Full Load 
  6. Now run a describe on DWH_INV_PROJECT table 

Expected Result: Attribute DWH_INV_PROJECT.test_url to be having length of 512, as in odf_custom_attributes.data_size. Both should match. 
Actual Result: DWH_INV_PROJECT.test_url is 255 characters, which fails Load Data Warehouse job if any value over 255 is entered via UI.

Example error message if value over 255 is added via UI and then Load Data Warehouse job is run:
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-12899: value too large for column "PPM_DWH"."DWH_INV_PROJECT"."TEST_URL" (actual: 258, maximum: 255) 

Cause:

This is caused by defect CLRT-79557

Resolution:
Resolved in PPM 14.4

Workaround:
Make sure the URL is less than or equal to 255 characters or
1. Ensure all scheduled instances of Load Data Warehouse job are paused 

2. Go to Administration, Studio: Objects, 'Project', Attributes tab

3. Select the URL attribute, uncheck the 'Included in Data Warehouse' option 

4. Now run Load Data Warehouse job - Full Load (This completely removes the database column DWH_INV_PROJECT.TEST_URL from the PPM_DWH database). 

5. Once done, go back to the attribute in the application and check it back to be Included in Data Warehouse. Save 

6. Execute the following statement on the dwh_meta_columns table: 

update dwh_meta_columns set attr_data_size=512 where attribute_code ='test_url' ; 

commit ; 

7. Run Load Data Warehouse job - Full Load 

8. This will insert the column properly in Data Warehouse with the correct size of 512 

Please be advised that removing/re-adding the attribute to DWH will revert the changes

 

Environment

Release: ESPCLA99000-14.3-Clarity-Extended Support Plus
Component: