Fact Table - Example

Example

If the business process is SALES, then the corresponding fact table will typically contain columns representing both raw facts and aggregations in rows 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"
• $50,000, being "sales for Los Angeles store for 16-Jan-2005"
• $21,000, being "average daily sales for Los Angeles Store for Jan-2005"
• $65,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 daily 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, salesperson, product) are stored.

All foreign keys between fact and dimension tables should be surrogate keys, not reused keys from operational data.

The centralized table in a star schema is called a fact table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys. Fact tables contain the content of the data warehouse and store different types of measures like additive, non additive, and semi additive measures.