UPDATE CUBE Statement
This statement portions out, according to a specified formula, the delta of an updated cell value to all of the children of that member. This method of updating the contents of a cube is called allocation, and is only supported on measures using the SUM aggregation type.
BNF
<update_statement> ::= UPDATE [CUBE] <cube_specification>
SET
<cell_update>[, <cell_update>...]
<cell update> ::= <tuple>.VALUE = <value>
[ USE_EQUAL_ALLOCATION |
USE_EQUAL_INCREMENT |
USE_WEIGHTED_ALLOCATION [BY < weight value_expression>]
USE_WEIGHTED_INCREMENT [BY <weight value_expression>] ]
<Tuple> is a set of coordinates. If the full set of coordinates is not specified, it is assumed that the unspecified coordinates are the default member of the dimension.
The <tuple> can be any cell in the multidimensional space (that is, it does not have to be an atomic cell). However, the cell must be aggregated with the SUM aggregate function and must not use a calculated member as one of its coordinates.
Remarks
It may be helpful to think of the UPDATE CUBE statement as a subroutine that will automatically generate a series of individual writeback operations to atomic cells that will roll up into a specified sum.
The following table describes the methods of allocation.
| Allocation method | Description |
|---|---|
| USE_EQUAL_ALLOCATION | Every atomic cell that contributes to the updated cell will be assigned an equal value that is:
<atomic cell value> = <value> / Count(atomic cells contained in <tuple>) |
| USE_EQUAL_INCREMENT | Every atomic cell that contributes to the updated cell will be changed according to:
<atomic cell value> = <atomic cell value> + (<value> - <existing value>) / Count(atomic cells contained in <tuple>) |
| USE_WEIGHTED_ALLOCATION | Every atomic cell that contributes to the updated cell will be assigned an equal value that is:
<atomic cell value> = <value> * <weight value expression> |
| USE_WEIGHTED_INCREMENT | Every atomic cell that contributes to the updated cell will be changed according to:
<atomic cell value> = <atomic cell value> + (<value> - <existing value>) * <weight value expression> |
If the value <weight value expression> is not provided, the following expression is assigned to it by default:
<weight value expression> = <atomic cell value>/<existing value>
The value of <weight value expression> should be expressed as a value between 0 and 1. This value specifies the ratio of the allocated value you want to assign to the atomic cells that are affected by the allocation. It is the client application programmer's responsibility to create expressions whose rollup aggregate values will equal the allocated value of the expression.
Caution The client application must take into account the allocation of all dimensions concurrently to avoid possible unexpected results, including incorrect rollup values or inconsistent data.
Each UPDATE CUBE allocation should be considered to be atomic for transactional purposes. This means that if any one of the allocation operations fails for any reason, such as an error in a formula or a security violation, then the whole UPDATE CUBE operation will fail. Before the calculations of the individual allocation operations are processed, a snapshot of the data is taken to ensure that the resulting calculations are correct.
Caution When used on a measure containing integers, the USE_WEIGHTED_ALLOCATION method can return imprecise results due to incremental rounding changes.
Examples
Using UPDATE CUBE
UPDATE CUBE [Budget Cube]
SET
([1999], [Marketing], [Budget], [All Departments]) = 1000
USE_WEIGHTED_ALLOCATION BY
([1998], [Sales], [Actual])/
([1999], [Sales], [Actual], [All Departments])