ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

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 is logged as potential defect DE57736 and engineering will try to optimize it 

 

Environment

Release : 15.7.1, 15.8, 15.8.1, 15.9, 15.9.1, 15.9.2

Component : CA PPM DATA WAREHOUSE

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;

    • With Toggle ON the Data Warehouse processing WIP transactions will use the DAILYRESOURCETIMECURVE from_date and number of periods rather than the fiscal time slices from_date and number of periods and will have  smaller range of WIP transactions which will help performance
    • Run Update Financial Plan Actuals for the first time before the Load Data Warehouse - Full, and then schedule accordingly before Load Data Warehouse - Full and Incremental

 

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