Need advice in architecture of model
-
- Posts: 12
- Joined: Thu Aug 23, 2012 8:15 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Need advice in architecture of model
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!
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!
-
- MVP
- Posts: 3667
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Need advice in architecture of model
The TM1 database and calculation engine is nothing like EP. Large dimensions, large cubes and sparseness are not things to be concerned about.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- Alan Kirk
- Site Admin
- Posts: 6610
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Need advice in architecture of model
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.no_surrender wrote: 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.
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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 12
- Joined: Thu Aug 23, 2012 8:15 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: Need advice in architecture of model
lotsaram and Alan thank you for your replies!
Now i don't see principled difference between that and using attributes.
Could you explain whats a problem to move employee element from one parent to another?Alan Kirk wrote: 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.
Now i don't see principled difference between that and using attributes.
- Alan Kirk
- Site Admin
- Posts: 6610
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Need advice in architecture of model
Think about it for a minute or two.no_surrender wrote:lotsaram and Alan thank you for your replies!
Could you explain whats a problem to move employee element from one parent to another?Alan Kirk wrote: 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.
Now i don't see principled difference between that and using attributes.
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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 12
- Joined: Thu Aug 23, 2012 8:15 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: Need advice in architecture of model
Yep, I really needed just think more about itAlan Kirk wrote:Think about it for a minute or two.
Sorry for that )
Thank you!
- stephen waters
- MVP
- Posts: 324
- Joined: Mon Jun 30, 2008 12:59 pm
- OLAP Product: TM1
- Version: 10_2_2
- Excel Version: Excel 2010
Re: Need advice in architecture of model
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.Alan Kirk wrote: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.
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Need advice in architecture of model
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.Alan Kirk wrote: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.
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.
Robin Mackenzie
-
- Posts: 12
- Joined: Thu Aug 23, 2012 8:15 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: Need advice in architecture of model
Thank you all! It's really helpful!