Full Load DWH Job Fails With CLARITY_UPDATED_DATE Error

Products

• Clarity PPM On Premise

Issue/Introduction

The BG DWH log has the error:

2022/02/28 22:41:50 - User Defined Java Class.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Unexpected error
2022/02/28 22:41:50 - User Defined Java Class.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_BOARD
2022/02/28 22:41:50 - User Defined Java Class.0 -  at Processor.processRow(Processor.java:64)
2022/02/28 22:41:50 - User Defined Java Class.0 -  at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)
2022/02/28 22:41:50 - User Defined Java Class.0 -  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
2022/02/28 22:41:50 - User Defined Java Class.0 -  at java.base/java.lang.Thread.run(Thread.java:834)
2022/02/28 22:41:50 - dwh_etl_interface_dim - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Errors detected!

ERROR 2022-02-28 22:41:50,440 [DWH_DIM_FIELD_MAPPING - Table input 2] dwh.event Table input 2 - org.pentaho.di.core.exception.KettleDatabaseException: 
An error occurred executing SQL: 
SELECT CASE WHEN a.record_count > 0 THEN 1 ELSE 0 END record_exists
FROM  (SELECT COUNT(1) record_count
       FROM   DWH_BOARD_V src
    WHERE (CLARITY_UPDATED_DATE IS NULL OR CLARITY_UPDATED_DATE >= to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss'))) a
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "CLARITY_UPDATED_DATE": invalid identifier

 

OR

ERROR 2023-09-16 05:00:29,337 [DWH_DIM_FIELD_MAPPING - Table input 2] dwh.event (clarity:user:session:Load Data Warehouse - Full) Table input 2 - org.pentaho.di.core.exception.KettleDatabaseException: 
An error occurred executing SQL: 
SELECT CASE WHEN a.record_count > 0 THEN 1 ELSE 0 END record_exists
FROM  (SELECT COUNT(1) record_count
       FROM   DWH_BOARD_V src
       WHERE (CLARITY_UPDATED_DATE IS NULL OR CLARITY_UPDATED_DATE >= to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss'))) a
ORA-00942: table or view does not exist

Cause

This can happen after rebuilding the DWH database, on any table.

Resolution

The following steps can be taken.

Resolution 1: 
Refresh the Clarity database.
Then rebuild the DWH database.

Make sure the dblink created is successfully tested/working.

Resolution 2:
Make sure all PMO Accelerator objects and attributes make its way to the DWH db.

Go to Classic UI > Administration Menu, Studio, Content Add-Ins
1. Click Accelerator: Program Management Office (id=csk)
2. Click Items tab
3. Filter for Type=Object. Click Filter.
4. Checkmark all items listed and Click 'Install' button.
5. After the objects are installed, run the full Load DWH job.