Load Data Warehouse job is failing on table related to deleted Studio object

Products

• Clarity PPM On Premise

Issue/Introduction

2021/01/14 15:32:24 - User Defined Java Class.0 - *****************************************************************
2021/01/14 15:32:25 - User Defined Java Class.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Unexpected error
2021/01/14 15:32:25 - 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_ODF_INVESTMENT_PRODU_01_LN
2021/01/14 15:32:25 - User Defined Java Class.0 -  at Processor.processRow(Processor.java:64)
2021/01/14 15:32:25 - User Defined Java Class.0 -  at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)
2021/01/14 15:32:25 - User Defined Java Class.0 -  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
2021/01/14 15:32:25 - User Defined Java Class.0 -  at java.base/java.lang.Thread.run(Thread.java:834)
2021/01/14 15:32:25 - dwh_etl_interface_dim - dwh_etl_interface_dim
2021/01/14 15:32:25 - dwh_etl_interface_dim - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Errors detected!
2021/01/14 15:32:25 - dwh_etl_interface_dim - dwh_etl_interface_dim

 

Environment

Release : 15.8.1

Component : CA PPM Data Warehouse

Cause

An object (table) was created and then deleted in Clarity Studio.

Normally, this would also clear the registration from the Data Warehouse.

In this case, for some reason unknown to us at this time, the registration was not cleared when the object was deleted.

Resolution

Confirm if an administrator or consultant had recently created and deleted any objects.

Identify the registrations in the database using the following queries:

Backup the following database tables in the Clarity schema

DWH_META_TABLES

DWH_META_COLUMNS

Run the following SELECT statements (replace DWH_ODF_INVESTMENT_PRODU_01 with the table name(s) reflected in the error in the bg_dwh.log):

SELECT * FROM dwh_meta_columns WHERE upper(dwh_table_name) LIKE 'DWH_ODF_INVESTMENT_PRODU_01%_LN';

SELECT * FROM dwh_meta_tables WHERE upper(dwh_table_name) LIKE 'DWH_ODF_INVESTMENT_PRODU_0%_LN';

If any rows are returned, run the following update statements:

UPDATE dwh_meta_columns SET IS_DELETED = 1 WHERE upper(dwh_table_name) LIKE 'DWH_ODF_INVESTMENT_PRODU_01%_LN';

UPDATE dwh_meta_columns SET IS_DELETED = 1 WHERE upper(dwh_table_name) LIKE 'DWH_ODF_INVESTMENT_PRODU_0%_LN';

commit;

Manually run a "Full Load" of the Data Warehouse.