Multidimensional Calculation Concepts in Essbase

Multidimensional Calculation Concepts:

The below screenshot (Fig. A), which is based on a simplified database, illustrates the nature of multidimensional calculations:

Fig. A: Calculating a Multidimensional Database

The database has three dimensions—Accounts, Time, and Scenario.
The Accounts dimension has four members:

• Sales and COGS are input values
• Margin = Sales - COGS
• Margin% = Margin % Sales (Margin as a percentage of Sales)

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.

An intersection of members (one member on each dimension) represents a data value. The following example has three dimensions; therefore, the dimensions and data values in the database can be represented as a cube, as shown in below screenshot Fig. B:

Fig. B:Three-Dimensional Database
As shown in the below screenshot (Fig. C), when you refer to Sales, you are referring to a slice of the database containing eight Sales values.

Fig. C:Sales, Actual, Budget Slice of the Database

As shown in the below screenshot (Fig. D), when you refer to Actual Sales, you are referring to four Sales values:

Fig. D:Actual, Sales Slice of the Database

To refer to a specific data value in a multidimensional database, you need to specify each member on each dimension. A data value is stored in a single cell in the database. In the below screenshot (Fig. E), the cell containing the data value for Sales, Jan, Actual is shaded.
In Essbase, member combinations are denoted by a cross-dimensional operator. The symbol for the cross-dimensional operator is ->. So Sales, Jan, Actual is written as:

Sales -> Jan -> Actual

Fig. E:Sales, Jan, Actual Slice of the Database

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:

1. Margin -> Jan -> Actual as a percentage of Sales -> Jan -> Actual. The result is placed in Margin% -> Jan -> Actual.
2. Margin -> Feb -> Actual as a percentage of Sales -> Feb -> Actual. The result is placed in Margin% -> Feb -> Actual.
3. Margin -> Mar -> Actual as a percentage of Sales -> Mar -> Actual. The result is placed in Margin% -> Mar -> Actual.
4. Margin -> Qtr1 -> Actual as a percentage of Sales -> Qtr1 -> Actual. The result is placed in Margin% -> Qtr1 -> Actual.
5. Margin -> Jan -> Budget as a percentage of Sales -> Jan -> Budget. The result is placed in Margin% -> Jan -> Budget.
6. Essbase continues cycling through the database until it has calculated Margin% for every combination of members in the database.