Part 2 of 3 - In this series of blogs we discuss the Consolidation and Dimensions that drive the power of the Cube in OneStream.
Hey! Where is my data?!?!
You explained during the POC. Then it was covered during the ABA training. As you discussed eliminations during design, you went over it again. It may be the fifth or sixth time the consolidation and entity dimension has been explained. But that first time your build team writes that first report, they ask, ‘Hey, why is there no data…’ Having done this what sometimes feels like a million times, the first thing you check is the point of view. Then you get to explain it again...what does each member of the consolidation dimension do, and what is stored in each member?
Consolidation Algorithm and its Members
Maybe you can give them this doc, or just explain it again. I like to start with explaining that what happens during the Consolidation depends on the relationship between the Entity and Consolidation Dimension. You can’t look at one without knowing the other. Moreover, you have to know if you are looking at the entity (every parent child relationship) or the node (specifically that parent and entity combination). So, if I load data to local, for an entity, it doesn’t matter how many time the entity exists in how many rollups, the data will not change. The data in the node will change. Elimination data for FRENCH BICYCLES will change if the parent is Europe or if the parent is Two-Wheelers.
That relationship is very difficult to explain – even when you do it 10 times. It's not obvious that data works its way up the Entity and Consolidation dimensions (see below).
Local and Translated will not change depending on where the entity is. If you have 2 or more hierarchies of the entity dimension, and the same entity appears multiple times, the value you will see will not change. The relationship between local and translated is important. You will see the data for the local currency and translated currency under the currency part of the consolidation dimension. So Local and Translated are more like pointers to the currency (Currency Dimension).
No, you don't want OneStream to just translate to every currency. The performance impact of that is huge. So, think about what currencies you really need.
Check your customers report – Did they specify the following?
ENTITY (“POV”) – ORIGIN (TOP) – CONSOLIDTAION (LOCAL)
That is not true for OwnerPostAdj, Elimination, Share, and OwnerPreAdj. Those members rely on the relationship of the parent and the entity. We sometimes call that the 'Node'. The Node is almost like an entity within the entity. Like Inception... It's a way to manage the values that will change by entity, depending on what the parent is.
- OwnerPreAdj – A place to put journal detail before the translated values move to the parent entity.
- Share – This member is calculated as translated balances + owner pre-adj journals * percent consolidation. It can be stored or calculated dynamically. This is where you will also calculate any proportional adjustments using special rules (like only 50% of the income statement consolidates).
- Elimination – A place for storing eliminating values. (These are stored as Dr Cr values against the Intercompany accounts, and offsets are stored in the clearing or “plug” account.)
- OwnerPreAdj – Place to store adjustments after Share and Elimination values are moved up to the Parent Entity’s Local member.
If they want to show Elimination for example - Check your customers report – Did they specify the following?
ENTITY (“POV”) – PARENT (“POV”) – ORIGIN (ELIMINATION) – CONSOLIDTAION (ELIMINATION)
So, once they realize there is a relationship between these members, they will understand where is the data?!!
Did you learn something new or interesting in this blog post? Let us know in the comments!
