Part 3 of 3 - In this series of blogs we discuss the Consolidation and dimensions that drive the power of the cube in OneStream.
I first used an Origin dimension with another product, but it was completely custom. OneStream smartly has this prebuilt and ready to use. The concept of this dimension is it will take the data that you see in consolidation and show you where it came from. Think about just looking at data in Local. Was it loaded from the ledger? Was it a journal? How about at the parent entity where it isn’t as clear?
I would strongly recommend reading the OneStream Design and Reference Guide for this dimension (I quoted the guide here a couple times, but it’s still a helpful read). It has a great overview called “How the Origin Dimension Works with the Consolidation Dimension” Page 96. (But that might change depending what version of the guide you’re reading.) The ideas around consolidation and aggregation tie very closely with the functionality of the Consolidation and Origin dimensions.
Let’s go through how this works, and some tips to get the most out of it.
Origin primarily calls out how a number originated. So, they are broken out by each of these.
Imports and Forms
The first break out of the data is when it's first loaded from stage. You will see that in the Import Member in the Origin Dimension. When data is entered in web or Excel Forms, it appears in the Forms Member in the Origin Dimension (with one exception discussed under Adjustments). The Import and Forms Members are coupled with the Local Member of the Consolidation Dimension.
Consolidation Dimension Origin Dimension
Adjustments
Journals (With a journal or an adjustment form) are in the Adjustments. This starts in the AdjInput Member of the Origin Dimension. Journal Adjustments can be made to Local, Translated, OwnerPreAdj and OwnerPostAdj of the Consoliation Dimension. When a consolidation is run, the AdjInput entries in child Entities are consolidated into the AdjConsolidated Members in the Parent. Users can drill down into the Adjustments Member in a Parent to show adjustments made in both the Parent and child Entities.
Consolidation Dimension Origin Dimension
Eliminations
One of the great things about OneStream is the detail you can see in the Elimination Origin Member. If you think about how people used to fill out green sheets (before Excel…) They would have four columns.
A + B + Eliminations = Total.
OneStream lets you see more detail –
A + (Eliminations for A) + B + (Eliminations for B) = Total
The Point of View for theses members are
E#“A”.C#Local:O#BeforeElim + P#”TOT”.E#“A”.C#Elimination:O#Elimination
Note: First notice you need the Parent, that is because you are in the Node. Second, notice the need to combine Consolidation and Origin to get the right value.
Consolidation Dimension Origin Dimension
Intercompany Elimination
So, now that we discuss where you see the eliminations, lets talk about the process a bit.
“The Entity structure above belongs to GolfStream, a fictitious golf manufacturer. If the Detroit Entity sold golf club shafts to Monterey who assembles the final club product, this would be an intercompany transaction. The following prerequisites must exist for the transaction to eliminate.
Monterey and Detroit must have there Is IC Entity property set to True.
The Accounts Intercompany Receivables and Intercompany Payables must be set with the Is IC Account property set to True and the Plug Account pointed to a third account.
The intercompany entries must properly note the intercompany partner in the IC Dimension Member.
For example, Detroit would book an entry to Intercompany Receivables and the IC Member for that entry would be Monterey. Intercompany Eliminations occur once the values roll up to a common Parent. As the consolidation begins, Detroit consolidates its values to Michigan and Monterey consolidates its values to California.
An elimination does not occur because they have not yet consolidated their values to a common Parent. The elimination occurs when Michigan and California are consolidated into the US common Parent. The two intercompany values will be eliminated at this level with any discrepancies being posted to a suspense (Plug) Account. “
Note: Do NOT use Extensible Dimensionality to extend Accounts used within the Intercompany.
The eliminations go through this process (maybe not quite in this order)
Looping through all accounts in the account dimension;
- Is the account an IC account?
- Is there a plug account identified?
- IS the IC member not “None” and a descendant of the Parent Entity?
- Next we write an offsetting value to the account, and the other side of the entry to the “Plug”
If both sides offset the plug account should be zero! This is a great place for a confirmation rule!!!
Direct and Indirect Eliminations
Ok, so my example above of how the Origin and Consolidation break out the detail of eliminations is great. But people still want the report to read the old way…
A + B + Eliminations = Total.
The Origin Dimension helps you do this by including the Direct and Indirect members. This will simplify reporting on Eliminations.
I like to say the Direct elimination are the eliminations of the children, Indirect are eliminations of all Descendants.
“The Direct member returns the results of Eliminations that occur from transactions, removing the direct children of a parent Entity. Indirect returns the total Eliminations that occurred outside the direct children of a parent Entity.”
“In the example, transactions that occurred between the HQ2 Entities eliminate at HQ2. Transactions between members of HQ1 and HQ2, such as a transaction between Paris and Hartford, eliminate at the first common parent, Total Company. Reporting on the results at the Total Company level, Direct returns results that occurred between the HQ1 and HQ2 groups. Indirect at Total Company would allow reporting on eliminations outside its direct children, HQ2 eliminations.”
So finally, your report might have a POV like this –
A + (Eliminations for A) + B + (Eliminations for B) = Total
A + B + Direct Elim Total = Total
Tell us in the comments, what tip/information did you find the most useful during this three part series?
