Load DWH is slower than before 15.9.2 on a lookup

Products

• Clarity PPM SaaS

Issue/Introduction

The Load DWH is taking several minutes to complete one lookup according to the DWH_ CFG_AUDIT_HISTORY table

 

Environment

Release : 15.9.2

Component : CA PPM SAAS DATA WAREHOUSE

Resolution

We rebuilt the DWH_<lookup> view in use the last modified date

Clarity Schema:

UPDATE dwh_meta_tables 
SET    src_date_column = 'last_updated_date' 
WHERE  UPPER(dwh_table_name) = 'DWH_LKP_LKP_ENTERPRISE_O_00';

CREATE OR REPLACE VIEW dwh_lkp_lkp_enterprise_o_00_v
AS 
SELECT a.name,
       a.unique_name,
       a.id,
       a.last_updated_date
FROM   prj_obs_units a
       INNER JOIN prj_obs_types oot ON a.type_id = oot.id
WHERE  oot.unique_name = 'ent_org';

 

 

DWH Schema:

UPDATE dwh_meta_table 
SET    src_date_col = 'LAST_UPDATED_DATE' 
WHERE  UPPER(src_table) = 'DWH_LKP_LKP_ENTERPRISE_O_00_V';