Execute Allocation

Allocate one or more given source amounts to a target range of cells in an aggregate storage database. The source amount can be allocated to the target proportionately to a given basis, or the source amount can be spread evenly to the target region.

Allocations are typically used in the budgeting process to distribute revenues or costs.

Minimum permission required: execute.

For more information about allocations and to understand the input parameters, see “Performing Custom Calculations and Allocations on Aggregate Storage Databases” in the Oracle Essbase Database Administrator's Guide.

Syntax

Keyword	Description
pov <mdx-set>	Required. Provide an MDX set defining the context region in which the allocation is performed.
amount <alloc-numeric>	Required. Provide an MDX numeric value expression indicating the amount to be allocated.
amountcontext <mdx-tuple>	Optional. Provide an MDX tuple with one member from each dimension missing from pov and amount. This clause is required when amount is an arithmetic expression and pov does not specify two or more dimensions. It should not be used otherwise.
amounttimespan <mdx-set>	Optional. Provide an MDX set indicating one or more time periods to be considered for the amount. The amount value is aggregated over the specified time periods, and the aggregated amount value is allocated. Time periods must be level 0 members in a Time dimension.
target <mdx-tuple>	Required. Provide an MDX tuple defining the database region where results are written.
targettimespan <mdx-set>	Optional. Provide an MDX set indicating one or more time periods to be considered for the target. Time periods must be level 0 members in a Time dimension.
targettimespanoptions	Optional, but required if targettimespan is used. Select a method for allocating values across the target time span: divideamount–Divide the amount evenly across the time periods repeatamount–Repeat the amount across the time periods
offset <mdx-tuple>	Optional. If offsetting entries are used, provide an MDX tuple defining the location in the database where an offsetting value is written for each source amount.
debitmember <mdx-mbr>	Optional. If double-entry accounting is used, provide an MDX member expression indicating the member to which positive result values are written.
creditmember <mdx-mbr>	Optional. If double-entry accounting is used, provide an MDX member expression indicating the member to which negative result values are written.
range <mdx-set>	Required. Provide an MDX set indicating the database region in which allocated values are calculated and written.
excludedrange <mdx-set>	Optional. Provide an MDX set specifying locations in the range where you do not want allocation values written.
basis <mdx-tuple>	Required in most cases. Provide an MDX tuple that, when combined with the range, defines the location of basis values that determine how the amount is allocated. The basis can consist of upper-level or level 0 members. Optional if the allocation method used is spread, and no values are skipped; required otherwise. Basis must be omitted when the allocation method spread is used without skip options.
basistimespan <mdx-set>	Optional. Provide an MDX set that indicates one or more time periods to be considered for the basis. Time periods must be level 0 members in a Time dimension.
basistimespanoptions	Optional, but required if basistimespan is used. Select a method for using the basis time span: splitbasis–Use the basis value for each time period individually combinebasis–Use the sum of the basis values across the time periods specified by basistimespan
share	Optional. Specify to allocate the amount(s) proportionately to the basis values. For syntax, see Allocation Method Specification in Notes.
spread	Optional. Specify to allocate the amount(s) evenly. For syntax, see Allocation Method Specification in Notes. You can include one or more of the following skip options when using spread allocation: skip_missing–Skip missing basis values skip_zero–Skip zero basis values skip_negative–Skip negative basis values
zeroamountoptions	Optional. If omitted, zero or #MISSING amount values are allocated. Otherwise, specify treatment of amount values that are zero or #MISSING: skip_to_next_amount–Skip to the next nonzero, non-#MISSING amount value abort–Cancel the entire allocation operation
zerobasisoptions	Optional. For share, this option specifies the action when the sum of all basis values is zero. For spread, this option specifies the action when all the basis values are skipped. Select one of the following options: skip_to_next_amount–Skip to the next nonzero, non-#MISSING amount value abort–Cancel the entire allocation operation
round	Optional. Specify rounding options. The following options are available: For syntax, see Rounding Method Specification in Notes. Round to a specified number of decimal places, using an integer or MDX numeric value expression. The value must be between 100 and -100, and is truncated if it is not a whole number. Perform rounding, but discard rounding errors Add rounding errors to the highest allocated value Add rounding errors to the lowest allocated value Provide an MDX tuple indicating a cell to which the rounding error should be added

Notes

The clauses following the with keyword can be entered in any order, each separated by white space.
Each clause can only be entered once.
The pov, amount , target, range, and basis clauses are mandatory; the others are optional.
You can specify only stored, level-0 members in all of the clauses except for amount, amountcontext, basis, and the number of rounding digits; for all other arguments, do not use upper-level members, attribute members, or dynamic calc members.

Allocation Method Specification

Syntax diagram for execute allocation ALLOC-METHOD spec.

Rounding Method Specification

Syntax diagram for execute allocation ROUNDING-METHOD spec.

Example

The following statement executes an allocation. For a more complete use case, see “Performing Custom Calculations and Allocations on Aggregate Storage Databases” in the Oracle Essbase Database Administrator's Guide.

execute allocation on database glrpt.db with
pov 			"Crossjoin({[VisionUS]}, 
				Crossjoin({[5740]}, 
				  Crossjoin({[USD]},
				    Descendants([Geography],[Geography].Levels(0)))))"
amount 		"Jan + Feb"
amountcontext	"([100], [Beginning Balance], [Actual], [CostCenter1])" 
target 		"([Allocation], [CostCenter1])"
offset 		"([Allocation], [CostCenter1], [100], [YearNA])"
debitmember 	"[Debit]"
creditmember 	"[Credit]"
range 		"Crossjoin(Descendants([999], [Department].Levels(0)), 
           Descendants([Year], [Year].Levels(0)))"
excludedrange 	"{[9994], [9995], [9996]}"
basis 		"([SQFT], [Balance], [Actual], [CostCenter2])"
share 
zeroamountoptions	abort
zerobasisoptions  abort
negativebasisoptions  zero_value 
targettimespanoptions  divideamount
round			"Currency.CurrentMember.CurrencyPrecision"
errors_to_location 	"([101], [Jan])" ;