TM1 model design: one cube vs many cubes

[vovanenok]

Hi all

I know this is very generic question and there are many things it could depend on, but I would like to hear your opinion on the next situation.
Let's say you have 50 departments and they need to forecast operating expenses. The problem is each department has its own expense groups to forecast by: it could be a particular product or a group of products (where 99% of products are department specific - they are produced by only 1 department) or just some services.

If I had just a few departments I would definitely create a cube for each, but with 50 cubes I need to think.

I see two possible solutions:
1). Create 50 cubes and keep all the things simple. With switching from Architect to Performance Modeler cubes could be organized by folders
2). Create 1 cube with dimensions: Period, Version, Department, Account, Measure and develop Account dimension to serve all the departments having something like:

REVENUE
---- Revenue Source 1
---- Revenue Source 2
...
OPERATING EXPENSES
---- dept1
-------- dept1_expense_category_1
-------- dept1_expense_category_2
---- dept2
-------- dept2_expense_category_1
-------- dept2_expense_category_2
...
EBITDA

With solution 1 everything is simple.
With solution 2 I will get a very sparse cube and need to handle Account subset for each department. Also there is chance at some point that cube will become very big and cause performance issues.

I personally tend to solution 1 but would be very interesting to hear your thoughts, feedback from real experience

[mvaspal]

Hi
I would think - based on the information you provided that are a bit controversial, for example you talk about products but in point 2) you do not list this dim - that creating 50 cubes for this is a very bad idea. From 50 cubes we cna usually cover full controlling systems at at least mid-size companies. I do not agree this is a simple solution because this means too many cubes to maintain. Just imagine what happens if in the future you need a TI or a rule to populate another cube having 50 cubes as data source...will you write 50 TIs?

Instead, I would use the combination of:
- subsets (I can imagine both static or dynamic)
- views (creation of 50 views and 50 subsets can be quite simply automated)
- element security
- cell security
- maybe active forms with drop downs on title and dynamic subsets on rows


Unless you are a consultant paid based on the nr of cubes ...

[vovanenok]

Hi
I would think - based on the information you provided that are a bit controversial, for example you talk about products but in point 2) you do not list this dim - that creating 50 cubes for this is a very bad idea. From 50 cubes we cna usually cover full controlling systems at at least mid-size companies. I do not agree this is a simple solution because this means too many cubes to maintain. Just imagine what happens if in the future you need a TI or a rule to populate another cube having 50 cubes as data source...will you write 50 TIs?

Instead, I would use the combination of:
- subsets (I can imagine both static or dynamic)
- views (creation of 50 views and 50 subsets can be quite simply automated)
- element security
- cell security
- maybe active forms with drop downs on title and dynamic subsets on rows


Unless you are a consultant paid based on the nr of cubes ...

Hi mvaspal and thanks for your comment
I didn't include Product dimension because expenses are not always product related, even opposite - in most cases it's just some expense groups (for example: sport equipment; equipment moving; expenses to produce all TV programs related to "men's tennis"; expenses to produce "Daily News" program only; expenses to gather news for "Daily News" program only; etc.). So I create these elements in Account dimension (in average it's from 10-40 different expense categories per department) and created Account subset for each department.

I've never had such situation before, it was always one cube with common GL accounts. But here they want to have custom accounts, which are mostly specific for a particular department.

If all 50 cubes have the same structure I don't need 50 TIs to copy data to somewhere. One parameterized TI is enough.
I've worked with really big models and big cubes where I had automated TI to create a series of cubes for each next year (including rules, security, etc.), the model was big, but performance was amazing and the client was happy (compared to the previous solution with one big cube and a very complex rule).
What makes me think more about it is that I use TM1 for other projects within this client so gradually I will need much more cubes and having hundreds cubes (plus a bunch of system cubes) doesn't look very well.

On the other hand that forecast cube has almost no calculations, which shouldn't cause any performance issues. Using attributes subset creation can be automated as well. When that cube becomes too big I could separate it into two: one to store current periods data and another one for history. So probably solution 2 makes more sense.