Performing Exceptions to the Rule

This topic provides an overview of performing exceptions to the rule.

A typical rule contains a formula for an entire data cube that the analytic calculation engine uses to calculate every value in the data cube. If you want some values of a data cube to calculate in a different manner than other values, you must create an exception to the rule. You can create exceptions to:

  • Perform different calculations for different members.

  • Perform different calculations for different groups of members.

This topic provides an overview of the calculation of only one member and the calculation of more than one member and discusses how to:

  • Create a calculation for only one member.

  • Create a calculation for more than one member.

Understanding the Calculation of Only One Member

The following example describes the reason for and process of creating a special calculation for one member.

Suppose your company must allocate the Administration department's expense equally to all of the other departments. To ensure proper allocation, the Administration department requires a different calculation than the other departments. To create this allocation, you must back out the expense for Administration and divide that expense equally among the other departments.

Your company's analytic model contains data cubes called EXPENSE and ADMIN_ALLOCATION. The DEPARTMENTS dimension is attached to both data cubes. Create the following formula to calculate ADMIN_ALLOCATION:

IF([DEPARTMENTS:Administration], - EXPENSE, EXPENSE[DEPARTMENTS:Administration] / 
(NUMMEMBERS(DEPARTMENTS) - 1))

The formula uses the IF function to calculate one result if a condition is true, and another result if the condition is false. Here is how the formula works:

The analytic calculation engine uses the [DEPARTMENTS:Administration] member reference to check whether Administration is the department that is being calculated.

  • If Administration is the department that is being calculated, the formula returns minus EXPENSE, backing out the expense for Administration.

  • If Administration is not the department that is being calculated, the formula returns the expense for Administration divided by the number of departments minus one.

    In other words, the formula divides the Administration expense equally among the other departments.

    Note: The formula uses the data cube slice EXPENSE[DEPARTMENTS:Administration] to refer to the expense for Administration. The NUMMEMBERS function returns the number of members in the specified dimension.

Understanding the Calculation of More Than One Member

The following formula provides an example of a calculation for more than one member. The formula returns one result for Administration, another result for Data Processing, and a third result for all other departments:

CASE([DEPARTMENTS:Administration] : ADMINISTRATION_RESULT, 
[DEPARTMENTS:Data Processing] : DATA_PROCESSING_RESULT, 
#DEFAULT : RESULT_FOR_ALL_OTHER_DEPARTMENTS)

Creating a Calculation for Only One Member

To create a calculation for only one member:

  1. Select Start > Programs > PeopleTools 8.5x > Application Designer to access PeopleSoft Application Designer.

  2. After signing in to the PeopleSoft Application Designer, open an analytic model definition.

  3. Select the data cube whose formula you want to define.

  4. Define a formula for the result data cube.

  5. Enter IF and an opening parenthesis.

    You are using the IF function to return different results, depending on a condition.

    See IF.

  6. Insert the member reference for the exceptional member.

    Note: When you use a member reference as a condition, it returns True if the analytic calculation engine is calculating values for that member; otherwise, it returns False.

    See Inserting a Dimension Member Reference into a Rule.

  7. Enter a comma, and then enter the result that should be returned if the exceptional member is being calculated.

  8. Enter another comma, and then enter the result that should be returned if one of the nonexceptional members is being calculated.

  9. Enter a closing parenthesis.

    Note: To perform the same calculation for several members, combine two or more member references with .OR. operators. For example: [DEPARTMENTS:Administration] .OR. [DEPARTMENTS:Data Processing].

Creating a Calculation for More Than One Member

To create a calculation for more than one member:

  1. Select Start > Programs > PeopleTools 8.5x > Application Designer to access PeopleSoft Application Designer.

  2. After signing in to the PeopleSoft Application Designer, open an analytic model definition.

  3. Select the data cube whose formula you want to define.

  4. Define a formula for the result data cube.

  5. Enter CASE and an opening parenthesis.

    You are using the CASE function to evaluate a Condition:Result pair for each special case.

    See CASE.

  6. Enter a Condition:Result pair for each special calculation:

    1. Insert a member reference for one of the members in the dimension.

      For example: [DEPARTMENTS:Administration].

      This condition tests whether results are being calculated for the specified member.

    2. Enter a colon to separate the condition from the result.

    3. Enter the appropriate result for the specified member.

    4. Enter a comma.

  7. Perform these steps to enter a final Condition:Result pair to return a result for all other members in the dimension:

    1. Enter #DEFAULT as the condition.

      #DEFAULT instructs the function to return the final result for all other members.

    2. Enter a colon to separate the condition from the result.

    3. Enter the result for all other members in the dimension.

    4. Enter a closing parenthesis.

You may want to calculate a data cube in different ways for different groups of members. To perform different calculations for different groups of members:

  1. Select Start > Programs > PeopleTools 8.5x > Application Designer to access PeopleSoft Application Designer.

  2. After signing in to the PeopleSoft Application Designer, open an analytic model definition.

  3. Create an association data cube that associates each member with a group.

    See Creating Association Data Cubes.

  4. Define a formula for the result data cube.

  5. Use the CASE function to evaluate two or more Condition:Result pairs.

    Perform the following steps for each Condition:Result pair:

    See CASE.

    1. Use a member reference to compare the association data cube to one of the members in the group dimension.

      This example is a formula for the INVEST_TYPE data cube:

      [TYPES:Stock]

      See Inserting a Dimension Member Reference into a Rule.

    2. Enter a colon to separate the condition and result.

    3. Enter the appropriate result for that group.

    4. To add another Condition:Result pair, enter a comma; otherwise, enter a closing parenthesis.

Example: Creating Different Calculations for Different Groups of Members

Suppose that you create an analytic model to track your investments in stocks, bonds, and rental properties, and you want to know your monthly income. Because the income for stocks, bonds, and rental properties is calculated differently, you need to perform different calculations for different groups of these investments.

Create a data cube that associates each investment with an investment type. Suppose the dimension of investment types is called TYPES, and the association data cube is called INVEST_TYPE. You can calculate the investment income for each investment as follows: 

CASE(INVEST_TYPE = [TYPES:Stock] : NUMBER_OF_SHARES * DIVIDENDS_PER_SHARE, 
INVEST_TYPE = [TYPES:Bond] : BOND_RATE * BOND_AMOUNT / 12, 
INVEST_TYPE = [TYPES:Rent] : MONTHLY_RENT)

See Creating Association Data Cubes.

The CASE function evaluates multiple conditions and returns the result for the first true condition. Each Condition:Result pair is separated by a comma.

See CASE.

In the preceding formula, the CASE function compares the invest type for an investment to each member in the TYPES dimension. The formula uses a member reference (for example, [TYPES:Stock] ) to refer to each member. When the CASE function finds the matching member from the TYPES dimension, it returns the corresponding result. For example, if the invest type for an investment is Bond, the formula returns BOND_RATE * BOND_AMOUNT / 12.