Manual Approach to Resolve Performance Issues

Checking for Poor Rules Design

To ensure correct results and optimal performance, as a best practice, always specify dimensions and members against which calculations are to be run.

Optimal consolidation rules performance requires the scope of calculation be restricted to the required dimensions and members. Not adding the necessary dimensions and members to a calculation forces the rule to run against all the possible member combinations. For example, the following sample rule requires that calculations be run only against the custom dimension member No Department. Adding the dimension member in the source results in faster execution by making the rule run only against No Department.

Checking for Multiple Stored Members in the Source of the Rule (Account Dimension)

Performance degradation may occur when many level 0 stored accounts in a source of consolidation rule are redirected to a single destination account. In such scenarios, Oracle recommends that you create a place holder account member with stored data storage and write an insertion rule in FCCS_110 (single currency applications) or FCCS_30 (multicurrency applications) to redirect the children amount into it. You can then use the place holder account in the source of the consolidation rules in place of the dynamic parent to ensure faster execution of the rule.

Use Case: Resolving Performance Issues After Deploying Configurable Consolidation Rules

This example assumes that 100 P/L accounts (Acc_001 through Acc_100) exist under the dynamic parent Retained Earnings Current, which is one of the many P/L accounts in an application. See the following graphic.

The rules that use Retained Earnings Current as a source either directly or indirectly through references are illustrated in the following graphic.

In the preceding indirect reference, all the Level 0 children of Retained Earnings Current are indirectly a part of the source because it comes under Total Equity.

The following steps are involved in changing the rule definition and adding a redirection script to make the rule execution faster:

1. Create a placeholder account.

   Create a memo account named Retained Earning_Memo under FCCS_BalanceSheet. Set the consolidation operator to ignore (~) and data storage to Store. All other properties will be identical to those of the dynamic parent. As shown in the following illustration, the Retained Earning_Memo place holder account will hold values for Retained Earnings Current.
   
   

2. Refresh the cube.
3. In FCCS_30, copy source income values to the Retained Earning_Memo. You may use the following sample script to copy the children values to Retained Earning_Memo. Be sure to modify the script as needed to fit your requirements. See Configurable Calculations Best Practices in Financial Consolidation and Close Administrator's Guide for information on best practices for performance of configurable calculations.

   SET HYBRIDBSOINCALCSCRIPT NONE;// Use with Hybrid environments only.
   FIX("FCCS_Entity Input", "Parent Currency", "Opening Balance", @RELATIVE("FCCS_Total Data Source", 0), @RELATIVE("FCCS_Intercompany Top", 0))
           "Retained Earning_Memo" (
              @CALCMODE(BOTTOMUP);
              @SUM(@RELATIVE("FCCS_Retained Earnings Current", 0) AND @LIST(@UDA("Account", "REVENUE") OR @UDA("Account", "LIABILITY") OR @UDA("Account", "EQUITY") OR @UDA("Account", "SAVED ASSUMPTION"))) - @SUM(@RELATIVE("FCCS_Retained Earnings Current", 0) AND @LIST(@UDA("Account", "EXPENSE") OR @UDA("Account", "ASSET")));
           ) 
   ENDFIX

4. Use Retained Earning_Memo as the source in configurable consolidation rules where Retained Earnings Current is used.
   1. If ILvl0Descendants(Retained Earnings Current) is directly used in source: The following illustration shows the rule before implementing the recommendations in this use case.
      
      

      The following illustration shows the rule after implementing the recommendations in this use case.
      
      

   2. If ILvl0Descendants(Retained Earnings Current) is indirectly used: The following illustration shows the rule before implementing the recommendations in this use case.
      
      

      The following illustration shows the rule after implementing the recommendations in this use case.