Load DWH full fails with "ORA-00904: "LAST_UPDATED_DATE"" on lookup DWH_LKP_OBJ_PROJECT_TEMP_00_V

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Load DATA WAREHOUSE (DWH) job - full load fails on the field: 'Project Template' with ID OBJ_METHODOLOGY

Message:

ERROR 2020-12-07 11:10:35,160 [DWH_LOOKUP_FIELD_MAPPING - Table input 2] dwh.event Table input 2 - Unexpected error
ERROR 2020-12-07 11:10:35,172 [DWH_LOOKUP_FIELD_MAPPING - Table input 2] dwh.event Table input 2 - org.pentaho.di.core.exception.KettleDatabaseException: 
An error occurred executing SQL: 
select (case 
            when exists (SELECT 1
       FROM DWH_LKP_OBJ_PROJECT_TEMP_00_V src
       WHERE 
        ( LAST_UPDATED_DATE is null or LAST_UPDATED_DATE >= to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss') )
      )  
            then 1 
            else 0 
        end) as record_exists
from dual
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "LAST_UPDATED_DATE": invalid identifier

1. Go to Objects -> Project -> Attributes
2. Look for the field "Project Template" with ID OBJ_METHODOLOGY
3. The attribute is checked for "Include in the Data Warehouse" and it is also grayed out.

Environment

Release : 15.9

Component : CLARITY DATA WAREHOUSE

Cause

The issue is that the lookup query PMO Project Templates is not having a last_updated_date and is not suitable for the Data Warehouse. This lookup is not meant to be included this is why it's restricted. For you, it was probably enabled previously, on the 14.x versions where we hadn't yet set those attributes to restricted to avoid DWH issues.

Resolution

Note: This solution is only valid for the OOTB field OBJ_METHODOLOGY.  This is an OOTB attribute that is NOT meant to be in Data Warehouse and is selected and greyed out. This solution is NOT VALID for custom attributes.

1. Run the below query (This will reset the attribute to be editable
   

   update odf_custom_attributes

   set is_system =0

   where object_name ='project'

   and internal_name ='obj_methodology'
   
   ---1 row will be updated
   commit

2. Restart services

3. In Clarity, uncheck the obj_methodology attribute on the Project object from the Data Warehouse

4. Save

5. Run the Load Data Warehouse job - Full Load

6. Once done, run the query to set the attribute back to System:
   
   

   update odf_custom_attributes

   set is_system =1

   where object_name ='project'

   and internal_name ='obj_methodology'

   commit
   
   

7.  Restart services again

Additional Information

For custom lookups please ensure the lookups follow the Best Practices here: Communities Tech Tip on Data Warehouse Lookups

Reference also:

• Load DWH job failures - troubleshooting
• Data Warehouse (DWH) known issues
• Load DWH Full - When to run to prevent failures