What are fact tables and dimension tables? How do you determine what goes into each when designing a data model?

Question

Accepted Answer

Fact tables and dimension tables are the building blocks of dimensional data models. Understanding the distinction and knowing how to classify business data into these categories is essential for BI analysts who design or work with data warehouses.

Fact tables store the measurements, metrics, or events of a business process. They contain the quantitative data that businesses want to analyze. Each row in a fact table represents a specific event or transaction at its most granular level. A sales fact table might have one row per line item on an order. Fact table columns include foreign keys to dimension tables and numeric measures like quantity, revenue, cost, and discount amount.

Fact tables are typically the largest tables in the warehouse because they accumulate data over time. They grow continuously as new transactions occur. The grain of a fact table, meaning the level of detail each row represents, should be defined clearly and consistently. Common grains include one row per transaction, one row per daily summary, or one row per line item.

Dimension tables store the descriptive context that gives meaning to fact table measurements. They answer the who, what, where, when, and how questions about the business events. A customer dimension describes who bought something. A product dimension describes what was bought. A date dimension describes when it happened. A store dimension describes where it occurred.

Dimension tables are typically wide with many descriptive columns but relatively few rows compared to fact tables. They change slowly over time and often use slowly changing dimension techniques to track historical changes in attributes like customer address or product category.

To determine what goes where, start by identifying the business process you are modeling, such as sales, inventory, or customer support. The measurements of that process become fact table columns. The contextual attributes that describe each measurement become dimension table columns.

Apply this test: if a value is numeric and additive, meaning you can meaningfully sum it across rows, it likely belongs in the fact table. If a value is descriptive text, a category, or a hierarchy level used for filtering and grouping, it belongs in a dimension table.

Foreign keys in the fact table connect each measurement to its dimensional context. A single row in the sales fact table has foreign keys pointing to the customer who bought, the product that was bought, the date of purchase, and the store where the transaction occurred. This connection enables the powerful analytical pattern of slicing and dicing facts by any combination of dimensions.