Human Resources Dimensional Modeling

I am in the process of creating a DW for Human Resources but I have a few challenges. I am thinking of creating 1 dimension and 1 fact table but I noticed in a post you responded to that you recommended different dimensions for things like position, department, job etc. I am seeing these as attributes of the employee and hence I want to move them to the employee transactions dimension. Changes in these will result in a change in the state of the employee and I want to use SCD type 2 to track these.

What are your thoughts on this?

I read the Human Resources chapter in the Dimensional Modeling book by Dr. Kimball and I am not sure how to capture the data for the number of new hires if the data is as granular as to the employee id. Do I put a number in the filed if the particular row for that employee represents a new employee? Is he referring to a count on the total number of employee repeating in each row?

Each row represents an employee. To get the number of "new" hires, you simply count(*) where hire date < search date.

You can also make Position, Department, etc, a type 2 dimension as well to track the history. The main point is you don't want all the dimension data in one huge dimension with a 1-1 relationship with the fact. That design performs poorly. A better design are smaller (as in row count) dimensions that used to filter the facts.

As B&L said, the Employee (transaction) dimension should contain hire and termination date attributes which can be used to produce the count for new hires in the snapshot fact table. If it is monthly snapshot, it is a fact of 1 or 0 on each employee depending on if the hire_date is within the month.

I guess whether you should include Position, Department etc. in Employee dimension depends on the following considerations:

a. Are position and department multivalued attributes, meaning an employee may work on more than one position, or across different departments at one point in time. In this case, you may only be able to put “primary” position or department in employee dimension if you don’t want to resolve all the relationship through bridge or fact table.

b. Does position or department contain rich set of attributes on its own. In my case, the position contains supervisory relationship and is defined by more than 20 attributes. Our department is really an org structure that is comprised of 7 level hierarchy. To complicate further, an employee can have primary position and secondary position. So denormalising all these attributes into employee dimension is a little uncomfortable, even from dimensional modelling stand point.

c. How often do you browse employees with their position and department together. If you always want to see the attributes together, you may just denormalise the essential attributes, say Code(Id) and Name, into employee dimension and only set type 2 on the natural keys (NK: eg. position_id or department_id), meaning you are only interested in tracking relationship changes. In this approach, you can still bring across other attributes through NK if necessary.

Approach C will work for us. We have situations where employees are seconded to other departments.

In my case, it is the position that is seconded to by employee, and the position belongs to a department. I think option C strikes reasonable balance between dimensional and relational thinking. If we put SK in employee as an outrigger, than the type 2 change on position will invalidate the SK in employee dimension and hence trigger another type 2 response. If we denormalise everything, the employee dimension becomes overcomplicated with two entities that have their own type 2 implication and complexities. Especially in my case, there is also a bridge on position to handle the recursive supervisory relationship.

On the other hand, if we don’t associate position to employee at dimension level, browsing employee with assigned position and department has to go through some type of fact or bridge table with at least two joins, and sometimes may involve subqueries on the fact table. All these report time complexities may be avoided by extending the employee dimension with a few additional relevant attributes taking advantages of both worlds.

Thank you guys. Hang, I have created all the relevant dimensions and create a transaction description to provide encapsulation for actions such promotion, transfer, hire etc. I am now seeing the complexity you mentioned in your post about going through the fact table to browse employee with assigned position. I am thinking that in addition to having these dimensions linking to the fact table, I should also do the association in the employee dimension to minimize the joins at query time.
What do you think.

Sofer17 wrote:I should also do the association in the employee dimension to minimize the joins at query time.
What do you think.

If you do so, the employee dimension becomes what Kimball called 'transaction dimension' where you insert a new record for change in any associated attribute with effective date pair to track the change like in a typical type 2 dimension. You could normalise some of the attributes, like position, if there are many of them belonging to the same dimension. If I do so, I would link them by NK if position is also a type 2 to avoid invalidation of SK in employee dimension due to the change in position. Otherwise, you may treat the SK as type 1 in employee dimension and leave the historical association in the fact table.

The point is, the transaction dimension can centralise all the attributes related to employee in a single dimension, and references it's SK in other fact table to provide a simple entry for the whole employee profile at any point in time. Transaction dimension itself is also a base to produce other useful facts.

However, Kimball lately introduced another concept 'Extreme Status Tracking For Real Time Customer Analysis' where you track the associated attributes in a fact table having both SK and DK (Durable Key) in it to cater for both current and historical views of type 2 dimensions to avoid self join on the dimensions. Please refer to the following article for full flavour of the concept: http://www.kimballgroup.com/2010/06/21/extreme-status-tracking-for-real-time-customer-analysis/ .

These two approaches are all effective and advanced concepts. You need to strike a proper balance based on your business case, especially the data volume and complexity around employee dimension.

Thanks again Hang. I will treat the SK as type 1 in employee dimension and leave the historical association in the fact table. I appreciate the knowledge on 'Extreme Status Tracking For Real Time Customer Analysis’.