I have a question about type 2 dimensions.
Within our HR system, it is possible to hire an employee with one date, and then at a later point in time, change the hire date if it had been entered incorrectly in the first place. This gets complicated when using Type 2 dimensions as the change would result in a new record in the dimension table.
So, I basically need a way to say that some updates (such as the one above) shouldn't result in a new record in the dimension table. But, for other instances such as if an employee moves to a new position, then I definitely need to create a record in the dimension table.
What are my options here?
A type 2 dimension doesn't need to apply to every attribute in the dimension. You can choose to make some attributes Type 1 which overwrites values and loses the history and some attributes Type 2 which creates the new record and retains history.
So in your example above you'd make the "Position" attribute type 2 and the "Hire date" type 1.
Check out this wikipedia link with a list of the different types of slowly changing attributes, http://en.wikipedia.org/wiki/Slowly_changing_dimension.
Most of the time you'll only need Type 1 and Type 2 tho.