Load Data Warehouse job fails with ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found on DWH_INV_PROJECT

Document ID : KB000033258
Last Modified Date : 12/03/2018
Show Technical Document Details
Issue:

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:
1. Connect to PPM database : observe the table PRJ_EV_HISTORY and indexes on it. See that there is no unique index on OBJECT_ID, OBJECT_TYPE and PERIOD_NUMBER
2. Now pick any entry with OBJECT_TYPE = 'PROJECT' and PERIOD_TYPE =0
3. Note the object_id on it, that's the project id reference to inv.investments.id
4. Insert a new value with exactly same OBJECT_ID, OBJECT_TYPE and PERIOD_NUMBER in PRJ_EV_HISTORY table
5. Now run the following query:
select investment_key from dwh_project_v
group by investment_key
having (count(investment_key) > 1)
6. Note that your object_id is now returned by this query
7. Now run Load Data Warehouse job 

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

Cause:

This is due to a known issue in CA PPM 14.2 and 14.3:

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

Resolution:

CLRT-79268 has been addressed and resolved in the CA PPM 14.4 release.

Workaround:
1. Identify the project having duplicates with the SQL query (for MSSQL and Oracle):

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

2. Connect to Clarity UI - Home - Reports and Jobs
3. Run Update Earned Value and Cost Totals for the same project(s) that you retrieved with the SQL query in step 1.
4. Once done, run the Load Data Warehouse job again

Note : If you still experience an issue to run the Load Data Warehouse job after following the steps, or if you have no results running the query: please contact CA Support and refer to this article.

Additional Information:
Reference KB000073073 - Load Data Warehouse Job Fails with error ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found