Load Data Warehouse job fails with error message ORA-02437: cannot validate (PPM_DWH.DWH_PFM_INVESTMENT_MAPPING_PK) - primary key violated

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Symptoms:
Load Data Warehouse fails with error message:
ORA-02437: cannot validate (PPM_DWH.DWH_PFM_INVESTMENT_MAPPING_PK) - primary key violated
Example of the error in bg-dwh.log:ORA-02437: cannot validate (PPM_DWH.DWH_PFM_INVESTMENT_MAPPING_PK) - primary key violated
2015/06/18 11:32:22 - User Defined Java Class.0 - null
2015/06/18 11:32:22 - User Defined Java Class.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Unexpected error
2015/06/18 11:32:22 - 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_PFM_INVESTMENT_MAPPING
2015/06/18 11:32:22 - User Defined Java Class.0 - at Processor.processRow(Processor.java:64)
2015/06/18 11:32:22 - User Defined Java Class.0 -
 

Cause

This is due to a known issue in CA PPM 14.2
CLRT-78087 Index PFM_INVESTMENTS PFM_INVESTMENTS_U1 index should be unique

Environment

CA PPM 14.x and up

Resolution

Run the query below to identify the portfolios that have duplicates (for MSSQL and Oracle):

select distinct pp.name
from pfm_portfolios pp
join (select pi.portfolio_id,pi.investment_id, count(*) as count2
from pfm_investments pi
group by pi.portfolio_id,pi.investment_id
having count(*) > 1) dups on pp.id = dups.portfolio_id

If duplicates are present and identified on a portfolio, do the following to workaround the issue:

1. Go to Clarity PPM UI - Home - Portfolios
2. Open the portfolio affected
3. Go to Contents Editor, note the included investments and investment types 
4. Uncheck the Included Investments Types and investments to exclude them 
5. Click Sync, wait until it syncs completely. This may leave the duplicates for now
6. Once the portfolio is synced, go to Contents Editor again 
7. Check the same investment types as in Step 3 
8. Run the Sync again. This will correct the investment information

Once this workaround is performed, the Load Data Warehouse job will run successfully.

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.