Technical data

Chapter 10 Creating Rules, Formulas, and User Functions
• 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 diffe
rent calculations for different members.
• Perform diffe
rent calculations for different groups of members.
Create Different Calculations for Different Members
This section p
rovides an overview of the calculation of only one member and the calculation of more than one
member and dis
cusses how to:
• Create a calculation for only one member.
• Create a calculation f or more than one member.
Understanding the Calcula tion of Only One Member
The following
example d escribes the reason for and process of creating a special calculation for one member.
Suppose your company must allocate the A dministration 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 analyti
c calculation engine uses the[DEPARTMENTS:Administration] member reference to check
whether Adm
inistration 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 num ber of departments minus one.
In other words, the formula divides the Administration expense equally among the other d epartments.
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.
Copyright © 1988-2007, Oracl e. All rights reserved. 131