Skipping Empty Tuples to Optimize Custom Calculations

Data sets can be very sparse and calculating empty result sets can noticeably degrade performance.

To avoid calculating empty result sets, you can use the NONEMPTYTUPLE property clause in custom calculation formulas. Using NONEMPTYTUPLE restricts where the system attempts to calculate. To set this up, you define a tuple that will only be calculated if it isn't empty. Then, you define a simple numeric operation to be applied to the tuple.

Syntax

The syntax for using NONEMPTYTUPLE is as follows:

tuple := [NONEMPTYTUPLE (nonempty_member_list)] numeric_value_expression;

Where:

• 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. A tuple must be present in numeric_value_expression.

• 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. You must use only arithmetic operators. An error is returned if you use non-arithmetic operators such as AND, OR, or IF.

For example:

[UnitCost] := NONEMPTYTUPLE ([TotalExpense], [NetBalance]) ([TotalExpense], [NetBalance]) / ([Units], [CostPool]);

This example says the following:

1. Look at the tuple ([TotalExpense], [NetBalance]).

2. If that tuple, or intersection, is not empty (that is, it contains a real value), then perform the following operation:

   Take the tuple ([TotalExpense], [NetBalance]) and divide it by the tuple ([Units], [CostPool]) to yield UnitCost.

3. If the tuple is empty, skip it and evaluate the next instance.

Rules for Use

A tuple is a combination of members from some set of different dimensions, for example:

([product2], [account5], customer3])

([myactivity], [yourdepartment])

The requirements for adding NONEMPTYTUPLE to the formula of Profitability and Cost Management custom calculation rules are as follows, where A, B, and C represent tuples:

• You can use NONEMPTYTUPLE only when a tuple has some value. Otherwise, the result could be unexpected. Suppose the following:

  • A=B+C -- You can't use NONEMPTYTUPLE on either B or C. If B or C is missing, the result will be None, which is incorrect.

    Note:

    To calculate A=B+C using NONEMPTYTUPLE, use two rules:

    • Rule 1: A=A+B with NONEMPTYTUPLE on B

    • Rule 2: A=A+C (or A=A-C) with NONEMPTYTUPLE on C

    In this case, whenever the value for B is not missing, it will be added to A; and whenever the value for C is not missing, it will be added to A.

  • A=B-C -- You can't use NONEMPTYTUPLE on either B or C. If B or C is missing, the result will be None, which is incorrect (unless you use two rules as described previously).

• You can use NONEMPTYTUPLE when the result is the same whether the tuple is missing or not. Suppose the following:

  • A=B -- You can use NONEMPTYTUPLE on B.

  • A=B*C -- You can use NONEMPTYTUPLE on either B or C.

  • A=B/C -- You can use NONEMPTYTUPLE on either B or C.

Note:

To summarize, you can safely use NONEMPTYTUPLE in multiplication or division operations. For addition or subtraction, you must evaluate its use for each separate case to avoid unexpected results.

For more information about writing custom calculation rules, see About Custom Calculation Rule Formula Syntax and the documents referenced there.