Allocation helps to distribute data from a source region to a target region using the specified driver.
Syntax
*RUNALLOCATION
*FACTOR=<driver>
*DIM P_ACCT WHAT=<soure>; WHERE=<target>; USING=<distribution key>; [TOTAL=<distribution key>]
*DIM <other dimensions>
*ENDALLOCATION
The source value represents the range of data values to be allocated by the allocation function. This value is qualified by one or several tuples in the cube.
Possible WHAT options are:
Options |
Descriptions |
Example |
---|---|---|
Member |
A specified dimension base member. |
|
[property]=”property value” |
A filter to select dimension members based on a specified property and a given value for that property. |
|
BAS |
All leaf level members of a specific dimension parent member |
|
same as WHERE |
Use the same members defined in the WHERE parameter for the specified dimension |
|
<> member |
The <> operand references all members not equal to a defined member. In the <>member mode, the member should be base member in the dimension. If the <> is used, all other base members in that dimension should be included in the region. |
|
You cannot use blank or [ALL] in WHAT condition.
You cannot use non-base members with <>.
The basis of the allocation is the definition of a portion or complete source value to be allocated. The method of defining the basis is the use of a factor. A factor can be the value or values that can be multiplied or divided to derive a value (added or subtracted potential future support).
A factor can also derive values based on a defined region of data referred as using, or the basis region.
Possible USING and TOTAL options are:
Options |
Descriptions |
Example |
---|---|---|
Member |
A specified dimension base member. |
|
BAS |
All leaf level members of a specific dimension parent member |
|
same as WHERE |
Use the same members defined in the WHERE parameter for the specified dimension. The same dimension must be for both WHERE and USING. Note WHERE only supports a single member reference. End of the note. |
|
<> member |
The <> operand references all members not equal to a defined member. In the <>member mode, the member should be base member in the dimension. If the <> is used, all other base members in that dimension should be included in the region. |
|
[property]=”property value” |
A filter to select dimension members based on a specified property and a given value for that property. |
|
You cannot use >0. For example, USING = Amount >0
is not supported.
USING and TOTAL must have the same definition.
You cannot use non-base members with <>.
You cannot use parent member directly in USING. BAS(parent) is still allowed as described above.
FACTOR can be used to define any arithmetic expression, written in the {expression} parameter, and can contain operands, parentheses, constants, and one or both of the keywords USING and TOTAL, representing respectively the amount coming from the USING region, the amount of the driver, and the amount coming from the TOTAL region, the sum of the drivers.
If FACTOR is omitted, the FACTOR defaults to 1.
If the arithmetic expression is omitted, the default is multiplication.
Possible FACTOR options are:
Options |
Descriptions |
Example |
---|---|---|
.8 |
Only a fixed amount is to be applied to the source value for allocation. |
Tuple value of the WHAT is 10 for product A 10*.8 = 8 |
USING |
The value of the USING region defined is used as the basis of the allocation. This provides the ability to change values without having to edit the definition of the allocation. |
Tuple value of the WHAT is 10 for product A, tuple value of the USING is .8 10*.8 = 8 |
-1 * USING/100 Or 1 * USING/100 |
Calculation takes the defined region as USING, and reverses the value (negative), then divides the value by 100. |
Tuple value of the USING region is 70 for product A. -70/100 = -.7 (-70%) (1-70)/100 = -.69 (-69%) Note This is used with driver accounts not reporting values. End of the note. |
USING/TOTAL |
Calculation takes the region defined as USING, and divides the total value for the region defined as TOTAL. |
Tuple value of USING region is 70 for product A. The total of the TOTAL region (tuples) is 700, assuming that the using region is for all regions not just US. 70/700 = .1 (10%) |
Basic Mathematical expression with USING & TOTAL (For example, 1 + USING, USING -1 , and 1 – USING) |
Calculation takes the region defined as USING, and does the necessary mathematical operations with the total value for the region defined as TOTAL. |
Tuple value of the USING region is 70 for product A. 1+70 = 71 70 -1 = 69 1-70 = -69 |
You cannot use any script logic keywords in FACTOR expression.
The target identifies the tuples to which the values should be allocated. It represents dimension member combinations to which the values should be distributed. The target identifies the dimension for which the value of the members is modified as compared to the source, and only explicitly mentioned and characterized dimension should be modified.
The keyword WHERE is used in combination with the keyword *DIM to identify the target dimension members (the values to be modified against the source).
Possible WHERE options are:
Options |
Descriptions |
Example |
---|---|---|
Member |
A specified dimension base member. |
|
BAS |
All leaf level members of a specific dimension parent member |
|
same as WHAT |
Use the same members defined in the WHAT parameter for the specified dimension |
|
blank or [All] |
The ability to assume all base members for given dimensions either via a blank parameter or [ALL] key word |
|
[property]=”property value” |
A filter to select dimension members based on a specified property and a given value for that property. |
|
*APP (ability to write in different application if needed), COUNT and LIST keywords inside RUNALLOCATION or any other options not mentioned above are not supported.
The account RENT is entered in entity GLOBALOPS, inter-company NON_INTERCO. This amount must be allocated using a percentage of allocation that is entered by the user in account PERCENT in the appropriate entities and for the desired members of the CATEGORY, TIME, DATASRC, and RPTCURRENCY dimensions.
This allocation demonstrates the following features:
It uses the {dimensiontype}DIM keyword to identify the dimensions by type
It uses the <<< and >>> keywords to reference the definitions used to the left or to the right
Before Allocation
Source Account (RENT) data:
RENT CHINA |
China |
|
RENT JAPAN |
Japan |
|
RENT INDIA |
India |
|
RENT ASAREST |
Rest of Asia |
|
RENT ASA |
Asia |
50,000,000.00 |
Driver (Percent Account) data:
PERCENT CHINA |
China |
10.00 |
PERCENT JAPAN |
Japan |
32.00 |
PERCENT INDIA |
India |
8.00 |
PERCENT ASAREST |
Rest of Asia |
50.00 |
Syntax
*XDIM_MEMBERSET TIME=2006.SEP
*XDIM_MEMBERSET P_ACCT=RENT,PERCENT
*XDIM_MEMBERSET ENTITY=ASA,INDIA,CHINA,JAPAN,ASAREST
*RUNALLOCATION
*FACTOR=USING/100
*DIM P_ACCT WHAT=RENT; WHERE=<<<; USING=PERCENT
*DIM ENTITY WHAT=ASA; WHERE=INDIA,CHINA,JAPAN,ASAREST; USING=<<<
*ENDALLOCATION
After Allocation
RENT CHINA |
China |
5,000,000.00 |
RENT JAPAN |
Japan |
16,000,000.00 |
RENT INDIA |
India |
4,000,000.00 |
RENT ASAREST |
Rest of Asia |
25,000,000.00 |
RENT ASA |
Asia |
50,000,000.00 |
The account RENT is entered in entity GLOBALOPS, inter-company NON_INTERCO. This amount must be allocated on the basis of the square meters of rented space used by all European entities.
This allocation demonstrates the following features:
Allocation uses historical rent as the driver
It uses the BAS( ) keyword to build a list of members
Before Allocation
Below is the history data shown for the 2008.JAN period. Asia is the parent node.
Entity |
CATEGORY |
2008.JAN |
||
---|---|---|---|---|
Asia all |
ACTUAL |
|||
Asia |
ACTUAL |
|||
China |
ACTUAL |
1,500.00 |
||
Japan |
ACTUAL |
7,000.00 |
||
India |
ACTUAL |
500.00 |
||
Rest of Asia |
ACTUAL |
5,000.00 |
||
Planning Asia |
ACTUAL |
Source Account (RENT) data:
To allocate 50,000 to base countries
This input can be received in the Planning Asia entity
Driver data:
The historical data is the Driver
Syntax
*XDIM_MEMBERSET TIME=2008.JAN,2009.JAN
*XDIM_MEMBERSET P_ACCT=RENT
*XDIM_MEMBERSET CATEGORY=ACTUAL,PLAN
*XDIM_MEMBERSET ENTITY=ASA,BAS(RASA)
//Create total rent data to be allocated.
*WHEN ENTITY
*IS "ASA"
*REC (EXPRESSION=50000,TIME=2009.JAN,CATEGORY=PLAN)
*ENDWHEN
*COMMIT
//Allocate to base countries.
*RUNALLOCATION
*FACTOR=USING/TOTAL
*DIM P_ACCT WHAT=RENT; WHERE=<<<; USING=<<<; TOTAL=<<<
*DIM ENTITY WHAT=ASA; WHERE=BAS(RASA); USING=<<< TOTAL=<<<
*DIM TIME WHAT=>>>; WHERE=2009.JAN; USING=2008.JAN; TOTAL=<<<
*DIM CATEGORY WHAT=>>>; WHERE=PLAN; USING=ACTUAL; TOTAL=<<<
*ENDALLOCATION
//Clear the total rent account.
*WHEN ENTITY
*IS "ASA"
*REC (EXPRESSION=0,TIME=2009.JAN,CATEGORY=PLAN)
*ENDWHEN
After Allocation
Entity |
CATEGORY |
2009.JAN |
||
---|---|---|---|---|
Asia all |
PLAN |
50,000.00 |
||
Asia |
PLAN |
50,000.00 |
||
China |
PLAN |
7,500.00 |
||
Japan |
PLAN |
35,000.00 |
||
India |
PLAN |
2,500.00 |
||
Rest of Asia |
PLAN |
5,000.00 |
||
Planning Asia |
PLAN |
2009.JAN is posted with rents for individual countries based upon their historical share.
The sum of all ADVERTISING expenses incurred by all European operations must be reallocated to each European operation based on their external SALES.
This allocation demonstrates the following features:
It uses the DOT({type}) keyword to identify the dimensions by type
It demonstrates the ability to perform aggregations in the WHAT region (SALESEUROPE and ALL_INTCO are parent members)
It shows a many-to-one redirection of a dimension (it reads the sum of the Inter-company members and writes it in the NON_INTERCO member of the INTCO dimension)
It shows a one-to-one redirection of a dimension (it reads the INPUT member and writes the results in the ALLOCATED member of the DATASRC dimension)
Before Allocation
Source data – History data for External Sales:
Actual – Periodic |
2008.JAN |
---|---|
Planning Asia |
|
China |
500,000 |
Japan |
600,000 |
India |
200,000 |
Rest of Asia |
800,000 |
Asia |
2,100,000 |
Asia all |
2,100,000 |
Data to be allocated (ADVERTISING):
Plan – Periodic |
2009.JAN |
---|---|
Planning Asia |
500,000 |
China |
|
Japan |
|
India |
|
Rest of Asia |
|
Asia |
|
Asia all |
500,000 |
Syntax
Factor: USING/TOTAL
APP or (Dim) or VALUE |
WHAT |
WHERE |
USING |
TOTAL |
---|---|---|---|---|
DOT(A) |
ADVERTISING |
<<< |
EXTSALES |
<<< |
DOT(E) |
SALESEUROPE |
BAS(SALESEUROPE) |
<<< |
<<< |
DOT(I) |
ALL_INTERCO |
NON_INTERCO |
>>> |
BAS(ALL_INTERCO) |
DATASRC |
INPUT |
ALLOCATED |
INPUT |
<<< |
*RUNALLOCATION
*FACTOR =USING/TOTAL
*DIM P_ACCT WHAT=ADVERTISING; WHERE=<<<; USING=EXTSALES; TOTAL=<<<
*DIM ENTITY WHAT=ASA; WHERE=BAS(RASA); USING=<<<; TOTAL=<<<
*DIM INTERCO WHAT=WORLD_INTERCO; WHERE=I_NONE; USING=>>>; TOTAL=BAS(World_InterCo)
*DIM P_DATASRC WHAT=MANUAL; WHERE=ALLOCATED; USING=MANUAL; TOTAL=<<<
*DIM TIME WHAT=2009.JAN; WHERE=2009.JAN; USING=2008.JAN; TOTAL=<<<
*ENDALLOCATION
After Allocation
Plan – Periodic |
2009.JAN |
---|---|
Planning Asia |
|
China |
119,048 |
Japan |
142,857 |
India |
47,619 |
Rest of Asia |
190,476 |
Asia |
500,000 |
Asia all |
500,000 |
This example implements the same scenario as example 3, but using a system variable for year. (%YEAR%). This returns current calendar year. Assume that the script is for year 2009.
Before Allocation
Source data – History data for External Sales:
Actual – Periodic |
2008.JAN |
---|---|
Planning Asia |
|
China |
500,000 |
Japan |
600,000 |
India |
200,000 |
Rest of Asia |
800,000 |
Asia |
2,100,000 |
Asia all |
2,100,000 |
Data to be allocated (ADVERTISING):
Plan – Periodic |
2009.JAN |
---|---|
Planning Asia |
500,000 |
China |
|
Japan |
|
India |
|
Rest of Asia |
|
Asia |
|
Asia all |
500,000 |
Syntax
Factor: USING/TOTAL
APP or (Dim) or VALUE |
WHAT |
WHERE |
USING |
TOTAL |
---|---|---|---|---|
DOT(A) |
ADVERTISING |
<<< |
EXTSALES |
<<< |
DOT(E) |
SALESEUROPE |
BAS(SALESEUROPE) |
<<< |
<<< |
DOT(I) |
ALL_INTERCO |
NON_INTERCO |
>>> |
BAS(ALL_INTERCO) |
DATASRC |
INPUT |
ALLOCATED |
INPUT |
<<< |
*RUNALLOCATION
*FACTOR =USING/TOTAL
*DIM P_ACCT WHAT=ADVERTISING; WHERE=<<<; USING=EXTSALES; TOTAL=<<<
*DIM ENTITY WHAT=ASA; WHERE=BAS(RASA); USING=<<<; TOTAL=<<<
*DIM INTERCO WHAT=WORLD_INTERCO; WHERE=I_NONE; USING=>>>; TOTAL=BAS(World_InterCo)
*DIM P_DATASRC WHAT=MANUAL; WHERE=ALLOCATED; USING=MANUAL; TOTAL=<<<
*DIM TIME WHAT=%YEAR%.JAN; WHERE=%YEAR%.JAN; USING=%YEAR%(-1).JAN; TOTAL=<<<
*ENDALLOCATION
After Allocation
Plan – Periodic |
2009.JAN |
---|---|
Planning Asia |
|
China |
119,048 |
Japan |
142,857 |
India |
47,619 |
Rest of Asia |
190,476 |
Asia |
500,000 |
Asia all |
500,000 |
All accounts in the profit and loss of category ACTUAL, for the three entities ITALY, FRANCE and UK, are copied into the corresponding accounts of the entity GLOBALOPS for category BUDGET. This allocation is basically an example of a simple copy action, which does not use FACTOR at all. In this example the engine performs a one-to-one copy (ACTUAL into BUDGET) and a many-to-one copy (ITALY, FRANCE and UK are added up and copied into GLOBALOPS).
Before Allocation
Actual – Periodic |
2007.DEC |
---|---|
UK (Great Britain) |
30,000 |
Italy |
10,000 |
France |
10,000 |
Budget – Periodic |
2008.FEB |
---|---|
Global Operations |
Syntax
Factor:
APP or (Dim) or VALUE |
WHAT |
WHERE |
---|---|---|
ACCOUNT |
[GROUP] = ”profit & loss” |
<<< |
CATEGORY |
ACTUAL |
BUDGET |
ENTITY |
SALESITALY; SALESFRANCE; SALESUK |
GLOBALOPS |
DOT(R) |
LC |
<<< |
A property not supported in ALLOCATION replaces a property with variable using a select statement. Ensure that the *SELECT statement selects only base members.
*XDIM_MEMBERSET ACCOUNT=ADVERTISING,EXTSALES
*XDIM_MEMBERSET ENTITY=UK,ITALY,FRANCE,GLOBAL
*XDIM_MEMBERSET P_DATASRC = INPUT
*XDIM_MEMBERSET TIME = 2007.DEC,2008.JAN
*XDIM_MEMBERSET CATEGORY = ACTUAL,BUDGET
*XDIM_MEMBERSET INTCO= I_NONE
*XDIM_MEMBERSET RPTCURRENCY = LC
*SELECT(%ACCT%, "[ID]",ACCOUNT,"[CALC]='N'","[GROUP]='PL'")
*RUNALLOCATION
*FACTOR=
*DIM ACCOUNT WHAT=[GROUP]="PL" AND [CALC]="N"; WHERE=<<<
*DIM CATEGORY WHAT=ACTUAL; WHERE=BUDGET
*DIM ENTITY WHAT=UK,ITALY,FRANCE; WHERE=GLOBAL
*DIM DOT(R) WHAT=LC; WHERE=<<<
*DIM TIME WHAT=2007.DEC; WHERE=2008.FEB
*ENDALLOCATION
After Allocation
Budget – Periodic |
2008.FEB |
---|---|
Global Operations |
50,000 |
Any software coding and/or code lines / strings ("Code") included in this documentation are only examples and are not intended to be used in a productive system environment. The Code is only intended to better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of the Code, except if such damages were caused by SAP intentionally or by its gross negligence.