document is intended to provide best practices for Business Rule Optimisation
for Planning and PBCS models.
cover items which can be edited within the script only i.e. syntax. Essbase.cfg
settings for on premises Planning will not be discussed in this blog.
2. Environment Setting
recommended Calculation Commands, which are the elements that instruct the
business rule how to execute the calculations.
UPDATECALC OFF turns off intelligent calculation, which is best
practice for business rules which use cross dimensional operators and
where there may be multiple users accessing the same data block
combinations. Using SET UPDATECALC OFF is considered best practice
for Planning/PBCS applications where multiple users write to the database.
If intelligent calculation is used, ensure it is producing expected
AGGMISSG OFF should be set for Planning/PBCS designs where
versions are Standard Target or where non leaf data regions are used and
cannot be excluded during your calculation. If the Planning/PBCS design
has versions setup as Standard Bottom Up, then data is loaded at level0,
where SET AGGMISSG ON will benefit.
NOTICE and SET MSG SUMMARY should only be used in development
environment for individual calculation analysis. These calc commands
should be removed once in production and/or after analysis is completed.
EMPTYMEMBERSETS ON should be used when Run Time Prompts (RTP) are
included in FIX statements for Planning/PBCS models so that empty sets are
not calculated. Prevents a calculation from running on all members when
the set is empty.
templates in Calc Manager business rules to avoid repeating sections of
code and make best use of RTP.
dependencies on dynamic calc members within each calculation. If possible,
change the calculation to avoid including repeated use of dynamic calc or
remove the dynamic calc.
- Use FIX
and IF to focus your calculation to ensure only data required is
the creation of a 0 (zero) data result (unless you want to see a zero e.g
inventory levels). This will create a block which will be included in all
FIX/IF statements and will be calculated. Remove zeros on data load, if
possible, or prevent their creation in business rules.
rules have a check to see if, for example: IF ((“Budget”==#missing) OR
(“Budget” ==0)). IF (“Budget”+1==1) or IF (Budget/Budget ==#missing) will
give the same check but avoids the use of Boolean logic within the IF
possible, perform dense calculations, which do not create blocks, before
sparse calculations, which do create blocks. If you need to perform
aggregations (e.g to get a total for an allocation calculation), ensure
that you only aggregate the section of the data that is required for that
the passes on a database where possible.
recursive formulas. Excessive recursion can create environment performance
issues as well as adding a requirement to clear values to produce
4. FIX Statements
statements are used to focus the business rule i.e. to keep the # blocks
being calculated to be as low as possible i.e. only calculate blocks that
- FIX is
recommended for sparse dimensions because, when used on sparse, it reduces
the # blocks that are required for the calc.
calculations are done on level 0 of all dimensions when versions are
bottomup in Planning/PBCS models.
outer FIX statements on sparse dimensions with inner IF statements on
dense dimensions where possible.
- All FIX
statements should include members from ALL dimensions, except dimensions
that are within the calculation. If a dimension is excluded, all members
from that dimension will be included and it is likely that this is not
FIX statements where possible to reduce the number of passes on the
database. Each full FIX requires a pass on the database. For example, use
an outer FIX for Version, Scenario, and/or any other dimension selections
that are static throughout the business rule.
Planning/PBCS business rules associated with web forms, leverage the
selected page and POV members in FIX statements to reduce the number of
5. IF Statements
- IF can
be used in member formula. FIX cannot.
should be used within FIX statements to reduce the #blocks that need to be
accessed. IF brings all blocks within the FIX into memory.
outer FIX statements on sparse dimensions with inner IF statements on
dense dimensions where possible.
ELSE instead of a combination of NOT and ELSEIF where possible to avoid
unnecessary analysis of member values during the calculation. However, if
an ELSE is not required, it is not necessary.
IF statements, if possible, where the most number of cases hit the first
IF in the block. Use NOT within the IF to ensure this, if applicable. See
for more information on using NOT in IF statements.
the Calc Member Block choice. A sparse member without dynamic calc
dependencies would be a better choice.
6. Only Calculate Blocks Required
Planning/PBCS models, use RTP to ensure that only the data required is
included in the business rule.
aggregate/calculate data that is required at each stage of the calculation
to ensure you keep the number of blocks included in the calculation as low
as possible for as long as possible.
7. Level of Calculations
Planning/PBCS models, ensure calculations are done on level 0 of all
dimensions when versions are bottomup.
Planning/PBCS models, aggregations should only be included in the BSO Plan
Type if required for the approval process. Other aggregations should be
moved to the ASO Plan Type.
- Try and
keep the number of blocks included in your calculations to be as low as
possible, for as long as possible.
use @LEVMBRSrather than @RELATIVEif used on the entire dimension.
@CHILDRENinstead of @RELATIVE, if applicable.
@REMOVE and @LEVMBRS if you only want to exclude some members from a FIX.
9. Block vs Cell Mode
block mode, where cells are grouped within the block and simultaneously
calculated, is generally faster but data dependencies must be carefully
considered e.g. SalesYTD = CurMth + PriorMth would have to be calculated
in cell mode so that each month is calculated in the order of the outline.
cell mode, each cell is calculated sequentially in the order of the dense
dimensions in the outline, is generally slower than block mode.
@CALCMODE to manually control block vs. cell mode.
debug mode application logs to verify calc mode. If a calculation is
performed in block mode, no message will appear. A log message will be
shown where calculations are performed in cell mode.
10. BottomUp vs TopDown
calculation function @CALCMODE(BOTTOMUP); or calculation command SET
FRMLBOTTOMUP to calculate existing blocks only (BOTTOMUP) instead of
potential blocks (TOPDOWN).
calculations will calculate all potential data blocks with the member. For
this reason, it is important that any irrelevant members within sparse
dimensions are removed.
test calculations using BOTTOMUP to ensure that blocks are created
correctly when using @CALCMODE.
testing is completed with clearing data and re-running the calculation to
ensure all blocks are created correctly, especially when using BOTTOMUP.
debug mode application logs to verify calcmode. If a calculation is
performed BOTTOMUP, no message will appear. A log message will be shown
where calculations are performed TOPDOWN.
11. Create Blocks
generally, will be created on the following actions:
calculations e.g. AGG or SparseMember = X * X/X;
- A sparse calculation is triggered:
sparse members are on the left of the =
the formula is within a sparse calc member block e.g. “Budget”(“Sales” =
“Sales”->”Actual” * 0.95;) where Scenario is sparse and Measures are
blocks can be carried out using the calculation commands SET
CREATEBLOCKONEQ, SET CREATENONMISSINGBLK or the calculation function @CREATEBLOCK.
It is recommended that if these settings are required that they are used
very sparingly and within a tight FIX statement. Test to see if it is
possible to avoid the use of these statements by changing the type of
calculation being performed.
creation is a design related topic. Where there is an issue, it is
important to prove that this is a block creation issue before using these
calculation commands or calculation function. Submit a 0 into the target
block and re-run the calc to prove a block creation issue.
- A sparse
dimension aggregation should be ordered starting with the dimension that
creates the fewest blocks to the one that creates the most blocks in order
to keep the number of blocks as low as possible for as long as possible.
Planning/PBCS models, end user business rules should not aggregate entire
Planning/PBCS models, any aggregations required for reporting only should
be moved to the ASO Plan Type.
Planning/PBCS models, only aggregate data that is required for the
Planning approval process.
- AGG vs
CALC DIM Calculation Commands
DIM will execute any member formula
DIM will aggregate dense or sparse dimensions.
performs aggregations based on outline structure.
does NOT execute member formula.
will only aggregate sparse dimensions.
both AGG and CALC DIM as performance can differ depending on levels of
aggregation involved in the calculation.
dimensions with dynamic calc on upper levels from all aggregations.
aggregate data that is required.
13. SET CALCPARALLEL / FIXPARALLEL
Planning/PBCS models i.e. multi-user applications with potential for rules
running concurrently, it is best practice for end user business rules to
be run in serial mode.
use SET CALCPARALLEL around full sparse dimension aggregations in batch
calculation is not recommended on small scripts (for example, less than 10
or 20 seconds) as the overhead of creating parallelism may outweigh the
used, always test SET CALCPARALLEL to ensure that it does give a benefit.
Sometimes serial calculation or calculations with lower levels of
parallelism can give better results.
- Test to
determine if FIXPARALLEL would provide better results than SET CALCPARALLEL?
Use Calc Manager debug mode to view logs to review.
consider user concurrency when using SET CALCPARALLEL or FIXPARALLEL.
more information on FIXPARALLEL, please refer to the documentation:
14. Debug Methodology for Developing
create a unit test i.e. a small subset of data where you know the source
data and the expected results and can easily follow the calculation
through manually for this set of data.
use Calc Manager debug mode, or application logs, to view calculation
entries to help with any debug during development.
all data that is required is present (e.g. if a total is required for
allocations) and has been pre-calculated prior to the calculation taking
- If the
script is long, start debug at the top of the script and work down. An
issue further up a script may be creating an issue further down. Debug
section by section to ensure that all data is created correctly. Check
that later sections of script do not overwrite earlier sections etc.
debug mode (or application log) to ensure that each section of script is
calculated sequentially where required.
clear data and reload (i.e. do not use a clear script) when testing any
business rule in order to ensure that all blocks are created successfully.
test re-running a script to ensure that the syntax creates correct results
for input data and/or populated data.
test all data results with more than one data set.
user input may change data sets e.g. allocations, also test data changing
from #missing to a value and from a value to #missing, to ensure that
previous calculated results are removed (if required) in second and
subsequent runse.g. if a user inputs against Product A,B, C in their first
calculation run and then Product B, C, D (and not A) in their second, is
the allocation result for Product A correctly removed in the second run.