Load Data Warehouse Job Failure when including new attributes

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse job with (Full Load) option fails when including new custom attributes to Data Warehouse.

app-ca log file reports:
------------------------------
WARN 2018-07-18 17:24:48,975 [http-nio-14001-exec-23] odf.view-generation (clarity:[email protected]:10663129__966CA035-71E2-4D24-B68C-F0FF652D8630:odf.enabledwhAttributes) A SQL exception occured when creating view DWH_COSTPLAN_V. The view will not be recreated. [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "ODF_CA_BENEFITPLAN"."APPROVAL_DATE": invalid identifier 

Environment

CA PPM SaaS and CA PPM On Premise

Cause

When a new attribute is included in Data Warehouse it immediately updates the referenced object view with its column name. 
- As in this case when trying to include an attribute under Cost Plan object to Data Warehouse.
- It tries to update the DWH_COSTPLAN_V view under PPM Schema and is failing due to "APPROVAL_DATE" attribute under Benefit Plan object.

Resolution

1. In this case one needs to Exclude the impacted attributes from the BENEFIT PLAN object to successfully Include the new attributes from Cost Plan object.
2. Once the referencing column for the new attribute gets successfully created under DWH_COSTPLAN_V view
3. Include all the attributes from BENEFIT PLAN that was excluded in step 1.
4. Run the Load Data Warehouse Job with (Full Load) option to make sure it completes successfully without any error under app-ca.log or bg-dwh.log file.

NOTE: Every time one tries to Include a new attribute to Data Warehouse it should immediately update the referenced object view with attribute column name. If it doesn't gets created look for the error message under app-ca.log file and repeat step 1 until the view successfully gets created.