Compartilhar via


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.

allocation setting

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:

USE_EQUAL_ALLOCATION

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:

Setting_USE_EQUAL_INCREMENT

 

Before_USE_EQUAL_INCREMENT      USE_EQUAL_INCREMENT

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.

Setting_USE_WEIGHTED_ALLOCATION

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.

Before_USE_WEIGHTED_ALLOCATION    USE_WEIGHTED_ALLOCATION

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

Setting_USE_WEIGHTED_INCREMENT

Looking at the before and after shows the following:

image   USE_WEIGHTED_INCREMENT

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.

Setting_USE_WEIGHTED_ALLOCATION_MDX

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.

image

This will give the following results:

USE_WEIGHTED_ALLOCATION_MDX

 

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

WriteBackTest.zip

Comments

  • Anonymous
    February 16, 2012
    If you wanted to PROPAGATE the value down to all the levels below, instead of allocated it, is that possible using the SSAS writeback?

  • Anonymous
    February 22, 2012
    Hi Michael, Unfortuneatly I am not aware of any way to PROPAGATE the value down. If you check in Books Online you can see that the only allocation commands that you can specify in the UPDATE CUBE statement are the following: NO_ALLOCATION      | USE_EQUAL_ALLOCATION            | USE_EQUAL_INCREMENT            | USE_WEIGHTED_ALLOCATION [ BY Weight_Expression]           | USE_WEIGHTED_INCREMENT [ BY Weight_Expression] If you would like to write the same value on all lower levels you have to do a bottom up planning.