Introduction to Data Warehousing and Analysis Services 2005 - Professional SQL Server Analysis Services 2005 with MDX - page 10

table the state ids 6, 31, 33, and 42 refer to the states MA, CA, OR, and WA,
respectively, and represent the order in which these states joined the United
States. Building the fact table is an important step towards building your data
warehouse.

Dimension Tables

The fact table typically holds quantitative data; for example, transaction data
that shows number of units sold per sale and amount charged to the custom-
er for the unit sold. To provide reference to higher-level roll-ups based on
things like time, a complementary table can be added that provides linkage to
those higher levels through the magic of the join (how you link one table to
another). In the case of time, the fact table might only show the date on which
some number of cases of beer was sold; to do business analysis at the
monthly, quarterly, or yearly level, a time dimension is required. The following
table shows what a beer products dimension table would minimally contain.
The product id is the primary key in this table. The product id of the fact table
shown previously is a foreign key that joins to the product id in the following
table:

Product ID Product SKU Product Name

1 SBF767 SuperMicro Ale

2 SBH543 SuperMicro Lager

3 SBZ136 SuperMicro Pilsner

4 SBK345 SuperMicro Hefeweizen

… … …

For illustrative purposes, assume that you have a dimension table for time
that contains monthly, quarterly, and yearly values. There must be a unique
key for each value; these unique key values are called primary keys. Mean-
while, back in the fact table you have a column of keys with values mapping
to the primary keys in the dimension table. These keys in the fact table are
called foreign keys. For now it is enough if you get the idea that dimension
tables connect to fact tables and this connectivity provides you with the ability
to extend the usefulness of your low-level facts resident in the fact table.