Multiple COA Best Practice

Post Reply
mnasra
Posts: 104
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Multiple COA Best Practice

Post by mnasra » Mon Jun 22, 2020 2:28 pm

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
Thanks
Micheline

Wim Gielis
MVP
Posts: 2467
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Multiple COA Best Practice

Post by Wim Gielis » Mon Jun 22, 2020 3:02 pm

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, ...)
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

mnasra
Posts: 104
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: Multiple COA Best Practice

Post by mnasra » Mon Jun 22, 2020 3:48 pm

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
Thanks
Micheline

User avatar
Steve Rowe
Site Admin
Posts: 2055
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Multiple COA Best Practice

Post by Steve Rowe » Mon Jun 22, 2020 5:09 pm

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
Posts: 38
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

Re: Multiple COA Best Practice

Post by scrumthing » Mon Jun 22, 2020 5:31 pm

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.
There is no OLAP database besides TM1!

lotsaram
MVP
Posts: 3412
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Multiple COA Best Practice

Post by lotsaram » Mon Jun 22, 2020 8:57 pm

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Wim Gielis
MVP
Posts: 2467
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Multiple COA Best Practice

Post by Wim Gielis » Tue Jun 23, 2020 9:33 am

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Selecting elements in the Subset Editor quickly

Post Reply