Figure 108, Calculating a Multidimensional Database, which is based on a simplified database, illustrates the nature of multidimensional calculations:
The database has three dimensions—Accounts, Time, and Scenario.
The Accounts dimension has four members:
The Time dimension has four quarters. The example displays only the members in Qtr1—Jan, Feb, and Mar.
The Scenario dimension has two child members—Budget for budget values and Actual for actual values.
The outline in Figure 108, Calculating a Multidimensional Database is illustrated as a three-dimensional cube in Figure 109, Illustration of a Three-Dimensional Database:
An intersection of members (one member on each dimension) represents a data value; a data value is stored in one cell in the database. To refer to a specific data value in a multidimensional database, you must specify each member on each dimension. In Essbase, member combinations are denoted by a cross-dimensional operator (->). Create the cross-dimensional operator using a hyphen (-) and a greater-than symbol (>). Do not include a space between the cross-dimensional operator and members.
The single cell containing the data value for Sales, Jan, Actual, as shown in Figure 110, Sales, Jan, Actual Slice of the Database, is written as Sales -> Jan -> Actual.
When you refer to Sales, you are referring to a slice of the database containing eight values, as shown in Figure 111, Sales, Actual, Budget Slice of the Database, which are:
Sales -> Jan -> Actual
Sales -> Feb -> Actual
Sales -> Mar -> Actual
Sales -> Qtr1 -> Actual
Sales -> Jan -> Budget
Sales -> Feb -> Budget
Sales -> Mar -> Budget
Sales -> Qtr1 -> Budget
When you refer to Actual Sales, you are referring to four values, as shown in Figure 112, Actual, Sales Slice of the Database, which are:
Sales -> Jan -> Actual
Sales -> Feb -> Actual
Sales -> Mar -> Actual
Sales -> Qtr1 -> Actual
When Essbase calculates the formula “Margin% = Margin % Sales,” it takes each Margin value and calculates it as a percentage of its corresponding Sales value.
Essbase cycles through the database and calculates Margin% as follows:
Margin -> Jan -> Actual as a percentage of Sales -> Jan -> Actual.
The result is placed in Margin% -> Jan -> Actual.
Margin -> Feb -> Actual as a percentage of Sales -> Feb -> Actual.
The result is placed in Margin% -> Feb -> Actual.
Margin -> Mar -> Actual as a percentage of Sales -> Mar -> Actual.
The result is placed in Margin% -> Mar -> Actual.
Margin -> Qtr1 -> Actual as a percentage of Sales -> Qtr1 -> Actual.
The result is placed in Margin% -> Qtr1 -> Actual.
Margin -> Jan -> Budget as a percentage of Sales -> Jan -> Budget.
The result is placed in Margin% -> Jan -> Budget.
Essbase continues cycling through the database until it has calculated Margin% for every combination of members in the database.