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

book

Article ID: 110060

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS STARTER PACK-CLARITY PPM

Issue/Introduction

Your Load Data Warehouse (DWH) 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 you find out which attribute an object it is on, to disable the lookup from DWH?

Environment

Release: All Supported Releases
Component: Clarity Data Warehouse

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 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:
    1. Go to Administration -> Objects - <object> -> Attributes
    2. 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

Reference also:

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?