Employee With Multiple Jobs

Hello;

I am developing an Employee Transaction Dimension such as that seen in Figure 8.2 of the "The Data Warehouse Toolkit". In my case, an employee may have multiple jobs. Some of the attributes found in the Employee Transaction Dimension are related to an employee while others are related to an employee's job. For example, Employee Name and Employee Address are related to an employee while Job Grade and Salary are related to an employee's job. Transactions affect job attributes only.

I am not sure how to model these relationships. I have considered the following

1. An Employee Transaction Dimension where the natural keys are Employee ID and Job ID. The employee related attributes would be duplicated for an employee with multiple rows (jobs) in this table.

2. An Employee Dimension where the natural key is Employee ID and a Job Transaction Dimension where the natural key is Job ID. An Employee to Job Transaction bridge would be built.

I am leaning towards option 2. Using this option I would split the Human Resources Snapshot Fact as seen in Figure 8.3 in to a Human Resources Employee Snapshot Fact to capture measures such as Retirement Fund Employee Contribution and Vacation Days Accrued and a Human Resources Job Snapshot Fact to capture Salary Paid and Overtime Paid.

Are there other options that I should consider?

Thank you. Pete.

Option 1. It's a normal reaction for folks with OLTP modeling experience to split this dimension into two different dimensions. There's clearly two different entities. But that's the point with this particular dimension, to reflect the employee state at a point in time. As long as the relation between employee and job remains 1-1, stick with Ralph and Margy's original design.

Are you saying an employee has multiple jobs at the same time? Or that an employee changes jobs over the course of employment? The latter is normal and covered by the model, while the former is pretty unusual. If the former is the case, could you elaborate?

Yes, in my organization (higher education) an employee can have two jobs at the same time. For example, one of our employees works as a part-time Graduate Assistant for one department and a part-time Academic Specialist for another department. A job FTE attribute indicates the percent employed for a given job.

Have you considered creating a fact table instead?

Since you have FTE, I don't see where there is a problem. Assuming you have an employee action fact table, the dimensions, among many others, would include employee, job, action, effective date, expiration date, and FTE would be one of the facts. From this you could derive a job history fact or other aggregates depending on what you need to report on.

With an FTE value, the number of jobs shouldn't be an issue as you would be recording events relating to a particular employee at a particular job. Even general events, such as the student leaving college amounts to leaving each individual job.

The thing is, you do not have a '...transaction dimension'. You have a transaction fact table that has dimensions which include employee and job.

I thought that the purpose of the Employee Transaction Dimension was to eliminate the need for a Employee Transaction Fact Table. Perhaps they both are useful.

Also, the natural key of the Employee Dimension is Employee ID and the natural key of the Job Dimension is Employee ID and Job ID. Is this a flaw in this design? Or is it acceptable?

I don't know what an employee transaction dimension would be. There could be a transaction dimension that describes the attributes of a particular transaction (or in HR venacular, an action) but the actual event is reflected in a fact table. Who, what job, when, the action and so on are all dimensions to the event. There are usually aggregates and snapshots of this to support headcount reporting and 'who is where' reporting.

As far as the job dimension goes, its natural key should be the job ID. It is safe to assume that employees will move in and out of a job ID and that the attributes of the job ID remain the same. Even if the attributes change, the employee in the job does not dictate that. But if you are combining employee ID and job ID into the dimension to store compensation data for example, your model is flawed. Again, compensation is a fact related to employee and job (among other things). The job dimension should address attributes that are related to the job and nothing else.

The Employee Transaction Dimension is illustrated in Figure 8.2 of the "The Data Warehouse Toolkit". I tried to make this work for employees with multiple jobs but it appears that I am better off moving forward with a Employee Transaction Fact and Employee and Job Dimensions. Thank you for your help.

There is nothing wrong with combining 2 the contents of 2 dimension tables if it makes sense to do so, particularly if it reduces the number of dimension tables on a wide fact table. The Employee Transaction dimension can be seen as a junk dimension. If there is a need to roll up the data to an aggregate table to the Employee Level or Job level, then additional surrogate keys can be added for the employee or Job. In essence, by combining Job and Employee into 1 dimension table, there are 2 hierarchies or roll up paths: From Employee/Job to Employee and Employee/Job to Job.

Combining the data into one table may or may not make sense, depending upon the data and how it's used. If combining the data blows up the dimension table and the data is frequently used separately in queries, then it may not make sense. But if Job and Employee are always used in the same queries and it doesn't blow up the dimension table, then it makes a lot of sense to combine them.

I have a similar situation (also higher education).
We have employee's with multiple concurrent jobs.

I have an Employee dimension,
A position dimension (which I think is the same as the job dismension). Has defaults for the position.

I am looking at using a Employee Transaction Fact table.

I have some fields that relate to an employee in a position such as hourly rate, Standard hours of the job, Grade,
Salary Grade and step etc THey can be different than the defaults for a position.

At the moment I have put these into a separate dimesnion that grows at the same rate as the fact table so looks more like the Employee Transaction Dimension as illustrated.

I don't want to put them into the fact table. Where else should I put them?

Thanks for your help.

Try one or two junk dimensions. A junk dimension is a collection of 'left over' attributes with a natural key of the attribute values. You group attributes together that are either low cardinality or have a moderate correlation between them. Set up correctly, the number of rows in a junk dimension are always significantly less than the number of rows in the fact.