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

Syntax diagram for execute allocation.DBS-NAMECUBE-AREA or MDX-SETALLOC-NUMERICMDX Tuple SpecificationCUBE-AREA or MDX-SETMDX Tuple SpecificationCUBE-AREA or MDX-SETMDX Tuple SpecificationMDX Member SpecificationMDX Member SpecificationCUBE-AREA or MDX-SETCUBE-AREA or MDX-SETMDX Tuple SpecificationCUBE-AREA or MDX-SET
KeywordDescription

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

Notes

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])" ;