CA PPM: Load Data Warehouse Job Fails with error ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Document ID : KB000073073
Last Modified Date : 14/05/2018
Show Technical Document Details
Issue:
The Load Data Warehouse (DWH) job - Incremental fails with error ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at "PPM_DWH152.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 with error message: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at "PPM_DWH152.DWH_INV_TASK_PERIOD_FACTS_LOAD"
Cause:
This is reported as defect DE37008
 
Resolution:
This is resolved in  CA PPM 15.4+

Workaround:

Check for project task duplication with query:

select TASK_KEY, INVESTMENT_KEY, COUNT(1)
from DWH_X_INV_TASK_SUM_FACTS_V
where clarity_updated_date >= to_date('1910/01/01 00:00:00', 'yyyy/mm/dd HH24:MI:SS')
group by TASK_KEY,INVESTMENT_KEY
having COUNT(*) > 1

If results are returned, go to the duplicated tasks
and click the Action > Update Cost Totals

This will fix the duplication and allow the full Load DWH job to complete.
 
Additional Information:

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