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

Products

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

Issue/Introduction

I am running Load Data Warehouse (DWH) - Full Load, and hitting a similar to this error on a custom field:
 
[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.

Cause

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

Environment

Release: All Supported Releases
Component: Clarity Data Warehouse (DWH)

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. Now run Load Data Warehouse - 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 is failing on a custom lookup, how to find out which attribute to disable?