Custom Calculations on Aggregate Storage Cubes

Custom calculations extend the analytical capabilities of Essbase by enabling the execution of recurring calculations on aggregate storage (ASO) cubes. You can target certain cube areas to calculate, providing debit, credit, and offset information for accounting.

You can write custom calculations for aggregate storage cubes that update target level 0 cells. Custom calculation scripts are expressed in MDX.

Using custom calculations, you can do basic math on account balances in a general ledger and write the results to targeted level 0 members of an Essbase aggregate storage cube. You can perform calculations on account balances or on fixed amounts and can be scheduled to repeat every accounting period.

Custom calculations can be useful when the cube is used for general ledger reporting, where double-entry accounting is in effect. Debit items, such as assets and expenses, must balance with credit items, such as equity and revenue.

Use the following workflow to create and execute custom calculations:

Create a calculation script expressed in MDX.
Select an area of the cube where the calculation will be executed. You provide the area at execution time using the target and POV (point of view) parameters.
If you use debit and credit processing, select the debit and credit members in the outline to write the positive and negative values. You provide these parameters at execution time.
If you use offsetting entries, select the area where offsetting entries should be made. You provide this parameter at execution time using an MDX tuple. If an offset is not specified or is empty, the offset calculation is not performed.

Note:

In general ledger bookkeeping, an offsetting entry is a counterbalancing measure on the opposite side of the ledger; for example, a $100 credit in January may have a $100 offset added to the debit side of the ledger, so the ledger can be balanced in preparation for an upcoming expense of that amount.
Execute the custom calculation script.

List of Custom Calculations Criteria

When you design custom calculations for Essbase aggregate storage (ASO) cubes, the critera to consider include: POV (context region), the MDX calculation script, the target (where results are written), the source region (referenced by formulas), an optional offset value, and optional credit and debit members.

Custom calculation functionality depends on a variety of specified criteria. Review these terms before continuing.

Table 38-1 Description of Custom Calculations Criteria

Criteria	Description
POV	A symmetric region in the database that describes the context in which custom calculations are performed. Attribute members cannot be used for this argument.
Calculation script	A calculation script expressed in MDX. Attribute members cannot be used in the left side of the equation.
Target	A tuple argument expressed in MDX that defines the region in the cube where calculation results are written. This argument is combined with left side of each formula and the offset to determine where the results and offset values are written. Attribute members cannot be used for this argument.
(Optional) Offset	The location in the database where an offsetting value for each source amount is written. Attribute members cannot be used for this argument.
(Optional) Credit and debit members	In double-entry accounting, balancing journal entries for one transaction. Both are MDX member expressions. The debit member indicates a member to which positive result values are written, and the credit member indicates a member to which negative result values are written. Attribute members cannot be used for this argument.
Source region	An MDX set expression specifying the region of the cube referred to by the formulas in the script.

Write and Run ASO Custom Calculations

Write and execute custom calculations for Essbase aggregate storage cubes to make updates to target level 0 cells. Custom calculation scripts are expressed in MDX. To run the scripts, use MaxL execute calculation.

Writing Custom Calculations

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 MDX functions is not supported for custom calculation scripts.

Note:

The secondary factor determining the target for results is the target parameter, and the third factor is the POV parameter. You specify the second and third parameters at calculation execution time, rather than as part of the calculation script.

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.

Executing Custom Calculations

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

You can also run custom calculation scripts using the API, by calling the Java API method IEssPerformCustomCalc.performCustomCalc, or the C API function EssPerformCustomCalcASO.

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

Sample Use Case for Custom Calculations

This sample use case for a custom calculation on an Essbase aggregate storage (ASO) cube utilizes a credit member, a debit member, and an offset to perform business rental expense allocations.

Consider an outline with the following dimensions:

Company, containing CompanyA, CompanyB, and other children.
Department, containing numbered departments such as 101, 102, 103.
Account, in which Account 5740 is a rent expense account and SQFT is a statistical account used to record square footage for each department.
Scenario, in which the Actual member is where data is posted, and the Allocation member is where allocations and custom calculations are stored. The Scenario member is a parent that aggregates the child members Actual and Allocation.
Year, a time dimension organized by months and quarters.
Geography, a dimension organized by states and cities.
AmountType, in which Debit is the target, and Credit is the offset.
Project, a dimension containing projects such as Proj1, Proj2.

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].

Note:

The offset is written to the debit member in the case that the sum of all result values is negative.

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,

The calculation script is executed in the context of the current POV combination.
One offset value is written to the target location.

Note:

Each formula (line in the calculation script) is executed simultaneously, rather than sequentially. Therefore, you cannot use the result of one formula in a subsequent formula.

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.

Optimize Custom Calculations with NONEMPTYTUPLE

Using the NONEMPTYTUPLE property, you can optimize an Essbase aggregate storage (ASO) custom calculation script to conserve memory resources by skipping empty tuples when calculating over large, sparse data sets.

Because large data sets can be very sparse, using the NONEMPTYTUPLE property in custom calculation scripts can optimize your script to conserve memory resources.

You create a custom calculation script with one or a series of tuple-expression pairs in MDX, terminated by semicolons. You can optionally filter out empty result sets from being calculated, by including the NONEMPTYTUPLE property clause in the custom calculation script.

Using the NONEMPTYTUPLE property clause in a custom calculation script indicates to Essbase that the cell value being calculated for a tuple is empty whenever the given nonempty_member_list is empty.

Syntax

use_optimized_way;
tuple := [NONEMPTYTUPLE (nonempty_member_list)] numeric_value_expression;

Where

use_optimized_way—a literal keyword, required to enable the use of NONEMPTYTUPLE property in the calculation script. If omitted, NONEMPTYTUPLE directives are ignored.
tuple—an MDX specification of one or more members, where no two members can be from the same dimension.
NONEMPTYTUPLE—an optional property you can use to optimize calculation performance. If used, then you must follow this literal property with nonempty_member_list.
nonempty_member_list—one or more comma-separated member names from different dimensions.
numeric_value_expression—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.

Examples

The following custom calculation script examples include a NONEMPTYTUPLE property clause to filter out empty tuples from being included in the calculation pass.

use_optimized_way;
([Balance].[Net Balance].[Net Change].[Allocation Out]):= NONEMPTYTUPLE ([Balance].[Remainder],[Rule]) -(([Balance].[Remainder],[Rule])*(20.24000/100));

([2014], [August], [Actual]):= NONEMPTYTUPLE ([2014], [January], [Actual]) ([2014], [January], [Actual]);

Further Information

MDX Optimization Properties