Type 2 Slowly Changing Dimension Table

You may notice that the DIM_EMPLOYEE table was used as an example of a dimension table in the basic example. I want to point out two additional things about this table in this example...

First, while this ERD probably does not make this obvious, this is intended to be a type 2 slowly changing dimension. A type 1 slowly changing dimension is a table that does not keep historical rows. A type 2 slowly changing dimension does keep historical rows. In this case, when an employee is promoted, the old employment information is not overwritten with the new. A new row is inserted and any fact table records that apply to the timeframe after the promotion point to this new row.

Second, since this table can be joined to both this fact table and the fact table in the other example, it is considered to be a conformed dimension (used in both places). In reality, two physical tables can be created and the dimension can still be considered conformed as long as the definition of an employee as well as the surrogate keys are the same between the two .