Data Warehouse Enable Plan Actuals Toggle for Financial Period Plan Facts

book

Article ID: 219974

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

To address the following issues, a new Load Data Warehouse Feature Toggle was added "Enable Plan Actuals": 

  • Slowness during Load Data Warehouse - Incremental and Full runs
  • Failures on Financial Period Plan Facts tables
  • Example of an error:
    ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING FROM DWH_X_PLAN_ACT_PER_FACTS. duplicate key value violates unique constraint "dwh_x_plan_act_per_facts_u3"
  • Hanging or locks/bloat on table DWH_X_PLAN_ACT_PER_FACTS

Environment

Release : 15.9.3+

Resolution

  • To improve performance, a new feature toggle was added. 
  • To apply it, run the query on the Clarity database: 

update dwh_settings set enable_plan_actuals = 1;

  • This will enable the Toggle ON.
  • With Toggle ON the Data Warehouse processing WIP transactions will use the DAILYRESOURCETIMECURVE from_date and number of periods
  • So with Toggle ON we can have a smaller range of WIP transactions which will help performance
  • For Cost Plans, rather than the fiscal time slices from_date and number of periods, the following internal slices will be used:
    • ODF_SSL_CST_DTL_AREV
    • ODF_SSL_CST_DTL_ACOST
    • ODF_SSL_CST_DTL_AUNITS  
  • The new job Update Financial Plan Actuals must be run to update the data once the Toggle is ON
  • We recommend to 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
  • To disable the toggle to OFF and use the fiscal time slice instead, run update dwh_settings set enable_plan_actuals = 0;