Update Cube


Updating cell values (Write back, What-if scenarios)


In MDX it is possible to change values of existing cells. It's main use is generating and checking what-if scenarios for data changes. It's as easy to update a single cell that it is to change a whole sub-cube.

The syntax of an write back is :


UPDATE CUBE [Sales] SET ([Geography].[Geo].[Europe], [Measures].[Amount]) = 20

This query redefines the value for a whole sub-cube defined by ([Geography].[Geo].[Europe], [Measures].[Amount]) to a new value, 20. Note that for missing hierarchies/dimensions the default member will be used and that calculated members are not allowed.

How is the query performing the change of value ? For each existing cell of the sub-cube a new value is assigned so the aggregation is equal to the entered amount. The user has at his disposal different ways of row value assignment.

Note, and this is a difference from MSAS, that only non empty fact rows are updated. Fact rows can be seen as rows of the underlying fact table and will match a cell if the table has one fact row per cell or the flag 'aggregate facts' has been activated in the cube definition. Pay attention that in highly sparse cube with a lot of dimensions updating a tuple may represent very quickly millions of cells.

There are different methods to allocate values to the sub-cube rows :

USE_EQUAL_ALLOCATION : row_tuple_value = new_value / num_rows_in_tuple ( note the former value is not used )

USE_EQUAL_INCREMENT : row_tuple_value = row_tuple_old_value + (new_value - old_value) / num_rows_in_tuple

without weight : row_tuple_old_value * new_value / old_value ( note this is equivalent to a percentual change on all cells )
with weight : new_value * weight * num_rows_in_tuple

without weight : row_tuple_old_value * new_value / old_value ( same as USE_WEIGHTED_ALLOCATION without weight )
with weight : row_tuple_old_value + (new_value - old_value) * weight


row_tuple_value = the new value of the fact row
row_tuple_old_value = the former value of the fact row
new_value = the value defined in the UPDATE CUBE MDX statement
old_value = the former value of the tuple defined in the UPDATE CUBE statement
num_rows_in_tuple = number of fact rows with a no empty value in the tuple

The UPDATE CUBE statement is performed in the scope of a transaction. A commit of this transaction will update the fact values with the modified values for all users. Note that this commit may change the type of the column to a Float/Double if the former type was an integer number (This is another difference with MSAS).

See Also



XMLA Properties

Find here the value of the XMLA properties when not specified otherwise.