No matter the kind of dimension, when you implement them, there are some best practices to take into account:
- A dimension table must have its own technical key column different to the business key column used in the operational database. This technical key is known as a surrogate key. While in the operational database, the key may be a string--for example, in an alphanumeric code, surrogate keys are always integers.
- Dimensions should have a special record for the unavailable data. This implies that besides one record for every member of the dimension, you should have a record with the key equal to zero, and N/A or unknown, or something that represents invalid data for all the descriptive attributes. ...
