Test Blog: Optimization of Planning Applications

In this edition of optimization of Planning applications, we'll address leveraging multiple plan types and calculation running in top-down mode.

Let's first address calculations running in top-down mode. When looking to optimize calculations I like to run simulations calculations to determine bottlenecks in the processing of data. As discussed previously, administrators can simulate calculations using SET MSG ONLY within a calculation script. A simulated calculation produces results that help you analyze the performance of a real calculation that is based on the same data and outline. By running a simulated calculation with a command like SET NOTICE HIGH, you can mark the relative amount of time each sparse dimension takes to complete. Then, by performing a real calculation on one or more dimensions, you can estimate how long the full calculation will take, because the time a simulated calculation takes to run is proportional to the time that the actual calculation takes to run.

Essbase uses one of two calculation methods to perform a calculation of a database outline, a bottom-ups calculation or a top-down calculation. By default, Essbase does a bottom-up calculation.

For a bottom-up calculation, Essbase determines which data blocks must be calculated before it calculates the database. Essbase then calculates only the blocks that must be calculated. The calculation begins with the existing block with the lowest block number and works up through each block in number order until the existing block with the highest block number is reached.

Forcing a bottom-up calculation on a formula usually increases performance time. If the formula contains complex functions (for example, range functions) or if the formula's dependencies are not straightforward, a bottom-up calculation may produce results different from those of a top-down calculation.

Multiple approaches can be leveraged to force a bottoms-up calculation; these include @CALCMODE, CALCMODE, SET FRMLBOTTOMUP, and CALCOPTFRMLBOTTOMUP. The information below contains how these approaches differ and the syntax leverage.

@CALCMODE

Enables within a calculation script, the choice of an execution mode of a formula. @CALCMODE can control two types of modes:

Whether a formula is calculated in block calculation or cell calculation mode when calculating formulas that contain certain functions (in particular the @ISMBR function)
Whether a formula assigned to a sparse member is calculated in bottom-up or top-down mode

Syntax

@CALCMODE (CELL|BLOCK|TOPDOWN|BOTTOMUP)

CELL	Turns on the cell calculation mode
BLOCK	Turns on the block calculation mode
TOPDOWN	Turns on the top-down calculation mode
BOTTOMUP	Turns on the bottom-up calculation mode

CALCMODE

CALCMODE allows you to set the calculation mode at the server, application, or database level within the configuration file instead of indicating it in a calculation script using @CALCMODE. NOTE: This setting does not apply to aggregate storage databases.

Syntax

CALCMODE [application_name [database_name]] [BLOCK| BOTTOMUP]

application_name—Optional. If you specify an application, all the databases in that application are affected by the CALCMODE setting. If you leave out the application and database name parameters, the CALCMODE setting applies to the entire server.
database_name—Optional. If you specify an application and database, the database you specify is affected by the CALCMODE setting. If you do not specify an application with the database, the CALCMODE setting will fail.
BLOCK—Turns on block calculation mode.
BOTTOMUP—Turns on bottom-up calculation mode.

SET FRMLBOTTOMUP

Leveraged within calculation scripts, SET FRMBOTTOMUP allows Essbase to perform a bottom-up calculation on formulas that would otherwise require a top-down calculation. This command optimizes the calculation of complex formulas on sparse dimensions in large database outlines.

Syntax

SET FRMLBOTTOMUP ON|OFF;

Parameter	Description
ON	Turns on the bottom-up sparse formula calculation method.
OFF	Turns off the bottom-up sparse formula calculation method. The default setting is OFF. You can change this setting by using CALCOPTFRMLBOTTOMUP TRUE in the essbase.cfg file.

Notes