The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

2.2. Additional Design Concepts and Techniques

Even though the dimensional modeling concepts we've described are fairly simple, they are applicable to a wide range of business scenarios. However, there are a few additional dimensional modeling concepts and techniques that are critical to implementing viable dimensional models. We start this section with a couple of key concepts: surrogate keys and slowly changing dimensions. Then we look at several techniques for modeling more complex business situations. Finally, we review the different types of fact tables. We briefly describe each concept or technique and provide references so you can find more detailed information if you need it.

2.2.1. Surrogate Keys

For your DW/BI system, you will need to create a whole new set of keys in the data warehouse database, separate from the keys in the transaction source systems. We call these keys surrogate keys, although they are also known as meaningless keys, substitute keys, non-natural keys, or artificial keys. A surrogate key is a unique value, usually an integer, assigned to each row in the dimension. This surrogate key becomes the primary key of the dimension table and is used to join the dimension to the associated foreign key field in the fact table. Using surrogate keys in all dimension tables reaps the following benefits (and more):

• Surrogate keys protect the DW/BI system from changes in the source system. For example, a migration to a new software package will likely create a new ...