We were recently asked by a client to define the difference between the Tilde (~) and the Caret (^) consolidation operators and explain when each should be used in an Essbase outline. It is a great question that is often overlooked because people tend to use the Tilde (~) by default and not consider the Caret (^). There is however a distinct difference between the two operators which lead to very different outcomes when consolidation occurs in Essbase.
Tilde(~) vs. Caret(^)
Let’s start with some basic concepts from the Essbase DBA Guide. Member consolidation properties determine how children roll up to their parent within a dimension. Typically, an Essbase outline will have consolidating and non-consolidating dimensions. In a consolidating dimension, children roll up to their parent along hierarchies in the dimension. This is common in the entity or periods dimensions. In a non-consolidating dimension, children do not rollup to the parent. Common non-consolidating dimensions include the year or scenario dimensions.
There are also dimensions in an Essbase outline that have both consolidating and non-consolidating hierarchies. The accounts/measures dimension often has an Income Statement or Balance Sheet hierarchy where members consolidate to their parents based on the financial statement consolidation logic it depicts. That same dimension may also have a non-consolidating hierarchy that includes statistical members where members do not consolidate to the parent. For this discussion, we will use the Sample:Basic Essbase application and focus on a non-consolidating hierarchy called ‘Statistics’ in the Measures dimension.
The consolidation operator assigned to members of a non-consolidating hierarchy is usually the Tilde (~). This consolidation operator tells Essbase to not use the member in the consolidation to its parent. The ‘Statistics’ member is tagged with the Tilde (~) so any data associated with this member will not consolidate to its parent ‘Measures.’ Taking a second look at ‘Measures’ and you will notice only ‘Profit’ consolidates to ‘Measures.’ When a user retrieves data for ‘Measures’, data for ‘Profit’ will be retrieved. ‘Headcount’, the first child of ‘Statistics’, is tagged with a Plus (+). This member will consolidate to its parent.
Let’s take a look at an example. The following spreadsheet reflects level-0 intersections in Sample:Basic across the five dimensions in the outline. A value of 100 is loaded to the ‘Headcount’ measure. With a Plus (+) consolidation operator, ‘Headcount’ consolidates to its parent ‘Statistics.’ A value of 100 is also retrieved for ‘Statistics’.
Let’s change the consolidation operator of ‘Headcount’ to a Tilde (~). Now, it will NOT consolidate to its parent. It will however, consolidate along the consolidating hierarchies among other dimensions in the Essbase outline.
We return to the spreadsheet example and retrieve the same intersection in the previous example. Now, ‘Statistics’ retrieves a value of #MISSING because we changed the consolidation operator of ‘Headcount.’
Although ‘Headcount’ will not consolidate to its parent, it will consolidate to a parent in a consolidating hierarchy in other Sample:Basic dimensions. Returning to our example, we alter the spreadsheet to display the ‘East’ hierarchy in the Market dimension. Data was loaded to each child of the member ‘East.’ Because the children of ‘East’ consolidate to its parent, the measure ‘Headcount’ will consolidate along the ‘East’ hierarchy, but NOT consolidate to its parent ‘Statistics.’
To recap, a member that is assigned the Tilde (~) consolidation operator will NOT be included in the consolidation of ONLY its parent. Consolidation of that member will still occur for in consolidating hierarchies of other dimensions in an Essbase outline.
Let’s turn our attention to the Caret (^) consolidation operator. A member assigned this consolidation operator will NOT consolidate to its parent. It will also NOT consolidated in hierarchies of other dimensions in an Essbase outline regardless if they are consolidating or non-consolidating. In the ‘Statistics’ hierarchy of the Sample:Basic application we have a member called ‘Currency Rate’ that is tagged with the Caret (^) consolidation operator.
In our example, we load a value of .81 to the ‘Currency Rate’ member. This value will NOT consolidate to its parent, ‘Statistics.’ It will retrieve a value of #MISSING.
Once again, we alter the spreadsheet to display the ‘East’ hierarchy in the ‘Market’ dimension. The member ‘East’ will also retrieve a value of #MISSING even though the member ‘New York’ where the value of .81 is store consolidates to its parent. The Caret (^) consolidation operator assigned to the member ‘Currency Rate’ in the Measures dimension prevents members consolidating to their parents in other dimensions.
Let’s recap the Caret (^) consolidation operator. A member that is assigned the Caret (^) consolidation operator will NOT be included in the consolidation of its parent and the consolidation of members in other consolidating hierarchies of other dimensions in an Essbase outline.
Now that we know the difference between the Tilde (~) and the Caret (^) consolidation operators, when should you use each of the operators? The following list summarizes use cases for each.
When should you use a Tilde (~), Do Not Consolidate Along Parent?
Use the Tilde (~) consolidation operator when the member needs to consolidate along other dimensions. Examples include:
– Statistics such as headcount and square footage where consolidation along other dimensions is relevant.
– Scenarios such as Actual and Budget – Consolidation is common across other dimensions for scenario members that are non-consolidating.
– Versions such as Working and Final – Consolidation is common across other dimensions for version members that are non-consolidating.
When should you use a Caret (^), Never Consolidate Operator?
– Currency Rates – Consolidation of currency rates across any dimension in an Essbase outline is NOT relevant.
– Flags – Often it is necessary to add measures to an Essbase outline that control calculations or the display of data. These are called flags and are typically NOT consolidated along any dimension.
– Typed Measures – This type of measure extends the analytical capabilities of Essbase allowing for the association of text or dates with numerical values stored in Essbase. By default these measures are assigned the Caret (^) consolidation operator because consolidation is never appropriate.
Wrap-Up and Additional Information
There is one final point pertaining to application size and performance that should be considered. Assigning a member the Tilde (~) consolidation operator that should have the Caret (^) can negatively impact the size and performance of an Essbase application. Let’s review the previous ‘Currency Rate’ rate example that does not consolidate along the ‘East’ hierarchy because it has a Caret (^) consolidation operator. If we change the consolidation operator to a Tilde (~), and execute an aggregation on the Market dimension, we will calculate a ‘Currency Rate’ for the ‘East’ member that is nonsensical. It will also unnecessarily create blocks (assuming Market is a sparse dimension) increasing the size of the Essbase application and time it takes to calculate. This is situation you want to avoid to ensure you are building an optimized Essbase application that is properly sized and calculates efficiently.
I hope that this review of the Tilde(~) vs. Caret(^) consolidation operators has been helpful.