DWH Load fails when a Parameterized lookup is included whose query produces duplicates

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Load DWH Full load fails with error SQLERRM : ORA-00001: unique constraint (PPM_DWH.DWH_LKP_XXXXXX_U1) violated.

STEPS TO REPRODUCE:
 
1. Login to Clarity.
2. Create a parameterized lookup which gives duplicates without the inclusion of Parameter.
3. Create an attribute and associate this attribute to Lookup. 
4. Include the attribute in DWH.
5. Run the DWH Full Load

Expected Results : Job Completes Successfully.
Actual Results : The Job fails with ORA-00001 unique constraint violation.

Environment

Release : 16.0.0.1, 16.0.1

Component : CLARITY DATA WAREHOUSE

Cause

This is caused by defect DE64044

Resolution

This defect is resolved on the following versions:

• 16.0.2
• 16.0.1.1

Additional Information

Workaround :

1. For customers who just upgraded to 16.0.0.1 or 16.0.1 and haven't run the DWH load, they need to run the attached sql query on DWH Schema which will re-create the Stored Procedure DWH_CREATE_MISSING_ODF_IDX_SP to re-solve the issue.
2. For customers who have upgraded to 16.0.0.1 or 16.0.1 and already ran the DWH load, they need to manually drop the Unique index which is causing the load failure. Once it's dropped run the attached SQL to prevent the unique index from being created again and run the DWH load.