16.0 upgrade failure on UNIQUE INDEX DWH_CMN_MV_LOOKUP

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Upgrade to 16.0 Failure when there are duplicates in DWH_CMN_MV_LOOKUP(PostgreSQL Only)

 

STEPS TO REPRODUCE: 

1. Take an 15.9.3 environment on Postgres.
2. Run the below query to check for the existence of duplicates in clarity (ODF_MULTI_VALUED_LOOKUPS) 
   SELECT OBJECT,pk_id,ATTRIBUTE,value,count(*) FROM ODF_MULTI_VALUED_LOOKUPS
   GROUP BY OBJECT,pk_id,ATTRIBUTE,value ORDER BY count(*) DESC;
3. Run the full load Datawarehouse job
4. Load job completes successfully
5. Upgrade clarity to 16.0.

 

Expected Results :- Upgrade completes successfully. 

Actual Results :- Upgrade fails.

Install.logs:
(admin) org.postgresql.util.PSQLException: ERROR: could not create unique index "dwh_cmn_mv_lookup_u1"
Detail: Key (table_name, column_name, table_record_key, lookup_value_key, language_code)=(DWH_INV_INVESTMENT_LN, TU_PLATFORMS_KEY, 5460145, 1, en) is duplicated.

or

CREATE UNIQUE INDEX DWH_CMN_MV_LOOKUP_U1 ON DWH_CMN_MV_LOOKUP ( TABLE_NAME, COLUMN_NAME, TABLE_RECORD_KEY, LOOKUP_VALUE_KEY, LANGUAGE_CODE )  

Detail: Key (table_name, column_name, table_record_key, lookup_value_key, language_code)=(DWH_RIM_RISK, ASSOCIATED_ISSUES_KEY, 5414005, 5414012, en) is duplicated.

Environment

Clarity 16.0

Cause

This is caused by product defect DE63197

Resolution

As part of the Defect resolution a cleanup script to clear duplicate rows from PPM and DWH schemas have been provided and it would ensure a smooth 16.0 upgrade.

On-premise Customers running 16.0 upgrades on PostgreSQL environment can raise a Broadcom support case for the Delete script. 

Steps to resolve:

1. Run the cleanup scripts shared against the PPM and DWH schemas
2. Validate duplicates on PPM schema
   SELECT OBJECT,pk_id,ATTRIBUTE,value,count(*) FROM ODF_MULTI_VALUED_LOOKUPS GROUP BY OBJECT,pk_id,ATTRIBUTE,value ORDER BY count(*) DESC;
3. Validate duplicates on Datawarehouse schema
   select table_name, column_name, table_record_key, lookup_value_key, language_code,count(*) from dwh_cmn_mv_lookup dcml group by table_name, column_name, table_record_key, lookup_value_key, language_code having count(*) >1;
4. If Duplciates are clear proceed with 16.0 upgrade