Diagnosing Performance Issues After Deploying Configurable Calculation (Insertion Point) Rules

Configurable calculations can be used to implement the use-cases that are not provided by the out of the box consolidation process.

Multi-currency applications: Configurable calculations can be written for the processing of Local Currency (untranslated entity level data), Translated (translated entity level data), and Consolidated data.
Single currency applications: Configurable calculations can be written for the processing of Local Currency (untranslated entity level data) and Consolidated data.

These calculations, invoked by Financial Consolidation and Close when a consolidation process is run, cannot be invoked as standalone calculations. Poorly written configurable calculations can severely impact the run time performance of the consolidation process. The Recommendations card of Financial Consolidation and Close displays warnings about rules that can cause performance degradation during consolidation. These warnings, which are pointers to the best practices that you should follow while writing configurable calculation scripts, could, potentially, improve the consolidation performance. For detailed information, see Resolving Calc Script Validation Warnings in Financial Consolidation and Close Administrator's Guide.

Additionally, use the following suggestions to improve the performance of configurable calculations.

Run Local Currency configurable calculations as on demand rules
Disable CALC DIM logic
Set HYBRIDBSOINCALCSCRIPT
Run configurable calculations in BOTTOMUP manner
Use BLOCK mode calculations
Consider the total members on right hand side of the expression

Running Local Currency Configurable Calculations as On Demand Rules

Configurable calculations written for Local Currency are run for Entity Currency and Entity Input members from Currency and Consolidation dimensions respectively. With some changes, Local Currency configurable calculations can be written as On Demand Rules (ODRs). Benefits to this approach include:

Shortens consolidation time.
Assume that it takes five minutes for a user to copy prior period (locked) values to current period at Entity Currency and Entity Input level. This task has to be done only one because the prior period value is not likely to change. Including this calculation in Local Currency configurable calculations will cause it to run every time consolidation is invoked causing the consolidation time to increase. Moving such calculations as ODRs allows you to invoke them when required, saving time. In this scenario, by avoiding the copying of prior period data saves you five minutes of consolidation time per period.
Helps debug Local Currency configurable calculations.
Consider a use-case where you need to execute calculation logic at Local Currency and Entity Input level during every consolidation. Because such rules have to be a part of Local Currency configurable calculations, each attempt to debug performance issues with rules requires you to run consolidation, which is a time consuming process. To save time, you can move Local Currency configurable calculations as ODRs, debug and fine tune them, and then move them back in as Local Currency configurable calculations.
Provides easy run-time access and statistics.
ODRs can be invoked from the Financial Consolidation and Close Rules card or directly from Oracle Hyperion Calculation Manager. ODRs run from Calculation Manager generate statistics that help identify where the processing time is being spent and the members on which the calculation is run. Follow these steps to collect run time statistics:
1. Access Calculation Manager and launch an ODR.
2. Select run time prompts.
3. Click OK in Launch Status.
  The Log Messages tab is displayed. This tab displays run time statistics.

Moving a Configurable Calculation to ODR

When configurable calculations are written for Local Currency, you cannot use members from Scenario, Year, Period, and Entity dimensions in FIX, as the anchor, or on the left side of an expression; you can only use members from Currency and Consolidation dimensions. As Local Currency rules are invoked during consolidation process, members of Scenario, Year, Period and Entities dimensions that are supplied to consolidation process as Run Time Prompts (RTPs) are considered.

You cannot use Scenario, Year, Period, Entity, Currency, and Consolidation dimension members in ODRs. Relevant dimension members are provided as RTPs to initiate the ODR run.

Disabling CALC DIM Logic

When you create configurable calculation scripts for an insertion point, Financial Consolidation and Close automatically generates data at parent levels so that these parent members are available in the next insertion point. Financial Consolidation and Close adds CALC DIM logic to roll up the data that you populate. Processing this added CALC DIM logic is a time-consuming process depending upon the complexity of your hierarchy. If you do not need to refer to recalculated parent members in later insertion points, you may speed up the overall consolidation process by disabling the CALC DIM logic that Financial Consolidation and Close adds for insertion points.

Note:

Disabling CALC DIM logic affects the consolidation script generation. Carefully check your consolidation results after Disabling CALC DIM logic.

To prevent Financial Consolidation and Close from generating unnecessary CALC DIM logic in multi-currency applications, define the appropriate Substitution Variables from the following list and set them to True

CONFCALC_FCCS10_DISABLECALCDIM
CONFCALC_FCCS20_DISABLECALCDIM
CONFCALC_FCCS25_DISABLECALCDIM
CONFCALC_FCCS30_DISABLECALCDIM
CONFCALC_FCCS40_DISABLECALCDIM
CONFCALC_FCCS50_DISABLECALCDIM
CONFCALC_FCCS60_DISABLECALCDIM
CONFCALC_FCCS70_DISABLECALCDIM

For example, assume that you have written custom logic in the FCCS_10 and FCCS_20 configurable calculation rules. If you want to disable CALC DIM logic for FCCS_20, but not for FCCS_10, you should define a Substitution Variable named CONFCALC_FCCS20_DISABLECALCDIM and set it to True.

To prevent Financial Consolidation and Close from generating unnecessary CALC DIM logic in single currency applications, define the appropriate Substitution Variables from the following list and set them to True

CONFCALC_FCCS110_DISABLECALCDIM
CONFCALC_FCCS120_DISABLECALCDIM
CONFCALC_FCCS130_DISABLECALCDIM
CONFCALC_FCCS140_DISABLECALCDIM
CONFCALC_FCCS150_DISABLECALCDIM

SET HYBRIDBSOINCALCSCRIPT

The SET HYBRIDBSOINCALCSCRIPT command controls whether or not the specific block in a calculation script execute in hybrid mode.

Consider the following configurable calculations script in Local Currency, After Opening Balance Carry Forward rule:


FIX("Entity Currency", "Entity Input") 
        FIX("Data Input", "No Intercompany", "Local GAAP", "No Intercompany", "No Products")
                 "Changes In Cash"(
                         "Cash" = "Bank" -> "Total Product";
                   )
        ENDFIX
ENDFIX

On initiating consolidation, for configurable calculations, Financial Consolidation and Close enables hybrid mode by encapsulating the rule with SET HYBRIDBSOINCALCSCRIPT FULL; and the SET HYBRIDBSOINCALCSCRIPT NONE; commands, which are not visible in Calculation Manager. These commands are not added for ODRs.

SET HYBRIDBSOINCALCSCRIPT FULL;
FIX("Entity Currency", "Entity Input") 
        FIX("Data Input", "No Intercompany", "Local GAAP", "No Intercompany", "No Products")
                 "Changes In Cash"(
                         "Cash" = "Bank" -> "Total Product";
                   )
        ENDFIX
ENDFIX

SET HYBRIDBSOINCALCSCRIPT NONE;

To ensure that switching-on hybrid mode during consolidation for configurable calculations is not impeding performance, turn off hybrid mode (see the following sample) and then run the consolidation.

SET HYBRIDBSOINCALCSCRIPT FULL;

SET HYBRIDBSOINCALCSCRIPT NONE;
FIX("Entity Currency", "Entity Input") 
        FIX("Data Input", "No Intercompany", "Local GAAP", "No Intercompany", "No Products")
                 "Changes In Cash"(
                         "Cash" = "Bank" -> "Total Product";
                   )
        ENDFIX
ENDFIX

SET HYBRIDBSOINCALCSCRIPT NONE;

Run Configurable Calculations in a BOTTOMUP Manner

Running configurable calculations in TOPDOWN manner may slow them down. Consequently the consolidation process that invokes the calculations slows down. To avoid this issue, make sure to run configurable calculations in a BOTTOMUP manner. For more information, see @CALCMODE in Essbase Technical Reference.

While switching to BOTTOMUP calculations, ensure that destination blocks exist and that the data is calculated correctly.

Use BLOCK Mode Calculations

Because CELL mode calculations may slow down configurable calculations, be sure to run them in BLOCK mode. For more information, see @CALCMODE in Essbase Technical Reference.

While switching to BLOCK mode calculations, carefully consider data dependencies within the block to ensure that the resulting data is accurate.

Consider the Number of Members on the Right Hand Side of the Expression

Some times, you may need to pull aggregated data to No members as in the following example:

"Target_Account_Stored" -> "Mvmt_None" = "FCCS_Mvmts_Subtotal"
 -> "Source_Account_DynamicCalc" -> "Total Custom1" -> "Total Custom2";

This example tries to store the result of the dynamic calculation on the right side into a stored dense member. Financial Consolidation and Close, by default, encapsulates this configurable calculation within SET HYBRIDBSOINCALCSCRIPT FULL; and SET HYBRIDBSOINCALCSCRIPT NONE; commands. However, even after encapsulation, this calculation may not perform well because it is reading from top members. To improve performance, you can rewrite such calculations to iterate over the level 0 members of required dimensions instead of reading from top members as illustrated in the following example:

SET HYBRIDBSOINCALCSCRIPT NONE; /*Turn off hybrid mode */
FIX(@RELATIVE( "Total Custom1", 0 ), @RELATIVE( "Total Custom2", 0 ), <other dimension members of FIX> )
       "FCCS_Mvmts_Subtotal" ( @CALCMODE( BOTTOMUP );
             "Target_Account_Stored" -> "Mvmt_None"  =  "Target_Account_Stored"  -> "Mvmt_None" + "Source_Account_DynamicCalc";
             )
ENDFIX