Best Cube Design for a Financial Model

Post Reply
pdxTm1Guy
Posts: 7
Joined: Wed Oct 16, 2013 1:09 am
OLAP Product: TM1
Version: 10.1.1 10.2.2
Excel Version: 2007 2010 2013
Location: Portland, Oregon, USA
Contact:

Best Cube Design for a Financial Model

Post by pdxTm1Guy » Wed Aug 22, 2018 4:14 am

Hello All,

I would like your input on the design of a Financial Cube Model. We switched out our GL source to MS AX and am debating on splitting the GL source into 4 separate cubes: Gross Margin, BS, SG&A, and Marketing or lumping everything into one cube.

The big question is weather to build one big cube or several smaller cubes that share some of the same dimensions. The cubes will not interact with each other as there are already a few summary cubes that will be loaded via TI's from the cube or cubes.

The source data is Microsoft AX with 9 different dimensions that consist of the following dimensions and # of elements.

Company: 5
Main Accounts: 687
Departments: 84
Product Category: 51
Product Sub-Cat: 116
Locations: 77
Marketing Accounts: 7749
Vendor Accounts: 1109
Project Accounts: 61
Month: 144

The biggest issue is the Gross Margin is used the most and will have to contain an allocation model but does not use the Marketing Accounts, Vendor Accounts, and Project Accounts. The users are in Finance and use perspectives and cube views 100% of the time. They do not use active forms and always use DBRW's. The BS accounts will also have rules and will require a forecast version.

I have thought about making one big cube but am worried with the allocations process and the # of marketing & vendor accounts (which grow every year), the user(s) will make more mistakes selecting dimensions and elements that are not used.

So one big Cube or several small cubes?

Thoughts?

babytiger
Posts: 71
Joined: Wed Jul 31, 2013 4:32 am
OLAP Product: Cognos TM1, EP, Analyst
Version: 10.2.2
Excel Version: 2013
Location: Sydney AU

Re: Best Cube Design for a Financial Model

Post by babytiger » Wed Aug 22, 2018 5:27 am

I guess you would have evaluate the pros and cons of each method:

Separate cube
pros:
  • less sparse cubes
  • easier to rule and feed
  • easier for user consumption (fewer dimensions to select from)
cons:
  • may require more TIs for data load or export
  • may introduce reconciliation issues (as one GL combining P&L and BS would generally makes sense)
  • may require drill-through between cubes
  • data may be duplicated between cubes
One large cube
pros:
  • fewer TIs for data import or exports
  • may be easier for user consumption through analytical cube viewer tools, such as cafe (or alike)
cons:
  • need to specify all dimensions in the cube for rules, even through a dimension is not relevant.
  • higher potential for over feeding, which in turn cause performance issues.
I am sure there's a lot more, pros and cons.

Based on your comments, I would go with multiple, fit-for-purpose cubes.

But, imo, a best design is one that fits the business' needs.
MK

pdxTm1Guy
Posts: 7
Joined: Wed Oct 16, 2013 1:09 am
OLAP Product: TM1
Version: 10.1.1 10.2.2
Excel Version: 2007 2010 2013
Location: Portland, Oregon, USA
Contact:

Re: Best Cube Design for a Financial Model

Post by pdxTm1Guy » Wed Aug 22, 2018 5:35 am

Thanks babytiger,

I am more concerned about the users over performance and not to mention the DBRW formulas wouldn't be as friendly with a smaller footprint. Also I agree that the rules could become complex if balance sheet and Income statement are combined.

Thank you for your input!

babytiger
Posts: 71
Joined: Wed Jul 31, 2013 4:32 am
OLAP Product: Cognos TM1, EP, Analyst
Version: 10.2.2
Excel Version: 2013
Location: Sydney AU

Re: Best Cube Design for a Financial Model

Post by babytiger » Wed Aug 22, 2018 5:59 am

If user usability outweighs performance, then I would be more incline to go with multiple cubes.

DBRW formulas on perspective would be shorter, since you don't have to select those non-relevant dimensions for a cube.
Users can still reference multiple cubes on the same sheet, (but it is more difficult for activeforms through when you have suppress zero on).
A bit of training in terms what data is held in each cube, then there wouldn't be any confusion of where to get data for a specific data set.

For me, multiple cubes is clearer from a usability point of view. But I don't know your data, nor your users.
MK

User avatar
tomok
MVP
Posts: 2490
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Best Cube Design for a Financial Model

Post by tomok » Wed Aug 22, 2018 12:28 pm

pdxTm1Guy wrote:
Wed Aug 22, 2018 4:14 am
Gross Margin is used the most and will have to contain an allocation model
Tread very carefully here. If you attempt to do this via rules you may be less than pleased with the results, as far as performance is concerned. It may work fine in development but once you roll it out and the cube is amped up with full production data it will slow down considerably.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
paulsimon
MVP
Posts: 647
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Best Cube Design for a Financial Model

Post by paulsimon » Wed Aug 22, 2018 8:46 pm

Hi

I agree with Tom, unless there is a real need to see allocations immediately that something changes, I would do them via a TI. In my experience most Accountants prefer to think of running the allocations and don't want things to change immediately. They tend to only run the allocations once the underlying figures have stabilised.

I would certainly keep the marketing cube separate given the size of the marketing accounts dimension relative to the others. From your description I cannot tell which other dimensions come from which ledger on AX, so I can't offer any comment on those.

Consider whether it is better to stick with the name used in AX or whether it is better to change the name of the dimension eg is marketing accounts really the salesperson, or the customer?

It might make sense to combine Gross Margin and BS if that gives a full balancing TB. However, it depends on the users and requirements. My major client capitalises a lot of expenditure so I&E without BS would be of little use for management accounts. However, in other companies the monthly management accounts is only based on I&E, and the BS is only used for Quarterly or Annual returns and then by the Financial Accountants who are a separate group of users from the management accountants.

I would also think about other dimensions you might need, such as Version, Measures, etc. Then look at the total dimension count. Too many dimensions can get overwhelming in Perspectives, though PAX makes better use of Default Elements.

Consider whether some of the dimensions on the ledger are really hierarchies, eg can Product Category and Product Sub-Category be combined into a Product dimension? Would it be better to do that? Are there rules in the ledger that would enforce the hierarchical relationship?

Consider who will be using the cubes. Eg are some cubes oriented towards the purchasing dept, others the sales dept, etc.

When loading the summary cubes consider whether it is better to load these at the same time as the ledger based cubes, or whether it is better to load the ledger based cubes then transfer from these to the summary cubes. Loading both in the same path can eliminate some timing differences.

Regards

Paul Simon

pdxTm1Guy
Posts: 7
Joined: Wed Oct 16, 2013 1:09 am
OLAP Product: TM1
Version: 10.1.1 10.2.2
Excel Version: 2007 2010 2013
Location: Portland, Oregon, USA
Contact:

Re: Best Cube Design for a Financial Model

Post by pdxTm1Guy » Wed Aug 29, 2018 5:52 am

Thank you all for the insight!

My main concern here was dealing with users that can easily make a mistake when multiple dimensions are involved. The customer wants "ONE BIG CUBE SO I CAN SEE EVERYTHING". They don't understand that in perspectives (as baby tiger referenced) you can reference multiple cubes using dbrws. Paul Simon you and I are on the same page with the fact that a marketing cube with 1000's of accounts should be stand alone not just for performance but for usability. Tomok, I don't do rules at leaf level (lessons learned) but thank you for reinforcing the argument!

My conclusion is to separate the GL into smaller cubes and then push everything (via TI) to a reporting cube. The entire set of processes to allocate and push to reporting is under 2 minutes (not bad considering end of month close).

One of the hardest things with TM1 is to get users to think over 2 dimensions!

Thanks again!

Post Reply