The Load DWH job fails on the CMN_OBS_HIERARCHY table population

Products

• Clarity PPM On Premise

Issue/Introduction

Moving the OBS level in Clarity PPM UI on a large customer dataset causes duplicates in PRJ_OBS_UNITS_FLAT table. This then causes the Load Data Warehouse (DWH) 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

 

Error from job log:

Couldn't execute SQL: BEGIN
FOR I IN (SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P'
AND STATUS = 'DISABLED')
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' ||I.TABLE_NAME|| ' ENABLE CONSTRAINT ' ||I.CONSTRAINT_NAME || ' EXCEPTIONS INTO DWH_FK_PK_EXCEPTIONS';
END LOOP I;
END;

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02437: cannot validate (PPM_DWH.DWH_CMN_OBS_HIERARCHY_PK) - primary key violated 

Environment

Clarity  PPM 14.2 -  15.5.1

Cause

This issue is caused by DE45016 and happens when 2 levels of the OBS are moved. 
 

 

Resolution

This issue is resolved in Clarity PPM 15.6 and back-ported to 15.5.1.4 

Workaround:

1. Check for DWH_CMN_OBS_HIERARCHY_V duplicates.

(Run the following command on the Clarity 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 the Clarity tables involved or the entire database before step 2)

2. Run following query on the Clarity PPM database to resolve the issue.

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) ;
commit;