MS BI Workshop #7 – What are all those keys in a star schema?

Here is my notes #7 about all those keys we’ve seen in a star schema.

This dimensional table is very typical. It has a primary key CurrencyKey, and an alternate key Currency code.

 

The above snapshot is from the DSV of SSAP project Adventure Works DW 2008 in BIDS. The table type is View (on SSAS side) based on table DimCurrency.

If you look at the the creation script of table DimCurrency, you will see that it has two constraints, a primary key constraint on CurrencyKey, and a uniqueness constraint on CurrencyAlternateKey.

 

In the DSV in BIDS, the alternate key has been renamed to a user friendly name Currency Code, and the view has been renamed to a user friendly name Currency.

1. Each dimension table has an primary key that is an integer and sequential. Typically we’d define it as an Identity column.

2. This primary key is also called Surrogate Key.

3. This surrogate key is used to join to the fact table(s), and can be indexed to speed up queries.

4. This surrogate key has no business meaning.

5. A typical dimension table can also have an alternate key.

6. This alternate key defines the uniqueness of each row.

7. This alternate key has business meaning, and is also referred to as Business Key or Natural Key.

8. This business key is typically the primary key from the operational source system.

9. This alternate key can be a composite key with more than one columns to make up the uniqueness.

10. Best practice is to append SK or AK to the column names.