Data Warehouse Enable Plan Actuals Toggle for Financial Period Plan Facts

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+

Cause

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

Resolution

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:
  • ODF_SSL_CST_DTL_AREV
  • ODF_SSL_CST_DTL_ACOST
  • ODF_SSL_CST_DTL_AUNITS  
• The new job Update Financial Plan Actuals and Forecast 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

Important:

• 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

 

Prior to Clarity 16.4.0, The 'Post WIP Actuals to Financial Plans' job  was named Update Financial Plan Actuals and Forecast.