Fact Tables

In dimensional modeling, every table is generally modeled as either a Fact or a Dimension.

NOTE

Ralph Kimball did not invent the terms “fact” and “dimension”. The terminology dates back to work by General Mills and Dartmouth University in the 1960s and was later adopted by companies such as AC Nielsen and IRI in the 1970s.

Fact tables capture business events, transactions, relationships or states, typically along with quantitative measurements. Most fact tables contain numeric measures, while factless fact tables contain no explicit measures. In dimensional modeling, the term "fact" represents a business measure and answers questions such as "how much" or "how many".

• The "Verbs": Think of fact tables as the verbs of the business. Each fact table typically captures measurable events within a business process, such as a customer purchasing a product or a warehouse receiving a shipment. The fundamental design of a fact table is entirely based on a physical activity and is not influenced by the eventual reports. Fact tables should not be replicated across departments or data marts because they are usually very large and must provide a centralized, consistent source of truth.
• Physical Characteristics: Fact tables are typically deep and narrow. They contain relatively few columns but can grow to millions or billions of rows as business activity accumulates.
• Clearly Defined Grain: Every fact table must have a clearly defined grain specifying exactly what a single row represents (for example, one row per product sold per order).
• Table Structure: Fact tables typically contain:
  1. Foreign Keys: Keys linking to the relevant dimension tables to provide context (the "who, what, when and where"). Rather than a NULL foreign key, the associated dimension table should have a default row representing the unknown or not available condition (as detailed in the Dimensions page).
  2. Measures: Numeric values captured from the business process (e.g., sales_amount, discount_amount, quantity_sold).
  3. Standalone Timestamps: When the business process requires time-of-day precision beyond the grain of the date dimension, a timestamp can be stored directly in the fact table. This is a regular fact-table column, not a foreign key to a time or date dimension, and can capture the exact date and time of the event.
  4. Fact Table Surrogate Keys: Fact tables do not generally require surrogate keys; row uniqueness should be determined by the declared grain and its identifying keys. A single-column key (e.g., sales_fact_key) may be useful as a stable row identifier, but it does not replace business/source identifiers needed to identify specific events or compensate for an unclear grain.

Atomic Grain

In data warehousing, the grain of a fact table defines what a single row represents. The atomic grain (also called atomic data) is the most detailed level at which a business process captures data, the point where it can't be broken down any further.