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. 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.

  • Pingback: Introduction to Dimensions | JohnSterrett.com [DBA + Developer = ME]()

  • Hello John,

    I don’t see anymore links/articles for this series…Did you discontinue it or am I missing something. It really super good info.

    Is there somewhere I can get the rest of this series.

    Thank you and great work….

  • NIKITA TANNA

    Well explained. This was really helpful l…where is the next part of the series?? :/

  • NIKITA TANNA

    I wanted to know how to create a data warehouse for retail industry for my BI project….i am not sure what the dimension and facts should be…plz help

  • John

    Your facts should be the meat of your data. Using retail as an example you would want you facts to include data that would be aggregated like TotalSales, CostOfSale and the key to all your dimensions. This table should have a few columns but the tables should have a lot of rows.

    Your dimensions table is used to describe the data in your fact tables. It’s mainly used for your filters. For example, you would have dates, products, employees, customers, etc.. These would have a lot of columns but not much data.

    You can find a demo at http://johnsterrett.com/2009/11/16/pittsburgh-sql-user-group-data-warehousing-presentation/

  • NIKITA TANNA

    Thank you so much 🙂