Need help with modeling employee and degrees earned

[Snabbles]

Requirements:
1) Provide monthly employee counts that can be sliced by an employee's degree granting institution(s) and/or institutional research activity levels

2) Count new hires by their highest degree earned at time of hire and/or by terminal degrees earned (a terminal degree is the highest degree you can earn in your dicipline, usually a PHD, but not always).

3) Identify new hires with ANY degree from a specific instition or research activity level.

4) Identify an employee's current highest degree, earliest baccalaureate, most recent masters, most recent doctorate, and current primary terminal degree

Here is what I am thinking, thoughts?

Institution Dimension
Institution Key
Effective Date Key
Expiration Date Key
Name (Type 1) (University of X, University of Y, etc)
Research Activity (Type 2) (High, Moderate, Low, etc)
Many more Attributes...

Degree Type Dim
Degree Type Key
Degree Type (Type1) (baccalaureate, masters, doctorate, etc)

Employee Dimension
Employee Key
Effective Date Key
Expiration Date Key
Name
Employee Type (Type 2)
Highest degree earned date key (Type 1)
Highest degree institution key (Type 1)
Highest degree type (Type 1)
Earliest baccalaureate degree date key (Type 1)
Earliest baccalaureate degree instituion key (Type 1)
Most recent masters degree date key (Type 1)
Most recent masters degree instituion key (Type 1)
Most recent doctorate degree date key (Type 1)
Most recent doctorate degree instituion key (Type 1)
Primary Terminal degree date key (Type 1)
Primary Terminal degree institution key (Type 1)
Primary Terminal degree type (Type 1)
Many more attributes...

Grain: One row per employee per month
Employee Monthly Snapshot Fact
Snapshot Date Key
Employee Key
Degree Group Key (People have multiple degrees.. I'm assuming this hits a bridge table that goes back to what? the my degree earned fact? Is that a smell?)
Highest Degree Type Key
Highest Degree Institution Key
Primary Terminal Degree Type Key
Primary Terminal Degree Institution Key
Demographic Key
Employee Type Key (I have this in Employee Dim as a type 2, it smells funny being in both places...)

Grain: One row per degree earned
Degree Earned Fact
Degree Earned Date Key
Employee Key
Degree Type Key (baccalaureate, masters, doctorate, etc)
Degree Institution Key

Grain: One row per new hire event
New Hire Fact
Hire Date Key
Employee Key
Highest Degree Type Key
Highest Degree Inistitution Key
Terminal Degree Type Key
Terminal Degree Instiution Key

[ngalemmo]

The model looks ok.

As for Employee Type Key, which implies an Employee Type (ET) dimension, I question the need for a type 2 use of Employee Type in the Employee dimension.

If I was to implement an ET dimension, I would include its FK in whatever fact tables it would apply and not rely on the employee FK for type. However, I would also include ET data in the employee dimension, but only carry the current values, to simplify most typical reporting scenarios (The employee dimension provides the current value, while the ET FK provides the point-in-time value). I would also place the current ET FK in the employee dimension as well, NOT for snowflaking (it would be hidden to end users), but as a convienience to the ETL process so it only needs to do a lookup on employee to get both FKs for the facts it is loading and as a means to update descriptive text for ET on the employee table should it be updated in the ET dimension.

[hang]

I would have a separate mini-dimension for the current employee education profile to wrap away those degree related type1 attributes. You may use employee natural key as the primary key for this min-dimension for simplicity, or more rigorously, you may use what Kimball called Durable key (DK) that is independent of the source system key in case of change on the natural key itself. Kimball has introduced similar concept in his recent article: http://intelligent-enterprise.informationweek.com/showArticle.jhtml;jsessionid=1CUSRQOQFBPYZQE1GHPCKH4ATMY32JVN?articleID=225700892.

The durable key will be FK in your normal employee dimension with other type1 and type2 attributes. This mini-dimension can be periodically populated by querying the Degree Earned Fact table. You may need a numeric Degree Level field in the Degree Type Dim to indicate the degrees’ ranking order. If the query does not take too long, you may just truncate the dimension and rebuild it whenever needed, or you may need to do upsert on the dimension for the changes if the performance is critical.

In your other two facts Employee Monthly Snapshot Fact and New Hire Fact, you may only need to have a single DK along with Employee Key and other dimension keys to make the whole set of current education profile available for the facts.

[Snabbles]

ngalemmo & hang - thanks for your thoughtful replies.

1) I adjusted the employee dimension so it provides the current value for employee type (ET) and I let the fact tables provide the point-in-time value.

2) I took a stab at creating an Education Profile Dimension. Luckly, I already have a durable key that is independent of the source systems! Because I need to know what a person's education level was at the time of hire I used a surrogate key as my primary key instead of the durable key. Next, I added a most recent flag and the durable key to the dimension. I plan on storing both the durable key and the surrogate key in my fact tables. This should let me look at my profile at the time of hire by joining on the surrogate key. Finally, I can see the current profile by joining on the durable key and the most recent flag, or I can recast the profile to a different point of in time using the effective dates.

Thoughts? I am a little worried that the names of my type 2 columns in the Employee Education Profile Dimension will be confusing (it's the most recent degree for the effective dated time period, etc...)

Changes in red.

Employee Dimension
Employee Key (PK)
Entity Durable Key
Name (Type 1)
Employee Type (Type 1)
...removed the current education profile attributes...
...other relativly stable attributes...

Education Profile Dimension
Education Profile Key (PK)
Effective Date Key
Expiration Date Key
Entity Durable Key
Most Recent Flag
Highest degree earned date key (Type 2)
Highest degree institution key (Type 2)
Highest degree type (Type 2)
Earliest baccalaureate degree date key (Type 1)
Earliest baccalaureate degree instituion key (Type 1)
Most recent masters degree date key (Type 2)
Most recent masters degree instituion key (Type 2)
Most recent doctorate degree date key (Type 2)
Most recent doctorate degree instituion key (Type 2)
Most recent terminal degree date key (Type 2)
Most recent terminal degree institution key (Type 2)
Most recent terminal degree type (Type 2)

Grain: One row per new hire event
New Hire Fact
Hire Date Key
Entity Durable Key
Employee Key
Education Profile Key
Employee Type Key

[hang]

I did not realised you would want to keep history of the education profile as they were originally type 1 in your employee dimension. However the type 2 attributes in the education profile dimension will cater for that. In your new design, you may not need the Entity Durable Key in the New Hire Fact as you can self join on the durable key with recent flag constraint on the profile dimension to retrieve the current profile, while straight join on profile key will give you the profile at point of time.

I would also include a type 1 current profile key in your employee dimension so it gives you a more clear and self contained employee dimension. Type 1 means it always points to the most recent profile, as the relationship with historical profile has been reflected in the fact table.

[Sponsored content]