Clarity PPM1

Expand all | Collapse all

Tech Tip: Load Data Warehouse is failing on a custom lookup, how to find out which attribute to disable?

  • 1.  Tech Tip: Load Data Warehouse is failing on a custom lookup, how to find out which attribute to disable?

    Posted 08-08-2018 10:45 AM

    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:

     

    1. Check the error message in Home - Job - Log or in bg-dwh.log
    2. 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

    1. Remove the DWH_LKP_ and the _00 if present. You need the lookup name only.
    2. 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%'

    )

    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.

    1. 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.
    2. Run Load Data Warehouse - Full Load and then Incremental Load. This should fix the issue.


  • 2.  Re: Tech Tip: Load Data Warehouse is failing on a custom lookup, how to find out which attribute to disable?

    Posted 08-08-2018 10:49 AM


  • 3.  Re: Tech Tip: Load Data Warehouse is failing on a custom lookup, how to find out which attribute to disable?

    Posted 11-29-2018 05:57 AM

    We are getting this error for a custom attribute which is being used in some of our Jaspersoft reports. Disabling the attribute works and we don't get the error. However, when we re-enable it, we again get the error. How to get rid of the error while keeping the custom attribute enabled? Error message is as below:

     

    User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING LOOKUP JOB EXECUTION FOR THE TABLE - DWH_LKP_XXGF_LKP_TEAM_ME_00



  • 4.  Re: Tech Tip: Load Data Warehouse is failing on a custom lookup, how to find out which attribute to disable?

    Posted 11-29-2018 11:00 AM

    Hi Deepak,

     

    Can you get the exact database error message from the bg-dwh.logs? This would give us more insight on what exactly caused it. Search on "JDBC Driver", or "ORA" if your database is Oracle. Or you can also attach all the updated bg-dwh logs here after a failure.



  • 5.  Re: Tech Tip: Load Data Warehouse is failing on a custom lookup, how to find out which attribute to disable?

    Posted 12-12-2018 06:31 AM

    Hello Nika,

     

    Thanks for your reply. We identified the error message from the logs as "ORA-30926: unable to get a stable set of rows in the source tables". Also found a KB article for same here:

    Load DWH job failure on Incremental ORA-30926: una - CA Knowledge 

     

    Issue was due to duplicates in the hidden key. We have now modified the hidden key as per the article and issue seems to be resolved for us.