Load datawarehouse job fails "R"."SR_REPORT_STATUS_KEY": invalid identifier ORA-00904

Products

• Clarity PPM On Premise

Issue/Introduction

Load Data warehouse job fails when running with full load or incremental option in production environment. The environment was recently upgraded from clarity 15.1 to 15.9.1 and the DB is oracle.

The error message in DWH log:

Bg-DWH.logs:

postgres? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "R"."SR_REPORT_STATUS_KEY": invalid identifier
postgres? - ORA-06512: at "PPM_DWH.DWH_CFG_ADDIN_EXTRAS_SP", line 39
postgres? - ORA-06512: at line 2

Environment

Clarity 15.9.1

Cause

Mismatch of metadata between Clarity and Datawarehouse schemas.

• Datwarehouse source column SR_REPORT_STATUS_KEY is associated to attribute called Report Status ( attribute code = cop_report_status).
• Clarity database source view concerned is DWH_COP_PRJ_STATUSRPT_V
• Clarity database tables are ODF_OBJECTS, DWH_META_TABLES, DWH_META_COLUMNS and ODF_OBJECTS

SQL queries used to run agains Clarity schema to check for Datawarehouse metadata discrepancies:

• SELECT * FROM DWH_META_TABLES WHERE SRC_TABLE_NAME='dwh_cop_prj_statusrpt_v'
  -- "The query should confirm if the status report is select for Datawarehouse, to match what is seen on the UI"
  
  
• SELECT *FROM DWH_META_COLUMNS WHERE lower(SRC_TABLE_name)LIKE 'dwh_cop_prj_statusrpt_v'. 
   -- "The query should confirm if the default status report attributes are select for DWH. ideally return rows with (is_deleted = 0 and is_system=1) especially for attribute code = cop_report_status"
  
  
• SELECT * FROM odf_objects where code ='cop_prj_statusrpt'   
  --    "The query should ideally have DWH enabled value =1. The statusreport object as it seen as enabled on the clarity UI> Clarity UI > Administration >Studio > Objects"
  

• SELECT column_name,IS_DW_ENABLED FROM odf_custom_attributes where object_name ='cop_prj_statusrpt' and column_name in ( 'NAME', 'CODE', 'cop_cost_effort_ext', 'cop_cost_effort_rev','cop_cost_eft_staff', 'cop_cost_eft_status','cop_effort_exp', 'cop_key_accomplish','cop_overall_status', 'cop_report_date','cop_report_status', 'cop_report_update','cop_sched_milestone', 'cop_schedule_exp','cop_schedule_status', 'cop_scope_change','cop_scope_deliver', 'cop_scope_exp','cop_scope_obj', 'cop_scope_status','cop_upcoming_act');
  -- "The query should return all 1s for the listed attributes"

Resolution

Steps to resolve in this particular scenario where the correction was only needed on the DWH_META_COLUMNS:

1. Backup table : DWH_META_COLUMNS
   
   
2. Update statement
   Update DWH_META_COLUMNS SET IS_DELETED=0 where IS_SYSTEM=1 AND lower(SRC_TABLE_name) LIKE 'dwh_cop_prj_statusrpt_v':
   commit;
   
   
3. Validate by running : 
   SELECT * FROM DWH_META_COLUMNS WHERE IS_DELETED=0 AND IS_SYSTEM=1 AND lower(SRC_TABLE_name) LIKE 'dwh_cop_prj_statusrpt_v';
   (no rows should be returned)
   
   
4. Run an instance of the Load Datawarehouse job in FULL mode. 

Additional Information

The SQL logic mentioned in the following support KB should help to get a more comprehensive picture for metadata mismatch between clarity and Datawarehouse:

Query to identify mismatch in Studio objects between the Clarity and Data Warehouse Schemas