OneStream Planning: Aggregated, Never (usually) Consolidated

by OP in OneStream PlanningComments Off on OneStream Planning: Aggregated, Never (usually) Consolidated on

An excerpt from Chapter 1 of OneStream Planning: The Why, How and When by Cameron Lackpour and Celvin Kattookaran.

The Need for Speed

The pattern of Planners’ data interaction with OneStream is (roughly) as follows: retrieve, review, input, save, calculate, sum to totals, retrieve and then repeat, ending only when the Planning process is complete. The longest system step is typically around summing Entity Parents because the processing of tens of thousands (or hundreds of thousands or more) of Base Dimension data intersections to Parent hierarchy levels takes time to retrieve, compute in memory, and write to disk. Time-consuming or not, the ability to see the impact of inputs and calculations at summed hierarchy levels is key to understanding plan data.

Implementors try to alleviate the time impact by making the Entity Dimension Consolidation process optional at a Form level, or by running it on a scheduled basis. These approaches may improve the User Experience but will delay analysis and may lead to data quality issues when those totals become stale and do not foot to base data.

Adding It Up

Happily, the mitigation strategies of scheduled or explicitly delayed processing are required only for the Entity-type Dimension; all other Dimension totals in OneStream are dynamic Aggregations. Entity is a stored hierarchy (largely for the purposes of true accounting-focused Financial Consolidations), and its totals can only be viewed after a Consolidation materializes them. The overhead concomitant with accounting principles and statutory requirements are not typically needed in a Planning application.

The release of OneStream 6.5 removes the need to perform a Consolidation to see data at Entity Parents by providing the option of a simpler Aggregation. Aggregations are fast.

What’ll It Do, Mister?

How fast? Using an example of a 17,000-Member Entity Dimension with two years’ worth of data (UD1 through UD6 were also populated), aggregating was seven times faster than consolidating.

Note: A 17,000-Member Entity Dimension is atypical in its size. A more reasonable Entity Dimension size of 700 saw a similar 83% improvement in speed. Fast, indeed. Of course, every Cube is different in size, density of data, and design of Dimensions, all of which impact Cube performance; therefore, actual performance in your Cube may vary.

Using It is Simplicity Itself

OneStream 6.5 introduced a new Consolidation Dimension Member: Aggregated. 

[Figure 1.1]

C#Aggregated is – unsurprisingly – where aggregated data sits in the Consolidation Dimension.

Launching an Aggregation in a Data Management step is accomplished by using C#Aggregated in the Consolidation Filter instead of the more typical (and default) C#Local. This really and truly is all there is to aggregating instead of consolidating.

[Figure 1.2]

C#Aggregated Default Population

Even when an Aggregation is not executed, C#Aggregated is valued at Base Entities but not at Parent-level Members.

[Figure 1.3]

An Aggregation returns data at E#East:C#Aggregated but not at C#USD, C#Top, C#Share, C#Translated, or C#Local, all Consolidation artifacts.

[Figure 1.4]

A Consolidation will value the typical currency, C#Share, C#Translated, and C#Local data points and will not populate C#Aggregated.

[Figure 1.5]

What’s Kept

Beyond the Aggregation speed improvements, currency conversion, share percentage, and – most importantly – financial intelligence are all retained.

What’s Lost

From a Consolidations Application perspective, the following Consolidations-only features are vital and thus preclude Aggregations: Business Rules on all Consolidation levels, recursive calculations on Entity and Consolidation to perform Eliminations, and accounting for Parent Journal Adjustments.

Three Notes

The Good

Aggregations consider already aggregated data values (e.g., if the Entity Pennsylvania’s data changes, impacting the ancestors East and Total Geography, the South Carolina and South hierarchy are not reaggregated).

The Bad

An Entity Dimension can be both aggregated and consolidated. Beyond the needless redundancy in having the same number stored twice in the Cube, the risk that a Consolidation and an Aggregation are not in step is high. Pick one, not the other, and in the case of Planning applications, pick Aggregations.

The Ugly

The C#Aggregated Member automatically reflects level zero Member data and will display Parent-level Member data on Aggregation. However, C#Aggregated is a read-only Member.

[Figure 1.6]

A single-month Quick View with nested Consolidation, Time, and UD1 Members poses no navigational issues but would quickly become untenable if expanded to a typical full year of 12 periods, resulting in 48 columns.

There are then two other approaches: separate input and reporting views, or a parameter-driven Consolidation toggle.

Separate Views

This approach creates two Cube Views that differ only in their Consolidation Member selection, i.e., C#Local versus C#Aggregated.

[Figure 1.7]

The above Cube View allows input but does not show a total at East.

[Figure 1.8]

A C#Aggregated Cube View shows (after Aggregation) data at the individual state and region level. However, it is read-only.

A Planner could toggle between both Cube Views using Workflow or a custom Dashboard. Beyond the cumbersome nature of changing Cube Views, this approach requires two Cube Views with their follow-on maintenance.

Single Parametrized Consolidation View

A simpler approach is to use a Delimited List parameter to drive the C#Local/C#Aggregated selection and use it in the Column Member definition.

[Figure 1.9]

Modifying the columns to use the |!Consolidation!| parameter will drive a popup Member selector on Cube View refresh…

[Figure 1.10]

…which then allows the User to select C#Aggregated or C#Local.

[Figure 1.11]

This results in a single Cube View that can display either Member.

Choosing Aggregated in the Member dropdown results in a C#Aggregated-valued Cube View that, when aggregated, can be used to view data totaled at East.

[Figure 1.12]

Selecting Local in the Member dropdown results in a C#Local-valued Cube View, allowing input at the East’s constituent states.

[Figure 1.13]


This must surely be the shortest principle in this chapter: Aggregate the Cube and report on C#Aggregated.

Hobson’s Choice

Unless true Consolidation requirements – like elimination – are required in the budgeting process, the performance boost so vital to User Experience means that Aggregation via the Consolidation Dimension’s C#Aggregate should be the only choice for Planning applications.

* * * * *

OneStream Planning: The Why, How and When by Cameron Lackpour and Celvin Kattookaran