When you are defining data warehouse dimensions, you need to ask yourself how changes to the information impact your fact tables.


Type 1: This is straightforward refresh. When a value changes in the dimension table, the fields are simply overwritten and history is not kept for the column. One example of a Type 1 dimension would be an employee name. If, for example, employee Jane Doe gets married and changes her name to Jane Smith, you may choose to simply overwrite the name. All fact tables referencing Jane Doe will suddenly reference Jane Smith as if she was always Jane Smith. Your client may always want the most current employee name and, therefore, would not want to track historical changes.


Type 2: This is known as a slowly changing dimension and all changes are recorded historically. Using the example above in which Jane Doe gets married and changes her name to Jane Smith, the company may choose to want to track these historical changes. When this type of change occurs, our dimension table will have two records for Jane, for example, JAN 2013 – MAR 2013 = Jane Doe, MAR 2013 – current = Jane Smith. Some of your fact table records will reference the Jane Doe record and others will reference the Jane Smith record. If you have a sales fact, sales from JAN – MAR will show Jane Doe as the representative, while sales after MAR will reference Jane Smith.


Type 3: This is also a slowly changing dimension, but in this case the full history is not recorded. In fact, Jane Doe will always maintain just a single record. However, the previous value will be recorded in an "OldValue" date, along with the date in which the value was changed. For example, when Jane Doe gets married, her record will be updated to Jane Smith. The OldValue field will be set to "Jane Doe" with a change date of MAR 2013. All sales figures will start displaying "Jane Smith" but access to the previous value of "Jane Doe" is still available. Therefore, you would always have your sales report display Jane Smith (previously Jane Doe) if you wanted to do so.