CELL.ALLOCATE
You can write back a value to an aggregated cube cell with CELL.ALLOCATE. There are different methods of allocating the value to the base cells (see Allocation type below).
CELL.ALLOCATE distributes a value to a slice of data defined by the arguments of the formula. To distribute a value in the same proportions as in a different slice of data (for example, the same slice but for a previous year), you use Weighted Allocation. In this case, the function takes an additional argument called Distribute MDX.
Distribute MDX is a statement of those elements that make the source slice of data different from the target slice.
Syntax
=CELL.Allocate(Distribution Type,Distribute
MDX,Value,Alias,Cube,Element 1, ...,Element n)
Examples
To allocate a figure to 2007 in the same proportions as for 2006, the Distribute MDX would be:
[time].[all years].[2006].
To allocate a figure to the 2007 budget in the same proportion as the 2006 actuals, the Distribute MDX would be:
[time].[all years].[2006], [version].[actual]
Arguments
| Argument | ||
|---|---|---|
| Distribution Type | Specify how the value is distributed to the subordinate elements. | 0 = Use_equal_allocation
1 = Use_equal_increment (not supported by Infor OLAP Server) 2 = Use_weighted_allocation 3 = Use_weighted_increment (not supported by Infor OLAP Server) |
| Distribute MDX | Enter the MDX distribution formula (section of the cube, which forms the basis of the distribution). | Only required when using Weighted allocation. |
| Value | Value | The value to be allocated |
| Alias | Name of the database alias | Best Practices OLAP |
| Cube | Name of the cube | [Sales] |
| Element 1
Element 2 |
Unique name of the element | [Product].[All Tires];
[Time].[All Years].[2008] |
Distribution type
| Distribution Type | Description |
|---|---|
| use_equal_allocation | The value is distributed equally to the subordinate elements. Existing values are overwritten. |
| use_equal_increment | The value is distributed equally to the subordinate elements and it is added to the existing values. |
| use_weighted_allocation | The value is distributed to the subordinate elements according to the existing values of a cube section. Existing values are overwritten. |
| use_weighted_increment | The value is distributed to the subordinate elements according to the existing values of a cube section and it is added to the existing values. |
Examples
Use_Equal_Increment (not supported by OLAP Server)
In this example, the sales targets for the first quarter of 2008 are increased by 300,000.
Select 1 = use_equal_increment as the allocation type.
=CELL.ALLOCATE(1,,300000,"Foodmart
2005","Budget","[Store].[All
Stores]","[Measures].[Amount]","[Time].[1997].[Q1]","[Account].[All
Account].[Net Income].[Total Expense].[General & Administration]")
The value is distributed equally to the subordinate elements. For each month of the first quarter, the value is increased by 100,000.
Use_Weighted_Allocation
You want to plan the expenses for 2008 for the US stores according to the expenses of the previous year. Use the allocation type 'use_weighted_allocation'.
=CELL.ALLOCATE(2,"Distribute MDX",900000,"FoodMart
2005","Budget","[Store].[All
Stores].[USA]","[Measures].[Amount]","[Time].[1998]","[Account].[Total
Expense]")
Distribute MDX (on Analysis Services):
([Account].CurrentMember,
[Category].CurrentMember,
[Measures].[Amount],
[Store].CurrentMember,
[Time].CurrentMember)/
([Account].[All Account].
[Net Income].[Total Expense],
[Category].[All Category],
[Measures].[Amount],
[Store].[All Stores].[USA],
[Time].[1997])
Distribute MDX (on OLAP Server):
[Account].[All Account].
[Net Income].[Total Expense],
[Category].[All Category],
[Measures].[Amount],
[Store].[All Stores].[USA],
[Time].[1997]
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | +2008 | |||
| 3 | -All Stores | 398755,6899 | ||
| 4 | +Canada | 0 | ||
| 5 | +Mexico | 0 | ||
| 6 | -USA | 398755,69 | ||
| 7 | +CA | 71980,1599 | ||
| 8 | -OR | 116702,1899 | ||
| 9 | +Portland | 44333,19 | ||
| 10 | +Salem | 72368,9999 | ||
| 11 | +WA | 210073,3399 | ||
| 12 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | +2009 | |||
| 3 | -All Stores | 1037817,52 | ||
| 4 | +Canada | 29052 | ||
| 5 | +Mexico | 790921,84 | ||
| 6 | -USA | - | ||
| 7 | +CA | - | ||
| 8 | -OR | - | ||
| 9 | +Portland | - | ||
| 10 | +Salem | - | ||
| 11 | +WA | - | ||
| 12 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | +2009 | |||
| 3 | -All Stores | 1719973,84 | ||
| 4 | +Canada | 29052,00 | ||
| 5 | +Mexico | 790921,84 | ||
| 6 | -USA | 900000,00 | ||
| 7 | +CA | 471392,66 | ||
| 8 | -OR | 138257,17 | ||
| 9 | +Portland | 57982,33 | ||
| 10 | +Salem | 80274,84 | ||
| 11 | +WA | 290350,17 | ||
| 12 |