The artificial (surrogate) key for a dimension is set via an identity column in SQL Server.
This artificial key normally, and by default, starts at one and progresses as far as is required.
A WhereScape standard for the creation of special rows in the dimension is as follows:
Key value | Usage |
|---|---|
1 upwards | The normal dimension artificial keys are numbered from 1 upwards, with a new number assigned for each distinct dimension record. |
0 | Used as a join to the dimension when no valid join existed. It is the normal convention in the WhereScape generated code that any dimension business key that either does not exist or does not match is assigned to key 0. |
-1 through -9 | Used for special cases. The most common is where a dimension is not appropriate for the record. For example, we may have a sales system that has a promotion dimension. Not all sales have promotions. In this situation, it is best to create a specific record in the dimension that indicates that a fact table record does not have a promotion. The stage table procedure would be modified to assign such records to this specific key. A new key is used rather than 0 as we want to distinguish between records that are invalid and not appropriate. |
-10 backward | Pseudo records. In many cases, we have to deal with different granularities in our fact data. For example, we may have a fact table that contains actual sales at a product SKU level and budget information at a product group level. The product dimension only contains SKU based information. To be able to map the budget records to the dimension, we need to create these pseudo keys that relate to product groups. The values -10 and backward are normally used for such keys. A template called 'Pseudo' is shipped with WhereScape RED to illustrate the generation of these pseudo records in the dimension table. |