An Oracle Partner Perspective on Improving Essbase Performance
One of the most common performance challenges we see as an Oracle partner supporting EPM Cloud and Essbase environments is long-running Business Rules, particularly calc scripts that become bottlenecks during close, forecasting, or batch processing.
In this post, we walk through how parallel calculation works in Essbase and when it can be an effective technique for improving calculation performance. The goal is not to apply parallelization everywhere, but to understand where it fits, how to test it correctly, and how to interpret the results.
Understanding the Cube Structure
For the purposes of this discussion, we will use a hypothetical BSO outline. In this example:
- Account and Period are dense dimensions with dynamic upper-level members
- All remaining dimensions are sparse
- The outline is ordered as follows:
- Account* (500 total)
- Period* (17)
- Scenario (3)
- Version (5)
- Year (10)
- Currency (8)
- Entity (400)
- Company (700)
- Cost Center (1000)
* Dense dimensions
This ordering reflects a common recommendation you will see in Essbase documentation and performance guidance: dense dimensions first, ordered from most to least dense, followed by sparse dimensions ordered from least to most sparse. As an Oracle partner, Elire’s EPM team regularly reviews dimension order as part of broader performance and optimization assessments, since it directly impacts how Essbase evaluates and executes calculations.
This dimension order becomes especially relevant when discussing parallel calculation, as Essbase uses outline order when determining task dimensions.
When Parallel Calculation Is Worth the Effort
Optimization takes time. Testing, documentation, and iteration are all required, and performance gains are not guaranteed. In some cases, improvements may be marginal or nonexistent.
If a calculation runs infrequently and completes in a reasonable timeframe, optimizing it may not deliver meaningful value. However, when a calculation runs multiple times per day or is part of a critical close or forecasting process, even incremental improvements can be worthwhile.
As an Oracle partner, we typically recommend focusing parallel calculation efforts on long-running batch rules where performance directly impacts business timelines.
Parallel Calculation Basics
Essbase calculations run in serial mode by default, using a single processor thread. Parallel calculation allows Essbase to divide work across multiple processor threads, potentially reducing total execution time.
This is achieved by splitting the calculation into tasks based on members from one or more sparse dimensions. For example, if a calculation runs across 50 state-level members under a single entity parent, those members can be divided across multiple threads and processed simultaneously.
There is overhead involved in task evaluation and scheduling, so performance improvements are rarely linear. Even so, parallel calculation often results in noticeable runtime reductions when applied appropriately.
Essbase supports two methods for parallel calculation:
- SET CALCPARALLEL
- FIXPARALLEL
We’ll start with SET CALCPARALLEL.
Using SET CALCPARALLEL
SET CALCPARALLEL enables parallel calculation for a calc script by specifying the number of processor threads to use:
SET CALCPARALLEL n;
Where n is a value between 1 and 128. In practice, values above 8 rarely deliver additional benefit and can negatively impact server resources, particularly in shared environments.
To disable parallel calculation and return to serial mode:
SET CALCPARALLEL 0;
If the entire script should run in parallel, place the command at the top of the script. Parallel mode automatically ends when the script finishes.
As an Oracle partner, we also recommend being mindful of concurrency. Multiple users running parallel calcs simultaneously can consume available processor threads quickly.
Reviewing Calculation Logs
In EPM Cloud, calculation execution logs are available through Calculation Manager. After running a script, review the Log Messages tab and export the log for reference.
Key log entries include:
- Calculating in parallel with [x] threads
- Calculation task schedule [x,y,z,…]
- Parallelizing using [x] task dimensions
- Empty tasks [x,y,z,…]
These messages explain how Essbase divided the work and whether parallelization was effective. High percentages of empty tasks often indicate sparsity issues that limit performance gains.
Understanding Feasibility and Limitations
When Essbase enters parallel mode, it performs a feasibility analysis. Parallel calculation may not be possible in scenarios involving:
- Certain member formulas
- Use of functions such as @VAR, @ARRAY, @XREF, or @XWRITE
- Circular references
- Sparse member formulas referencing other sparse dimensions
In these cases, FIXPARALLEL may be a better option.
Controlling Task Dimensions with CALCTASKDIMS
Essbase automatically selects task dimensions starting from the bottom of the outline. While you cannot control which dimensions are selected, you can influence how many are used:
SET CALCTASKDIMS x;
Choosing the right number of task dimensions requires careful log review and testing. Too few or too many can result in errors or inefficient task distribution.
Using FIXPARALLEL for Greater Control
FIXPARALLEL allows you to explicitly define both the number of threads and the member set used for task distribution.
Example:
FIX(Budget, Working, FY23, USD)
FIXPARALLEL(8, @CHILDREN(“USA”))
[calc commands]
ENDFIXPARALLEL
ENDFIX
This approach is especially useful when you want to parallelize calculations across a specific subset of members, such as states under a country.
The calculation log clearly reflects this behavior, showing total tasks, task dimensions, and thread usage.
We often recommend FIXPARALLEL when SET CALCPARALLEL cannot meet performance needs or when more precise control is required.
Additional Considerations
Parallel calculation is not always appropriate. For example:
- Run-on-save rules typically do not benefit
- Hybrid BSO cubes may drop into serial mode in certain scenarios
- Aggregations in Hybrid BSO cubes may see limited gains
Each environment is different, and experimentation is often required.
Parallel Calculation Overall
Parallel calculation can significantly reduce calculation runtimes when applied to the right scenarios. It is best suited for large, long-running batch calculations where performance improvements directly impact business operations.
Elire regularly helps organizations evaluate when and how to use techniques like SET CALCPARALLEL and FIXPARALLEL as part of broader EPM optimization strategies. The key is disciplined testing, careful log review, and knowing when parallelization adds value.
Always keep backups of original scripts, document changes, and save execution logs to support informed optimization decisions.
To learn more about Elire’s EPM capabilities, contact [email protected] or view our Oracle Fusion Cloud EPM Services page. Follow Elire on LinkedIn to receive industry updates directly to your phone.