Data Warehouse: Facts and Measures

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.

Introduction to Dimensions

What is a Dimension Table?

A dimension table provides the description behind the analytic numbers.  It describes the who, what, when, where and why behind the facts. Dimensions are normally broken down into groups (tables) and they contain several attributes (columns).   Unlike a fact table the dimension table is not normalized.  Generally, dimension tables have many columns but a limited amount of rows. 

Dimension tables normally provide two purposes in a data warehouse, it can be used to filter queries and to select data.

Building a Data Warehouse with SQL Server

What is a Data Warehouse?

In its simplest form a Data Warehouse is a way to store data information and facts in an format that is informational.  Hopefully, you were able to pull this information from the photos above.   Personally, I like to think of a Data Warehouse as a tool used by decision makers to improve decision‐making.