When you run the Project Earned Value Report, for some projects you see multiple (duplicated rows) or duplicated values in the results.
The issue can also happen when running Load Data Warehouse, the job will fail with either of the two error messages below:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at "PPM_DWH.DWH_X_INV_TASK_SUM_FACTS_LOAD"
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at "PPM_DWH.DWH_INV_TASK_PERIOD_FACTS_LOAD"
Steps to Reproduce:
1. Ensure all prerequisites for the Data Warehouse are met
2. Run the Load Data Warehouse job in full mode
3. Note that it completes successfully
4. Now run the Load Data Warehouse job in incremental mode
Expected Result: The incremental Load Data Warehouse job completes successfully
Actual Result: The jobs fails
This is caused by duplicate rows in the database for this project. To prove this, run the query:
select * from prj_ev_history
where object_type = 'PROJECT'
and project_id = 5021196 ------- replace this ID with the project correct internal ID
order by period_start_date
You will see some periods have duplicate rows.
Release: CODFSS99000-15.4.1-PPM SAAS FedRAMP-Sandbox-Small Environment
DE39375 Project Earned Value Report Query
- fixed in the Jaspersoft report provided with 15.4.1
This fix will ensure the report will only return one row even if duplicates exist
DE39970 Update Earned Value and Cost Totals job should be incompatible with itself, leading to duplicates in PRJ_EV_HISTORY
- fixed in PPM 15.5
This fix will ensure the duplicates DO NOT happen in PRJ_EV_HISTORY as we found they sometimes happen due to the jobs running at the same time
DE37008 Load Data Warehouse - Incremental fails with ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at "PPM_DWH152.DWH_INV_TASK_PERIOD_FACTS_LOAD", line 56
- fixed in PPM 15.4
1. Backup the table PRJ_EV_HISTORY
2. Find all duplicates with query:
SELECT OBJECT_ID, OBJECT_TYPE, PERIOD_NUMBER, count(1) from PRJ_EV_HISTORY group by OBJECT_ID, OBJECT_TYPE, PERIOD_NUMBER having count(1) > 13. Delete the duplicates from PRJ_EV_HISTORY (contact Support if need assistance) Queries to use depending on Database vendor:
KB000033258: Load Data Warehouse job fails with ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found on DWH_INV_PROJECT