Fact table

From Wikipedia, the free encyclopedia

In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema, surrounded by dimension tables.

Fact tables provide the (usually) additive values which act as independent variables by which dimensional attributes are analyzed. Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined. The grain of a SALES fact table might be stated as "Sales volume by Day by Product by Store". Each record in this fact table is therefore uniquely defined by a day, product and store. Other dimensions might be members of this fact table (such as location/region) but these add nothing to the uniqueness of the fact records. These "affiliate dimensions" allow for additional slices of the independent facts but generally provide insights at a higher level of aggregation (region is made up of many stores)

[edit] Example

If the business process is SALES, then the corresponding fact table will typically contain values representing both raw facts and aggregations such as:

• $12,000, being "sales for New York store for 15-Jan-2005"
• $34,000, being "sales for Los Angeles store for 15-Jan-2005"
• $22,000, being "sales for New York store for 16-Jan-2005"
• $40,000, being "sales for Los Angeles store for 16-Jan-2005"
• $21,000, being "average daily sales for Los Angeles Store for Jan-2005"
• $35,000, being "average daily sales for Los Angeles Store for Feb-2005"
• $33,000, being "average daily sales for Los Angeles Store for year 2005"

"average monthly sales" is a measurement which is stored in the fact table. The fact table also contains foreign keys from the dimension tables, where time series (e.g. dates) and other dimensions (e.g. store location, salesman, product) are stored.