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

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.

image 

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.

image 

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.

Categories: Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: