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

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse (DWH) job is failing on a custom lookup. How to find out which attribute an object it is on, in order to disable the lookup from the DWH?

Environment

Release: All Supported Releases

Cause

The lookup might not be having a proper query to work with DWH.

Few known issues where this can be the cause:

• Load DWH job fails with ORA-00904 invalid identifier on a custom field
• Load DWH fails with error "ORA-30926: unable to get a stable set of rows in the source tables"
• Load DWH job fails with ORA-01427 for table DWH_CMN_MV_LOOKUP when custom lookup query is used

 

Resolution

Find the attribute / object affected for the custom lookup:

1. Check the error message in Home - Job - Log or in bg-dwh.log
2. Take the lookup table name. For example, below 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
   
   
3. Remove the DWH_LKP_ and the _00 or _01 if present. You need the lookup name only.
4. 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
from (
select clt.id, clov.id, oca.id,
  clt.lookup_type, clt.source,
  clov.sql_text_id, clov.object_code,
  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%'
) sub
group by object_name, column_name, lookup_type
order by object_name, lookup_type
   
   Note: It has to include the % at the end as sometimes not the full lookup name is used in the table name.
5. The query will give you the exact object name and attribute that has to be unchecked from the Data Warehouse.
   To uncheck it:
   • Go to Administration - Objects - <object> - Attributes
   • Select the attribute and then uncheck it from the DWH
6. Run Load Data Warehouse - Full Load
7. Run Load DWH - Incremental Load

Additional Information

To discuss the issue on the Communities, please connect here: Tech Tip: Load Data Warehouse is failing on a custom lookup, how to find out which attribute to disable?