How to control allocations when using write-back in Excel 2010
One of the first questions that I usually gets when showing write-back in Excel 2010 to Analysis Services cubes is how you can control the allocation of values between dimension members. By default you will get an equal allocation based on the number of dimension members.
This setting will divide the value by the number of dimension members. This means that you will get the following result when writing to a measure:
As seen above I have written the number 100 to each group, in group 1 and 2 it has been divided among the three members in each group. In group 3 and 4 it has been divided between 4 and 2 members.
The following is the command that is sent from Excel:
UPDATE CUBE [WriteBackTest] SET
([DimAlloc].[Hierarchy].[Dim Group].&[1], [Measures].[M Divided Equal]) = 100 USE_EQUAL_ALLOCATION,
([DimAlloc].[Hierarchy].[Dim Group].&[2], [Measures].[M Divided Equal]) = 100 USE_EQUAL_ALLOCATION,
([DimAlloc].[Hierarchy].[Dim Group].&[3], [Measures].[M Divided Equal]) = 100 USE_EQUAL_ALLOCATION,
([DimAlloc].[Hierarchy].[Dim Group].&[4], [Measures].[M Divided Equal]) = 100 USE_EQUAL_ALLOCATION
If I would like to get a increment based on previous values I specify that setting in the what-if setting for the pivottable:
As can be seen in the before and after pictures Excel have now incremented the values based on the previous values.
Excel uses the following command to write to the cube:
UPDATE CUBE [WriteBackTest] SET
([DimAlloc].[Hierarchy].[Dim Group].&[1], [Measures].[M Increment Equal]) = 100 USE_EQUAL_INCREMENT,
([DimAlloc].[Hierarchy].[Dim Group].&[2], [Measures].[M Increment Equal]) = 100 USE_EQUAL_INCREMENT,
([DimAlloc].[Hierarchy].[Dim Group].&[3], [Measures].[M Increment Equal]) = 100 USE_EQUAL_INCREMENT,
([DimAlloc].[Hierarchy].[Dim Group].&[4], [Measures].[M Increment Equal]) = 100 USE_EQUAL_INCREMENT
As you can see basically the same query is sent to the cube, the only thing that is different is the USE_EQUAL_ALLOCATION as opposed to the USE_EQUAL_INCREMENT keyword. This controls if equal allocation or incremented allocation should be used.
USE_EQUAL_INCREMENT uses the following formula <leaf cell value> = <leaf cell value> + (<New Value > - <existing value>) / Count(leaf cells contained in <tuple>)
So for key 1 that formula would be
10 + (100 - 45 / 3)
Now if you would like to do some more advanced allocation you can use the weighted allocation option.
This allows you to enter a weight expression to control how the values are allocated between the dimension members. If we test this option on the simple cube.
Since I have not written any weight expression the following command is sent to the cube:
UPDATE CUBE [WriteBackTest] SET
([DimAlloc].[Hierarchy].[Dim Group].&[1], [Measures].[M Allocated]) = 100 USE_WEIGHTED_ALLOCATION,
([DimAlloc].[Hierarchy].[Dim Group].&[2], [Measures].[M Allocated]) = 100 USE_WEIGHTED_ALLOCATION,
([DimAlloc].[Hierarchy].[Dim Group].&[3], [Measures].[M Allocated]) = 100 USE_WEIGHTED_ALLOCATION,
([DimAlloc].[Hierarchy].[Dim Group].&[4], [Measures].[M Allocated]) = 100 USE_WEIGHTED_ALLOCATION
Weighted allocation means that the value written on the group is specified using the following formula: <leaf cell value> = < New Value> * Weight_Expression
The weight expression can be any number; if nothing is specified as a weight expression the following formula is used Weight_Expression = <leaf cell value> / <existing value>.
So that means that the formula for key 1 would be
100 * (10/45)
We can also combine both the setting for weighted allocation and the increment based on previous values
Looking at the before and after shows the following:
Now as you can see the values written to the cube is the same as in the previous example but a different command is actually used:
UPDATE CUBE [WriteBackTest] SET
([DimAlloc].[Hierarchy].[Dim Group].&[1], [Measures].[M Allocated Increment]) = 100 USE_WEIGHTED_INCREMENT,
([DimAlloc].[Hierarchy].[Dim Group].&[2], [Measures].[M Allocated Increment]) = 100 USE_WEIGHTED_INCREMENT,
([DimAlloc].[Hierarchy].[Dim Group].&[3], [Measures].[M Allocated Increment]) = 100 USE_WEIGHTED_INCREMENT,
([DimAlloc].[Hierarchy].[Dim Group].&[4], [Measures].[M Allocated Increment]) = 100 USE_WEIGHTED_INCREMENT
The USE_WEIGHTED_INCREMENT uses the following formula: <leaf cell value> = <leaf cell value> + (<New Value> - <existing value>) * Weight_Expression and since I have not used a weight_expression the following formula is used for that Weight_Expression = <leaf cell value> / <existing value>
This means that the following formula is used for key 1
10 + (100 – 45) * ( 10 / 45)
Now what you probably would like to do is to use another measure as the weight expression and this can be done as well. You just need to specify that in the Weight Expression field.
The weight expression can be any MDX statement that would return a decimal value between 0 and 1, that can be used as the ratio. In my case the Weight measure has the following values.
This will give the following results:
As you have seen you have great ability to control how values are allocated when using the new writeback capabilities in Excel. I have included the project used in this post