DaveWentzel.com            All Things Data

dimension tables


What are slowly changing dimensions?
A Slowly Changing Dimension is a dimension table that needs to track changes to data over time.  For instance, a customer dimension needs to track if a customer has moved to a new state, otherwise the row will either contain old data (hence, the customer was never updated), or will lose its history (ie, all old reports will look as though customer sales occurred for the new state, not the old state).  There are three excepted ways to handle Slowly Changing Dimensions (SCDs) in a datawarehouse, each with its pros and cons. 

Type 1 Slowly Changing Dimensions
These are handled by overwriting the existing dimension record with new values.  However, all history is lost after the update is performed.  An example would be a customer who changes addresses. 
Type 2 Slowly Changing Dimensions
These are handled by creating a new record for each change in the original soure record.  For example, purchases occurring prior to customer relocation associate to the old zip code and purchases occurring after teh relocation associate to the new zip code.  Most DW designers favor this method of handling SCDs.  Mechanically, you need a new key for the row, known as the Surrogate Key.  Key the natural key so the users can identifier the dimension row easily. 
Type 3 Slowly Changing Dimensions
This method is very unorthodox where another table stores old values and the dimension table always stores the most recent version of the data element.  This method is not handled well by analysis and query tools because it requires extra join
Good Dimension Design Principles
  • Hierarchies help manage dimension volume.  Grouping the data by attributes makes analysis more helpful as well. 
  • Dimension members should have only one parent.  If there are multiple parents then the attributes should be independent. 
  • Degenerate dimensions should be kept in the fact table and should be available for drill-through but should not be in the cube.  This is detail information that likely won't be sliced and diced.