Optimize Configurations and Extensions

Use of suboptimal logic in consolidation extensions and configurations can adversely impact performance.

You can extend the default consolidation logic of Financial Consolidation and Close applications. Methods that can be used to extend the consolidation logic include the following:

Member formulas
Calculation logic
Translation or consolidation overrides

Use the information in the Activity Report, specifically, the information in the following sections, to identify scripts that take considerable time to run:

Top 10 Worst Performing Business Rules over 30 Seconds
Top 5 Worst Performing Calc Scripts Commands over 1 Min
Top 10 Worst Performing Essbase Queries over 15 seconds

See About the Activity Report in Getting Started Guide for Administrators.

Review Member Formulas

Review member formulas to optimize calculations and logic and to remove unnecessary formulas. You can review member formulas using Oracle Smart View for Office.

Use the Edit Member Properties screen to review, edit, and remove formulas from a dimension. You can remove formulas only from custom dimensions. Seeded formulas on default (out-of-the-box) dimensions, for example, YTD, cannot be removed.

To edit or remove formulas from custom dimensions:

Sign in to Financial Consolidation and Close as a Service Administrator.
On the Home page, click Application and then Overview.
Click Dimensions to open the Simplified Dimension Editor.
Click the name of the dimension that you want to work with.
Click Zoom in All Levels .
In the Console Formula column, locate the formula that you want to edit or remove.

You may remove columns from the current view to declutter the screen by right-clicking in the column header and deselecting some columns.
Edit or delete the formula, as needed, and then click Save to preserve your changes.
Click Save and then Cancel in Edit Member Properties.

Disable Custom Calculations Deployed to the Application

Financial Consolidation and Close uses many predefined rules templates to assist in the local currency or multi-currency calculation process. You may have modified these by including custom scripts and redeploying them to the application. To verify that custom calculation scripts are not affecting performance, disable (comment) out the custom scripts, redeploy them to the application, and then perform the consolidation.

To disable custom calculations:

Sign in to Financial Consolidation and Close as a Service Administrator.
On the Home page, click Application and then Consolidation.
On the Consolidation Process tab, click Local Currency.
Click After Opening Balance Carry Forward or Final Calculations to open Calculation Manager.
In Calculation Manager, display available rules by expanding EPM Cloud, then the node for your application, then Consol, and then Rules.
Comment out the custom script and redeploy the rule:
1. Right-click a rule, for example, FCCS_10_After Opening Balance Carry Forward_Local_Currency, and select Open.
2. Comment out any custom script to revert the rule to its default state, as shown in the following illustration:
3. Redeploy the rule and then run consolidation to check performance.
  If performance improves, revise and optimize the script, paying special attention to roll ups and ad hoc calculations that may affect performance. You should then redeploy the rule to the application.
  
  If performance does not improve, you can assume that this rule is not contributing to performance degradation. You should restore the custom script and then redeploy the rule to the application.
Perform Step 6 for each rule.

Undeploy Consolidation Rules

Sign in to Financial Consolidation and Close as a Service Administrator.
On the Home page, click Application and then Consolidation.
On the Consolidation Process tab, click Consolidated.
Click Configurable Consolidation.
Manage Consolidation Rules opens.
Undeploy the consolidation rule set, one at a time, and then run consolidation to assess performance.

If performance improves, revise and optimize the rules in the rule set. You should then redeploy the rules and rule sets to the application.

If performance does not improve, you can assume that this rule set is not contributing to performance degradation. You should redeploy the rule set to the application.

Add Substitution Variables

Consider adding these substitution variables to evaluate whether they increase the consolidation performance.

Note:

These substitution variables are not created by default; you must manually create them and enable them. For each substitution variable, use the setting Cube = Consol to enable the variable and Value = true to utilize it during Consolidations.

EnableSimpleAggregation

Create this substitution variable and set its value to true to optimize consolidation performance during roll-up.

Note:

EnableSimpleAggregation and EnableOptimalBlockCreation are mutually exclusive. When EnableSimpleAggregation is set to true, the consolidation process treats EnableOptimalBlockCreation value as false, whether or not a value is set in the application.
OptimizeDBRefresh

Create this substitution variable and set its value to true to optimize performance while pushing exchange rates from Rates to the Consolidation cube.
OptimizeYTDCalculation

Create this substitution variable and set its value to true to optimize the performance of YTD calculations during Consolidations.

Note:
OptimizeYTDCalculation shows optimized consolidation performance for extended dimensionality application only.
OptimizeConcurrency

Create and enable this substitution variable and set its value to true to take advantage of higher concurrency during Consolidations.

The Consolidate and Translate business rules in Financial Consolidation and Close execute on multiple entities. Oracle Essbase can execute business rules calculations concurrently using multi-threading. Using the OptimizeConcurrency variable forces Financial Consolidation and Close calculations to make judicious use of multiple Essbase process threads so that calculations run faster.
OptimizePelimCalculation
This substitution variable, when set to True, improves Partner Eliminations (PElim) performance. If there is a consolidation performance degradation when deploying a user-created Partner Eliminations Configurable Consolidation Rule that has an account re-direction, adding this variable can provide significant performance improvement.
EnableYearlyConsol
This substitution variable, when set to True, can improve performance for multi-period consolidations in applications that use the Dense/Sparse Optimization option (where Period and Movement are the Dense dimensions). This substitution variable is applicable if the application meets all of these conditions:
- The application uses the Dense/Sparse Optimization option (Period and Movement are Dense dimensions)
- The application has two or more dirty periods, and two or more hierarchy levels
- The dirty entities are identical across periods
- Equity Pickup sequence is not enabled
ParallelCustomDimDSO
If set to True, this substitution variable improves the performance of the consolidation process in applications with Dense/Sparse Optimization, where the Period and Movement dimensions are Dense dimensions. To see performance improvements, you should set this substitution variable to True.
ParallelCustomDimTranslation
When set to True, this substitution variable can improve the performance of the consolidation process in applications with Dense/Sparse Optimization, where the Period and Movement dimensions are Dense dimensions. To see performance improvements, you should set this substitution variables to True. If any degradation is noticed, set the variable to False or delete it.

Note:

The degree of performance improvement provided by OptimizeConcurrency, OptimizePelimCalculation, EnableYearlyConsol, ParallelCustomDimDSO, and ParallelCustomDimTranslation varies widely across applications as it is driven purely by the application design and data distribution.