DWH Numeric scale of decimal fields is wrong after change in attribute properties (PostgreSQL)

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

If decimals have been updated on a DWH enabled attribute they are not getting updated in DWH.

This happens on all objects in PostgreSQL, i.e. Project, Cost Plan Detail. Steps to Reproduce below are using object Benefit Plan Detail

STEPS TO REPRODUCE:

1. Go to Clarity – Objects – Benefit Plan Detail
2. In Attributes, add two number fields on Benefit Plan Detail
   • Field Test – number – with API Alias – Include in DWH
   • Field Test2 – number, 2 decimals, with API Alias, Include in DWH
3. Add the fields to a Benefit plan details view – enter values in test2 (i.e. 6.77)
4. Run DWH Full Load
5. Note both numeric fields are created, one as 32,6 and the other as 32,0
6. Now in Studio set field Test to have 2 decimals as well
7. Add a value in test for a specific benefit plan, i.e. 2.33
8. Run Load DWH - Full Load
9. Note the field decimals are not coming through
10. Uncheck the field test from Include in DWH
11. Run a Full Load
12. Now check the field test back in DWH
13. Run a Full Load

Expected Results: the column to be dropped and recreated in DWH_FIN_BENEFIT_PLAN with the correct precision/scale

Actual Results: The column is not dropped and because of this it stays as 32,0 every time, although the correct values of precision and scale 32,6 are saved in dwh_meta_column table

Environment

Clarity 16.2.2, 16.2.3 with PostgreSQL only

Cause

DE153176

Resolution

DE153176 is fixed in 16.3.0 (Targeted to release November 2024) and also backported to 16.2.3 patch 1 

Broadcom SAAS customers will have this fix included with 16.2.3 PROD upgrade

Workaround:

1. Make sure the decimals are updated correctly in DWH in dwh_meta_column (Follow Steps to update DWH Decimals)
2. Engage the DBA to perform the below:
   Update the table column scale manually by running ALTER Table DWH_FIN_BENEFIT_PLAN ALTER TEST SET DATA TYPE NUMERIC(32,6);
3. Run Load DWH - Full Load job