Load DWH fails with Roadmap item attributes - PostgreSQL only (DE58927)

book

Article ID: 208383

calendar_today

Updated On:

Products

Clarity PPM SaaS STARTER PACK-CLARITY PPM Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse (DWH) job fails if a boolean field on the roadmap items object is included in the DWH.

ERROR: cannot change name of view column "dw_updated_date" to "<attribute name>"

STEPS TO REPRODUCE:

  1. Create a boolean attribute on the project object
    • Attribute name: boo
    • Attribute ID: boo
    • API Attribute ID: z_boo
    • Include in DWH checked
  2. Create a boolean attribute on the roadmap item object
    • Attribute name: boo
    • Attribute ID: boo
    • API Attribute ID: z_boo
    • Include in DWH checked
  3. On the Modern UX, go to Roadmaps
  4. Create a roadmap
  5. On the grid view, add the Boolean attribute
  6. Add a couple of projects as roadmap items to the roadmap
  7. Sync linked items: ensure matching custom attributes checkbox is on
  8. Run the Load DWH job in full mode.

Expected results: The Load DWH job to succeed
Actual results: The Load DWH job fails 

Error that can be seen on the job failure:

set variables - An error occurred executing this job entry : 
Couldn't execute SQL: CALL DWH_CFG_ADDIN_EXTRAS_SP();
CALL DWH_PROC_NEW_VIEWS_SP();
ERROR: cannot change name of view column "dw_updated_date" to "boo"
  Where: SQL statement "CREATE OR REPLACE VIEW dwh_cmn_scenario_tgt_facts_v AS SELECT f.scenario_key, SUM(CASE WHEN f.attribute = 'totalActualCost' THEN f.attribute_value ELSE 0 END) actual_total_cost, SUM(CASE WHEN f.attribute = 'actualCapitalCost' THEN f.attribute_value ELSE 0 END) actual_capital_cost, SUM(CASE WHEN f.attribute = 'actualOperatingCost' THEN f.attribute_value ELSE 0 END) actual_operating_cost, SUM(CASE WHEN f.attribute = 'benefit' THEN f.attribute_value ELSE 0 END) benefit, SUM(CASE WHEN f.attribute = 'capacity' THEN attribute_value ELSE 0 END) capacity, SUM(CASE WHEN f.attribute = 'actual' THEN attribute_value ELSE 0 END) actual_capacity, SUM(CASE WHEN f.attribute = 'remainingCapacity' THEN attribute_value ELSE 0 END) remain_capacity, SUM(CASE WHEN f.attribute = 'irr' THEN f

Cause

Caused by DE58927

Environment

Release : 15.9, 15.9.1

Resolution

Fixed in 15.9.2 as defect DE58927

Workaround:
Try taking the custom attributes out of Data Warehouse, and running a Full Load

If this doesn't work, apply the attached Stored Procedure with the fix for DE58927

Attachments

1613059798153__DWH_PROC_NEW_VIEWS_SP.zip get_app