Updating cell values (Write back, What-if scenarios)
Description
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
USE_WEIGHTED_ALLOCATION :
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
USE_WEIGHTED_INCREMENT :
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
where:
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
DefaultMember
Examples
Test Write back using USE_EQUAL_ALLOCATION method
Query
UPDATE CUBE [Sales] SET ([Geography].[Geo].[Europe], [Measures].[Amount]) = 20
WITH
MEMBER NewAmount as [Measures].[Amount]
SELECT
{[Measures].[NewAmount]} ON 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) ON 1
FROM
[SALES]
Result
|
NewAmount |
| Europe |
20.0 |
| France |
2.5 |
| Paris |
2.5 |
| Spain |
5.0 |
| Barcelona |
2.5 |
| Madrid |
2.5 |
| Valencia |
|
| Switzerland |
12.5 |
| Geneva |
2.5 |
| Lausanne |
7.5 |
| Zurich |
2.5 |
Assertion : MDX Equals
WITH
CONST FUNCTION NonEmptyEuropeChildrenCount() AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS([Geography].[Geo].[Europe],[Geography].[Geo].[City])) ))
FUNCTION NonEmptyChildrenCount(X) AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS(X,[Geography].[Geo].[City]) ) ))
FUNCTION IsDescendantOrSame(_MEMBER,_DESCENDANT) AS isAncestor(_MEMBER,_DESCENDANT) OR _MEMBER is _DESCENDANT
MEMBER NewAmount AS IIF( IsDescendantOrSame( [Geography].[Geo].[Europe], [Geography].[Geo].currentMember ),
IIF( NonEmptyChildrenCount([Geography].[Geo].currentMember) = 0, NULL, 20 * NonEmptyChildrenCount([Geography].[Geo].currentMember)/NonEmptyEuropeChildrenCount()) ,
([Measures].[Amount],[Geography].[Geo].currentMember))
SELECT
{[Measures].[NewAmount]} on 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) on 1
FROM
[Sales]
Test Write back using USE_EQUAL_INCREMENT method
Query
UPDATE CUBE [Sales] SET ([Geography].[Geo].[Europe], [Measures].[Amount]) = 300 USE_EQUAL_INCREMENT
WITH
MEMBER NewAmount as [Measures].[Amount]
SELECT
{[Measures].[NewAmount]} ON 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) ON 1
FROM
[SALES]
Result
|
NewAmount |
| Europe |
300.0 |
| France |
9.625 |
| Paris |
9.625 |
| Spain |
14.25 |
| Barcelona |
7.625 |
| Madrid |
6.625 |
| Valencia |
|
| Switzerland |
276.125 |
| Geneva |
133.625 |
| Lausanne |
72.875 |
| Zurich |
69.625 |
Assertion : MDX Equals
WITH
CONST FUNCTION NonEmptyEuropeChildrenCount() AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS([Geography].[Geo].[Europe],[Geography].[Geo].[City])) ))
FUNCTION NonEmptyChildrenCount(X) AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS(X,[Geography].[Geo].[City]) ) ))
FUNCTION IsDescendantOrSame(_MEMBER,_DESCENDANT) AS isAncestor(_MEMBER,_DESCENDANT) OR _MEMBER is _DESCENDANT
MEMBER NewAmount AS IIF( IsDescendantOrSame( [Geography].[Geo].[Europe], [Geography].[Geo].currentMember ),
IIF( NonEmptyChildrenCount([Geography].[Geo].currentMember) = 0, NULL, ([Measures].[Amount],[Geography].[Geo].currentMember) + NonEmptyChildrenCount([Geography].[Geo].currentMember) * ((300 - ([Geography].[Geo].[Europe], [Measures].[Amount])) / NonEmptyEuropeChildrenCount() ) ) ,
([Measures].[Amount],[Geography].[Geo].currentMember))
SELECT
{[Measures].[NewAmount]} on 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) on 1
FROM
[Sales]
Test Write back using USE_WEIGHTED_ALLOCATION method without weight
Query
UPDATE CUBE [Sales] SET ([Geography].[Geo].[Europe], [Measures].[Amount]) = (255*1.2) USE_WEIGHTED_ALLOCATION
WITH
MEMBER NewAmount as [Measures].[Amount]
SELECT
{[Measures].[NewAmount]} ON 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) ON 1
FROM
[SALES]
CELL PROPERTIES VALUE
Result
|
NewAmount |
| Europe |
306.0 |
| France |
4.8 |
| Paris |
4.8 |
| Spain |
3.5999999999999996 |
| Barcelona |
2.4 |
| Madrid |
1.2 |
| Valencia |
|
| Switzerland |
297.6 |
| Geneva |
153.6 |
| Lausanne |
67.19999999999999 |
| Zurich |
76.8 |
Assertion : MDX Equals
WITH
CONST FUNCTION NonEmptyEuropeChildrenCount() AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS([Geography].[Geo].[Europe],[Geography].[Geo].[City])) ))
FUNCTION NonEmptyChildrenCount(X) AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS(X,[Geography].[Geo].[City]) ) ))
FUNCTION IsDescendantOrSame(_MEMBER,_DESCENDANT) AS isAncestor(_MEMBER,_DESCENDANT) OR _MEMBER is _DESCENDANT
MEMBER NewAmount AS IIF( IsDescendantOrSame( [Geography].[Geo].[Europe], [Geography].[Geo].currentMember ),
IIF( NonEmptyChildrenCount([Geography].[Geo].currentMember) = 0, NULL, 1.2*([Measures].[Amount],[Geography].[Geo].currentMember) ) ,
([Measures].[Amount],[Geography].[Geo].currentMember))
SELECT
{[Measures].[NewAmount]} on 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) on 1
FROM
[Sales]
CELL PROPERTIES VALUE
Test Write back using USE_WEIGHTED_ALLOCATION method with weight
Query
UPDATE CUBE [Sales] SET ([Geography].[Geo].[Europe], [Measures].[Amount]) = 33 USE_WEIGHTED_ALLOCATION BY 1.2
WITH
MEMBER NewAmount as [Measures].[Amount]
SELECT
{[Measures].[NewAmount]} ON 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) ON 1
FROM
[SALES]
Result
|
NewAmount |
| Europe |
316.8 |
| France |
39.6 |
| Paris |
39.6 |
| Spain |
79.2 |
| Barcelona |
39.6 |
| Madrid |
39.6 |
| Valencia |
|
| Switzerland |
198.0 |
| Geneva |
39.6 |
| Lausanne |
118.80000000000001 |
| Zurich |
39.6 |
Assertion : MDX Equals
WITH
CONST FUNCTION NonEmptyEuropeChildrenCount() AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS([Geography].[Geo].[Europe],[Geography].[Geo].[City])) ))
FUNCTION NonEmptyChildrenCount(X) AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS(X,[Geography].[Geo].[City]) ) ))
FUNCTION IsDescendantOrSame(_MEMBER,_DESCENDANT) AS isAncestor(_MEMBER,_DESCENDANT) OR _MEMBER is _DESCENDANT
MEMBER NewAmount AS IIF( IsDescendantOrSame( [Geography].[Geo].[Europe], [Geography].[Geo].currentMember ),
IIF( NonEmptyChildrenCount([Geography].[Geo].currentMember) = 0, NULL, 33 * 1.2 * NonEmptyChildrenCount([Geography].[Geo].currentMember) ) ,
([Measures].[Amount],[Geography].[Geo].currentMember))
SELECT
{[Measures].[NewAmount]} on 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) on 1
FROM
[Sales]
Test Write back using USE_WEIGHTED_INCREMENT method without weight
Query
UPDATE CUBE [Sales] SET ([Geography].[Geo].[Europe], [Measures].[Amount]) = (255*1.2) USE_WEIGHTED_INCREMENT
WITH
MEMBER NewAmount as [Measures].[Amount]
SELECT
{[Measures].[NewAmount]} ON 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) ON 1
FROM
[SALES]
CELL PROPERTIES VALUE
Result
|
NewAmount |
| Europe |
306.0 |
| France |
4.8 |
| Paris |
4.8 |
| Spain |
3.5999999999999996 |
| Barcelona |
2.4 |
| Madrid |
1.2 |
| Valencia |
|
| Switzerland |
297.6 |
| Geneva |
153.6 |
| Lausanne |
67.19999999999999 |
| Zurich |
76.8 |
Assertion : MDX Equals
WITH
CONST FUNCTION NonEmptyEuropeChildrenCount() AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS([Geography].[Geo].[Europe],[Geography].[Geo].[City])) ))
FUNCTION NonEmptyChildrenCount(X) AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS(X,[Geography].[Geo].[City]) ) ))
FUNCTION IsDescendantOrSame(_MEMBER,_DESCENDANT) AS isAncestor(_MEMBER,_DESCENDANT) OR _MEMBER is _DESCENDANT
MEMBER NewAmount AS IIF( IsDescendantOrSame( [Geography].[Geo].[Europe], [Geography].[Geo].currentMember ),
IIF( NonEmptyChildrenCount([Geography].[Geo].currentMember) = 0, NULL, 1.2* ([Measures].[Amount],[Geography].[Geo].currentMember) ) ,
([Measures].[Amount],[Geography].[Geo].currentMember))
SELECT
{[Measures].[NewAmount]} on 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) on 1
FROM
[Sales]
CELL PROPERTIES VALUE
Test Write back using USE_WEIGHTED_ALLOCATION method with weight
Query
UPDATE CUBE [Sales] SET ([Geography].[Geo].[Europe], [Measures].[Amount]) = 300 USE_WEIGHTED_INCREMENT BY 1.2
WITH
MEMBER NewAmount as [Measures].[Amount]
SELECT
{[Measures].[NewAmount]} ON 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) ON 1
FROM
[SALES]
Result
|
NewAmount |
| Europe |
687.0 |
| France |
58.0 |
| Paris |
58.0 |
| Spain |
111.0 |
| Barcelona |
56.0 |
| Madrid |
55.0 |
| Valencia |
|
| Switzerland |
518.0 |
| Geneva |
182.0 |
| Lausanne |
218.0 |
| Zurich |
118.0 |
Assertion : MDX Equals
WITH
CONST FUNCTION NonEmptyEuropeChildrenCount() AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS([Geography].[Geo].[Europe],[Geography].[Geo].[City])) ))
FUNCTION NonEmptyChildrenCount(X) AS count( NonEmpty( ([Measures].[Amount],[Product].[Prod].[Licence].members,DESCENDANTS(X,[Geography].[Geo].[City]) ) ))
FUNCTION IsDescendantOrSame(_MEMBER,_DESCENDANT) AS isAncestor(_MEMBER,_DESCENDANT) OR _MEMBER is _DESCENDANT
MEMBER NewAmount AS IIF( IsDescendantOrSame( [Geography].[Geo].[Europe], [Geography].[Geo].currentMember ),
IIF( NonEmptyChildrenCount([Geography].[Geo].currentMember) = 0, NULL, ([Measures].[Amount],[Geography].[Geo].currentMember) + NonEmptyChildrenCount([Geography].[Geo].currentMember) * (300 - ([Geography].[Geo].[Europe], [Measures].[Amount])) * 1.2 ) ,
([Measures].[Amount],[Geography].[Geo].currentMember))
SELECT
{[Measures].[NewAmount]} on 0,
Descendants([Geography].[Geo].[Europe], [Geography].[Geo].[City],SELF_BEFORE_AFTER) on 1
FROM
[Sales]
XMLA Properties
Find here the value of the XMLA properties when not specified otherwise.
icCube OLAP version: 3.0.3 03-Mar-2013 14:06:07 GMT