|Update Cube||(standard MDX)|
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 Cube SET Tuple = NEW_VALUE [WRITE_BACK_METHOD] [BY [Weight]]
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).
Find here the value of the XMLA properties when not specified otherwise.