search cancel

Incremental Load DWH slow on table DWH_X_PLAN_ACT_PER_FACTS  

book

Article ID: 199187

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse (DWH) job is taking too long / slow performance while populating table DWH_X_PLAN_ACT_PER_FACTS

STEPS TO REPRODUCE: 

  1. Prerequisite to reproduce the problem is to have huge data set with around 35+ million record in DWH_X_PLAN_ACT_PER_FACTS
  2. Under Administration --> System option --> Data warehouse option uncheck INCLUDE ONLY FINANCIAL PLAN OF RECORD
  3. Run the Load Data Warehouse in incremental load
  4. Observe the load time of each table by navigating to Administration --> Security & Diagnostic --> Data Warehouse Portlet

Expected Results: The table DWH_X_PLAN_ACT_PER_FACTS load time should be faster and the cost of query execution should be minimal

Actual Results: The table DWH_X_PLAN_ACT_PER_FACTS load time is most expensive and takes around 5 hour to populate DWH_X_PLAN_ACT_PER_FACTS only and if you generate an explain plan on the query execution it will be seen that while reading from DWH_X_PLAN_ACT_PER_FACTS_V to populate DWH_X_PLAN_ACT_PER_FACTS the cost of query execution is more

Cause

  1. The data volume is huge and read of the view DWH_X_PLAN_ACT_PER_FACTS_V is expensive 
  2. This was logged as DE57736 and fixed with the feature described below

Environment

Feature added in Clarity 15.9.3

Resolution

  • Try to bring only Plan of record to Data Warehouse by checking  INCLUDE ONLY FINANCIAL PLAN OF RECORD by Under Administration --> System option --> Data Warehouse option
  • The way actuals are loaded in improved in 15.9.3 and below steps needs to be followed 
    • To improve performance, a new feature toggle was added. 
      • To Enable the new feature it, run the query on the Clarity database: 

        update dwh_settings set enable_plan_actuals = 1;

         

      • To Disable the new feature it, run the query on the Clarity database:

update dwh_settings set enable_plan_actuals = 0;

 

Additional Information

Below is the time where the load time of slowness was observed 

able Name Updated Date Load Start Date Load End Date Duration (seconds) Row Count
DWH_X_PLAN_ACT_PER_FACTS 02/09/2020 03/09/2020 03/09/2020 14,802 41,358,803
DWH_FIN_TRANSACTION 02/09/2020 03/09/2020 03/09/2020 4,515 29,807,763
DWH_CMN_MV_LOOKUP 02/09/2020 03/09/2020 03/09/2020 3,220 673,646
DWH_FIN_PLAN_PERIOD_FACTS 02/09/2020 03/09/2020 03/09/2020 2,780 50,328,588
DWH_FIN_PLAN 02/09/2020 02/09/2020 03/09/2020 1,611 11,597,065
DWH_FIN_TRANSACTION_LN 02/09/2020 03/09/2020 03/09/2020 1,390 29,807,763
DWH_FIN_TRANSACTION_FACTS 02/09/2020 03/09/2020 03/09/2020 1,144 29,780,639