Attributes as part of employee dimension and/or own dimension

I'm currently modeling Job Title as part of my employee dimension, however I've found a need to also use it elsewhere (I also need to model an "open position" which presents the requirement for Job Title too), so that prompts me to break job title into it's own dimension vs. just sitting solely on the employee dimension.

How can I keep job title in the employee dimension while also having it as it's own dimension? That sounds like an outrigger, but I'm hesitant to use them. This is how I *want* to model it:

dim_employee
- id
- first_name
- last_name
- employee_number
- job_title_id
- etc.
- row_effective_date
- row_expiration_date
- row_current

dim_job_title
- id
- job_title_name
- job_title_description
- job_title_classification

Would this be an acceptable approach? As I said, I'm hesitant because it's easy to see this morph into removing many attributes and exchanging them for foreign keys as outriggers (state, address, manager, etc.)

This same issue applies to the "Work Location" of the employee. Each employee has the office they work at. This could be represented as dim_work_location and also sit on dim_employee as well as an outrigger.

Hi - a dimensional model is not, and should not be, normalised. A DM should simplify your transactional model and reduce the number of joins.
If an Employee has job title attributes then put them in your Employee dimension; if another entity also has job title attributes then include them in the dimension for that entity.
If you need to associate Job Title attributes to a Fact and none of your dims that already hold these attributes is associated to (or relevant to) this fact then just create a Job title Dim as well.
If an attribute has a 1:1 relationship with a Dimension then you can include it in that dimension e.g. an Employee can only have one Job Title

Hi Nick,

I guess I haven't seen anywhere within the Data Warehouse Toolkit where the same attributes were modeled in separate dimensions with the intention of them being the exact same concept (I could have overlooked this or simply forgotten about that). So, this is a bit cumbersome as I'd have to handle the SCD Type 2 rules for both sets of these attributes (as I'd like to track history), so this felt awkward to me. I'm still trying to shake off the OLTP designer in me and understand where the boundaries of a "rule" are (i.e. outriggers ARE permissible, but determining when is a difficult concept)

Update for anyone who finds this:

The book, Star Schema The Complete Reference, has a great, very clear breakdown of how to handle this type of thing in their "Conformed Dimensions" section.

The conforming dimension thing is more about attributes than it is about keys. In fact it has almost nothing to do with dimension tables and keys.

When users query they query attributes and measures. It doesn't matter in which row or table it appears, what it means is the attribute value is constant no matter where it resides.

What it does introduce is maintenance challenges when needing to update such attributes. This is one of the arguments put forth by those who support a 3NF master repository of all data.

Design of dimensions inherently leads to data redundancy. The objective is query simplicity and performance rather than load performance. So the complexities are placed on the load process.

ngalemmo wrote:The conforming dimension thing is more about attributes than it is about keys.  

Correct, however if in conforming two separate dimensions, that involves opting to move the attributes to an outrigger or as a separate dimension (a byproduct of attempting to conform a dimension), you now have a new set of keys to deal with. For my situation, I had 3 options clearly laid out in front of me with the pros / cons enumerated in the book Star Schema The Complete Reference; an outrigger was one of those 3 options (albeit not necessarily suggested).

All this being said, I'm still left having difficulty discerning what *should* be a separate dimension vs what should sit on my employee table.

There are many attributes which could be candidates for their own dimension, however I also have seen these modeled as sitting ON the employee dimension as well. For example:

dim_empoyee

• id
  
• first_name
  
• last_name
  
• job_title (could be dim_job_title)
  
• job_title_classification  (could be dim_job_title)
  
• job_eeo_category (could be dim_job_title)
  
• department_name  (could be dim_department)
  
• organizational_group_name  (could be dim_organization)
  
• client_name (already also exists in dim_client)
  

In my case, employees are assigned to a client as well (just like they are assigned to a department or an organizational group), but client is also a dimension in my DW. Do I just conform one or more of these client attributes from dim_client and place them on my employee as I have illustrated above? This tends to be one of my largest struggles is understanding what is common practice in this case. Their own dimension vs. sitting on the employee record or some hybrid / alternative. Any clear examples of how to handle this would be much appreciated.

Thank you both for your help thus far.

There can always reasons for breaking the rules but you should always start by trying to conform to the rules and then look to break them only if you have a complex situation that calls for a non-standard solution.
Additionally, as ngalemmo said: "The objective is query simplicity and performance rather than load performance" - so any DM design (within reason!) that enhances simplicity or performance is not wrong.

Given that, my thoughts on the "rules" I would normally follow are:

Attributes in a Dimension
A dimension should be in 2NF: to quote Wikipedia "a table is in 2NF if and only if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table" i.e. each attribute is dependent on the key.
If you think of the key as the "grain" of the dimension, any attribute that conforms to that grain can be included in that Dimension. So, if an employee can have one and only one job title you can include it in the employee dimension.
Don't split attributes into separate dimensions unnecessarily. If you never run a query that uses job title except in the context of an Employee then there is unlikely to be any advantage in splitting job title out of Employee (or duplicating the values in a separate Dim)

Linking Dimensions
The default design pattern is to only link Dimensions through Fact tables.
There are a number of valid exceptions to this but they are more 'special cases' than standard design patterns

Thank you, Nick for your clear and detailed answer. Much appreciated and helpful.