HR periodic snapshot with employee transaction dimension

Hi DM gurus,

I am trying to understand the design that was in the Kimball's book 'The DW Toolkit 2nd Edition' pg 192, figure 8.3. Seems like the grain of the fact table is one row per employee, per month, per Org. if that is the case then wouldn't the up Employee Count, Transfer count, and promotion count will be either 1 or 0 only in this design. So to get a monthly snap shot of headcount you would have to sum the employee count and the same for the other two. My question is if this is a monthly snap shot then the Employee Transaction Key should not be there, or am I missing something.

Please help.

Thank you in advance.

Haven't looked at the book, but my guess is he is describing individual HR actions that affect employment, transfers and promotions, not a monthly snapshot. The measures would reflect the net effect in specific categories, to avoid the need to interpret the nature of the action during a query. If that is the case, you would see +1, -1 and 0 values in the measures. If an employee transferred you would see two rows, one with negative values for the org the employee left and another with positive values for the org the employee went to. Both would carry the same transaction id. An alternative would be to have one row with two sets of dimensions for the from and to organizations, positions, and job.

If you want to create a monthly snapshot of counts, I would not include the employee dimension and include measures such as end employee count, hire count, exit count, transfers in, transfers out, and promotion count.

Thank you for your promt reply. I agree with you that for the for the weekly or monthly snapshot the employee dim key should not be included, but it does so in the book.

Thanks again for your input.

Letap

The employee dim should be included in the model - it represents the employee attributes at the end of the snapshot period.

The model works extremely well - Ralph proposed it in about 1998, i've used it successfully since about 2002.

Thanks for your input LAndrews. I know Dr. Kimball is very thorough in all his work but this baffles me.

If that is the case then what is going to be the value of say Employee Count at the end of a given month. Will it be 1 as the grain is one row per employee, per month, per org. or will it be xxx where xxx represents the total employee count at the end of a given month? Where am I missing the boat?

Thank you in advance for your time and input.

Letap

The grain of the fact is one row, per employee, per month ... therefore employee_count=1.

Employee_count is then an additive measure for all dimensions except time (because it is a snapshot fact). I find it easier to create a measure on the fact rather than within the BI tool having count(employee_number).

The real value of the fact comes from the other measures (num_hires,num_promotions,num_terminations,vacation_balance etc).....

Thanks for your prompt post. Really appreciate it.


Letap

I think the real value of employee transaction dimension is to facilitate point in time analysis on employee profile by turning a factless fact table into a dimension. The monthly periodic snapshots give you the ability to conduct trend analysis on the key business metrics under the historical (month end) dimensional context provided by the transaction dimension.

As Andrew said, the grain is at employee level, so employee count is 1 for each record, a typical response turning count into sum in a fact(less) table. However all other measures are monthly summaries for each employee and they may be 0, 1 or greater.

Why is organization not an attribute of the employee? This would still result in 2 rows in the fact table at the end of the month.1 with the neww organization where the eployee is with a count of 1 and one with the previous organization with a count of 0. I am not sure if I get this reasoning...Thanks for your help.

The organization attributes (e.g. Department or supervisor) can be part of the employee dimension as type-2 attributes.

The fact table discussed in kimball's book can also have an organization dimension. Organizations are typically very ragged hierarchies, and are best supported using a bridge structure between the fact and dimension.

The grain of the fact table is one record for each employee, representing the employee at the end of the period. (e.g. month). If an employee transfers from organization A to organization B within a month, the fact record shows him in Org B. If you have a measure for transfers, the value=1.

Thank you. We dont really use organization. We have departments and worktypes that are currently used for reporting. We would like to show employees in a certain department by worktype as at the end of the month. Should I create different dimensions or can I include these in the employee transaction dimension?

Will the fact table be able to do things like show retirement benefits over time or employee count year by year? I am wondering because you mentioned 1 entry in the fact table which shows the current state of the employee.

In modeling HR, you need to separate people from positions, titles, cost centers, business units, departments, whatever... So there is an employee dimension that describes the person. The position, job, title and so on, are all contexts of employment of the employee. The state a person is in at a point in time is a fact, not a dimension. The position, job, cost center, etc... are dimensions of the fact. So, the employee action fact table is simply a record of these actions (hire, promote, salary change, etc...) with the appropriate dimensions. The dimensions would reference the new state (transfers, promotions).

The state of employment at a point in time would be an aggregation of the states in effect at that time.

I think I understand the idea of recording the states of the employee in the fact table on a monthly basic. So if the employee is transferred to a department within the month then that employee will be recorded in that department as a transfer at the end of the month in the fact table. The employee transaction dimension will have the new state of the employee with a flag of current or true.

Therefore the currect state of the employee is always in the fact table...hired, terminated, transferred, promoted, sick leave balance, vacation leave balance, helath insurance paid this month, salary paid this month...etc. ?

It is not storing the state of the employee on a monthly basis, it is tracking the changes in state over time. Each state has a begin and end period which covers the period to the next change.

Vacation and sick balances would be kept in another fact because these balances are independent of state changes. Although you could record these values in the state fact and create rows whenever the balance changes. Either way has good and bad points.