Load DWH job fails with "ORA-01722: invalid number" or "ORA-00904: "A"."LANGUAGE_CODE": invalid identifier"

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

You have enabled multiple custom attributes / lookups for inclusion in the Data Warehouse (DWH) and now the Load Data Warehouse job (incremental and full) is failing with errors like this:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_ODF_TEST_00). SQLERRM : ORA-00904: "A"."LANGUAGE_CODE": invalid identifier
2018/06/27 16:51:43 - ClarityDB - isOracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_ODF_TEST_00). SQLERRM : ORA-00904: "A"."LANGUAGE_CODE": invalid identifier
 
 
2018/06/28 22:45:37 - Oracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_DIM_LOAD (DWH_INV_INVESTMENT). SQLERRM : ORA-01722: invalid number
2018/06/28 22:45:37 - Oracle? - ORA-06512: at line 31 

Additional tables that can be impacted include:

• DWH_INV_TASK
• DWH_INV_PROJECT

Environment

Release: Any

Cause

This usually happens when the lookups are not created according to best practices for Data Warehouse.

Resolution

1. If you recently enabled any custom fields for DWH, especially Lookups, uncheck them from DWH and run a Full Load
2. If you do not recall which ones you have enabled, uncheck all the custom attributes again from the affected object, Save
3. Run Load Data Warehouse - Full to completion
4. Now test all the attributes on your Test environment thoroughly - make sure they do not affect the job. You can start with attributes that are not lookups first
5. Once you confirmed the lookups do not cause a problem, you can enable them in Production.
6. If you see any lookup that fails continuously, ensure it's created according to our Lookups Best Practices for Data Warehouse.

Example troubleshooting using attribute impacted on project object: DWH_CMN_ERROR_MESSAGE table only shows an insert statement into DWH_INV_PROJECT table but does not point out which attribute is causing the job to fail.

1. Run the following sql query to identify the most recently created attribute on Project Object based on the 'created_date' column.
   
   select * from dwh_meta_columns where object_code = 'project' order by created_date DESC
   
   
2. Based on the output, look for the Attribute_Code of the latest created attribute
3. On the Project object attribute page search for the attribute and uncheck from DWH.
4. After that run the Load Data Warehouse (in full load).

Additional Information

Reference also:

• Load DWH failing on custom lookup - finding the attribute to disable