OracleBIBlog Search

Monday, January 18, 2010

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]

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

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/

0 comments: