PRJ_EV_HISTORY duplicates cause wrong data in DWH

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

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"
    or
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

Environment

Release: Any Supported Releases

Cause

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.

 

Possible causes include (but not limited to):

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 

Note: If you are on a current release, you can proceed with the workaround

Resolution

Workaround:

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) > 1
3.  Delete the duplicates from PRJ_EV_HISTORY (contact Support if need assistance) Queries to use depending on Database vendor:
   Oracle query:
   DELETE FROM PRJ_EV_HISTORY
         WHERE ROWID IN ( SELECT RID
                           FROM ( SELECT ROWID RID,
                                         ROW_NUMBER() OVER
                                         ( PARTITION BY OBJECT_ID, OBJECT_TYPE, PERIOD_NUMBER ORDER BY LAST_UPDATED_DATE DESC) RN
                                  FROM PRJ_EV_HISTORY WHERE PERIOD_NUMBER = 0
                                 )
                           WHERE RN <> 1);
   
   MSSQL query:
   WITH CTE AS
           (
           SELECT ROW_NUMBER() OVER( PARTITION BY OBJECT_ID, OBJECT_TYPE, PERIOD_NUMBER ORDER BY LAST_UPDATED_DATE DESC)
            AS ROWNUMBER, * FROM PRJ_EV_HISTORY WHERE PERIOD_NUMBER = 0
           )
           DELETE FROM CTE WHERE ROWNUMBER <> 1;
4. Run the Update Earned Value and Cost Totals job for this project
5. Go to CA PPM UI - Administration - Reports and Jobs
6. Browse for Update Earned Value and Cost Totals
7. Set the job to be Incompatible with itself

Alternate workaround:

1. Identify the project that has the issue
2. Connect to Clarity UI
3. Run Update Earned Value and Cost Totals job for this project

Most times the job will resolve the duplicates.