Load DWH job fails with error: "ORA-00904 ... invalid identifier" after an attribute is deleted

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

In Clarity, once a Data Warehouse (DWH) enabled attribute is deleted from Clarity studio the subsequent partial and full load jobs fail. In the bg-dwh logs, you will see the below error

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-06550: line 7, column 1188:PL/SQL: ORA-00904: "xxxx": invalid identifier ORA-06550: line 6, column 1:PL/SQL: SQL Statement ignored

Environment

Clarity 15.8.1 and higher releases

 

Cause

The metadata for attributes included in the DWH is stored in both the PPM and DWH schemas. When the attribute metadata goes out of sync, the Load DWH job tends to fail with the invalid identifier error in the bg-dwh logs.

Resolution

To recover from the load DWH failure situation

The action required would be to add the deleted attribute back to Clarity studio the exact way it was before it was deleted. 

1. In Clarity, go to Administration ->Studio -> Objects
2. Open the respective object
3. Go to Attributes
4. Create attribute "xxxx" with the same id for the attribute prior to it being deleted.
5. From the attribute properties check the box "include in the data warehouse"
6. Run a FULL Load DWH job

To delete an attribute

The ideal steps to delete an attribute already checked for Data Warehouse job processing is as follows:

 

    1. From Clarity studio, bring up the Object> Attribute > Attribute properties

    2. Un-check the "Include in the Data Warehouse" box for removing the attribute for Data Warehouse processing

    3. Run a FULL Load DWH job

    4. After the job is successful, then you can choose to deactivate or delete the attribute from the system.

Additional Information

Reference also:

• Load DWH job fails with ORA-00904 invalid identifier on a custom field
• Load Data Warehouse Job - Documentation that references information on the full load vs incremental load Data Warehouse job