A dialog will appear as shown below. There are four choices for the default generation of the dimension table and its update procedure. The first choice being a normal dimension where a dimensional record is updated and changed whenever any of the non business key information changes. The second choice is a slowly changing dimension where new dimension records are created when certain identified columns in the dimension change. The third choice allows the storing of the last value of selected fields in secondary columns. The fourth choice supports source systems that provide start and end dates.

With any dimension we identify a business key that uniquely identifies the dimension records. For example in the case of the product dimension from the tutorial the product code is deemed to be the business key. The code uniquely identifies each product within the dimension. The product may also have a name or description and various other attributes that distinguish it. (e.g. Size, shape, color etc.). A common question when handling dimensions is what to do when the name or description changes. Do we want to track our fact table records based only on the product code or do we also want to track records based on different descriptions.

An example :

This product has been sold for many years and we consequently have a very good history of sales and the performance of the product in the market. The company does a '20% extra for free' promotion for 3 months during which time it increases the size of the can to 18oz. The description is also changed to be '15 + 3oz can of brussel sprouts'. At the end of the promotion the product is reverted to its original size and the description changed back to its original name.

The question is do we want to track the sales of the product when it had a different description (slowly changing) , or should the description of the product simply change to reflect its current name (normal). For this scenario a previous value dimension would not provide much advantage, so it is not discussed.

The decision is not a simple one and the advantages and disadvantages of each of the two choices is discussed below.