Performing Custom Calculations and Allocations on Aggregate Storage Databases

Custom calculation functionality depends on a variety of specified criteria. Table 210 provides a brief description of the criteria used to define custom calculations. Review these terms before continuing.

A custom calculation script is a file that you create and store with a .csc extension. Create the custom calculation script with one or a series of tuple-expression pairs in MDX, terminated by semicolons. The syntax:

tuple := numeric_value_expression;

The tuple is an MDX specification of one or more members where no two members can be from the same dimension. The tuple must be on the left side of the equation and is the primary factor in determining where results of the custom calculation are written.

Only member names are allowed in the tuple expression. The use of member functions is not supported for custom calculation scripts.

The numeric_value_expression is a simple MDX numeric value expression, such as a number or an arithmetic operation. The expression must be on the right side of the equation. Only arithmetic operators are permitted. An error is returned if non arithmetic operators (such as AND, OR, or IF statements) are used.

Member names can be used in the numeric value expression, but the use of member functions is not supported for custom calculation scripts.

Attribute members cannot be used on the left side of the equation in a custom calculation script.

You must also define the source region, which serves as a performance hint for Essbase. Essbase pre-fetches the data specified in the source region, and uses that to perform the calculation specified in the script.

For an example of a custom calculation script, and more information about defining the source region, see Sample Use Case for Custom Calculations.

You can execute custom calculations using the MaxL execute calculation statement (aggregate storage version).

You can execute custom calculation scripts using the Essbase API. Use EssPerformCustomCalcASO to perform or verify a custom calculation on an aggregate storage database. Provide the information for the custom calculation in the ESS_PERF_CUSTCALC_T structure.

You can also use Hyperion Calculation Manager to design a custom calculation and deploy it for execution to Enterprise Scheduling Services.

Consider an outline with the following dimensions:

The POV is an MDX set expression indicating where the custom calculation should be executed. It is specified as follows:

CrossJoin( { ( [Company], [101], [Jan], [Scenario] ) },
           Descendants( Geography, Geography.Levels(0)) )

The DebitMember is an MDX member expression indicating a debit member to which positive result values should be written. It is specified as [BeginningBalance_Debit].

The CreditMember is an MDX member expression indicating a credit member to which negative and offsetting result values should be written. It is specified as [BeginningBalance_Credit].

The offset is an MDX tuple expression indicating where offsetting entries should be made. It is specified as ([Account_NA], [Project_NA]).

The offset expression is combined with Target and POV to determine the location where offsetting entries are made. If dimensions overlap, the order for resolving the offset location is the offset, the target, and the POV, in that order.

The target is an MDX tuple expression indicating where to write the results of the custom calculation. It is specified as(Allocation).

The target expression is combined with POV, and the left side of each line in the custom calculation script, to determine the location where results are written. If dimensions overlap, the order for resolving the target location is the left side of the equations, the target, and the POV, in that order. In this example, results are written to the Allocation member, because the target overrides the Scenario member specified in the POV.

The following is an example of a custom calculation script:

(AccountA,Proj1) := 100;
([AccountB], [Proj1]) := ([AccountB], [Proj1]) * 1.1;
(AccountC,Proj1) := 
	((AccountB,Proj1,2007) + (AccountB, Proj1)) / 2;
(AccountA,Proj2) := 
	((AccountD,Proj1) + 
	  (AccountB,Proj2)) / 2;

For each combination in the POV,

To define the source region, examine the custom calculation script and determine which members are referenced on the right sides of equations. At a minimum, the source region should include all members from the right sides of the assignment statements in the custom calculation script.

Define the source region as a single MDX set. If the members on the right sides of the equations are from more than one dimension, you can use CrossJoin to create the set from two sets. CrossJoin only accepts two sets, so you may have to use nested CrossJoins.

The source region for the above custom calculation script is:

Crossjoin(
 {[AccountB], [AccountD]}, 
   Crossjoin(
    {[Proj1], [Proj2]}, {[2007]}
   )
)

It is not necessary to include any members in the source region that are not assigned in the script. For example, if you added to the source region an [AccountC], which is not used in the script, then it would be ignored, and could cause a slight detriment to performance.

It is not necessary to account for numbers in the source region. For example, the following assignment in a custom calculation script requires nothing to be added to source region: ([Bud Var]):=10.

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

Essbase uses various regions in the database when performing allocations. Each region consists of at least one member from each dimension defined in the region.

Allocation parameter values can be expressed in the following ways:

For more information about how to express allocation parameter values, see the Oracle Essbase API Reference.

POV specifies a symmetric region in the database 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. See Setting the Basis.

Range specifies a symmetric region in the database 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 in Table 213, Essbase writes 1 in each cell in the range.

As illustrated in Table 214, 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.

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

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:

You can use these parameters to further define the amount:

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 in Table 216, 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.

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 in Table 217:

(Optional) 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.

You can use these parameters to further define the basis:

Target, when combined with the range, defines the region in the database where allocation values are written. The target can consist of only level 0 members.

You can use these parameters to further define target:

See Understanding Settings for Basis and Target Time Span.

Allocation method specifies whether to allocate the amount evenly or proportionally.

The rounding method specifies 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:

(Optional) Offset specifies the location in the database where an offsetting value for each source amount is written.

Offset works the same for allocations and custom calculations. See Understanding Offset Handling for Custom Calculations and Allocations.

(Optional) debitMember and creditMember can consist only of level 0 members.

The debit member and the credit member must be two different members from the same dimension.

debitMember and creditMember work the same for allocations and custom calculations. See Understanding Credit and Debit Processing for Custom Calculations and Allocations.

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, respectively, as summarized in Table 222. 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.

alloc_amt = (sum_across_basis_time_span/basis_total_range) * amount

alloc_amt = (basis_mbr_value/basis_total_range) * amount

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 Table 233:

The allocation is performed for each of the POV combinations:

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 Table 234:

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. Table 235 shows the allocated share amount for each cost center:

The objective of this sample use case is to use 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:

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:

Formula members can be used in the amount and the basis but not in the target. However, because aggregate storage databases 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:

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: