Suggestion on Model Design

[lav4you]

Hi All Guru,

I want to design an application for headcount and staffcost planning.
The budget is created on a monthly basis after considering different benefits and gross salary measure(appx 25 items in all) at each employee level.
Also i have to capture various other details related to employe like name, emp number, nationality etc(appx 10 items)
And the company has 30,000(thirty thousand) employees.

Now the question is it feasible in TM1 to create a dimension with Emp code with 30,000 items?
or should I adopt any other approach.

Just to clarify further. I am aware about the fact that numbers can be reduce by introducing a logical additional dimension like department or aggregation stop(1000, 2000) etc.. but that is not the answer I am looking for..

thanks in advance for your thoughts on this query

Regards,

Lav

[tomok]

Now the question is it feasible in TM1 to create a dimension with Emp code with 30,000 items?

Yes, it is certainly feasible as I have done it before. Not 30,000 employees but pretty close to that. As long as you size your environment properly it shouldn't be an issue. As far as summarizing and reporting the results, i.e., combining the headcount data with the other budget amounts, you may want to incorporate TI processes to move the data to summarized cubes. Having live links into the the employee cube may cause performance problems.

[lav4you]

Hi Tomok,

thank you for your reply. I am worried about the dimension size as it will have 30,000 items and since its with employee code there will be a frequesnt update as well. will it be able to handle such kind of dimensional adjustment.
About summary cube not much issue it can be handled

[garry cook]

Shouldn't be a problem as long as you're not doing anything crazy with it. Had dimensions much bigger than this running fine with a much larger number of elements. Eg, Had 200,000 contracts in a full P&L / BS model with fully modelled chart of accounts without any adverse effects.

Start throwing in rules with dodgy feeders though and the game changes

[lav4you]

Sounds great garry,

further i would like to know that should I create this dimension with aggregation at small intervals Like 1000 for (1-1000) 2000(1001-2000) etc..
or should i try to have all of them as leaf elements.
which one will give better performance for cube and end users ??

[tomok]

further i would like to know that should I create this dimension with aggregation at small intervals Like 1000 for (1-1000) 2000(1001-2000) etc..
or should i try to have all of them as leaf elements.

By aggregate, I assume you mean hierarchy. The hierarchy(s) you create in this dimension are going to depend on what makes sense for your model. You might decide to create a department hierarchy, rolling the people to the department they are in, a hierarchy by who they report to for HR purposes, etc. You are not limited to just one hierarchy.

[lav4you]

Hi,

Hierarchy is not must for my model requirement.
Since I am going to build a large dimension(approx 30,000 elements), i just want to know whether I must include all items as leaf node without hierarchy or I must create a hierarchy levels?
which dimension structure will give best overall performance?(considering cube view, ease of use, dimension maintenance, data spread, rules etc..)

If answer is hierarchy than which type of hierarchy structure
A) small intervals like 100 to 200 elements
B) large intervals like 5000 - 10000
C) multiple level 10000 > 1000 > 100 > Node (001 to 100)
D) or anything else

[tomok]

Let me be blunt with you here. You really need to take a class in TM1 model design because you clearly don't understand how this works. You are doing a great disservice to your client to tackle something as complex as a headcount planning model as your first TM1 project. Randomly creating a hierarchy in the employee dimension makes no sense at all. Yes, pulling a subtotal of employees from the cube will be faster than pulling all employees, but only to the extent that pulling that subtotal is what the user wants. Just randomly throwing sub-total points in the hierarchy will not make the overall total calculate any faster. You need to create the organization of your model around what the user needs, not what may make it run faster.

[Gregor Koch]

Hi
Agree with tomok. Either you need to look at a total/aggregation/consolidation of something or you don't. If you do then create the consolidation.
Anyhow in HR models it is a lot of times better to have Departments, Reporting Lines and the like in a separate dimension, as employees move between or are even split between them (the departments) and not necessarily get a new employee number every time the do move. If you understand why the additional dimensions are of advantage then you can answer the question on what hierarchies you actually really need in you employee dimension.
Cheers

[lav4you]

Hi Tomok,

With all due respect to your comment and without any offenses to whatever you said

You should understand basic difference between expertise and curiosity.
This site belongs users alike Martin Ryan and Jim who are ready for the dumbest question ever.
Like these
difference between prolog, metadata, data, epilog

This place is to explore and learn and advice,
nobody has forced anyone participate.
and its none of your business to see what I know and how I manage my work.