Essbase - Consolidations / Aggregations

Member consolidation Operators, determine how children roll up into their parents.

Consolidation define how the data are aggregate.

Consolidation is also known as aggregation rules.

Consolidation is the most frequently used calculations in Essbase.

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.

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

The consolidation operator define the consolidation path.

The definition of the consolidation operator define the consolidation path.

The TBC application has several consolidation paths:

• Individual products roll up to product families, and product families consolidate into Product. The TBC outline also requires multiple consolidation paths; some products must consolidate in multiple categories.
• States roll up to regions, and regions consolidate into Market.
• Months roll up into quarters, and quarters consolidate into Year.

Consolidation operators define how Essbase rolls up data for each member in a branch to the parent. For example, using the default addition (+) operator, Essbase adds 100‑10, 100‑20, and 100‑30 and stores the result in their parent, 100, as shown in the figure below, TBC Product Dimension.

The Product dimension contains mostly addition (+) operators, which indicate that each group of members is added and rolled up to the parent.

Diet has a tilde (~) operator, which indicates that Essbase does not include the Diet member in the consolidation to the parent, Product. The Diet member consists entirely of members that are shared. The TBC product management group wants to be able to isolate Diet drinks in reports, so TBC created a separate Diet member that does not impact overall consolidation.

Essbase calculates the data of a branch in top-down order. For example, if you have, in order, two members tagged with an addition (+) operator and a third member tagged with a multiplication (*) operator, Essbase adds the first two and multiplies that sum by the third.

Because Essbase always begins with the top member when it consolidates, the order and the labels of the members is important.

When siblings have different operators, Essbase calculates the data in top-down order. The following section describes how Essbase calculates the members listed below:

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

Essbase calculates Member1 through Member4 as follows:

(((Member1 + Member2) + (-1)Member3) * Member4) = X
(((10 + 20) + (-25)) * 40) = 200

If the result of this calculation is X, Member5 consolidates as follows:

(X/Member5) * 100 = Y
(200/50) * 100 = 400

If the result of the Member1 through Member4 calculation is Y, Member6 consolidates as follows:

Y/Member6 = Z
400/60 = 66.67

Because Member7 is set to No Consolidation(~), Essbase ignores Member7 in the consolidation.

Essbase - Shared Members also affect consolidation paths. The shared member concept enables two members with the same name to share the same data. The shared member stores a pointer to data contained in the other member, so Essbase stores the data only once. Shared members must be in the same dimension. Data can be shared by multiple members.

• Calculating Members with Different Operators.