ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Load DWH job fails with ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found on DWH_INV_PROJECT

book

Article ID: 33258

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse job fails and the following error message gets thrown in bg-dwh.log:

2015/10/30 21:01:18 - User Defined Java Class.0 - 2015/10/30 21:01:18 - Week Start Date evaluation - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
2015/10/30 21:01:18 - User Defined Java Class.0 - 2015/10/30 21:01:18 - Week Start Date evaluation - ORA-06512: at "PPM_DWH.CMN_SAVEDROP_CREATE_INDEXES_SP", line 27
2015/10/30 21:01:18 - User Defined Java Class.0 - 2015/10/30 21:01:18 - Week Start Date evaluation - ORA-06512: at line 27
2015/10/30 21:01:18 - User Defined Java Class.0 - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : Unexpected error
2015/10/30 21:01:18 - 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_INV_PROJECT


Steps to Reproduce:

  1. Connect to the Clarity database
  2. Observe the table PRJ_EV_HISTORY and indexes on it. See that there is no unique index on OBJECT_ID, OBJECT_TYPE and PERIOD_NUMBER
  3. Pick any entry with OBJECT_TYPE = 'PROJECT' and PERIOD_TYPE =0
  4. Note the object_id on it, that's the project id reference to inv.investments.id
  5. Insert a new value with exactly the same OBJECT_ID, OBJECT_TYPE and PERIOD_NUMBER in PRJ_EV_HISTORY table
  6. Now run the following query:

    select investment_key from dwh_project_v
    group by investment_key
    having (count(investment_key) > 1)
  7. Note that your object_id is now returned by this query
  8. Now run Load Data Warehouse job 

Expected Result: Load Data Warehouse job to run successfully
Actual Result: Load Data Warehouse job fails with error message ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Cause

CLRT-79268 PRJ_EV_HISTORY allows for duplicates for object_id, which can cause DWH_PROJECT_V to return duplicate values and fail Load DWH job

Environment

Release: 14.2 and 14.3

Resolution

Resolved in the CA PPM 14.4 release.

Workaround:

  1. Identify the project having duplicates with the SQL query (for MSSQL and Oracle):

    select i.name as Project_Name, i.code as Project_code from inv_investments i
    where id in (
    select object_id
    from prj_ev_history
    where OBJECT_TYPE='PROJECT'
    and PERIOD_NUMBER=0
    group by object_id
    having (count(object_id) > 1))
  2. In the Clarity UI go to Home - Reports and Jobs
  3. Run Update Earned Value and Cost Totals for the same project(s) that you retrieved with the SQL query in step 1.
  4. Once done, run the Load Data Warehouse job again

Additional Information

Reference also:

Powered by Wolken Software