1. Calculation and Query Reference for Oracle Essbase
  2. Calculation Functions
  3. Calculation Function List
  4. @CALCMODE

@CALCMODE

The @CALCMODE calculation function for Essbase enables you to select the execution mode of a formula: block mode or cell mode.

You can use this function to manually control whether block or cell mode is used for formula execution. Block mode is generally faster, but cannot be used to fully calculate when there are dependencies between cells in a block. In cell mode, each cell is calculated sequentially in the order of the dense dimensions in the outline. For example, SalesYTD = CurMth + PriorMth should be calculated in cell mode so that each month is calculated in the order of the outline.

In hybrid cubes, the calculation of cells are computed in an order dictated by the solve order of the block members.

@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 (for example, @ISMBR )

  • Whether a formula assigned to a sparse member is calculated in bottom-up or top-down mode

Understanding Block Calculation and Cell Calculation Modes

Using block calculation mode, Essbase groups the cells within a block and simultaneously calculates the cells in each group. Block calculation mode is fast, but you must carefully consider data dependencies within the block to ensure that the resulting data is accurate.

Using cell calculation mode, Essbase calculates each cell sequentially, following the calculation order, which is based on the order of the dense dimensions in the outline.

Understanding Bottom-Up and Top-Down Calculation Modes

Essbase uses one of two methods to do a full calculation of an outline: bottom-up calculation (the default) or top-down calculation. If the outline contains a complex member formula, Essbase performs a top-down calculation for that member. When a formula is compiled, if the formula is to be calculated top-down, Essbase logs a message in the application log file.

For a bottom-up calculation, Essbase determines which existing data blocks need to be calculated before it calculates the database. Essbase then calculates only the blocks that need to be calculated during the full database calculation. The calculation begins with the lowest existing block number and works up through each subsequent block until the last existing block is reached.

In contrast, a top-down calculation calculates the formula on all potential datablocks with the member. A top-down calculation may be less efficient than a bottom-up calculation because more blocks may be calculated than is necessary. Although a top-down calculation is less efficient than a bottom-up calculation, in some cases top-down calculations are necessary to ensure that calculation results are correct. See Example 4.

Syntax

@CALCMODE (CELL|BLOCK|TOPDOWN|BOTTOMUP)

Parameters

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

Notes

Cell and block modes are mutually exclusive. Top-down and bottom-up modes are mutually exclusive. Within one @CALCMODE specification, you can specify only one option. To specify both types of modes, perform the instruction twice; for example:

@CALCMODE (CELL)
@CALCMODE (TOPDOWN)

Block calculation mode (enabled when Essbase configuration setting CALCMODE is set to BLOCK) is not applicable for federated partition cubes. Calculation processing is pushed to Autonomous Data Warehouse. If an exception exists and the calculation is processed on the Essbase Server instead, then solve order determines the dependency analysis.

Knowing When Essbase uses Cell or Block Mode and Top-down or Bottom-up Mode

  • When Essbase compiles a formula, it prints a message in the application log file explaining the mode of execution for the formula similar to the following message: 

    Formula on member Profit % will be executed in CELL and TOPDOWN mode.

    When Essbase determines that the formula will be executed in block and bottom-up mode, no message is written in the application log file.

  • In calculation scripts, @CALCMODE statements must be placed within parentheses and associated with a specific database member.

  • By default, for a simple formula such as A = B + C, Essbase does a bottom-up calculation. A is calculated only if B or C exists in the database. The dependency of the formula on B and C is known before the calculation is started.

    For a complex formula such as A = B->D + C->D, Essbase performs a top-down calculation because every possible combination of A must be examined to see whether B->D or C->D exists.

  • By default, Essbase uses cell calculation mode for formulas containing:

    • @ANCEST

    • @CURRMBR

    • @ISMBR on a dense member

    • @MDANCESTVAL

    • @MDPARENTVAL

    • @MDSHIFT

    • @NEXT

    • @PARENT

    • @PARENTVAL

    • @PRIOR

    • @SANCESTVAL

    • @SPARENTVAL

    • @SHIFT

    • @XWRITE

    For all other formulas, Essbase uses block calculation mode by default.

  • Essbase calculates in cell mode for any calculation script that uses VAR in a FIX...ENDFIX block (during serial calculation), or that uses THREADVAR in a FIXPARALLEL...ENDFIXPARALLEL block (during parallel calculation).

Understanding Data Dependency Issues With Block Calculation Mode

Data dependency occurs if the accurate calculation of one or more members depends on another member or other on members being calculated previously. Most data dependency issues with block calculation mode occur when a formula contains IF ELSE or IF ELSEIF conditions. However, data dependencies can occur in other formulas; for example, when using the @PRIOR function.

Data Dependency Issues With IF ELSE and IF ELSEIF

When Essbase uses block calculation mode to calculate a formula that contains IF ELSE or IF ELSEIF conditions, it separates the members being calculated into two groups. The first group contains the members that satisfy the IF condition. The second group contains the members that satisfy the ELSE or ELSEIF conditions.

Essbase simultaneously calculates the members in the first group before simultaneously calculating the members in the second group. See Example 1.

If a formula contains data dependencies, ensure that the following conditions are met:

  • Members on which the accurate calculation of other members depends are in the first group.

  • Dependent members are in the second group.

If an IF condition has multiple ELSEIF conditions, Essbase evaluates each ELSEIF condition, placing the members that satisfy the ELSEIF condition in the first group and the members that satisfy subsequent ELSEIF or ELSE conditions in the second group. See Example 2.

Understanding Other Data Dependency Issues

Data dependencies can occur in formulas that do not contain IF ELSE conditions. See Example 3 for an example of data dependency in a formula containing @PRIOR.

Example

Example 1, Example 2, and Example 3 illustrate use of the BLOCK and CELL options of @CALCMODE. Example 4 illustrates use of the BOTTOMUP and TOPDOWN options.

Example 1

Consider a database with two dense dimensions, Time and Accounts. The following formula is placed on the Budget Sales member of the Accounts dimension. Because this is a formula containing @ISMBR applied to a dense member (Budget Sales), by default Essbase uses cell calculation mode. Use @CALCMODE(BLOCK) to specify block calculation mode for this formula. 

@CALCMODE(BLOCK);
IF(@ISMBR(Feb))
   "Budget Sales"=100;
ELSE
   "Budget Sales"=Feb+10;

According to the above formula, we expect that if the member being calculated is Feb, the Budget Sales value is 100. If the member being calculated is not Feb, the Budget Sales value is 100+10 (the value for Feb + 10).

Assume that we load the values 10, 20, and 30 into the Budget Sales data block for Jan, Feb and Mar, as follows:

Table 2-17 Values loaded in the Budget Sales Data Block

(axis) Jan Feb Mar
Budget Sales 10 20 30

Using block calculation mode, Essbase calculates the members satisfying the IF condition first. In this example, Feb is the only member that satisfies the IF condition. After calculating Feb, Essbase calculates the members Jan and Mar. In this example, the results are as expected:

Table 2-18 Results of Block Calculation Mode

(axis) Jan Feb Mar
Budget Sales 110 100 110

Example 2

Now consider the same database as in Example 1, but we place the following formula on the Budget Sales member of the Accounts dimension. As in Example 1, because this is a formula containing @ISMBR applied to a dense dimension member (Budget Sales), by default Essbase uses cell calculation mode. However, we use @CALCMODE(BLOCK) to specify the block calculation mode for this formula. 

@CALCMODE(BLOCK);
IF(@ISMBR(Mar))
   "Budget"->"Sales"=Feb+20;
ELSEIF(@ISMBR(Jan))
   "Budget"->"Sales"=Feb+10;
ELSE
   "Budget"->"Sales"=100;
ENDIF

According to this formula, we want the Jan and Mar Budget Sales values to be calculated based on the Feb Budget Sales value, which is 100. We want to see the following results:

Table 2-19 Desired Calculation Results

(axis) Jan Feb Mar
Budget Sales 110 100 120

Assume that we load the values 10, 20, and 30 into the Budget Sales data block for Jan, Feb, and Mar, as follows:

Table 2-20 Values Loaded in Budget Sales Data Block

(axis) Jan Feb Mar
Budget Sales 10 20 30

Using block calculation mode, Essbase calculates the members satisfying the IF condition first, followed by the members satisfying the ELSEIF condition, followed by the members satisfying the ELSE condition. In this example, Essbase calculates the members in the following order: Mar, Jan, Feb. The results are not what we want, because the calculation of Jan and Mar is dependent on the calculation of Feb, and Feb is calculated after Jan and Mar. The inaccurate results are as follows:

Table 2-21 Inaccurate Calculation of Budget Sales Data Block

(axis) Jan Feb Mar
Budget Sales 30 100 40

To achieve the desired results, use @CALCMODE(CELL).

Example 3

The following formula calculates the members Opening Inventory and Ending Inventory using the @PRIOR function. There is a data dependency between Opening Inventory and Ending Inventory. The formula is placed on the Opening Inventory member. The example shows the results for January, February, and March. 

@CALCMODE(BLOCK)
"Opening Inventory"=@PRIOR("Ending Inventory")+10;
"Ending Inventory"="Opening Inventory";

Before the calculation, there is no data for these members (the data is #MISSING or #MI):

Table 2-22 Missing Data Before Inventory Calculation

(axis) Jan Feb Mar
Opening Inventory #MI #MI #MI
Ending Inventory #MI #MI #MI

Using block calculation mode, Essbase calculates the members simultaneously, taking the previous month's Ending Inventory #MISSING value as 0 for all member combinations and adding 10. This is not the desired result.

Table 2-23 Inaccurate Results for Inventory Calculation

(axis) Jan Feb Mar
Opening Inventory 10 10 10
Ending Inventory 10 10 10

The following formula on the Opening Inventory member causes Essbase to use cell calculation mode (the default for formulas containing @PRIOR ): 

"Opening Inventory"=@PRIOR("Ending Inventory")+10;

"Ending Inventory"="Opening Inventory";

The results are as follows:

Table 2-24 Cell Calculation Mode Inventory Results

(axis) Jan Feb Mar
Opening Inventory 10 20 30
Ending Inventory 10 20 30

Example 4

Depending on the formula and the structure of the data, calculating a formula top-down versus bottom-up may involve two issues: performance (reflecting the number of calculations that must be made) and accuracy. This example compares calculation results to illustrate both of these issues.

Before the calculation, assume that Actual and Budget are members of a sparse dimension and they contain the following data:

Table 2-25 Data for Actual and Budget Members

(axis) Cola New York Sales
(axis) Actual Budget
Jan #MISSING 50
Feb 200 #MISSING
Mar 400 450

The following formula is calculated bottom-up.

Budget(
   @CALCMODE(BOTTOMUP);
   Budget=Actual*1.10;
)

In a bottom-up calculation, Essbase executes formulas only from existing data blocks. Therefore, only two values—Jan and Mar—are calculated, based on existing combinations of Budget.

Table 2-26 Bottom-up Calculation Results for Actual and Budget

(axis) Cola New York Sales (Comment)
(axis) Actual Budget -
Jan #MISSING #MISSING (#MISSING*1.10)
Feb 200 #MISSING (No calculation is performed)
Mar 400 440 (400*1.10)

The following formula is calculated top-down.

Budget(
   @CALCMODE(TOPDOWN);
   Budget=Actual*1.10;
)

In a top-down calculation, Essbase materializes every potential data block that is relevant to the calculation, and executes formulas in those blocks. Therefore, all three values—Jan, Feb, and Mar—are calculated, based on all potential combinations of Budget. The results are:

Table 2-27 Top-down Calculation Results for Actual and Budget

(axis) Cola New York Sales (Comment)
(axis) Actual Budget -
Jan #MISSING #MISSING (#MISSING*1.10)
Feb 200 220 (200*1.10)
Mar 400 440 (400*1.10)