Load Data Warehouse Incremental Fails when an lookup tied to DWH_LOOKUP_DIM_MAPPING

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

If a custom attribute is using a lookup that is associated with a lookup_type in DWH_DIM_MAPPING the incremental job will fail with the error on Invalid Identifier

 

STEPS TO REPRODUCE: 

 

   1. Create a custom attribute on the investment object
  Type = Lookup
  Associated Lookup = Browse Entity GL Periods (BROWSE_ENTITY_GL_PERIODS)
  You do need to do the mapping to entity_id
  
 2. Include attribute in DWH
 3. Run full load of DWH
 4. On the project list page you can add the column created in step 1.
 5. Add some values to a few projects
 6. Run incremental Load Data Warehouse
 
Expected Results: Job Completes

Actual Results: Job fails with error: 
   Execute SQL For Lookups - An error occurred, processing will be stopped: 
   Couldn't execute SQL: BEGIN
    DWH_DIM_REFS_MAPPING_SP
     (
      p_dwh_table => xxx_LN',
      p_dblink => 'xxx',
      p_last_load_date => to_date('2022/06/30 08:30:58', 'yyyy/MM/dd HH24:mi:ss'));
   END;

   [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: Encountered exception while updating reference columns for DWH_CMN_PERIOD_LN. SQLERRM: ORA-00904: "xxx": invalid identifier
   ORA-06512: at "CLARITY1602DWH.DWH_DIM_REFS_MAPPING_SP", line 79
   ORA-06512: at line 2
                

 Note that the xxx in the error can be for the specific attribute causing the error.

Workaround:  Exclude the attribute from the data warehouse and then run Full Load Data Warehouse 

Environment

Release : 16.0.1, 16.0.02

Component : Clarity Data Warehouse

Cause

Reported as DE65766

Resolution

Resolved in 16.1.0. It was originally tracked as DE65766.