Multiple COA Best Practice

[mnasra]

Hi Experts,

I need your advice.
I have a customer that have multiple companies, multiple Chart of accounts.
They do have Controller for the Consolidations.

We need to budget by Company /COA in TM1.
My question: Do I create 1 dimension that contains all Chart of accounts in different hierarchies? Meaning 1 cube, for all entities, and build security to determine which hierarchy belongs to which Entity.
or Do I create different 'sets' of cubes for each entity/Coa?

Thanks

[Wim Gielis]

Different sets of cubes. Not for each entity/COA but 1 set for each ERP packet containing source data.
Several subsidiaries in the group could share the same ERP / COA. This is a staging layer.
Then add mapping cubes towards a reporting layer and a common reporting COA. Map all local elements onto the reporting elements, for several dimensions (accounts, cost centers, profit centers, companies, trading partners, ...)

[mnasra]

Perfect. that confirms what I thought.
And yes, obviously, it will be 1 set per ERP (which can have more than 1 entity).

I inherited this account from another consultant, who tried 1 COA for all (because it is easier to report I guess). But here I am, and he is not.
That says it.

Thanks again for your super quick response.
Stay Safe

[Steve Rowe]

Wim's very experienced but just take care, as just because person A supports a particular methodology this does not mean it is the most right for the situation you find yourself in.

There is a lot of devil in the detail, not least what is value for money for the customer. especially if they have gone down a particular route already.

A single cube approach to this where there is one COA with the different accounts (probably concatenated with the ledger code to maintain uniqueness) from the different ledgers mapped directly into a single consolidation structure with a larger entity dimension is an equally valid build IMO. Yes the COA will be large and "untidy" but with the multi-cube approach you still have to maintain all the mapping relationships.

You mention the key requirement in your 2nd response "it's easier to report from", we build reporting systems not "pretty systems".

What about linking cubes up in the multi-cube approach when you create your reporting cube, what are you going to use? Rule and feeders or TI, how are you going to handle the summarisation required. Whichever you choose has an overhead, which does not exist with the single cube approach.

What about when a user wants to drill down to the N level accounts from the summary position, how are you going to handle that?

Just to be clear I'm not knocking the multi-cube approach, just pointing it out is far from obvious what the right approach is and to make sure you understand all the requirements and all the implications of the design choices being made.

[scrumthing]

As always there is no right answer. I like the multi-cube approach as Wim described it from a technical point of view.
but I had a customer who reported all 500 subsidiaries in 167 countries with just one cube. The dimensions were a mess when you looked at it but they had hundreds of very good organized views and subsets so this thing was not pretty but a real workhorse which run for the last ten years without any big problems related to the structure of this cube.

[lotsaram]

If this is primarily for consolidation and you have business segment dimensions aside from Legal Entity then it could get messy and separate cubes per ERP/GL are probably a good idea. However, if all you care about is Legal Entity consolidation and statutory IFRS/GAAP reporting based on the CoA then you can by all means do it all in one cube. Each CoA can exist as a separate rollup in the CoA dimension and the group reporting CoA also is just a rollup with aome level of the accounts rolling into its nodes (which are all C elements). This could be at CoA leaf level into the Group CoA, but more likely 1 or 2 levels up the individual Legel Entity CoAs is a logical level to roll into the group reportgin structure.

[Wim Gielis]

I should have added that indeed, there are multiple approaches to this. There is no single best way. You should consider all aspects. In a multi-cube architecture, drill through to the local level can be done by having cubes with BOTH the reporting elements AND the local elements.