Optimization of Planning Applications – Part 2

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

• Forcing a bottom-up calculation on a formula may produce results that are inconsistent with a top-down calculation if:
  
  • The formula contains complex functions (for example, range functions)
    
  • The formula's dependencies are not straightforward
    
• Before using the SET FRMLBOTTOMUP command in a production environment, be sure to check the validity of calculation results produced when the command is enabled (set to ON).
  

CALCOPTFRMLBOTTOMUP

Specifies within the configuration file that Essbase optimizes the calculation of complex formulas on sparse dimensions in large database outlines. If enabled, this setting tells Essbase to perform a bottom-up calculation on formulas that would otherwise require a top-down calculation.

This setting does not apply to aggregate storage databases.

Syntax

CALCOPTFRMLBOTTOMUP TRUE | FALSE

TRUE—Optimizes the calculation of formulas on sparse dimensions in large database outlines by forcing a bottom-up calculation.

FALSE—Does not force a bottom-up calculation for formulas on sparse dimensions in large database outlines. The default is FALSE.

Leveraging Multiple Plan Types

As you create accounts, entities, and other elements of the application, you associate them with plan types, so the database for each plan type contains only application dimensions, members, and data values relevant to the plan type. This optimizes application design, size, and performance. When architecting applications for Planning I prescribe to the method of leveraging all databases within an application that are available for use. This means deploying all three available databases inherent to an application and WorkForce and Capex if these modules are available. Segmenting the accounts dimension to specific databases based upon purpose allows for the number of stored dimension members in the typically dense Accounts dimension to be reduced, thus decreasing the block size and creating more opportunities for optimization and creating dynamic roll-ups within the database/