Incremental DWH Job is taking longer than after upgrade to 16.0.2

Products

• Clarity PPM On Premise

Issue/Introduction

 Post upgrade to 16.0.2 the incremental DWH is taking longer with Oracle as database 

Steps to Reproduce:

1. Benchmark the timings of Load Dataware house job (Incremental & Full)
2. Upgrade to Clarity 16.0.2 
3. Ensure to have around 30-40 multi value lookup attribute included in DWH in order to reproduce this 
4. Run the load Load Dataware house job both incremental and full load and note the timing 

Expected Results: The load timing of both incremental and full load should be same

Actual Results: The timing of incremental load DWH increases drastically 

 

Environment

Release : 16.0.2, 16.0.3 

Component : Clarity Data Warehouse

Cause

The key reason have noticed for this issue because of MVL attribute display values updates are repeated for every lookup table and this was due to unconditional select query in DWH_MVL_FIELD_MAPPING_SP procedure logic. This procedure logic tries to update MVL display values in dwh_cmn_mv_lookup table if any of the input lookup table values are modified but there is no restriction on lookup table to consider the values to update, the logic tries to consider all lookup tables and update the display values and this happened repeatedly.

DE65663

 

Resolution

The DE65663 is fixed in 16.0.3 Patch 1 and 16.1.0 and now updates to dwh_cmn_mv_lookup table display values on input lookup table rather doing updates for all lookup tables