Enables the choice of an execution mode of a formula. @CALCMODE can control two types of modes:
@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 |
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)
Understanding Block Calculation and Cell Calculation Modes
Using block calculation mode, Analytic Services 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, Analytic Services calculates each cell sequentially, following the calculation order, which is based on the order of the dense dimensions in the outline. For more information on calculation order, see the Database Administrator's Guide.
For more information on which mode Analytic Services uses and on data dependency issues, see Notes.
Understanding Bottom-Up and Top-Down Calculation Modes
Analytic Services uses one of two calculation methods to do a full calculation of a database outline: bottom-up calculation (the default) or top-down calculation. If the database outline contains a complex member formula, Analytic Services performs a top-down calculation for that member. When a formula is compiled, if the formula is to be calculated top-down, Analytic Services logs a message in the application log file.
For a bottom-up calculation, Analytic Services determines which existing data blocks need to be calculated before it calculates the database. Analytic Services 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.
For more information about bottom-up and top-down calculation modes, see the Database Administrator's Guide.
Knowing When Analytic Services uses Cell or Block Mode and Top-down or Bottom-up Mode
Formula on member Profit % will be executed in CELL and TOPDOWN mode.When Analytic Services determines that the formula will be executed in block and bottom-up mode, no message is written in the application log file.
For all other formulas, Analytic Services uses block calculation mode by default.
You can also set CALCMODE BLOCK or CALCMODE BOTTOMUP at the Analytic Server, application, or database level using the configuration setting CALCMODE.
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 Analytic Services 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.
Analytic Services 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, Analytic Services 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 the @PRIOR function.
You can also set CALCMODE BLOCK or CALCMODE BOTTOMUP at the Analytic Server, application, or database level using the configuration setting CALCMODE.
Example 1, Example 2, and Example 3 illustrate use of the BLOCK and CELL options of the @CALCMODE function. Example 4 illustrates use of the BOTTOMUP and TOPDOWN options.
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 Analytic Services uses cell calculation mode. Use the @CALCMODE(BLOCK) function 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:
Jan | Feb | Mar | |
Budget Sales | 10 | 20 | 30 |
Using block calculation mode, Analytic Services calculates the members satisfying the IF condition first. In this example, Feb is the only member that satisfies the IF condition. After calculating Feb, Analytic Services calculates the members Jan and Mar. In this example, the results are as expected:
Jan | Feb | Mar | |
Budget Sales | 110 | 100 | 110 |
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 Analytic Services uses cell calculation mode. However, we use the @CALCMODE(BLOCK) function 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:
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:
Jan | Feb | Mar | |
Budget Sales | 10 | 20 | 30 |
Using block calculation mode, Analytic Services 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, Analytic Services 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:
Jan | Feb | Mar | |
Budget Sales | 30 | 100 | 40 |
To achieve the desired results, use the @CALCMODE(CELL) function.
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):
Jan | Feb | Mar | |
Opening Inventory | #MI | #MI | #MI |
Ending Inventory | #MI | #MI | #MI |
Using block calculation mode, Analytic Services 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.
Jan | Feb | Mar | |
Opening Inventory | 10 | 10 | 10 |
Ending Inventory | 10 | 10 | 10 |
The following formula on the Opening Inventory member causes Analytic Services to use cell calculation mode (the default for formulas containing the @PRIOR function):
"Opening Inventory"=@PRIOR("Ending Inventory")+10; "Ending Inventory"="Opening Inventory";
The results are as follows:
Jan | Feb | Mar | |
Opening Inventory | 10 | 20 | 30 |
Ending Inventory | 10 | 20 | 30 |
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:
Cola | New York | Sales | ||
Actual | Budget | |||
Jan | #MISSING | 50 | ||
Feb | 200 | #MISSING | ||
Mar | 400 | 450 |
The following formula is calculated bottom-up.
@CALCMODE(BOTTOMUP); Budget=Actual*1.10;
This bottom-up calculation calculates two values, based on existing combinations of Budget, with the following results:
Cola | New York | Sales | (Comment) | |||
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.
@CALCMODE(TOPDOWN); Budget=Actual*1.10;
This top-down calculation calculates three values, considering all potential combinations of Budget, with the following results:
Cola | New York | Sales | (Comment) | |||
Actual | Budget | |||||
Jan | #MISSING | #MISSING | (#MISSING*1.10) | |||
Feb | 200 | 220 | (200*1.10) | |||
Mar | 400 | 440 | (400*1.10 |
@WITHATTR
CALCMODE configuration setting
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |