Examples of Member Consolidation

Essbase member consolidation properties control how data values roll up, from child members in the hierarchy to their parents. Examples follow for using most of the consolidation operators in outline member properties, including + (add), - (subtract), * (multiply), / (divide), % (percentage calculation), and ~ (no consolidation).

In these examples, assume that initially, the Essbase cube has not yet been calculated, and only level-0 members have values. These values have been loaded to the cube, but prior to calculation, the data has not yet been rolled up to parent members.

Consolidation using member operators happens in top-down order. As the default consolidation is addition, the (+) operator can be demonstrated first.

Example with (+) Operator

In this example below, P1 has no value before calculation. Its value will display as #MISSING if queried in the spreadsheet before the cube has been calculated.

P1
  M1 (+)  10
  M2 (+)  15
  M3 (+)  20

Once the cube has been calculated, P1 will have a value of 45. The consolidation proceeds top down, as follows:

P1 = P1+M1 = #MISSING+10 = 10
P1 = P1+M2 = 10+15 = 25
P1 = P1+M3 = 25+20 = 45

Example with (-) Operator

Now, consider a similar hierarchy with members that consolidate using subtraction.

P2
  M1 (-)  10
  M2 (-)  15
  M3 (-)  20

Once the cube has been calculated, P2 will have a value of -45. The consolidation proceeds top down, as follows:

P2 = P2–M1 = #MISSING–10 = -10
P2 = P2-M2 = -10-15 = -25
P2 = P2-M3 = -25–20 = -45

Example with (*) Operator

Consider a hierarchy with members that consolidate using multiplication.

P3
  M1 (*)  10
  M2 (*)  15
  M3 (*)  20

Once the cube has been calculated, P3 will have a value of #MISSING. The consolidation proceeds top down, as follows:

P3 = P3*M1 = #MISSING*10 = #MISSING
P3 = P3*M2 = #MISSING*15 = #MISSING
P3 = P3*M3 = #MISSING*20 = #MISSING

The final consolidated value of P3 may not have been the intended result. If the requirement is for P3 to be the multiplied product of all the child members, try using the (+) operator on the first child member:

P3
  M1 (+)  10
  M2 (*)  15
  M3 (*)  20

With the first operator changed to addition, P3 will have a non-#MISSING value after the cube is calculated. The consolidation proceeds top down, as follows:

P3 = P3+M1 = #MISSING+10 = 10
P3 = P3*M2 = 10*15 = 150
P3 = P3*M3 = 150*20 = 3000

Example with (/) Operator

Consider a hierarchy with members that consolidate using division (except for the first child member).

P4
  M1 (+)  10
  M2 (/)  15
  M3 (/)  20

Once the cube has been calculated, P4 will have a value of .033. The consolidation proceeds top down, as follows:

P4 = P4+M1 = #MISSING+10 = 10
P4 = P4/M2 = 10/15 = 0.666
P4 = P4/M3 = 0.666/20 = 0.033

Example with (%) Operator

Consider a hierarchy with members that consolidate using percentage calculation ([a/b]*100), except for the first child member.

P5
  M1 (+)  10
  M2 (%)  15
  M3 (%)  20

Once the cube has been calculated, P5 will have a value of 333. The consolidation proceeds top down, as follows:

P5 = P5+M1 = #MISSING+10 = 10
P5 = (P5/M2)*100 = (10/15)*100 = 66.6
P5 = (P5/M3)*100 = (66.6/20)*100 = 333

Example with Many Operators

Consider the following hierarchy that consolidates with a variety of operators.

Parent1
  Member1 (+)  10
  Member2 (+)  20
  Member3 (-)  25
  Member4 (*)  40
  Member5 (%)  50
  Member6 (/)  60
  Member7 (~)  70

Essbase calculates Member1 through Member6 as follows:

Parent1 = Parent1+Member1 = #MISSING+10 = 10;
Parent1 = Parent1+Member2 = 10+20 = 30; 
Parent1 = Parent1+Member3 = 30-25 = 5;
Parent1 = Parent1+Member4 =  5*40 = 200;
Parent1 = Parent1+Member5 = (200/50)*100 = 400
Parent1 = Parent1+Member6 = 400/60 = 6.666;

Because Member7 is set to No Consolidation(~), Essbase ignores Member7 in the consolidation. The final value of Parent1 is therefore 6.666.

See also some important considerations in Using the Calculation Operators *, /, and %.

Member Consolidation Properties

When you define the Essbase consolidation properties for outline members, you determine how child members roll up into their parents. By default, new members are given the addition (+) operator, meaning that members are added.

For example, Jan, Feb, and Mar figures are added, and the result stored in their parent, Qtr1.

Note:

Essbase does not use consolidation properties with members of attribute dimensions. See Calculating Attribute Data.

The member consolidation properties are listed below.

Table 6-2 Consolidation Operators

Operator	Description
+	Adds the member to the resulting value from calculations performed on prior members within the branch. + is the default operator.
-	Multiplies the member by –1 and adds it to the sum of previous calculations performed on other members.
*	Multiplies the member by the result of previous calculations performed on other members.
/	Divides the member into the result of previous calculations performed on other members.
%	Divides the member into the sum of previous calculations performed on other members. The result is multiplied by 100 to yield a percentage value.
~	Does not use the member in the consolidation to its parent.
^	Does not use the member in any consolidation in any dimension except attribute dimensions.

To set member consolidation properties, see Understanding Dimension Worksheets.

Operation Results on #MISSING Values and Zero (0) Values

If a data value does not exist for a unique combination of members, Essbase gives the combination a value of #MISSING. A #MISSING value is different from a zero (0) value. Therefore, Essbase treats #MISSING values differently from 0 values.

The following tables shows how Essbase calculates #MISSING values. In this table, X represents any number.

Table 6-3 How Essbase Calculates Missing Values

Calculation/Operation	Result
X + `#MISSING`	X
X – `#MISSING` `#MISSING` – X	X -X
X * `#MISSING`	`#MISSING`
X / `#MISSING` `#MISSING` / X X / 0	`#MISSING` `#MISSING` `#MISSING`
X % `#MISSING` `#MISSING` % X X % 0	`#MISSING` `#MISSING` `#MISSING`
X == `#MISSING`	False, unless X is `#MISSING`
X != `#MISSING` X <> `#MISSING`	True, unless X is `#MISSING` True, unless X is `#MISSING`
(X <= `#MISSING`)	(X <=0)
(X >= `#MISSING`)	(X >=0) or (X == `#MISSING`)
(X > `#MISSING`)	(X > 0)
(X < `#MISSING`)	(X < 0)
X AND `#MISSING`: 1 AND `#MISSING` (1 represents any nonzero value) 0 AND `#MISSING` `#MISSING` AND `#MISSING`	`#MISSING` 0 `#MISSING`
X OR `#MISSING`: 1 OR `#MISSING` (1 represents any nonzero value) 0 OR `#MISSING` `#MISSING` OR `#MISSING`	1 `#MISSING` `#MISSING`
IF (`#MISSING`)	IF (0)
f (`#MISSING`)	`#MISSING` for any Essbase function of one variable
f (X)	`#MISSING` for any X not in the domain of f, and any Essbase function of more than one variable (except where specifically noted)