Need advice in architecture of model

[no_surrender]

Hello!

We need application to plan and approve salaries of emploees.
First idea that we have is to create one cube with next dimensions:
- elist dimension (list of departments) with structure like:

All depts
...Dept1
...Dept2

- employees dimension with structure like:

All depts
...Dept1
......Alan
......Jack
......Sarah
...Dept2
......Jonh
......Tom
..

I think its not good idea cause in this case we have a huge sparseness and unnecessary cells.

Are there any best practices for this case?

Thanks!

[lotsaram]

The TM1 database and calculation engine is nothing like EP. Large dimensions, large cubes and sparseness are not things to be concerned about.

[Alan Kirk]

We need application to plan and approve salaries of emploees.
First idea that we have is to create one cube with next dimensions:
- elist dimension (list of departments) with structure like:

All depts
...Dept1
...Dept2

- employees dimension with structure like:

All depts
...Dept1
......Alan
......Jack
......Sarah
...Dept2
......Jonh
......Tom
..

I think its not good idea cause in this case we have a huge sparseness and unnecessary cells.

I wouldn't get too fixated on sparsity; it's something that TM1 handles standing on its head which is what makes it the product that it is.

IMHO the second option is a bad design. Employees and their departments are two separate characteristics of a value for one basic reason; the employee can move between different departments over time.

Try attending some company AGMs some time just for laughs, and watch how many of them extol the virtues of their latest restructure which will give "a flatter, more accountable structure which more closely aligns management to shareholder interests and provides a synergistic leveraging of our divisions to provide them with the resources that they need to allow a win-win situation with more positively accentuated stakeholder outcomes". The more trouble a company has finding new revenue, the more frequent the restructures.

Once they do that your neat structure of Dept 1 consisting of employees X, Y and Z goes out the window. For that reason I'd make them two separate dimensions.

[no_surrender]

lotsaram and Alan thank you for your replies!

Once they do that your neat structure of Dept 1 consisting of employees X, Y and Z goes out the window. For that reason I'd make them two separate dimensions.

Could you explain whats a problem to move employee element from one parent to another?
Now i don't see principled difference between that and using attributes.

[Alan Kirk]

lotsaram and Alan thank you for your replies!

Once they do that your neat structure of Dept 1 consisting of employees X, Y and Z goes out the window. For that reason I'd make them two separate dimensions.

Could you explain whats a problem to move employee element from one parent to another?
Now i don't see principled difference between that and using attributes.

Think about it for a minute or two.

Your boss wants a report showing departmental values in June vs departmental values in December. In June employee X was in department 1. In December s/he is in department 2. So if you simply move him or her in your combined department / employee dimension, you can no longer run the report showing the values as they were last June because the element is no longer under that department. Attributes are hardly going to help you with this, especially if there is more than one move over that period.

If you have two separate dimensions then the June column can show a value for Employee X in department 1, and the December column can show a value for Employee X in department 2.

[no_surrender]

Think about it for a minute or two.

Yep, I really needed just think more about it
Sorry for that )

Thank you!

[stephen waters]

If you have two separate dimensions then the June column can show a value for Employee X in department 1, and the December column can show a value for Employee X in department 2.

This design also allows you to easily apportion an employee's costs between departments\cost centres if needed. eg 30% Dept 1, 70% Dept 2. This is a fairly common requirement.

[rmackenzie]

If you have two separate dimensions then the June column can show a value for Employee X in department 1, and the December column can show a value for Employee X in department 2.

Exactly... and zero suppression (a menu option in the cube view) is the key to not showing the June value for Employee X in Department 2 and the December value for Employee X in Department 1. You can also look at separating out Role/ Job Title from the person and having 3 dimensions: Person, Role and Department as it is also likely that in a large company an individual can move between roles, as well as departments, on different occasions.

Just to play devils advocate - a couple of other things might happen that provide exceptions to the rule of using this as a best practice (which it most commonly is):

1. Sales Representatives move between regions but their results should always 'follow' them - in this case, it could be considered an acceptable option to have the Region (read Department) drill down to the Person, as per your second hierarchy.

2. The boss in Alan's example has their own boss. They want to see a list of all departments and be able to drill into each one and see the current membership of the department. Because they may need to forecast some information on that row, having zero suppression switched on can sometimes be a pain if the person is 'new' and has neither an 'actual' nor a 'forecast' value. In this case, storing the current membership hierarchically comes back into play.

YMMV.

[no_surrender]

Thank you all! It's really helpful!