1. Database Administrator's Guide for Oracle Essbase
  2. Custom Calculations and Allocations on Aggregate Storage Cubes
  3. Custom Allocations on Aggregate Storage Cubes

Custom Allocations on Aggregate Storage Cubes

Custom allocations enable you to allocate a given source amount to a target range of cells in an Essbase aggregate storage (ASO) cube. Allocations are used in the budgeting process to distribute revenues or costs.

The source amount can be allocated to the target proportionately, based on a given basis, or the source amount can be spread evenly to the target.

You can perform ASO allocations using the MaxL execute allocation statement.

You can also perform ASO allocations by using the using the API, by calling the Java API method IEssPerformAllocation.performAllocation or the C API function EssPerformAllocationAso.

A single allocation has its own POV (point of view), range, amount, basis, target, and, optionally, offset. To perform allocations with different sets of values for these parameters, you must make a sequence of individual API calls.

Allocations are performed in the allocation engine and then written back to the cube using temporary data load buffers that Essbase creates.

List of Allocation Criteria

When you design custom allocations for an Essbase aggregate storage (ASO) cube, you base them on many required and optional criteria, including POV, range, amount, basis, target, allocation method, and rounding method.

Allocations are based on a variety of specified criteria. This topic provides a brief description of the criteria used to define allocations. Review these terms before continuing.

Table 39-2 Description of Allocation Criteria

Criteria Description See

POV

A symmetric region in the database that describes the context in which allocations are performed

Setting the POV

Range

A symmetric region in the database in which allocated values are calculated and written

Setting the Range

(Optional) Excluded range

Locations in the range where you do not want allocation values written

Setting the Range

Amount

The amount to be allocated

Setting the Amount

(Optional) Amount context

Additional context, or specificity, for the amount

Setting the Amount

(Optional) Amount time span

One or more time periods to be considered for the amount

Setting the Amount

(Optional) Zero amount options

Treatment of zero or #MISSING amount values

Setting the Amount

Basis

When combined with the range, defines the location of basis values that determine how the amount is allocated

Setting the Basis

(Optional) Basis time span

One or more time periods to be considered for the basis

Setting the Basis

(Required if basis time span is set)

Basis time span option

Method for calculating the basis across the basis time span:

  • Combine

  • Split

Setting the Basis

Zero basis options

Treatment of zero basis values

Setting the Basis

(Optional) Negative basis options

Treatment of negative basis values

Setting the Basis

Target

When combined with the range, defines the region in the database where allocation values are written

Setting the Target

(Optional) Target time span

One or more time periods to be considered for the target

Setting the Target

(Required if target time span is set)

Target time span option

Method for allocating values across the target time span:

  • Divide the amount

  • Repeat the amount

Setting the Target

Allocation method

Method for allocating the amount:

  • Share: Allocates the amount proportionately to the basis values

  • Spread: Allocates the amount evenly

Setting the Allocation Method

(Optional) Spread skip options

For spread allocation method, whether to skip basis values in the range that are zero, #MISSING, or negative

Setting the Allocation Method

Rounding method

Whether to round allocated values.

If you choose to round, specifies the method for handling rounding errors:

  • Discard rounding errors

  • Add the total rounding error to:

    • The greatest allocated value

    • The lowest allocated value

    • A specific location

Setting the Rounding Method

(Optional) Round digits

The number of decimal places to which allocation values are rounded:

  • To the nearest integer

  • To a specified number of decimal places

  • To a power of 10

Setting the Rounding Method

(Required if rounding method is set to a specific location)

Round to location

The location to which to add the total rounding error

Setting the Rounding Method

(Optional) Offset

The location in the database where an offsetting value for each source amount is written

Setting the Offset

(Optional) Credit and debit members

In double-entry accounting, balancing journal entries for one transaction

Balancing Allocations

Regions in ASO Allocations

Essbase uses source, target, basis, and offset regions in the aggregate storage (ASO) cube when performing custom allocations. Each region consists of at least one member from each dimension defined in the region.

Table 39-3 List of Regions Used in Allocations

Region Name Region Definition Description

Source

(POV X amount [X amount context] X [amount time span])

The region containing the amount values that are to be allocated.

The source region and target region cannot overlap.

Target

(POV X target X debit member/credit member X range X [target time span])

The region containing the locations to which allocated values are written.

The source region and target region cannot overlap.

The target region need not need be empty before performing an allocation. Essbase overwrites non-empty cells either with allocation data or with zeros. For cells with #MISSING, the cells remain #MISSING unless Essbase writes allocation data to those cells.

Basis

(POV X basis X range X [basis time span])

The region containing the basis values that are used to determine how the source amount is allocated.

Basis might override part of the POV.

Offset

(POV X offset X debitMember/creditMember)

The region containing the locations to which offset values are written.

Specifying Allocation Criteria

Use MDX syntax when you specify criteria for custom allocations in an Essbase aggregate storage (ASO) cube.

Allocation parameter values can be expressed in the following ways:

  • MDX member expression

  • MDX set expression

  • MDX tuple expression (where no two members can be from the same dimension)

  • Constant

For more information about how to express allocation parameter values, see the the MaxL execute allocation statement or Oracle Essbase and Provider Services Java API Reference.

Shared Members in Allocation Parameters

When shared members are specified in allocation parameters, Essbase maps the shared members to their prototype members before performing the allocation.

Duplicate Members in Allocation Parameters

When duplicate members are specified in allocation parameters, either because a member name is repeated or because a member and its shared member are both specified, Essbase removes the duplicate members and issues a warning.

Setting the POV

Essbase custom allocations for aggregate storage (ASO) cubes require a POV to be given as one of the allocation criteria. POV specifies a symmetric region in the cube that describes the context in which allocations are performed.

The POV can consist of only level 0 members. The dimensions defined in the POV cannot be used in other parameters, except for the basis and the basis time span.

The allocation is repeated for every combination of members in the POV set. The number of POV combinations is the product of the number of members from dimensions with multiple members. (Dimensions with only one member are not used to calculate the number of combinations.)

For example, assume that the POV consists of two dimensions (CostCenter and Project), and allocations are to be made to two cost centers (CostCenter1 and CostCenter2) and three projects (Project1, Project2, and Project3). The number of POV combinations is six:

Project1,CostCenter1
Project1,CostCenter2
Project2,CostCenter1
Project2,CostCenter2
Project3,CostCenter1
Project3,CostCenter2

The allocation is repeated six times by successively setting the allocation context to each combination.

Values considered as the basis for the allocation are dependent on the POV combination.

Note:

If time periods are specified in the POV, you cannot use the amount time span and the target time span options.

Setting the Range

Essbase custom allocations for aggregate storage (ASO) cubes require a range to be given as one of the allocation criteria. Range specifies a symmetric region in the cube in which allocated values are calculated and written.

If you do not want allocation values written to certain cells within the range, use the excluded range parameter to express a symmetrical subset of the range. Even when excluding a subset of the range, Essbase uses all cells in the range to calculate allocated values.

When you exclude cells from the range, the sum of allocated values might be less than the value of the amount.

The following examples assume that the range consists of six member combinations, the amount is 6, and the allocation method is spread, in which Essbase evenly allocates the amount across the range. The allocation spread amount is 1 (6/6 = 1).

As illustrated below, Essbase writes 1 in each cell in the range.

Table 39-4 Example: Allocating the Amount to Each Member in the Range

- CostCtr1 CostCtr2
Project1 1 1
Project2 1 1
Project3 1 1

As illustrated below, if the excluded range is set to the member combination of (Project2,CostCtr2), Essbase does not write the allocation spread amount to that cell. Therefore, the sum of allocated values (5) is less than the amount (6). The value of the excluded cell after the allocation process is either #MISSING or zero.

Table 39-5 Example: Allocating the Amount to Only Some Members in the Range

- CostCtr1 CostCtr2
Project1 1 1
Project2 1  
Project3 1 1

The range and excluded range can consist of only level 0 members.

Setting the Amount

Essbase custom allocations for aggregate storage (ASO) cubes require an amount to be given as one of the allocation criteria. Amount specifies the source of the allocation. The amount value is allocated to cells in the target region.

The amount, which can consist of upper-level or level 0 members, can be expressed as a numeric value expression, a tuple, or a constant.

How you express the amount determines certain requirements:

  • Numeric value expression:

    • All members in the expression must be from the same dimension.

    • Tuples cannot be used in the expression.

    • Only arithmetic expressions (+, -, /, and *) can be used in the expression.

    • MDX functions (such as Avg and Parent) are not allowed.

    For example:

    (Acc_1000 + Acc_2000)/2
    AccA + AcctB
    Balance * 1.1

  • Tuple:

    • The tuple must use one member from every dimension that is not specified in the POV.

    • The amount context must be empty.

    For example:

    (Balance,Cost_Center_00,Project_00)
    (Balance,Cost_Center_00,Actual)

  • Constant:

    • The amount context must be empty.

    • The amount time span must be empty.

    For example:

    100

You can use these parameters to further define the amount:

  • (Optional) Amount context provides additional context, or specificity, for the amount. The amount context, which can consist of upper-level or level 0 members, can be expressed as a tuple. By specifying the amount context, you can include a member from a dimension that is not specified in the POV.

    When using amount context, these requirements apply to the amount and the amount context:

    • The parameters cannot refer to members in the same dimensions.

    • Together, the parameters must use members from every dimension not specified in the POV.

  • (Optional) Amount time span specifies 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.

    When amount is specified using an arithmetic expression, and amount time span is used, amount time span takes precedence over any formulas in the amount or any formula members used in the amount. For example, assume that the amount is specified as Dept_A/Dept_B and amount time span is set to Jan, Feb, Mar, and Apr for each department, as shown below. The amount to be allocated for the POV is calculated by dividing the amount time span value for Dept_A (10) by the amount time span value for Dept_B (20), which is 0.5.

    Table 39-6 Example: Amount Time Span Takes Precedence Over Formulas in Amount

    Members in Amount Time Span Dept_A Dept_B
    Jan 1 2
    Feb 2 4
    Mar 3 6
    Apr 4 8
    Total 10 20

  • (Optional) Zero amount options specifies how to treat the amount if the value is zero or #MISSING. You can choose to allocate zero values (the default), skip to the next nonzero or non-#MISSING amount value, or cancel the entire allocation operation.

You can use amount context and amount time span to achieve the same result, as shown in following example. The amount is the value of Dept_A, but amount time span is used to focus only on the months of Jan, Feb, Mar, and Apr for Dept_A. As shown below, the aggregated value of the members included in the amount time span (10) is the amount value that is allocated across the cells in the range.

Table 39-7 Example: Amount Time Span

Members in Amount Time Span Dept_A
Jan 1
Feb 2
Mar 3
Apr 4
Total 10

You can achieve the same amount value by specifying amount as an arithmetic expression of Jan + Feb + Mar + Apr and setting the amount context as Dept_A, as shown below:

Table 39-8 Example: Amount Context

Amount Context Jan Feb Mar Apr Total
Dept_A 1 2 3 4 10

Setting the Basis

Essbase custom allocations for aggregate storage (ASO) cubes usually require a basis to be given as one of the allocation criteria. Basis, 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.

Basis is optional if the allocation method used is spread, and no values are skipped. Basis must be omitted when the allocation method spread is used without skip options.

You can use these parameters to further define the basis:

  • (Optional) Basis time span specifies one or more time periods to be considered for the basis. Time periods must be level 0 members in a Time dimension.

  • (Required if basis time span is set) Basis time span option specifies how the basis is calculated across the time periods specified by the basis time span. You can choose to use the basis value for each time period individually (split) or use the sum of the basis values across the time periods specified by the basis time span (combine).

    • If basis time span specifies multiple time periods and the target time span specifies one time period or is empty, you must set the basis time span option to combine. Essbase ignores the target time span option.

    • If basis time span and target time span specifies multiple time periods, and you set the basis time span option to split, the periods specified by basis time span and target time span must be identical. Essbase ignores the target time span option.

  • Zero basis options specifies how to treat a zero basis value. You can choose to skip to the next nonzero or non-#MISSING amount value or cancel the entire allocation operation. Essbase processes the zero basis options setting based on the allocation method.

  • (Optional) Negative basis options specifies how to treat a negative basis value. The options available for the negative basis options depend on the allocation method used.

Note:

The basis is ignored when using the spread allocation method and you have not set any spread skip options.

Setting the Target

Essbase custom allocations for aggregate storage cubes require a target to be given as one of the allocation criteria. Target, when combined with the range, defines the region in the ASO cube where allocation values are written.

The target can consist of only level 0 members.

You can use these parameters to further define target:

  • (Optional) Target time span specifies one or more time periods to be considered for the target. Time periods must be level 0 members in a Time dimension.

  • (Required if target time span is set) Target time span option specifies the method for allocating values across the time periods specified in target time span. You can choose to divide the amount value or repeat the amount value across the specified time periods.

    • If basis time span specifies multiple time periods, and the target time span specifies one time period or is empty, you must set the basis time span option to combine. Essbase ignores the target time span option.

    • If basis time span and target time span specifies multiple time periods, and you set the basis time span option to split, the periods specified by the basis time span and target time span must be identical. Essbase ignores the target time span option.

Setting the Allocation Method

When designing custom allocations for aggregate storage (ASO) cubes, you can specify an allocation method to tell Essbase whether to allocate the amount evenly (spread) or proportionally (share).

  • The share method allocates a percentage of the amount (alloc_share_amt) by dividing the basis value for the current member in the range (basis_mbr_value) by the sum of the basis across the range (basis_range_sum). The allocated amounts are based on the number of valid basis values in the range. The algorithm for calculating the allocation share amount: 

    alloc_share_amt = (basis_mbr_value/basis_range_sum) * amount

    Basis values and Essbase action:

    • Zero, Essbase writes a zero to the corresponding target cell.

      If the sum of all basis values is zero (which would result in a division-by-zero error), Essbase uses the zero basis options setting.

    • #MISSING, Essbase either leaves the target cell as #MISSING, or, if the target cell already has a value, overwrites the existing value with zero.

    • A negative number, Essbase uses the negative basis options setting. You can choose to use the negative basis value (the default), skip to the next amount value (no data is allocated for the current amount value, and Essbase skips to the next POV combination), or cancel the entire operation.

    The following examples illustrate the share allocation method. In both examples, the amount to allocate is 10.

    In the following example, assume that the amount (10) represents the rent expense for a building, and the basis represents the head count of each department in the range. Essbase uses the basis values for departments with non-#MISSING head count (Dept_A through Dept_D) to calculate the allocation share amounts, which is the rent allocation.

    The rent allocation for Dept_A is the basis value of Dept_A (3), divided by the sum of valid basis values across the range (3 + 2 = 5), multiplied by the amount (10): 3/5 * 10 = 6. For Dept_D, the rent allocation is 2/5 * 10 = 4. The total of the target cells in the range equals 10.

    Table 39-9 Share Allocation Method Example

    Members in Range Basis (Head Count) Target (Rent Allocation)
    Dept_A 3 6
    Dept_B    
    Dept_C 0 0
    Dept_D 2 4

    In the following example, assume that all basis values are to be considered in calculating the share allocation amounts. The allocation for Mbr1 is the basis value of Mbr1 (3), divided by the sum of valid basis values across the range (3 + -1 + 2 = 4), multiplied by the amount (10): 3/4 * 10 = 7.5. For Mbr3, the allocation is -1/4 * 10 = -2.5; for Mbr4, the allocation is 2/4 *10 = 5. The total of the target cells in the range equals 10.

    Table 39-10 Share Allocation Method Example: Negative Basis Options — Default

    Members in Range Basis Target
    Mbr1 3 7.5
    Mbr2 #MISSING  
    Mbr3 -1 -2.5
    Mbr4 2 5.0

  • The spread method allocates the amount evenly across the range (alloc_spread_amt). The number used to divide the amount and, therefore, the number of target cells where the allocation spread amount is to be written, is based on the number of valid basis values in the range (#_valid_basis_values). The algorithm for calculating the allocation spread amount: 

    alloc_spread_amt = amount/#_valid_basis_values

    When using the spread allocation method, you can use the optional spread skip options parameter to skip all basis values in the range that are zero, #MISSING, or negative. You can specify multiple options.

    Basis values and Essbase action:

    • Zero, Essbase writes a zero to the corresponding target cell.

      If spread skip options is set to skip zero, no data is allocated.

    • #MISSING, Essbase either leaves the target cell as #MISSING; or, if the target cell already has a value, Essbase overwrites the existing value with zeros.

      If spread skip options is set to skip #MISSING, no data is allocated.

    • A negative number, Essbase uses the negative basis options setting (which takes precedence over the spread skip options setting of skip negative). You can choose one of the following actions:

      • Use the negative basis value (the default)

      • Skip to the next amount value (no data is allocated for the current amount value)

      • Use the absolute value of the negative number

      • Treat the negative number as $MISSING (no value is allocated to the target cell)

      • Treat the negative number as a zero (zero is allocated to the target cell)

      • Cancel the entire operation

    If all basis values have been skipped (which would make the denominator in the allocation zero), Essbase uses the zero basis options setting. See Setting the Basis.

    The following examples illustrate the spread allocation method. In both examples, the amount to allocate is 10.

    In the following example, assume that the spread skip options parameter is not specified. Therefore, Essbase considers all four basis members in the range. Essbase divides the amount (10), by the number of valid basis members in the range (4), and spreads that value (2.5) to each target cell in the range: 10/4 = 2.5.

    Table 39-11 Spread Allocation Method Example: Do Not Skip Basis Values

    Members in Range Basis Target
    Mbr1 2 2.5
    Mbr2 #MISSING 2.5
    Mbr3 3 2.5
    Mbr4 -6 2.5

    In the following example, assume that the spread skip options parameter is set to ignore #MISSING and negative numbers. Therefore, Essbase considers only the two basis members with positive values (Mbr1 and Mbr3). Essbase divides the amount (10), by the number of valid basis members in the range (2), and spreads that value (5) to the Mbr1 and Mbr3 target cells: 10/2 = 5.

    Table 39-12 Spread Allocation Method Example: Skip #MISSING and Negative Basis Values

    Members in Range Basis Target
    Mbr1 2 5
    Mbr2 #MISSING  
    Mbr3 3 5
    Mbr4 -6  

Setting the Rounding Method

When designing custom allocations for aggregate storage (ASO) cubes, you can specify a rounding method to tell Essbase whether to round allocated values (the default is not to round).

If you choose to round values, the rounding method specifies how to handle rounding errors. You can choose to discard rounding errors, or to round all allocated values and add the total rounding error to the highest allocated value, the lowest allocated value, or to a specific cell. If you choose to add the rounding error to the highest or lowest allocated value, and there are multiple highest or lowest allocated values, Essbase chooses one of the highest or lowest values to which to add the rounding error.

If you choose to round allocation values, you can use these parameters to further define the rounding method:

  • (Required if rounding allocated values) Round digits specifies the number of decimal places to which allocated values are rounded. You can choose to round to the nearest integer (the default), to a specified number of decimal places, or to a power of 10.

    Round digits must be a number from -100 to 100 and can be expressed as an integer, an MDX numeric value expression, or a tuple.

    Using an MDX numeric value expression is helpful when the setting for round digits is based on the currency of the allocated value. For example, assume that the database contains a dimension named Currency, which is part of the POV, and an associated attribute dimension named NumCurrencyDigits, which specifies how to round allocated values based on the currency of the allocated values. You can express round digits as:

    Currency.currentMember.NumCurrencyDigits

    Note:

    If, for the rounding method, you choose not to round allocated values, the round digits parameter value must be 0 (which is the default). If you want to round to 0 digits, the parameter value must be -1.

  • (Required if rounding method is set to a specific location) Round to location specifies a cell to which to add the total rounding error. Expressed as a tuple, the cell must be in the range and have the same dimensionality as the range. Round to location can consist of only level 0 members.

    Note:

    If, for the rounding method, you choose an option other than to round to a specific cell, the round to location parameter must be empty.

Setting the Offset

When designing custom allocations for Essbase aggregate storage (ASO) cubes, you can optionally specify an offset. Offset specifies the location in the cube where an offsetting value for each source amount is written.

Offset works the same for allocations and custom calculations.

Balancing Allocations

When designing custom allocations for Essbase aggregate storage (ASO) cubes, the optional debitMember and creditMember can consist only of level 0 members.

The debit member and the credit member are optional. If used, they must be two different members from the same dimension.

debitMember and creditMember work the same for allocations and custom calculations.

Basis and Target Time Span

When designing custom allocations for Essbase aggregate storage (ASO) cubes, you can specify one or more time periods to be considered for the basis, and one or more time periods to be considered for the target.

The number of members that are specified for the basis time span and target time span affect how Essbase treats the basis time span option and target time span option.

In situations where the basis or target time span is empty, or set to a single time period, Essbase ignores any setting that you might have set for the respective basis or target time span option. In situations where one or both of the basis or target time spans are set to multiple time periods, Essbase requires a particular setting for the respective basis or target time span option.

Table 39-13 Summary: Basis and Target Time Span, and Basis and Time Span Option

Basis Time Span Target Time Span Basis Time Span Option Target Time Span Option See
Empty or single member Empty or single member Ignored Ignored Example 1
Empty or single member Multiple members Ignored Divide or repeat Example 2
Multiple members Empty or single member Combine Ignored Example 3
Multiple members Multiple members Split Ignored Example 4
Multiple members Multiple members Combine Divide or repeat Example 5

Example 1: Basis and Target Time Span—Empty or Single Member

In this example, the basis time span and target time span are not set or are set for only one time period. Essbase ignores any setting you might have chosen for the basis time span option or target time span option.

Example 2: Basis Time Span—Empty or Single Member; Target Time Span—Multiple Members

In this example, the basis time span is not set, and multiple time periods are specified for the target time span. The basis time span option is ignored. For the target time span option, you can select either divide or repeat.

Assume the amount is 1000. The example below shows the basis for each department (Dept_1 = 1) and the total basis for the range (21):

Table 39-14 Example 2: Basis Values

Image of a space is used for empty thead cells Range Total
Image of a space is used for empty thead cells Dept_1 Dept_2 Dept_3 Dept_4 Dept_5 Dept_6 Image of a space is used for empty thead cells

Basis

1Foot 1

 2 3 4 5 6

21Foot 2

Footnote 1

Basis for the member

Footnote 2

Total basis for the range

The setting for target time span option determines how the allocation is calculated.

  • Repeat the allocated amount across the specified target time span periods:

    In this scenario, Essbase performs the allocation for a single period and copies the allocated amount value to all members in the target time span.

    The algorithm Essbase uses: 

    alloc_amt = (basis_mbr_value/basis_total_range) * amount

    As shown below, for Dec 07,Dept_1, the member basis value (1) is divided by the total basis across the range (21), and the result (0.04762) is multiplied by amount (1000): (1/21) * 1000 = 47.62. Essbase copies 47.62 into the cells for Jan 08, Feb 08, Mar 08, and Apr 08. Essbase continues to perform allocations for Dec 07 for each department. For each target time span, the sum of the allocated values across the range equals the amount (1000).

    Table 39-15 Example 2: Allocation Using Target Time Span Option Set to Repeat

    Image of a space is used for empty thead cells Range Total
    Members in Target Time Span Dept_1 Dept_2 Dept_3 Dept_4 Dept_5 Dept_6 Image of a space is used for empty thead cells
    Dec 07 47.62 95.24 142.86 190.48 238.10 285.71 1000
    Jan 08 47.62 95.24 142.86 190.48 238.10 285.71 1000
    Feb 08 47.62 95.24 142.86 190.48 238.10 285.71 1000
    Mar 08 47.62 95.24 142.86 190.48 238.10 285.71 1000
    Apr 08 47.62 95.24 142.86 190.48 238.10 285.71 1000

    Image of a space is used for empty thead cells

    		            

    5000Foot 3

    Footnote 3

    Total allocated values

    The total allocated amount is the original amount value (1000) multiplied by the number of target time span members (5): 1000 * 5 = 5000.

  • Divide the allocated amount across the specified target time span periods

    In this scenario, Essbase performs the allocation for one period and evenly divides the allocated amount across all members in the target time span.

    The algorithm Essbase uses: 

    alloc_amt = ((basis_mbr_value/basis_total_range) * amount)/#_target_time_span_periods

    As shown below, for Dec 07,Dept_1, Essbase performs the same calculation as described for the repeat target time span option scenario to arrive at 47.62. However, this amount is evenly divided across all five target time span periods for Dept_1; therefore, 9.52 is written in each target cell: 47.62/5 = 9.52. Essbase continues to perform allocations for each department. For each target time span, the sum of the allocated values across the range equals (200).

    Table 39-16 Example 2: Allocation Using Target Time Span Option Set to Divide

    Image of a space is used for empty thead cells Range Total
    Members in Target Time Span Dept_1 Dept_2 Dept_3 Dept_4 Dept_5 Dept_6 Image of a space is used for empty thead cells
    Dec 07 9.52 19.05 28.57 38.10 47.62 57.14 200
    Jan 08 9.52 19.05 28.57 38.10 47.62 57.14 200
    Feb 08 9.52 19.05 28.57 38.10 47.62 57.14 200
    Mar 08 9.52 19.05 28.57 38.10 47.62 57.14 200
    Apr 08 9.52 19.05 28.57 38.10 47.62 57.14 200

    Image of a space is used for empty thead cells

    		            

    1000Foot 4

    Footnote 4

    Total allocated values

    The total allocated values across the range is the original amount value (1000): 200 * 5 = 1000.

Example 3: Basis Time Span—Multiple Members; Target Time Span—Empty or Single Member

In this example, multiple time periods are specified for the basis time span, but the target time span is not set. The target time span option is ignored. The only valid choice for the basis time span option is combine.

Assume the amount is 1000. As shown below, the basis to be used for each department is the sum of the basis values for the basis time span (Dept_1 = 15); the total basis for the range is the sum of all department basis values (147):

Table 39-17 Example 3: Basis Values

Image of a space is used for empty thead cells Range Total
Members in Basis Time Span Dept_1 Dept_2 Dept_3 Dept_4 Dept_5 Dept_6 Image of a space is used for empty thead cells
Dec 07 1 2 3 4 5 6  
Jan 08 2 3 4 5 6 7  
Feb 08 3 4 5 0 7 8  
Mar 08 4 5 6 1 8 9  
Apr 08 5 6 7 2 9 10  
Total

15Foot 5

 20 25 12 35 40

147Foot 6

Footnote 5

Basis for each range member summed across the basis time span periods

Footnote 6

Total basis for the range

The allocation is calculated using the basis time span setting of combine, which uses the sum of the basis values across the basis time span periods.

The algorithm Essbase uses for each range member: 

alloc_amt = (sum_across_basis_time_span/basis_total_range) * amount

As shown below, the allocated value for each department is written to one target location, because the target time span is not set to multiple periods. For the allocated amount for Dept_1, the sum of the basis time span (15) is divided by the total basis for the range (147), and the result (0.10204) is multiplied by amount (1000): (15/147) * 1000 = 102.04. Essbase continues to perform allocations for each department in the range.

Table 39-18 Example 3: Allocation Using Basis Time Span Set to Combine

Image of a space is used for empty thead cells Range Total
Image of a space is used for empty thead cells Dept_1 Dept_2 Dept_3 Dept_4 Dept_5 Dept_6 Image of a space is used for empty thead cells
Target 102.04 136.05 170.07 81.63 238.10 272.11

1000Foot 7

Footnote 7

Total allocated values

The total allocated values across the range is the original amount value (1000).

Example 4: Basis and Target Time Span—Multiple Members; Basis Time Span Option—Split

In this example, multiple time periods are specified for the basis time span and the target time span, and the basis time span option is set to split. When using the split basis time span option, the periods specified by the basis time span and target time span must be identical.

Assume the amount is 1000. As shown below, the total basis for the range is the sum of all department basis values (165):

Table 39-19 Example 4: Basis Values

Image of a space is used for empty thead cells Range Total
Members in Basis Time Span Dept_1 Dept_2 Dept_3 Dept_4 Dept_5 Dept_6 Image of a space is used for empty thead cells
Dec 07 1 2 3 4 5 6

21Foot 8
Jan 08 2 3 4 5 6 7 27
Feb 08 3 4 5 6 7 8 33
Mar 08 4 5 6 1 8 9 39
Apr 08 5 6 7 2 9 10 45

Image of a space is used for empty thead cells

            

165Foot 9

Footnote 8

Total basis for each basis time span period

Footnote 9

Total basis for the range

The allocation is calculated using the basis time span setting of split, which uses the basis value for each time period individually.

The algorithm Essbase uses: 

alloc_amt = (basis_mbr_value/basis_total_range) * amount

As shown below, for Dec 07,Dept_1, the member basis value (1) is divided by the total basis for the range (165), and the result (0.00606) is multiplied by amount (1000): (1/165) * 1000 = 6.06. Essbase continues to perform allocations for each time period for each department.

Table 39-20 Example 4: Allocation Using Basis Time Span Set to Split

Image of a space is used for empty thead cells Range Total
Members in Target Time Span Dept_1 Dept_2 Dept_3 Dept_4 Dept_5 Dept_6 Image of a space is used for empty thead cells
Dec 07 6.06 12.12 18.18 24.24 30.30 36.36 127.27
Jan 08 12.12 18.18 24.24 30.30 36.36 42.42 163.64
Feb 08 18.18 24.24 30.30 36.36 42.42 48.48 200.00
Mar 08 24.24 30.30 36.36 42.42 48.48 54.55 236.36
Apr 08 30.30 36.36 42.42 48.48 54.55 60.61 272.73

Image of a space is used for empty thead cells

            

1000Foot 10

Footnote 10

Total allocated values

The total allocated values across the range is the original amount value (1000).

Example 5: Basis and Target Time Span—Multiple Members; Basis Time Span Option—Combine

In this example, multiple time periods are specified for the basis and target time spans; however, because the basis time span option is set to combine, the basis and target time spans need not contain the same member set.

Assume the amount is 1000. As shown below, the basis to be used for each department is the sum of the basis values across the basis time span (Dept_1 = 10); the basis for the range is the sum of all department basis values (113):

Table 39-21 Example 5: Basis Values

Image of a space is used for empty thead cells Range Total
Members in Basis Time Span Dept_1 Dept_2 Dept_3 Dept_4 Dept_5 Dept_6 Image of a space is used for empty thead cells
Dec 07 1 2 3 4 5 6  
Jan 08 2 3 4 5 6 7  
Feb 08 3 4 5 6 7 8  
Mar 08 4 5 6 0 8 9  

Total

10Foot 11

 14 18 15 26 30

113Foot 12

Footnote 11

Basis for each range member summed across the basis time span periods

Footnote 12

Total basis for the range

The setting for target time span option determines how the allocation is calculated.

  • Repeat the allocated amount across the specified target time periods:

    In this scenario, Essbase performs the allocation for a single period and copies the allocated amount value to all members in the target time span.

    The algorithm Essbase uses for each range member: 

    alloc_amt = (sum_across_basis_time_span/basis_total_range) * amount

    As shown below, for Dec 07,Dept_1, the basis for Dept_1 (10) is divided by the total basis for the range (113), and the result (0.0885) is multiplied by amount (1000): (10/113) * 1000 = 88.50. Essbase copies 88.50 into the cells for Jan 08, Feb 08, Mar 08, and Apr 08. Essbase continues to perform allocations for Dec 07 for each department. For each target time span, the sum of the allocated values across the range equals the amount (1000).

    Table 39-22 Example 5: Allocation Using Target Time Span Option Set to Repeat

    Image of a space is used for empty thead cells Range Total
    Members in Target Time Span Dept_1 Dept_2 Dept_3 Dept_4 Dept_5 Dept_6 Image of a space is used for empty thead cells
    Dec 07 88.50 123.89 159.29 132.74 230.09 265.49 1000
    Jan 08 88.50 123.89 159.29 132.74 230.09 265.49 1000
    Feb 08 88.50 123.89 159.29 132.74 230.09 265.49 1000
    Mar 08 88.50 123.89 159.29 132.74 230.09 265.49 1000
    Apr 08 88.50 123.89 159.29 132.74 230.09 265.49 1000

    Image of a space is used for empty thead cells

    		            

    5000Foot 13

    Footnote 13

    Total allocated values

    The total allocated values is the original amount value (1000) multiplied by the number of target time span members (5): 1000 * 5 = 5000.

  • Divide the allocated amount across the specified target time periods:

    In this scenario, Essbase performs the allocation for a single period and evenly divides the allocated amount across all members in the target time span.

    The algorithm Essbase uses: 

    alloc_amt = ((basis_time_span/basis_total_range) * amount)/#_target_time_span_periods

    As shown below, Essbase performs the same calculation as described for the repeat target time span option scenario to arrive at 88.50. However, this amount is evenly divided across all five target time span periods for Dept_1; therefore, 17.70 is written in each target cell: 88.50/5 = 17.70. Essbase continues to perform allocations for each department. For each target time span, the sum of the allocated values across the range equals (200).

    Table 39-23 Example 5: Allocation Using Target Time Span Option Set to Divide

    Image of a space is used for empty thead cells Range Total
    Members in Target Time Span Dept_1 Dept_2 Dept_3 Dept_4 Dept_5 Dept_6 Image of a space is used for empty thead cells
    Dec 07 17.70 24.78 31.86 26.55 46.02 53.10 200
    Jan 08 17.70 24.78 31.86 26.55 46.02 53.10 200
    Feb 08 17.70 24.78 31.86 26.55 46.02 53.10 200
    Mar 08 17.70 24.78 31.86 26.55 46.02 53.10 200
    Apr 08 17.70 24.78 31.86 26.55 46.02 53.10 200

    Image of a space is used for empty thead cells

    		            

    1000Foot 14

    Footnote 14

    Total allocated values

    The total allocated values across the range is the original amount value (1000): 200 * 5 = 1000.

Examples of Aggregate Storage Allocations

The following example illustrates how changing the POV combination affects the values considered as the basis for the allocation. The example, which uses the share allocation method, allocates the total rent from the previous year to all cost centers in the current year, based on each cost center's head count. Assume that the aggregate storage database has four dimensions—Departments, Time, CostCenter, and Measures—and the allocation criteria is specified as shown in the following example:

Table 39-24 POV Example: Allocation Criteria

Criteria Definition
POV Dept_A, Dept_B
Amount

2007, CCNA, TotalRent

Assume that the amount values are:

  • Dept_A = 1000

  • Dept_B = 2000
Basis Jan 2008, Head count
Target Jan 2008, RentalAllocation
Range Level 0 descendants of CostCenter

Assume that the range evaluates to the following cost centers:

  • CostCenter1

  • CostCenter2

  • CostCenter3

  • CostCenter4

The allocation is performed for each of the POV combinations:

  • Dept_A

  • Dept_B

Each POV combination has its own set of basis values that are used in calculating the allocation: the head count for each cost center in the range and the total Jan 2008 head count, as shown in the following example:

Table 39-25 POV Example: Basis Values for Each POV Combination

Image of a space is used for empty thead cells Member Basis Values Range Basis Value
POV CostCenter1 CostCenter2 CostCenter3 CostCenter4 2008 Head Count Total
Dept_A 1 2 3 5 11
Dept_B 5 0   10 15

For each POV, Essbase divides the head count of each cost center (the basis value of each member) by the total head count of the range (the basis value of the range), and then multiplies that value by the total rental amount for each department (amount). For example, for Dept_A,CostCenter1, the member basis value (1) is divided by the basis of the range (11), and the result (0.09090909) is multiplied by amount (1000): (1/11) * 1000 = 90.90909. For Dept_B,CostCenter1, the allocated amount is 666.6667: (5/15) * 2000 = 666.6667. The following example shows the allocated share amount for each cost center:

Table 39-26 POV Example: Target Values for Each POV Combination

Image of a space is used for empty thead cells Member Target Values Amount Value
POV CostCenter1 CostCenter2 CostCenter3 CostCenter4 Rental Allocation Total
Dept_A 90.90909 181.8182 272.7273 454.5455 1000
Dept_B 666.6667 0   1333.333 2000

Sample Use Case for Aggregate Storage Allocations

This sample use case for a custom allocation on an Essbase aggregate storage (ASO) cube uses the share allocation method to proportionally redistribute the total monthly rent expense across departments, based on the square footage each department occupies.

Consider an outline with the following dimensions:

  • Company: Contains multiple ledgers. The rent expense allocations take place in the Vision US ledger.

  • Department: Contains the following members:

    • 100, which stores the total monthly rent expense, of $100,000, for Vision US. This amount is proportionally allocated to the children of department 999.

    • 999, which is the parent of the following departments:

      • 101, which receives 45% of the rent allocation

      • 102, which receives 30% of the rent allocation

      • 103, which receives 25% of the rent allocation

  • Account: Contains the following members:

    • 5740, which is a rent expense account

    • SQFT, which is a statistical account used to record square footage for each department

  • AmountType: Contains PeriodActivty, which is the parent of the following members:

    • PeriodActivityDebit, which is the target location

    • PeriodActivityCredit, which is the offset location

You can accomplish the rent expense allocation in several ways, each with the same result. Two scenarios are presented. For each scenario, assume that the following parameters are defined as follows:

  • Allocation method: Share

  • Range: The descendents of department 999:

    • 101

    • 102

    • 103

    No cells in the range are excluded.

  • Basis: The square footage of each range member for the period of activity (which is monthly). 

    SQFT,PeriodActivity

  • Zero amount option: (Default) Allocate zero amount values.

  • Zero basis option: If the basis value is zero, cancel the allocation operation.

  • Basis time span option: (Default) Split, use the basis value for each time period individually.

  • Rounding method: Round allocation values to the nearest 1,000 and add the total rounding error to department 101.

  • Debit member: If the sum is positive, write the value to PeriodActivityDebit.

  • Credit member: Write the value to PeriodActivityCredit.

Scenario 1: Aggregate Storage Allocations

For scenario 1, assume the following parameters are defined as follows:

  • POV: Consists of one member, Vision US, from the Company dimension.

    Because only one member from one dimension is specified, the POV does not change and, therefore, the allocation is performed only once.

  • Amount: The source value of the allocation is from the following cross-dimensional member: 

    5740,100,Beginning Balance

  • Target: Write allocated values to account 5740 for each department.

  • Offset: Write the offsetting entry to member 5740,100.

Scenario 2: Aggregate Storage Allocations

For scenario 2, assume the following parameters are defined as follows:

  • POV: Consists of one member each from two dimensions:

    • Vision US, from the Company dimension

    • 5740, from the Amount dimension

      In this scenario, account 5740 is a part of the POV. In the basis, account 5740 is overridden with member SQFT.

    Because only one member from each dimension is specified, the POV does not change and, therefore, the allocation is performed only once.

  • Amount: The source value of the allocation is from the following cross-dimensional member: 

    100,Beginning Balance

  • Target: Not set. Because the combination of POV, target, debit member/credit member, and range uses members from all dimensions, the target can be empty.

  • Offset: Write the offset entry to department 100.

Data Consistency and Formulas

When designing custom allocations for Essbase aggregate storage (ASO) cubes, you can use formulas in the amount and the basis, but not in the target. However, because ASO cubes do not support transaction semantics, you might experience data inconsistency issues when using formula members in the amount or the basis.

In the following example, User 1 posts revenue values for a set of departments and User 2 performs an allocation of bonus money, for which the year-to-date revenue for each department is basis of the allocation. The order in which these operations are performed affects the result:

  • Scenario 1: User 1 posts revenue before User 2 runs the allocation.

    The allocation results are based on the updated revenue values.

  • Scenario 2: User 2 runs the allocation before User 1 posts revenue.

    The allocation results are based on prior revenue values, not on the updated revenue values.

  • Scenario 3: User 1 posts revenue and User 2 runs the allocation concurrently.

    The allocation results are based on the updated or prior revenue values, depending on which user operation started first.

    Oracle does not recommend running these operations concurrently when using formula members in the amount or the basis.

Also, assume that an MDX formula is used to calculate the year-to-date revenue for the allocation. The complexity of the formula can affect the result:

  • Scenario 4: The year-to-date revenue formula involves members from one dimension and uses only the following arithmetic expressions: +, -, /, and *.

    The allocation results are based entirely on either the updated or prior revenue values.

    Oracle recommends using simple MDX formulas, as described in scenario 4.

  • Scenario 5: The year-to-date revenue formula is more complicated than the formula in scenario 4.

    It is possible that some of the allocation results are based on the updated revenue values and some are based on the prior revenue values.