Updating Your UDM Data - Professional SQL Server Analysis Services 2005 with MDX - page 546

Similarly you can use the USE_WEIGHTED_INCREMENT option to writeback to
the cell corresponding to Amy Alberts and see that the budget gets distributed
based on weights and also gets added to existing budget amounts.
You learned how to update a cube's cell data using the cell writeback feature of
Analysis Services 2005. You also learned that the changes are propagated back
to the cube only when you issue a commit statement. Therefore, you can perform
many what-if scenarios by doing allocations followed by queries; this will surface
the influence of the allocations on the financial status of the company. Typically we
expect calculations to be defined in MDX scripts that make use of the measure
values such as budget, which will reflect the overall profit or key performance in-
dicators of the company. If the updates you have done do not yield the expected
results, you can roll back the complete transaction thereby preventing the entire
update operation to be propagated to the writeback table.
You learned how to writeback values to your cube and do what-if analysis or scen-
arios by sending MDX queries to see the effects of the writeback. Similar to dimen-
sion writeback where you had certain limitations, there are some things you should
be aware of while updating cell values using the UPDATE statement. Assume you
have a large cube with several dimensions (greater than twenty dimensions). Due
to multidimensionality, a specific cell is now referred to by multiple dimensions.
If you do an allocation using the UPDATE statement that includes only a few di-
mension's granularity attributes, Analysis Services will try to equally distribute the
value allocated to a cell in the cube to all the leaf-level cells (across all the hier-
archies in each dimension). Hence if you do an update on a cell that is referred to
by the topmost-level on certain dimensions, the update to leaf levels can be quite
expensive because Analysis Services needs to equally distribute the value across
all members of all dimensions. Such an update will result in a lot of rows being
entered into the writeback table. Hence whenever possible please make sure you
do the writeback to the appropriate level intended. We are just warning you about
data expansion.
To understand the data expansion problem better, consider the following example
of updating Amy Alberts's budget for the year 2003 which is referred to by the tuple
([WB Employee].[Manager].&[290], [WB Period].[Period].&[2003]). This will update
all the leaf-level members, which is the product of all the members reporting to
Amy Alberts and all the quarters in 2003. This update results in changes to 16
cells at the leaf level. Imagine dimensions that have hundreds or even thousands
of members. As with a Product dimension, a simple mistake of updating at the top-