Your Load Data Warehouse job is failing on a custom lookup, could be with one of the errors below:
ORA-00904: invalid identifier
ORA-30926: unable to get a stable set of rows in the source tables
ORA-01427: single-row subquery returns more than one row
The lookup might not be having a proper query to work with DWH, how can I find out which attribute and object it is on, to disable the lookup from DWH?
How to find out the attribute / object affected for the custom lookup:
- Check the error message in Home - Job - Log or in bg-dwh.log
- Take the lookup table name, for example here is a part of the error: ERROR: THERE WERE ERRORS DURING LOOKUP JOB EXECUTION FOR THE TABLE - DWH_LKP_TEST_VAL_00
Lookup table is DWH_LKP_ TEST_VAL _00
- Remove the DWH_LKP_ and the _00 if present. You need the lookup name only.
- Modify the query below to include the corrected value, example for TEST_VAL:
select object_name as Object_id, column_name as Attribute_code, lookup_type as Lookup_id
select clt.id, clov.id, oca.id,
oca.object_name, oca.internal_name, oca.column_name, oca.data_type,
oca.partition_code, oca.is_multivalued, oca.default_value, oca.derived_object_code,oca.internal_name
from cmn_lookup_types clt
join cmn_list_of_values clov
on clt.lookup_type = clov.lookup_type_code
and clov.is_system = 0
join odf_custom_attributes oca
on oca.lookup_type = clt.lookup_type
and clt.lookup_type like '%TEST_VAL%'
group by object_name, column_name, lookup_type
order by object_name, lookup_type
It has to include the % at the end as sometimes not the full lookup name is used in the table name.
- The query will give you the exact object name and attribute that has to be unchecked from Data Warehouse. Connect to Administration - Objects - <object> - Attributes - Select and Uncheck it.
- Run Load Data Warehouse - Full Load and then Incremental Load. This should fix the issue.