1. Scripting Reference for Oracle Essbase
  2. MaxL
  3. MaxL Statements (Aggregate Storage)
  4. Execute Allocation (Aggregate Storage)

Execute Allocation (Aggregate Storage)

The MaxL execute allocation statement for ASO mode helps you allocate one or more given source amounts to a target range of cells in an Essbase 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.

Syntax


Description of execalloc.gif follows
Description of the illustration execalloc.gif

Keywords

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:

  • 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

override|add|subtract values

Optional. Generated allocation values can be added to (or subtracted from) existing values, instead of overwriting them. Overwriting is the default.

When performing custom allocations on an aggregate storage cube with a federated partition, you can only override existing values. You cannot add to, nor subtract from, existing values.

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.


execute allocation process 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])"
add values;