How to Improve Calculation Performance in Oracle Fusion Cloud EPM
Calculation performance is a common challenge in Oracle EPM environments, especially when Business Rules and calc scripts handle large data volumes or complex logic. When calculations run longer than expected, they can slow planning cycles and reduce flexibility for finance teams.
In this blog post, we will explore these scripts and the use of multiple threads – the power of parallel calculation – to make them run more efficiently.
Understanding the Example Environment
For the purposes of this discussion, we will use a hypothetical block storage outline as an example. Let’s assume that Account and Period are dense dimensions with dynamic upper-level members and that the remaining dimensions are sparse. We will also assume that dimensions are in the following order in the outline (with number of total members):
- Account* (500 total)
- Period* (17)
- Scenario (3)
- Version (5)
- Year (10)
- Currency (8)
- Entity (400)
- Company (700)
- Cost Center (1000)
* Dense dimensions
This order—where dense dimensions are ordered largest-to-smallest, followed by sparse dimensions smallest-to-largest—is one of many recommendations users may encounter when researching Essbase dimension order or reviewing Oracle EPM documentation.
The order of dimensions is critical in the structure and performance. Dense dimensions calculate faster than sparse dimensions. When ordering dimensions, place the dense dimensions first, followed by the sparse dimensions. Within the Dense section, dimensions should be ordered from most to least dense. Within the Sparse section, dimensions should be ordered from least to most sparse.
Why does this matter? Dimension order directly impacts calculation performance, especially when parallel calculation is introduced.
When Parallel Calculation Is Worth the Effort
Optimization takes time. Users will need to run multiple tests and track results carefully, so it’s important to focus on calculations where performance gains will actually matter.
The improvements gained can be dramatic, they can be small, or they can gain nothing. If users are optimizing a calc that runs once per day and completes in 15 minutes, the effort may outweigh the benefit. However, if a rule runs for an hour or more and is executed multiple times per day, even modest improvements can deliver meaningful operational value.
Parallel calculation is best suited for large, long-running batch processes where performance constraints affect close cycles, planning windows, or downstream processing.
Parallel Calculation Basics
Optimizing calculations using parallel calculation starts with understanding how Essbase performs calculation processes.
By default, calculations run in serial mode, using a single thread. Using parallel calculation, users can leverage multiple threads to perform the same calculations. This is completed by parallel calculation allowing Essbase to divide work into tasks that can be processed simultaneously across multiple threads, often resulting in significantly faster execution times.
There are two methods in Essbase to invoke parallel calculation: SET CALCPARALLEL and FIXPARALLEL. SET CALCPARALLEL tends to be slightly simpler to use.
Using SET CALCPARALLEL
SET CALCPARALLEL is the simplest way to enable parallel calculation. Users invoke it by adding the following command to the calc script:
SET CALCPARALLEL n;
Where n is a value between 1 and 128 representing the number of threads to use.
In practice, values above 8 rarely provide meaningful benefit and can introduce contention, especially in shared environments. Be mindful that each concurrent user executing a parallel calc will consume multiple threads.
To disable parallel calculation and return to serial mode, use:
SET CALCPARALLEL 0;
Understanding Execution Logs
One of the messages that users see when using CALCPARALLEL is:
Calculating in parallel with [X] threads
This tells users that Essbase is able to calculate in parallel mode and will use the number of threads specified in the SET CALCPARALLEL command. It indicates that Essbase has assessed the feasibility of using parallel calc and determined that there are no conditions that prevent it. This is important, as there are several reasons that users may not be able to use parallel calc, including one or more of the following member formulas:
- A formula on a dense member that relies upon a member in a task dimension
- Certain formulas that use @VAR, @ARRAY, @XREF, or @XWRITE
- Multiple member formulas that create a circular reference
- Sparse member formulas that refer to a member from another sparse dimension
In the above cases, consider using FIXPARALLEL instead.
Users will see the following message when parallel calc is enabled:
Parallelizing using [x] task dimensions
As part of Essbase’s feasibility analysis, it was determined that [x] dimensions will be used to generate the list of tasks for parallelization. These are the last [x] dimensions in the outline. Note that this may include dimensions that aren’t the best choices.
Finally, users will see the following messages at the beginning and end of the block of code being calculated in parallel:
Calculation task schedule [x,y,z,…]
Empty tasks [x,y,z,…]
These messages show the task schedule and the number of empty tasks for each scheduled group. Empty tasks are simply tasks where there was nothing to calculate. In general, if the number of empty tasks is greater than 50% of those that are scheduled, then the sparsity of the task dimension is preventing a parallel calc from improving performance.
Users may also see log messages like “x% of tasks were empty, this may signal there were too many tasks, and performance is being negatively impacted.
Using CALCTASKDIMS
When using CALCPARALLEL, Essbase automatically determines the number of task dimensions. Though users cannot change the order in which Essbase selects the task dimensions, they can specify how many task dimensions Essbase uses in a CALCPARALLEL operation by adding the following statement:
SET CALCTASKDIMS x;
Essbase will attempt to use the specified number of dimensions for tasks. If this value is too small or too large users may receive errors about insufficient or too many tasks.
Careful testing is essential, as too many or too few task dimensions can cause errors or reduce efficiency.
Using CALCPARALLEL and CALCTASKDIMS users are afforded some control over how Essbase parallelizes calculations; however, there are limits. When it comes to selecting task dimensions, users are fixed with the order of dimensions in the outline. It is possible to reorder dimensions so the bottom ones suit parallel calc better, but this may not be ideal for all calcs and could create other issues.
When FIXPARALLEL Is the Better Option
If a user performs an action on the children of USA, a member of the entity dimension, the users calc script may look like:
FIX(Budget, Working, FY26, USD, @CHILDREN(“USA”))
[calc commands]
ENDFIX
In this case, @CHILDREN(“USA”) would be an ideal member set to parallelize on, as the user is performing the same action on all fifty members. But using CALCPARALLEL can only specify the entire Entity dimension, and even then, users would have to use CALCTASKDIMS with a value of 3, which would also include all the members of the Cost Center and Company dimensions (Remember, CALCPARALLEL selects task dimensions from the bottom up).
Here lies the opportunity to use FIXPARALLEL! To do so, modify the calc to look like this:
FIX(Budget, Working, FY23, USD)
FIXPARALLEL(8, @CHILDREN(“USA”))
[calc commands]
ENDFIXPARALLEL
ENDFIX
FIXPARALLEL uses two parameters: the number of threads to use (maximum 8), and the member set to use as tasks to parallelize. In this case, the fifty states will be used as “slices” of the database to divide among the eight threads provided for parallel calculation.
Using the above, users will find two messages in the log for each FIXPARALLEL/ENDFIXPARALLEL pair:
Calculating FIXPARALLEL: Task dimensions = [1]. Total tasks = [50]
(and later in the log)
Calculated in FIXPARALLEL with [8] threads. Task dimension = [Entity] with [50] fixed members. Task size = [1]. Total tasks = [50]
This serves as a prime example of when FIXPARALLEL is a better choice, other preferrable situations include:
- Where CALCPARALLEL gives errors or doesn’t meet performance requirements
- When using the DATACOPY, DATAEXPORT, or CLEARBLOCK commands or the @XREF or @XWRITE functions
- When exporting regions of the database in parallel (note that each region will produce a separate export file)
Other Considerations
When considering whether to use parallel calculation (of either type), consider the following:
- Users’ situations may not be as clear-cut as the examples above, so test rigorously
- Parallel calc isn’t usually a good solution for run-on-save business rules on a data form. Forms usually have sparse dimensions as POV/Page members, so the form may represent a single block and there won’t be anything to gain
- In a Hybrid BSO cube where one or more sparse dimensions is fully dynamic at upper levels, FIXPARALLEL will drop into serial mode if the script references an upper-level member of such a dimension. This is because of the hybrid aggregation necessary to derive that upper-level value
- When used to perform aggregations, parallelization may not provide much benefit in Hybrid BSO cubes in general
Overall, using parallel calculation can decrease calculation times, and is a strategy best used for large, long-running batch calculations and aggregations. Always keep a backup of the original script, take notes on optimization attempts, and save log files.
What’s Next
Looking to optimize Oracle EPM calculation performance in your environment? Elire helps organizations evaluate, tune, and modernize EPM business rules using proven performance strategies.
- Talk with our EPM experts: Reach out to [email protected]
- Explore real-world examples: View our Oracle Fusion Cloud EPM services page
- Stay informed: Follow Elire on LinkedIn or subscribe to our monthly cloud newsletter for practical Oracle EPM insights and updates