Creating Rules, Formulas, and User Functions

This chapter provides overviews of rules, formulas, and user functions, filter user functions, and design time rule error messages and discusses how to:

• Define and edit data cube formulas.

• Define and edit user functions.

• Work with the elements of rules.

• Perform exceptions to the rule.

• Work with circular formulas and circular systems.

Understanding Rules, Formulas, and User Functions

This section lists common elements and discusses rules, formulas, and user functions and the rule bar display.

Common Elements Used in This Chapter

Rules, Formulas, and User Functions

In PeopleSoft Analytic Calculation Engine, you use the rule bar to create rules that define the calculation of data. You use rules within formulas and user functions.

Formulas define the calculation of data cubes. You enter the formula within the rule bar of the data cube that you want to calculate.

You can create a formula and save it as a user function, which can be reused with various data cubes by entering the name of the user function in the rule bar of the relevant data cube. You also create user functions to create filters and to define the calculation of aggregates.

PeopleSoft Analytic Calculation Engine enables you to create rules that contain references to other parts. When the values of these other parts change, the analytic calculation engine recalculates the rule and stores the results in the field mapped to the calculated part. These kinds of rules can be useful for working with assumption data. When end users work with analytic instance data within an application, they can enter assumption values into one data cube, and then view the results of those assumptions in the values of other calculated data cubes.

For example, suppose an analytic model contains three data cubes called PROFIT, INCOME, and EXPENSE. The PROFIT data cube contains this formula:

INCOME - EXPENSE

When an end user changes a value that is tied to the INCOME or EXPENSE data cube, the analytic calculation engine recalculates the formula and stores the result in the field that is mapped to the PROFIT data cube.

Filter User Functions

You apply a filter user function to a specific dimension, on the Dimensions tab of the cube collection's properties.

See Defining Additional Cube Collection Dimension Properties.

This section provides overviews of:

• Data filters.

• Dimension member filters.

Data Filters

You can create filter user functions to display only the dimension members whose values meet a certain condition. For example, this is the formula for the FILTER_PROD_OVER_2000 filter user function, which is applied to the PRODUCTS dimension:

IF (SALES > 2000, RETURN(1), RETURN(0))

In the analytic model, only the PRODUCTS dimension is attached to the SALES data cube. In the analytic grid, the end user views the SALES data cube but only has access to the products that have sold over 2000 units.

Here is the formula for the FILTER_RED_PRODUCTS filter user function, which is applied to the PRODUCTS dimension:

IF(PRODUCT_COLOR = "RED", RETURN(1), RETURN(0))

In this example, the end user only has access to the products whose members have the red attribute.

When a filter user function is applied to a dimension that is attached to a multidimensional data cube, the end user has access to a different set of members depending on whether the filtered dimension is in the column axis/row axis or slice bar.

Using the first filter user function example, the PRODUCTS, MONTHS and REGIONS dimensions are attached to the SALES data cube. When only the PRODUCTS dimension is in the column or row axis—and the other dimensions are in the slice bar—the end user has access to only the PRODUCTS members that have sold over 2000 units in the currently selected region and month in the slice bar. If the end user changes the region or month selection in the slice bar, the filter is reapplied and the analytic grid may display a different set of PRODUCTS members.

However—when the PRODUCTS dimension plus one or more dimensions are in the slice bar—the end user has access to a different set of dimension members. For example:

• If the PRODUCTS dimension is in the slice bar, the end user has access to all PRODUCTS members that have sold over 2000 units in all regions over the course of all months, regardless of where these dimensions are displayed in the grid.

• If the PRODUCTS dimension is in the row headings, the MONTHS dimension is in the column headings and the REGION dimension is in the slice bar, the end user has access to all PRODUCTS members that have sold over 2000 units in the currently selected region in the slice bar, over the course of all months.

• If the PRODUCTS and MONTHS dimensions are in the row headings—and the MONTHS dimension is indented below the PRODUCTS dimension—plus the REGION dimension is in the slice bar, the end user has access to all PRODUCTS members that have sold over 2000 units in the currently selected region in the slice bar, for the month under which the products are displayed. This means that the analytic grid may display a different set of products for each month.

Dimension Member Filters

You can create filter user functions to display only the dimension members that are referenced in the filter function. For example, this is the formula for the FILTER_DIGITAL_CAMERAS filter user function, which is applied to the PRODUCTS dimension:

IF(MEMBER(PRODUCTS) = [PRODUCTS:Digital Cameras], RETURN(1), RETURN(0))

In this example, the end user only has access to the Digital Cameras member of the PRODUCTS dimension.

You can also create filter functions that filter data by userID by using the OPRID built-in function.

See OPRID.

Rule Bar Display

The information that is displayed in the rule bar depends on the selected part. This table lists the parts and the resulting rule bar display:

To edit a formula or user function, click in the rule bar. The rule bar displays buttons that enable you to edit rules.

Understanding Rule Error Messages

When creating an analytic model, it is important that you create rules that follow certain guidelines. For example, a multiplication symbol needs a value or expression on both sides of the symbol; therefore, if you create a rule such as 3 + 5 *, the analytic calculation engine cannot interpret the rule.

When you either click the Accept Changes button to accept a rule or you select Tools, Validate Project, the analytic calculation engine examines the analytic model's rules for errors. All error messages for rules appear in the Output window. When you click an error message, the cursor moves to the part or rule in the analytic model definition that caused the error message. At this time, you can edit the rule in question and fix the error.

The following table describes PeopleSoft Analytic Calculation Engine's rule error messages and how to resolve them.

Note. When %1 or %2 appears in this table, it denotes that the actual error message includes context-specific information. For example, the Invalid Dimension %1 error message would yield the error Invalid dimension PRODUCTS in the Output window if a rule referred to a nonexistent PRODUCTS dimension.

ARGUMENTS($DIM, @ExprToLookup, @Condition, @Direction := #FORWARD);

ARGUMENTS($Dim, @ExprToLookup, @Condition, @Direction := #FORWARD);

 ARGUMENTS(argument1, argument2...argumentN)

Circular Reference

If a data cube's formula refers directly or indirectly to a current value of the same data cube, the analytic calculation engine generates a circular reference error. Following are some examples of circular references:

Case 1

A = A + B

When the analytic calculation engine evaluates the formula A + B, the analytic calculation engine changes the value of A. Then, the analytic calculation engine must evaluate the formula again, using the new value of A, consequently changing the value of A again. For this reason, the analytic calculation engine must evaluate the formula again, and so on. Because the analytic calculation engine does not contain a method to exit this cycle, it refuses to accept a formula that contains a circular reference.

This formula contains a direct circular reference because A refers to itself in its own formula.

Case 2

A = B + C
B = A + D

This case is slightly more complex, but is a result of the same issue presented in Case 1. When the analytic calculation engine evaluates the formula B + C, the analytic calculation engine changes the value of A. The analytic calculation engine must then evaluate the formula A + D, using the new value of A, consequently changing the value of B. For this reason, the analytic calculation engine must re-evaluate B + C, consequently changing the value of A. For this reason, the analytic calculation engine must re-evaluate A + D, and so on. These two formulas create an endless circle.

These formulas contain an indirect circular reference because neither A nor B refers to itself in its own formula. Instead, the circularity is created by the two formulas working together. The following statement describes this circularity: A depends on B, which depends on A.

Case 3

A = B + C
B = D + E
D = F + G
F = A + H

In this case, A depends on B, which depends on D, which depends on F, which depends on A.

The analytic calculation engine traps all circular errors and does not allow you to inadvertently create circular references. Though this is the case, you may have to rethink the logic of the analytic model to ensure proper calculation. A circular reference is often the result of a logical error, which is an attempt to define something in terms of itself. If you encounter a circular reference error, you may need to step through the formulas in the analytic model definition to discover where the thinking is circular. Once you find this answer, you must rework the logic to remove the circularity.

A circular reference error occurs when a data cube directly or indirectly refers to a current value of itself. On the other hand, if a data cube refers to a previous value of itself, the formula is not only valid but useful.

See PREVSELF.

See Working with Circular Formulas and Circular Systems.

Syntax Error

When you receive a syntax error, the current rule does not follow the basic guidelines for a rule. This is often the result of a typographical error. Possible violations of the rule guidelines include:

• An arithmetic operator does not have a value on both sides.

  For example, the expression A+B+ generates a syntax error because the second plus operator does not have a value on both sides.

• Two values exist without an operator between the values.

  For example, the expression Profit 0.50 generates a syntax error because there is no operator between the data cube reference and the number.

• The rule contains a symbol that the analytic calculation engine does not recognize.

  For example, a dollar sign ($) generates a syntax error.

• Either a function does not contain the correct number of arguments, or it contains an argument of the wrong type.

  If the syntax error occurs within a function, you may need to check the entry for that function in the built-in function reference.

  See Built-in Function Reference.

• The rule contains too many closing parentheses.

  For example, the expression A * (B + C)) generates a syntax error because there is an extra closing parenthesis.

  Note. If there are too few closing parentheses, you receive the error Unbalanced parentheses.

Defining and Editing Data Cube Formulas

To define or edit a data cube formula:

1. Select the data cube whose formula you want to define or edit.

2. Click inside the rule bar.

3. Enter a new rule or edit the existing rule.

   See Working with the Elements of Rules.

4. Perform one of these actions:

   • Click the Accept Changes button to accept the changes.

   • Click the Exit Formula Mode button to keep the changes without validating the formula.

   • Click the Cancel Changes button to cancel the changes.

Defining and Editing User Functions

To define or edit a user function:

1. Perform one of these actions:

   • To define a new user function, select Part, New, User Function.

     The Edit Part Name dialog box appears. Enter the name for the user function and click OK.

   • To edit an existing user function, select the user function whose formula you want to edit.

2. Click inside the rule bar.

3. Enter a new rule or edit the existing rule.

   See Working with the Elements of Rules.

4. Perform one of these actions:

   • Click the Accept Changes button to accept the changes.

   • Click the Exit Formula Mode button to keep the changes without validating the formula.

   • Click the Cancel Changes button to cancel the changes.

Working with the Elements of Rules

This section provides an overview of the elements of rules and discusses how to:

• Insert a built-in function into a rule.

• Insert a user function into a rule.

• Insert a numeric value or text value into a rule.

• Insert a data cube reference into a rule.

• Insert a dimension reference into a rule.

• Insert a dimension member reference into a rule.

• Insert a blank line into a rule.

• Insert a comment into a rule.

Understanding the Elements of Rules

This section discusses the various elements that are included in rules.

Built-in Functions

There are many useful calculations that are difficult or impossible to perform with simple arithmetic. You can perform many such calculations by using PeopleSoft Analytic Calculation Engine's built-in functions.

Most functions have one or more arguments that supply the information that the function needs to perform the calculation. Arguments are enclosed within parentheses after a function name.

When a function contains more than one argument, the arguments are always separated by commas. For example, the following formula uses the MIN function to calculate the minimum of CASH_NEEDED and CREDIT_AVAILABLE to determine the values of the CASH_ADVANCE data cube:

MIN(CASH_NEEDED, CREDIT_AVAILABLE)

Some functions do not take any arguments because they do not require additional information to calculate a result. For example, the PI function returns the mathematical constant pi. Because this function does not require any information, it does not take any arguments. Nevertheless, you must still follow the function name with parentheses. For example, the following formula calculates the circumference of a circle using the PI function:

PI( ) * DIAMTER_OF_CIRCLE

The parentheses following PI indicate that the name is a function rather than a data cube reference.

Many functions have one or more optional arguments. If you leave out an optional argument, the analytic calculation engine supplies a default value for the argument. For example, the CHANGE function calculates the change between members of a dimension and takes these arguments in order:

1. The dimension for which you want to calculate the change.

2. The information for which you want to calculate the change.

3. How many members back to look.

   Note. The third argument is optional; if you do not include it, the analytic calculation engine assumes you want to calculate the change from only the previous member.

For example, suppose you want to calculate the monthly change in sales. You can use the CHANGE function and leave out the third argument, as shown in the following formula:

CHANGE(MONTHS, SALES)

For each month, the analytic calculation engine calculates the change in sales from the previous month.

Now suppose you want to calculate the yearly change in sales. You can use the CHANGE function and supply 12 as the third argument, as shown in the following formula:

CHANGE(MONTHS, SALES, 12)

For each month, the analytic calculation engine calculates the change in sales from 12 previous months. To summarize:

• You must always place parentheses after a function name.

• If a function contains arguments, place the arguments inside the parentheses.

• If a function contains more than one argument, separate the arguments with commas.

• You can leave out an optional argument if the default value for the argument is satisfactory.

See CHANGE.

Conditions and Conditional Formulas

A condition is an expression that evaluates as true or false. A conditional formula returns different values for different conditions. The most simple conditional formula returns one value if a specified condition is true, and a different value if the condition is false. A complex conditional formula may return many different values based on many different conditions. These are types of conditions and conditional formulas:

• Comparison operators.

• Truth functions.

• Compound conditions.

See “Comparison Operators”, “Truth Functions”, “Compound Conditions.”

Comparison Operators

You can compare the values of two expressions using one of PeopleSoft Analytic Calculation Engine's comparison operators.

A comparison returns either a True value (1) or a False value (0), depending on the values of the two expressions.

Note. The analytic calculation engine always interprets a nonzero value as True and a zero value as False.

The expressions in a comparison can contain mathematical operators, parentheses, and functions, as well as data cubes and numbers. The analytic calculation engine evaluates the expressions on both sides of the comparison operator before it evaluates the truth of the comparison. Following are some examples of comparisons:

ADVERTISING >= 10000
ADVERTISING + PROMOTION < 0.5 * (MARKETING_EXPENSE - MARKETING_SALARIES)

The following table describes PeopleSoft Analytic Calculation Engine's comparison operators:

Truth Functions

A truth function is a function that returns 1 (True) or 0 (False), depending on whether the arguments of the function satisfy a condition. The analytic calculation engine uses truth functions to evaluate conditions that are too complex to express easily with comparison operators.

For example:

IF(FIRST(MONTH),  0, SET(&RunningTotal , &RunningTotal + THISCUBE())

In this example, if the current month that is calculated is the first month, the function returns 0. If the current month that is calculated is not the first month, the function returns the running total.

See FIRST, MATCH.

Logical Operators

A logical operator determines whether a condition is true. This table describes the logical operators:

Compound Conditions

A compound condition tests whether some combination of conditions is true by combining two or more comparisons or truth functions using logical operators.

The analytic calculation engine evaluates the .NOT. operator before the .AND. and .OR. operators, and evaluates the .AND. and .OR. operators from left to right. You can override the precedence of the logical operators with parentheses, just as you can with the mathematical operators. This table provides some examples of compound conditions:

SALES > 50000 .AND. ADVERTISING < 10000

CASH_REMAINING < 1000 .OR. PROJECT_DONE

.NOT. IS_FIRST .AND. .NOT. IS_LAST

.NOT. (IS_FIRST .OR. IS_LAST)

Predefined Constants

PeopleSoft Analytic Calculation Engine provides several predefined constants that you can use in rules. You can use constants in the same way that you use numbers in rules. For example, you can test whether a data cube equals the constant, or you can return the constant as a result.

This table describes predefined constants:

CASE(Condition 1 : Result 1, Condition 2 : Result 2, #DEFAULT : Default Result)

Mathematical Operators

This table describes PeopleSoft Analytic Calculation Engine's operators and their order of execution:

Order of Precedence

If you use more than one kind of operator in a rule, you must understand the precedence that the analytic calculation engine follows with the operators. Precedence refers to the order in which the different operators are evaluated.

For an example of precedence, the rule 3 + 2 * 4 evaluates as 11, not as 20. The analytic calculation engine performs the multiplication of 2 and 4 before it adds the number 3 because multiplication has a higher precedence than addition.

You can use parentheses to override the precedence of operators. For example, the rule (3 + 2) * 4 evaluates as 20, because the analytic calculation engine first evaluates the operation within parentheses. You can nest parentheses to exercise more control of precedence; the operations within the inner sets of parentheses are evaluated first. For example, the analytic calculation engine calculates the rule (8 + (3 + 2) * 4) * (6 + 7) in the order described in this table:

The analytic calculation engine performs the multiplication of 5 * 4 before the addition of 8. The analytic calculation engine performs multiplication before addition unless you override this order of execution with parentheses.

Note. If you use parentheses, you must balance each opening parenthesis with a closing parenthesis. If you do not balance the parentheses, the analytic calculation engine generates an Unbalanced parentheses error. When this situation occurs, you must correct the rule.

See Understanding Rule Error Messages.

Values

A value is a number or a text string. For example, the NET_PRESENT_VALUE data cube contains this rule: NPV(MONTHS, ANNUAL_DISCOUNT_RATE / 12, NET_REVENUE_BY_PRODUCT). In this rule, the value is 12.

Data Cube References

Use a data cube reference to refer to a specific data cube. For example, you can use data cube references to multiply the values of two data cubes and place the calculation totals in a result data cube. Using this example, the PROD_SALES data cube contains the following rule: UNIT_COST * UNITS_SOLD.

Member References

Use a member reference to refer to a dimension member to access its data or to perform a calculation. Use the following syntax to refer to a member:

[DIMENSION_NAME:Member]

For example, you could use this member reference to refer to the Hard Drives member from the PRODUCTS dimension: [PRODUCTS: Hard Drives]

If an aggregate member and detail/leaf member share the same name, use the following syntax to reference the desired member:

• [DIMENSION_NAME:NODE.Member]

  Access an aggregate member.

• [DIMENSION_NAME:DETAIL.Member]

  Access a detail or leaf member.

Note. Navigation related functions such as PREV, NEXT, and PREVSELF operate on detail member names that are persisted in the main record. These functions do not use trees to determine the order of members.

See Understanding Dimension Members, Logic for Determining the Order of Members.

Blank Member References

You reference blank members in user functions by using the MBR2TEXT or TEXT2MBR built-in function with this string: “” (two quotation marks). Do not include spaces between the quotation marks. For example:

AT(Product, TXT2MBR(""), SALES)

Or

IF(MBR2TXT(Product) = "", X, Y)

Note. When blank members are mapped to date fields, they are written to the database as values of 1/1/1900.

See Types of Dimension Members.

Variables

When referencing variables in rules, you must always begin the variable reference with the & symbol, followed by the variable name.

Note. The variable name can only contain letters or numbers; it cannot contain spaces.

To set a value to a variable, use the following:

&Index := 1

The following formula sets the first character of an account number to a variable, and then uses that variable to set the account category:

&AcctCode := LEFT(MBR2TEXT(ACCOUNTS), 1);
	CASE(&AcctCode = "1" : [ACCT_CAT:Assets],
	     &AcctCode = "2" : [ACCT_CAT:Liabilities],
	     &AcctCode = "3" : [ACCT_CAT:Income],
	     #DEFAULT : [ACCT_CAT: Expense]
	)

Setting the value to a variable makes it unnecessary to repeat the expression for each condition of the CASE function, or to create an intermediate cube to hold the account code.

You can increment or decrement a variable with the INC:

INC(&Index);
DEC(&Index);
INC(&Profit, REVENUE);
DEC(&Profit, EXPENSE)

The lifetime of a variable is a single evaluation of the rule; the value of a variable is not preserved across multiple evaluations.

Inserting a Built-in Function into a Rule

To insert a built-in function into a rule:

1. Select the place in the rule where you want to add the function.

2. Click the Paste Built-in Function button.

   The Choose Built-in Function drop-down list box appears.

3. Use the scroll bar to scroll through the list of built-in functions.

4. Click the desired built-in function.

   PeopleSoft Analytic Calculation Engine pastes the built-in function and argument names into the rule bar.

5. For each argument:

   1. Highlight the argument.

   2. Replace the highlighted argument with the argument value.

6. Complete your work on the rule, and then:

   • Click the Accept Changes button to accept the changes.

   • Click the Exit Formula Mode button to keep the changes without validating the rule.

   • Click the Cancel Changes button to cancel the changes.

See Also

Built-in Function Reference

Inserting a User Function into a Rule

To insert a user function into a rule:

1. Select the place in the rule where you want to add the user function.

2. Click the name of the user function in the part browser.

   PeopleSoft Analytic Calculation Engine pastes the user function into the rule bar.

   Note. If you enter a user function name that does not exist, the analytic calculation engine returns an error when validating the analytic model.

Inserting a Numeric Value or Text Value into a Rule

To insert a numeric value in a rule, enter the value (for example, 12).

To use a text value in a rule, enter the value and enclose it in double quotes (for example, "Smith").

Note. PeopleSoft recommends that you do not enter an assumption directly into a rule. Instead, you should create a data cube for the assumption and refer to the data cube in the formula. For example, do not calculate TAXES by multiplying INCOME by 0.38. Instead, create a data cube called TAX_RATE and enter 0.38 as its value. Then calculate TAXES by multiplying INCOME by TAX_RATE. Performing the procedure in this fashion simplifies the process of changing the assumptions and makes the analytic model easier to understand and audit.

See Inserting a Data Cube Reference into a Rule.

Inserting a Data Cube Reference into a Rule

To insert a data cube reference into a rule:

• Place the cursor where you want to insert the data cube reference.

• Perform one of these actions:

  • In the part browser, click on the data cube to which you want to refer.

  • Enter the name of the data cube.

    Note. If you enter a data cube reference for a data cube that does not exist, the analytic calculation engine returns an error when validating the analytic model.

  • Select Edit, Paste Cube Name and click on the data cube to which you want to refer.

Inserting a Dimension Reference into a Rule

To insert a dimension reference into a rule:

• Place the cursor at the location of the rule into which you want to insert the dimension reference.

• Perform one of these actions:

  • In the part browser, click the dimension to which you want to refer.

  • Enter the name of the dimension.

    Note. If you enter a dimension reference for a dimension that does not exist, the analytic calculation engine returns an error when validating the analytic model.

  • Select Edit, Paste Dimension and click on the dimension to which you want to refer.

Inserting a Dimension Member Reference into a Rule

This section discusses how to:

• Enter a member reference into a rule.

• Refer to one slice of a data cube.

Entering a Member Reference into a Rule

To enter a member reference into a rule:

1. Place the cursor at the position in the rule where you want to enter a member reference.

2. Select Edit, Paste Member Ref...

   The Choose Member Reference dialog box appears.

3. Click the dimension for which you want to enter a member reference.

   The dimension and a generic member reference appears in the rule bar.

4. Highlight the word member.

5. Replace the word member with the name of the dimension member.

Referring to One Slice of a Data Cube

When you want to access particular values within a data cube, use member references to refer to a slice of the data cube.

To refer to one slice of a data cube:

1. Define or edit a rule.

2. Enter the data cube in the rule (for example, SALES, which uses the MONTHS, PRODUCTS, and REGIONS dimensions).

3. Enter a member reference (for instance, the East region from the REGIONS dimension).

   The rule bar now displays: SALES [REGIONS.East]. This rule returns SALES for the East region for all PRODUCTS and all MONTHS.

4. Repeat step 3 to make the slice as small as you want.

   A single value from the data cube is the smallest possible slice.

   For example, to access SALES for the East region for the Hard Drives product for 2004/03, use the following rule:

   SALES [REGIONS.East] [PRODUCTS.Hard Drives] [MONTHS.2004/03]

See Also

Slicing Analytic Grid Data

Inserting a Blank Line into a Rule

You can insert a blank line into a rule to enhance legibility. To insert a blank line into a rule, press Ctrl+Enter.

Inserting a Comment into a Rule

To insert a comment into a rule, use the symbols << and >>. This is an example of a comment in a rule:

<< Loop through all products >>

Performing Exceptions to the Rule

This section provides an overview of exceptions to the rule and discusses how to:

• Create different calculations for different dimension members.

• Create different calculations for different groups of members.

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

Create Different Calculations for Different Members

This section 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.

You 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. Define a formula for the result data cube.

2. Enter IF and an opening parenthesis.

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

   See IF.

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

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

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

6. 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. Define a formula for the result data cube.

2. Enter CASE and an opening parenthesis.

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

   See CASE.

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

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

Creating Different Calculations for Different Groups of Members

Sometimes 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. Create an association data cube that associates each member with a group.

   See Creating Association Data Cubes.

2. Define a formula for the result data cube.

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

Working with Circular Formulas and Circular Systems

This section provides overviews of circular formulas, circular systems, recursive system resolution, and circular system resolution and discusses how to change circular formula and circular system options.

See Also

“Circular Reference”

Understanding Circular Formulas

When a data cube's formula refers either directly or indirectly to that same data cube, it is considered to be a circular formula.

Note. The analytic calculation engine determines—on the data cube level—whether formulas are circular. However, the analytic calculation engine resolves circular systems and recursive systems on the cell level.

Direct Circular Formulas

This is an example of a direct circular formula for the SALES data cube:

SALES + SALES_GROWTH

This formula states that sales equals sales plus the sales growth.

It is a direct circular formula because the data cube's formula refers directly to that same data cube.

Indirect Circular Formulas

In an indirect circular formula, a data cube's formula refers indirectly to that same data cube, as in this example:

• Formula for BONUS data cube

  BONUS_PERCENTAGE * NET_INCOME

• Formula for EXPENSE data cube

  SALARY + BONUS

• Formula for NET_INCOME data cube

  INCOME - EXPENSE

In this example, none of the data cubes refer directly to themselves. However, each data cube refers indirectly to itself by means of the other data cubes:

• BONUS refers to NET_INCOME.

• NET_INCOME refers to EXPENSE.

• EXPENSE refers to BONUS.

Understanding Circular Systems and Recursive Systems

When the analytic calculation engine determines—on the data cube level—that a circular formula exists, it analyzes the calculation conditions of the cells within the circular formula to determine whether the cells are dependent on those same cells for their values. If so, these cells either create a recursive system or a circular system.

In a recursive system, the values of the cells are not dependent on the values of those same cells.

In a circular system, the values of the cells are dependent the values of those same cells.

Understanding Recursive System Resolution

The analytic calculation engine resolves recursive systems immediately without using the process of iteration.

To understand the process of how the analytic calculation engine determines and resolves recursive systems, consider the following formula for the SALES data cube:

PREVSELF(MONTHS) + SALES_GROWTH

The analytic calculation engine determines that this is a recursive system by performing the following:

1. The analytic calculation engine determines that this is a circular formula because the PREVSELF built-in function – which refers to the SALES data cube – exists within the formula.

2. The analytic calculation engine analyzes the calculation conditions of the cells within this formula and determines that these cells create a recursive system, because the cells within this formula are not dependent on the values of those same cells.

The analytic engine then resolves this recursive system immediately without iteration.

Understanding Circular System Resolution

The analytic calculation engine uses the process of iteration to attempt to resolve all of an analytic model's circular systems. You set the iteration parameters by using the General tab of the analytic model's properties. If the cells converge on a solution within the iteration parameters, the circular system is resolved. If the cells do not converge on a solution within the iteration parameters, the analytic calculation engine returns an error. All cells within the circular system remain unresolved.

Note. Because you enable or disable iteration for all circular systems, you cannot enable or disable iteration for a particular circular system.

When you enable the resolution of circular systems through iteration, you must select one of the following iteration options:

• Maximum number of iterations: When the values of the cells have not changed more than the specified maximum amount, the circular system is considered to be resolved.

• Maximum change in values: When the analytic calculation engine has calculated the cells within the circular system for the specified maximum number of times, the circular system is considered to be resolved.

Example of Resolving a Circular System

In this example, an analytic model contains the following formulas (for simplicity, assume that each data cube contains only a single cell):

• Formula for BONUS data cube

  BONUS_PERCENTAGE * NET_INCOME

• Formula for EXPENSE data cube

  SALARY + BONUS

• Formula for NET_INCOME data cube

  INCOME - EXPENSE

First, the analytic calculation engine determines that this is an indirect circular formula because the data cubes' formulas refer indirectly to those same data cubes.

Next, the analytic calculation engine analyzes the calculation conditions of the cells within this indirect circular formula and determines that these cells create a circular system. This is because the cells within this formula are dependent on the same values of those same cells.

Assuming that the value for the BONUS_PERCENTAGE cell = 5, the value for the INCOME cell = 10,000, and the value for the SALARY cell = 6,000, then the circular system is resolved when the analytic calculation engine iterates until it returns these solutions:

• Cell for BONUS = 190.48.

• Cell EXPENSE = 6,190.48.

• Cell for NET_INCOME = 3,809.52.

If you plug these values into the preceding formulas, each formula is true: the left side of the formula is (almost) equal to the right side of the formula. Once this occurs, the circular system is considered to be resolved.

Changing Circular Formula and Circular System Options

Select the analytic model in the part browser.

Description	Enter a description of the analytic model. Note. This field pertains to the analytic model as a whole, not to circular formula options.
Resolve circular system through iteration	Select to attempt to resolve all of an analytic model's circular systems through iteration. By default, this option is disabled. See Changing Circular Formula and Circular System Options.
Maximum number of iterations	Enter the number of iterations in which the analytic calculation engine is to resolve circular systems. By default, the maximum number of iterations is 100. If the analytic calculation engine cannot resolve a circular system during this number of iterations, the analytic calculation engine returns an error. Note. You must select the Resolve circular system through iteration check box to activate this option.
Maximum change in values	Enter the maximum change in values. By default, the maximum change in values is 0.001000. A circular system is considered to be resolved when the values of its cells do not change more than the specified maximum change. If you enter a smaller value, the solution is more accurate but may require a longer calculation time. If you enter a larger value, the solution not as accurate but requires a shorter calculation time. Note. You must enable the Resolve circular system through iteration check box to activate this option.
Warn about circular formulas	Every time a circular formula is defined: Select for the analytic calculation engine to provide a circular formula warning every time a circular formula is defined.