The Calculation Options dialog box can be opened:
From the formula editor dialog box for multidimensional calculation.
From the object navigator dialog box on executing multidimensional calculation:
Select the Run context menu item.
Select the Object > Run main menu item.
Double-click.
This dialog box is used to determine parameters for calculation on DB server.
On selecting the Calculation Period checkbox the user needs to indicate start and end of calculation period. The last entered period is offered by default. The Each Month Separately checkbox can also be selected, which enables the user to calculate for each month to reduce calculation time. For example, selecting the period 01.01.2007-01.06.2007 does not generate one procedure for the entire period, but a number of procedures with the periods 01.01.2007-31.01.2007, 01.02.2007-28.02.2007, and so on.
Use the Calculate box to determine a set of elements to calculate:
All Elements. A calculation is executed by all cube coordinates.
Selected Elements Only. A calculation is executed only for the coordinates matching the elements selected in the active destination dimension. This option is automatically selected if multiple elements are selected in the active destination dimension.
NOTE. If the dialog box has been opened from the object navigator, this box becomes unavailable, and the calculation is executed for all cube coordinates (the All Elements option).
The Before and During Calculation box is used to determine calculation method:
Clear Calculating Elements. Before calculation starts, all calculated coordinates of the destination cube are cleared.
NOTE. If there are multiple facts in a cube, the values of the coordinate to be calculated are cleared for all facts even if the calculation formula is defined not for all the facts.
Clear All Elements for Specified Period. Before calculation all values of destination cube coordinates are cleared for all facts for the specified period.
Update Calculating Elements. Before calculation starts, the calculated coordinates of the destination cube are not cleared. The value of a coordinate to be calculated is updated only after the result has been received.
NOTE. If the cube contains multiple facts, the values of calculated coordinate are updated for all facts even if the calculation formula is defined for some of the facts.
Selecting the Consider Dependencies Between Formulas checkbox enables the use of formula dependencies in calculations using the recursion method. Before the calculation of the coordinate value, first, all the coordinate values used in its expression and having formulas defined are calculated. If the checkbox is deselected, formula dependencies are not used: the system uses the available coordinate values to calculate expressions.
NOTE. Dependencies between formulas in multidimensional calculations will work only when a source cube and destination cube coincide. If the source and destination differ, formula dependency will not work.
A multidimensional calculation uses the same cube as the source one and the destination one. This cube consists of a recursive dimension and contains two facts. The following formulas are specified for the cube coordinates in the multidimensional calculation formula editor:
| Destination cube coordinate | Specified formula |
| b1(Fact1) | b3(Fact1) |
| b1(Fact2) | b3(Fact1) |
| b2(Fact1) | b4(Fact1) |
Before calculation the cube contained the following data:
The values, which are to be calculated, are underlined in red. The following results were obtained using this method:
Calculated values are underlined in green. Even though the value of the b2 element for the Fact1 has not changed after calculation, the value for this coordinate for the Fact2 has been erased, because this fact has no formula defined for it.
Update Calculated Elements Using Non-Empty Values. Before calculation starts, the calculated coordinates of the destination cube are not cleared. The value of a calculated coordinate is updated only after the result has been received and only if it is not NULL. If this method is used, destination cube formulas must be defined for all facts. Otherwise, the pre-calculated coordinate values for these facts are not replaced after the calculation. When the cube contains several facts, it is recommended to use the method described below.
Update Calculated Elements Using Non-Empty Values by Indicators. Before calculation starts, the calculated coordinates of the destination cube are not cleared. The value of a calculated coordinate is updated only after the result has been received and only if it is not NULL. The coordinate value is updated only for the coordinate fact, which have a formula defined. This method should be used when there are multiple facts in a destination cube, and calculation formulas for cube coordinates are not set for all facts.
Compare two calculation methods:
A multidimensional calculation uses the same cube as the source one and the destination one. This cube consists of a recursive dimension and contains two facts. The formula editor for multiple calculation uses the b2(Fact1) formula for the b1(Fact1) (highlighted in green) cube coordinate. Before calculation the cube contained the following data:
Below is the data that was obtained on calculation by the Update Changed Elements with Non-Empty Values (left) and Update Changed Elements with Non-Empty Values by Indicators (right) methods:
The result of the applied formula is the same for both cases (underlined in red): the b1(Fact1) value is equal to the b2(Fact1) value. On using the Update Calculated Elements Using Non-Empty Values method (left), the b1 coordinate value by Fact2 is empty, because the formula by this coordinate fact was set; on using the Update Calculated Elements Using Non-Empty Values by Indicators method (right), the value is changed only by the coordinate facts, for which the formula was set.
Example:
A multidimensional calculation uses the same cube as the source one and the destination one. This cube consists of a recursive dimension and contains 1 fact. The following formulas are specified for the cube coordinates in the multidimensional calculation formula editor:
| Destination cube coordinate | Specified formula |
| b1 | b2 + b3 + b4 |
| b2 | b3 +10 |
| b3 | b4 +10 |
All expressions are defined according to the existing cube fact. Before calculation the cube contained the following data:
This data was calculated using the Update Changed Elements method with the selected and deselected Consider Dependencies Between Formulas checkbox (on the left and on the right, respectively):
When the checkbox is selected (the result is shown on the left), before calculating the b1 coordinate value first the values of the b2 coordinate and the b3 coordinate are calculated, because they have formulas defined. To get the value of the b2 coordinate, the value of the b3 coordinate should be calculated, and so on. After all the values of the formula coordinates have been calculated, the value of the b1 coordinate is calculated.
When the checkbox is deselected (the result is shown on the right), to calculate the b1 coordinate, the system uses the available values of the coordinates b2, b3 and b4: 6, 5 and 10, respectively. To calculate the b2 coordinate, the system uses the available value of the b3 coordinate (5), and so on.
NOTE. When the Shift function is used in formulas, remember that this function does not take part in the recursion, that is, existing coordinate values are always used for its calculation. This feature is a development restriction.
Clicking the Start Calculation button starts multidimensional calculation on DB server. During calculation the progress bar is displayed in the lower part of the dialog box. When calculation is completed, a message with the calculation results is displayed.
See also:
Multidimensional Calculation on DB Server