Part 1 of 3 - In this series of blogs we discuss the Consolidation and dimensions that drive the power of the cube in OneStream
I was in a meeting with a large group of consultants years ago. Some were Budget and Forecast guys, others were Consolidation folks. This was back when the EPM products were broken by domain, and nothing like we see in a post OneStream world. We were talking about our projects and the need for common design sessions. The Consolidation people needed time for data reconciliation, every number needed to reconcile. They needed to validate sign off and procedure with Sarbanes Oxley rules, along with complex ownership and elimination data. The Budget and Forecast folks didn’t care about any of that! They had complex allocation rules, worried about fast aggregations to get sub totals quickly, and worked with data volumes that would overwhelm a consolidation. And it didn't matter if the data was locked down, they would rebuild a database every time they loaded data. However, Consolidation focused people were terrified of dropping a table, as it would mean reconciling data all over again.
Only a tool like OneStream allows both groups to consider what’s important to them at the same time. There are options that were created to meet each of these concerns, like direct load and the ownership module. But in this blog let’s just talk about the difference between Consolidation and Aggregation.
Aggregation is straight forward. It is just adding the data up by each of the dimensional hierarchies.
Consolidation is more than just adding up numbers, there is a specific accounting process that happens as the numbers are added up. Each step by itself is simple. But layers and volume make looking at the data a bit overwhelming. OneStream can help make that data easier to understand. However, what does that help if the process is completely not needed?
If you look in the Consolidation dimension, you will see an Aggregated member. Instead of all the steps required by Consolidation needed under the Top member, the straightforward adding up of numbers is done in the Aggregated member.
This aligns with planning use cases. And one can expect 80%-90% performance gains on Consolidation!
Some things to consider:
Like everything in life there is a ‘give’ and a ‘get’, and this is no exception. There are some things to consider if you want to use this feature.
- No Parent Journal Adjustments
- No Eliminations – No surprise here,a s they are part of the consolidation process, but the elimination rules won’t run either so you are limiting something creative for rules.
- Business Rules (execute at the base level only) – just like elimination above, some of that speed is gained by limiting what rules are running and where
Launching an Aggregation, follows the same process as launching a Consolidation using context aware algorithms once the Aggregated member of the Consolidation (C#Aggregated) is selected. For example, right-click on the appropriate cell in a Cube View or Form to view Process by clicking:
- Consolidate
- Force Consolidate
- Consolidate with Logging
- Force Consolidate with Logging
For example (see below) you will Aggregate Jan 2018 via Consolidate - Right click number and pick Consolidate to Aggregate numbers (uses context aware algorithms to recognize Aggregated Cons member)
Some things to think about, Base Level Entities the Aggregated member displays the data that is stored in the “Local” Member. While Parent Entities the Aggregated member stores the results of the Fast Entity Aggregation process that occurs on its child entities.
The Aggregation Algorithm works like this:
- Execute chart logic (the business rules and member formulas for calculate) on the Local Consolidation member for all Base Entities.
- Execute the following steps recursively for each Parent and its direct children starting from lower-level entities to the Parent Entities.
For each child
- Translate its stored data in memory
- Calculate its Share amount in memory
- Add the data cells from each child in memory
- Store the results in the Aggregated member for the Parent Entity
Setting up a Cube View (setup) GolfStreamExample works like this:
Rows
Entity: E#[North America].TreeDescendantsInclusive
Columns
Consolidation: C#Aggregated, C#Aggregated:V#CalcStatus
Column: Time: T#2018M1, T#2018M12
Did you find this helpful? Let us know in the comments!
Stay tuned for part 2 coming in two weeks!
2 Comments
