Data Warehouse Enable Plan Actuals Toggle for Financial Period Plan Facts
search cancel

Data Warehouse Enable Plan Actuals Toggle for Financial Period Plan Facts


Article ID: 219974


Updated On:


Clarity PPM SaaS Clarity PPM On Premise


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


Release : 15.9.3+


  • To improve performance, a new feature toggle was added. 


Toggle ON

  • To apply it, run the query on the Clarity database:

update dwh_settings set enable_plan_actuals = 1;

  • 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:
  • 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

Toggle OFF

  • To disable the toggle to OFF and use the fiscal time slice instead, run update dwh_settings set enable_plan_actuals = 0;

Additional Information


  • If you have developed custom reports using fiscal DWH slices, the data from those may or may not display the same way anymore with Toggle On
  • We recommend testing the toggle on a Test environment for impact before applying in Production