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"
This is reported as defect DE37008
This is resolved in CA PPM 15.4+
Check for project task duplication with query:
select TASK_KEY, INVESTMENT_KEY, COUNT(1)
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.
If results are not returned, then the only workaround is to run the full load job.