The Load Data Warehouse job fails and the following error message gets thrown in bg-dwh.log:
2015/10/30 21:01:18 - User Defined Java Class.0 - 2015/10/30 21:01:18 - Week Start Date evaluation - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
2015/10/30 21:01:18 - User Defined Java Class.0 - 2015/10/30 21:01:18 - Week Start Date evaluation - ORA-06512: at "PPM_DWH.CMN_SAVEDROP_CREATE_INDEXES_SP", line 27
2015/10/30 21:01:18 - User Defined Java Class.0 - 2015/10/30 21:01:18 - Week Start Date evaluation - ORA-06512: at line 27
2015/10/30 21:01:18 - User Defined Java Class.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Unexpected error
2015/10/30 21:01:18 - 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_PROJECT
Steps to Reproduce:
select investment_key from dwh_project_v
group by investment_key
having (count(investment_key) > 1)
Expected Result: Load Data Warehouse job to run successfully
Actual Result: Load Data Warehouse job fails with error message ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
CLRT-79268 PRJ_EV_HISTORY allows for duplicates for object_id, which can cause DWH_PROJECT_V to return duplicate values and fail Load DWH job
Release: 14.2 and 14.3
Resolved in the CA PPM 14.4 release.
Workaround:
select i.name as Project_Name, i.code as Project_code from inv_investments i
where id in (
select object_id
from prj_ev_history
where OBJECT_TYPE='PROJECT'
and PERIOD_NUMBER=0
group by object_id
having (count(object_id) > 1))
Reference also: