The ETL job fails for CMN_OBS_HIERARCHY Table population

Document ID : KB000004272
Last Modified Date : 21/05/2018
Show Technical Document Details
Issue:

Moving the OBS level in PPM UI on a large customer dataset causes duplicates in PRJ_OBS_UNITS_FLAT table. This then causes the Load Data Warehouse job to fail. The job fails for CMN_OBS_HIERARCHY table population.

Steps to Reproduce:  

1. Run Load Data Warehouse Job 

Expected Result: Load Data Warehouse job completes

Actual Result: Load Data Warehouse job fails with the following error: 

ERROR:

Job Started 12/7/15 2:29 PM 

Error 12/7/15 2:34 PM
ETL Job Failed. Please see log bg-dwh.log for details.
Error 12/7/15 2:34 PM
User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_CMN_OBS_HIERARCHY
at Processor.processRow(Processor.java:64)
at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
at java.lang.Thread.run(Thread.java:745)
Error 12/7/15 2:34 PM
dwh_etl_interface_dim - Errors detected!
Error 12/7/15 2:34 PM
dwh_etl_interface_dim - Errors detected!
Error 12/7/15 2:34 PM
dwh_db_check_ - An error occurred executing this job entry : 
Couldn't execute SQL: DECLARE
TYPE DIM_REC IS RECORD (CHILD_LEVEL NUMBER(32), CHILD_OBS_UNIT VARCHAR2(360), CHILD_OBS_UNIT_KEY INTEGER, CLARITY_CREATED_DATE DATE, LAST_UPDATED_DATE DATE, IS_INVESTMENT_OBS NUMBER(32), IS_RESOURCE_OBS NUMBER(32), OBS_TYPE VARCHAR2(240), OBS_TYPE_ID VARCHAR2(60), OBS_TYPE_KEY INTEGER, PARENT_LEVEL NUMBER(32), PARENT_OBS_UNIT VARCHAR2(360), PARENT_OBS_UNIT_KEY INTEGER, dw_updated_date date);
TYPE ARRAY IS TABLE OF DIM_REC;
l_data ARRAY;
l_cursor SYS_REFCURSOR;
V_SQL_TEXT VARCHAR2(32000);

BEGIN
CMN_SAVEDROP_CREATE_INDEXES_SP('DWH_CMN_OBS_HIERARCHY','SAVE_DROP');

V_SQL_TEXT := 'select CHILD_LEVEL, CHILD_OBS_UNIT, CHILD_OBS_UNIT_KEY, CLARITY_CREATED_DATE, LAST_UPDATED_DATE, IS_INVESTMENT_OBS, IS_RESOURCE_OBS, OBS_TYPE, OBS_TYPE_ID, OBS_TYPE_KEY, PARENT_LEVEL, PARENT_OBS_UNIT, PARENT_OBS_UNIT_KEY, to_date(''2015/12/07 11:33:06'', ''yyyy/mm/dd HH24:MI:SS'') as dw_updated_date from DWH_CMN_OBS_HIERARC
Job Completed 12/7/15 2:34 PM
NJS-0401: Execution of job failed.
Error 12/7/15 2:34 PM

Environment:
Clarity 14.2 and higher 
Cause:

After investigating the issue as CLRT-80534, it was determined that this issue is not a defect, but it is due to corrupted data. This issue is not reproducible with a vanilla database.

 

Resolution:

Resolution:

1. Check for DWH_CMN_OBS_HIERARCHY_V duplicates.

(Run the following command from the PPM database)

select OBS_TYPE_KEY,PARENT_OBS_UNIT_KEY,CHILD_OBS_UNIT_KEY, count(1)
from DWH_CMN_OBS_HIERARCHY_V where LAST_UPDATED_DATE >= to_date('1910/01/01 00:00:00', 'yyyy/mm/dd HH24:MI:SS')
group by OBS_TYPE_KEY,PARENT_OBS_UNIT_KEY,CHILD_OBS_UNIT_KEY
having count(1) > 1 

If there are records returned,
prj_obs_units_flat table has duplicate records.

(Backup PPM database before running the following command:)

Run following query from the PPM database to resolve the issue.

2.

DELETE FROM prj_obs_units_flat

WHERE id IN (SELECT MIN(id)

FROM prj_obs_units_flat 

GROUP BY unit_id, branch_unit_id
HAVING count(*) > 1) ;