In addition to the already-mentioned advantages for using surrogate keys (and the upcoming explanation in this chapter of handling history in the data warehouse), there is another good reason to use surrogate keys. Sometimes data will be loaded in a fact table and no corresponding dimension key can be found based on the natural key and/or the validity of the dimension record. For those cases, you need a mechanism that stores the facts anyway to make sure that no data is omitted during processing. For this reason, we recommend having an unknown record in each dimension table. Key generation in a dimension usually starts at 1, so that leaves the number 0 as a perfect candidate for the unknown dimension record key. This record should have a value of ‘‘unknown’’ in all attribute fields. Picture below shows a partial example of the unknown record in a customer dimension table. пятница, 19 сентября 2014 г.
Unknown Dimension Keys
In addition to the already-mentioned advantages for using surrogate keys (and the upcoming explanation in this chapter of handling history in the data warehouse), there is another good reason to use surrogate keys. Sometimes data will be loaded in a fact table and no corresponding dimension key can be found based on the natural key and/or the validity of the dimension record. For those cases, you need a mechanism that stores the facts anyway to make sure that no data is omitted during processing. For this reason, we recommend having an unknown record in each dimension table. Key generation in a dimension usually starts at 1, so that leaves the number 0 as a perfect candidate for the unknown dimension record key. This record should have a value of ‘‘unknown’’ in all attribute fields. Picture below shows a partial example of the unknown record in a customer dimension table. 