Load DWH job fails with ORA-00904 invalid identifier on a custom field

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Running the Load Data Warehouse (DWH) - Full Load, you hit an error similar to the below on a custom attribute:
 
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_PROJECT). SQLERRM : ORA-00904: "CUSTOM_FIELD": invalid identifier
ORA-06512: at line 31
 
Note: The name of the field and the object table may vary.

On PostgreSQL the error is similar to below:

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INV_IDEA. column "test_key" does not exist

Environment

Release: All Supported Clarity Releases

Cause

• This issue occurs sometimes when there is a mismatch in what's included in the DWH object view on the Clarity side.
• This can also happen if an attribute is deleted from the UI before being removed from the data warehouse.

Resolution

Uncheck/check the attribute to trigger the view regeneration:

1. In Clarity go to Administration -> Objects
2. Open the affected object
3. Go to Attributes
4. Find the attribute affected
5. If it's unchecked, enable it for Data Warehouse
6. If it's checked, then uncheck it from being enabled in Data Warehouse
7. Run the Load Data Warehouse job - Full Load

How to prevent this issue from happening?
Before you delete an attribute make sure it is not included in the data warehouse and the Load DWH job has run to ensure it has come out.

Tip: To find out which attribute a lookup is associated with see: Load DWH failing on custom lookup - finding the attribute to disable

Additional Information

Reference also: Data Warehouse (DWH) known issues