Rule design, from the sources that count
Most slow Planning applications are slow in their rules, and the fixes are documented, scattered across the Operations Guide and a decade of practitioner writing. This page is that material in one place, each practice with its reason.
Note: In Planning, Oracle's module where budgets and forecasts are built, business rules are the scripts that calculate the plan. Most slow applications are slow because of how these are written. This page is the verified set of design practices, from Oracle's own operations guide.
The whole module is mapped on the Planning index.
◆ The command set, verbatim-based, five practices from the Operations Guide, each with its reason.
| Practice | Verbatim basis, from Oracle's Operations Guide |
|---|---|
| SET UPDATECALC OFF | Best practice for rules using cross-dimensional operators where multiple users write to the database. If you leave intelligent calculation on, verify it produces expected results. |
| SET AGGMISSG | OFF where versions are standard target or non-leaf regions cannot be excluded; ON is beneficial where standard bottom-up versions load data at level 0. This one command ties directly to the version design you chose. |
| SET NOTICE / SET MSG | Development environments only, for individual calculation analysis. Left on in production they cost time and fill logs. |
| Block mode over cell mode | Cell mode calculates each cell in dense-outline order and is generally slower. Control it with @CALCMODE, and read the debug log: a message appears when a calculation runs in cell mode, silence means block mode, per the practitioner write-up of Oracle's guidance. |
| Bottom-up over top-down | @CALCMODE(BOTTOMUP) or SET FRMLBOTTOMUP calculates existing blocks only. Top-down calculates every potential block, which is why stray members in sparse dimensions cost real time. |
◆ The IF example, and the method, branch ordering, calculation mode, and the unit test.
The guide's own worked example is worth restating because it teaches how Essbase thinks. A year-to-date formula can put January in the IF branch or the ELSE branch:
-- eleven months satisfy the first branch, one month the second
"SalesYTD" (
IF (NOT (@ISMBR("Jan")))
"SalesYTD" = "Sales" + @PRIOR("SalesYTD");
ELSE
"SalesYTD" = "Sales";
ENDIF )Put the common case in the IF and the exception in the ELSE, so eleven twelfths of the data takes the first test and stops. The guide also notes the member calculates in cell mode here, so January computes first regardless of branch order, because it comes first in the outline. Two lessons in one fragment: order branches by frequency, and know which mode you are in before reasoning about order at all.
The debug method that goes with all of this comes from the same practitioner canon. Build a unit test, a small data slice where you know the inputs and can follow the calculation by hand. Then use Calc Manager debug mode from the top of the script down. Rules written without a unit test get debugged in production, by users.
The problem: Rules have grown slow over years of edits, and nobody can name the offender.
What we build: We profile the rule set against the practice list on this page, fix the worst scripts, and leave the unit-test method behind.
What you get: Batch times measured before and after, and a written method so the next edit does not undo it.
- intelligent calculation
- Essbase recalculating only dirty blocks. Off for multi-user rules.
- block mode
- Faster. The debug log stays silent when you have it.
- bottom-up
- Existing blocks only. Top-down touches every potential block.
- unit test
- A hand-checkable data slice. The difference between debugging and guessing.