Data Warehouse: Facts and Measures

A few days ago I wrote a post that gave an introduction to dimensions.  Today, we are going to continue covering the basic concepts included in dimensional modeling by covering an introduction to fact tables and measures.  These posts are all part of the introduction to building a data warehouse with sql server series.

What is a Fact Table?

A fact table is a table that joins dimension tables with measures.  For example, Lets say you wanted to know the time worked by employees, by location, by project and by task.  If you had a dimension for employees, location, project and task you would create a composite primary key using these foreign keys and add an additional column for the time worked measure. (more on measures in a little bit)

Keep in mind that fact tables are huge unlike dimension tables.  Fact tables are usually built to contain a hundred thousand records on the low side up to billions of records on the high side.  Therefore, these tables must be normalized to be efficient.

A fact table is normalized when it consists of the following two things:

1. 1. A composite primary key using the foreign keys to the dimension tables.
   2. Measure(s) used for numerical analysis.

What is a measure?

A measure is normally an additive numerical value that represents a business metric.  You are also not limited to a single measure.  You can have multiple measures within a fact table.  For example, if your fact table is used to track purchases internationally you might have measures for each type of currency.  If you are building a fact table for the retail industry you might also have the following measures cost, list price, average sale price.

Next, you can take a look at my demo on building your first cube.