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 violated2015/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 -
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:
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.