Custom lookup fails Load Data Warehouse job with error message ORA-00904: "A"."LANGUAGE_CODE": invalid identifier or ORA-01722: invalid number

Document ID : KB000107086
Last Modified Date : 19/07/2018
Show Technical Document Details
Question:
We have enabled multiple custom attributes for Data Warehouse and now the Load Data Warehouse 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
 
What could this be and how to address this?
Answer:
This usually happens when the lookups are not created according to best practices for Data Warehouse.
To resolve the issue, do the following:

0.    If you recently enabled any custom fields for DWH, especially Lookups, please uncheck them from DWH and run a Full Load
1.    If you do not recall which ones you have enabled, uncheck all the custom attributes again from the affected object
2.    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 may start with attributes that are not lookups first
5.    Once you confirmed the lookups do not cause a problem, you may enable them in Production

If you see any lookup that fails continuously, ensure it's created according to our Lookups Best Practices for Data Warehouse.