EPM Tech Tips · EPCM · 09
An Oracle Enterprise Profitability and Cost Management calculation that ran in twenty minutes at go-live and now runs for hours is a common complaint. The whole model is seldom to blame. Usually it is a few rules with wide ranges, a driver calculated on the fly, rule sets that run serially without need, or a calculation cube that keeps growing. A fixed order of checks finds the slow part with evidence before you change anything.
Before you start
Reproduce the slow run with the same POV, model and calculation options, and keep it as your baseline for run time and results.
Pick your route. If a few rules take most of the time, go through steps 1 to 4. If every rule got slower at the same pace, do step 1 and then steps 5 and 6.
Step 1 · Which rules take the time?
What happens. A long calculation usually hides a short list of expensive rules. Tuning fast rules will not move the total.
How to check.
Go to Home › Modeling › Calculation Analysis and select the slow calculation record.
Click More Actions › Calculation Statistics - Summary. Read the Top 10 longest running rules section, which ranks rules by elapsed time.
For more detail, select up to five records and run More Actions › Calculation Statistics - Diagnostics. On the Rule Statistics worksheet, look at Total Time, Execution Time Percent, Cells Updated and Potential Sources, Destinations and Allocations.
Include an older, faster run. On the Job Statistics worksheet, see whether the extra time sits in the clear, the rules or the aggregation.
Fix. List the three to five rules that consume most of the time and work only on those. Old calculation records are purged automatically, so keep your baseline with Export to Excel on the Calculation Analysis page.
Step 2 · Are the source and destination ranges bigger than they need to be?
What happens. Every member you select is expanded to its level-0 descendants, and the combinations multiply across dimensions. A source with the top member on four dimensions of 1,000 level-0 members each means a trillion (10^12) potential source locations. The rule spends its time on empty combinations.
How to check.
Open each slow rule in Home › Modeling › Designer and review the Source and Destination selections dimension by dimension.
Flag top-of-hierarchy members, rules where source and destination both use a broad member on the same dimension, and long lists of individual members.
Fix. Narrow the selection to the members the business allocates. Where you list many children, select one parent instead, which Essbase usually processes faster. If no such parent exists, build an alternate hierarchy for rule processing or use an attribute filter. Use Same as Source in the destination where the amount should stay on its own member. For a source range that is still extremely large and sparse, Calculation Segmentation (Source Dimension Member Editor icon on the Source/Destination tab, up to two dimensions) splits the rule into smaller runs. Oracle advises contacting Support before you use it.
Step 3 · Is the rule more complex than the logic needs?
What happens. Oracle's Operations Guide ranks allocation patterns by performance: Simple Dimension Extension is fastest, followed by Complex Dimension Extension, Simple Reclassification, Complex Reclassification, and Same as Source Different Dimension, which is the slowest. Rules that use non-level-0 drivers, sources or destinations are more expensive to calculate than rules restricted to level 0.
How to check.
For each slow rule, note which dimensions change between source and destination, and which use Same as Source.
Check whether the driver is a level-0 tuple or uses parent members.
Look for rules carried over from an older design that a simpler pattern could replace.
Fix. Where the result is the same, move the rule to a simpler pattern: keep more dimensions as Same as Source, point the driver at level-0 members, or split one complex rule into two simpler ones.
Step 4 · Is the driver stored, or calculated during the rule?
What happens. During an allocation, the rule looks up the driver value at every possible destination. If the driver is a dynamic member with a formula, that formula runs for every possible destination, which Oracle warns can mean many unnecessary computation steps.
How to check.
Open the driver member used in the rule and find it in the dimension editor.
Check whether its Data Storage is Dynamic Calc or it has a member formula, such as a ratio or a total calculated on retrieval.
Fix. Add a custom calculation rule earlier in the sequence that calculates the driver and writes it to a stored member, then point the allocation at that member. In custom calculation rules, keep the target range small and use the NONEMPTYTUPLE clause, which skips empty tuples on sparse data. It is safe with multiplication and division. For addition or subtraction (A=B+C), split the formula into two rules (A=A+B, then A=A+C), as Oracle's syntax guide shows, or you can get wrong results.
Step 5 · Can the rule set run in parallel?
What happens. A Serial rule set runs its rules one after another, by sequence number. With Parallel Calculation, rules that share the same sequence number run at the same time, up to the number of calculation threads set for the application.
How to check.
In Home › Modeling › Designer, on the Waterfall Setup tab, open the rule set and look at its calculation option: Serial, Parallel or Iterative.
For a parallel candidate, confirm that no rule allocates into another rule's source range.
Check where the rules' destinations overlap. When two rules write to the same locations, Essbase makes one wait for the other.
Fix. Switch independent rule sets to Parallel Calculation, give the parallel-safe rules the same sequence number, and set the Calculation Threads application setting under Application › Settings. Start at 4 threads and increase slowly. Oracle notes that more than five threads rarely helps. Keep reciprocal allocations in Iterative rule sets and dependent rules serial, because their order changes the result.
Step 6 · Is PCM_CLC carrying too much data?
What happens. Clearing previous results and each rule's temporary storage slows down as the PCM_CLC calculation cube grows.
How to check.
Go to Application › Overview › Cubes and review the size of PCM_CLC.
Check how many closed periods and old POVs still sit in PCM_CLC.
Fix. After you validate a period, move its results to PCM_REP or a reporting archive. Then clear old calculated data you no longer need with Home › Modeling › Calculation Control (select the POV, then Actions › Clear) or epmautomate clearDataByPointOfView, and avoid keeping near-identical copies of the same model. A clear cannot be undone, so take a snapshot or export the data first and confirm the copy in PCM_REP ties before you clear anything. If the slowdown persists after you reduce data volumes, test the optional substitution variables (created for All Cubes) EPCM_CALC_CLEAR_TYPE (default Logical; test Physical) and, for Simple Reclassification rules with very large, sparse ranges, EPCM_CALC_NET_ENABLE=Yes (or the Allocation Rules Use Non Empty Tuple application setting). Regression-test against your baseline before production, as Oracle advises.
Quick checklist
Rules that consume most of the time · Calculation Analysis › Calculation Statistics - Summary and Diagnostics
Source and destination ranges · Designer › rule › Source and Destination
Rule pattern and level-0 drivers · Designer › rule definition
Stored driver, no dynamic formula · Dimension editor (Data Storage) and custom calculation rules
Parallel-safe rule sets and thread count · Waterfall Setup › rule set calculation options, Calculation Threads
PCM_CLC size and clear method · Application › Overview › Cubes, Calculation Control › Clear, substitution variables
Pedro San Martín · Founder & Principal, Asher & Company
