Incremental Load DWH slow on table DWH_X_PLAN_ACT_PER_FACTS

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

Environment

Feature added in Clarity 15.9.3

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

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;

• • Detailed information on new feature in KB : Data Warehouse Enable Plan Actuals Toggle for Financial Period Plan Facts

 

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