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 typically captures measurable events within a business process, such as a customer purchasing a product or a warehouse receiving a shipment. Facts 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:
- Foreign Keys: Keys linking to the relevant dimension tables to provide context (the "who, what, when, and where").
- Measures: Numeric values captured from the business process (e.g.,
sales_amount,discount_amount,quantity_sold).
Fact Types by Grain
The grain of a fact table defines exactly what a single row represents. Choosing the correct grain is one of the most important decisions in dimensional modeling.
Fact tables generally fall into three categories based on their grain:
Transaction Facts
A transaction fact table records individual business events at a defined grain.
- Characteristics: Highly detailed, potentially very large, and often contain additive measures.
- Example: One row per product sold per order line.
| Date | Store | Customer | Product | Quantity | Sales Amount |
|---|---|---|---|---|---|
| Jan 1 | Store A | Alice | Laptop | 1 | $1,000 |
| Jan 1 | Store A | Alice | Mouse | 1 | $50 |
Periodic Snapshot Facts
A periodic snapshot fact table records the state of a business process at regular intervals, such as daily, weekly, or monthly.
- Characteristics: Regular intervals, predictable growth, and often semi-additive measures.
- Example: Daily inventory levels.
| Date | Warehouse | Product | Quantity on Hand | Total Value |
|---|---|---|---|---|
| Jan 31 | WH 1 | Laptop | 50 | $50,000 |
| Feb 28 | WH 1 | Laptop | 45 | $45,000 |
Snapshots avoid repeatedly reconstructing historical states from large volumes of transactions.
Accumulating Snapshot Facts
An accumulating snapshot fact table tracks the lifecycle of a business process through predefined milestones. The row is updated as the process progresses.
- Characteristics: Multiple milestone date keys and often lag/duration measures.
- Example: Order fulfillment.
| Order ID | Placed Date | Picked Date | Shipped Date | Delivered Date | Days to Ship |
|---|---|---|---|---|---|
| ORD-100 | Jan 1 | Jan 2 | Jan 3 | Jan 5 | 2 |
| ORD-101 | Jan 2 | Jan 3 | NULL | NULL | NULL |
ORD-101 is still in progress, so its later milestones remain NULL.
Choosing the Fact Type
The three fact types differ primarily in what a row represents and how the data changes over time:
- Transaction → records an event
- Periodic Snapshot → records state at regular intervals
- Accumulating Snapshot → records a process lifecycle
Fact Types by Additivity
Facts can be classified based on how they behave when aggregated across dimensions:
Additive Facts
Additive facts can be safely summed across all dimensions. They are the easiest facts to aggregate.
For example, sales_amount can be summed across products, customers, stores, and time.
| Date | Product | Store | Sales Amount |
|---|---|---|---|
| Jan 1 | Laptop | Store A | $1,000 |
| Jan 1 | Phone | Store A | $500 |
| Jan 2 | Laptop | Store B | $1,200 |
| Jan 2 | Phone | Store B | $700 |
The total sales amount can be calculated across any combination of these dimensions.
Semi-Additive Facts
Semi-additive facts can be summed across some dimensions but not others. This commonly occurs with measurements that represent a point-in-time state, where summing across time would produce a meaningless result.
For example, an account balance can be summed across customers on a given day, but adding account balances across multiple days is not meaningful.
| Date | Customer | Account Balance |
|---|---|---|
| Jan 1 | Alice | $1,000 |
| Jan 1 | Bob | $2,000 |
| Jan 2 | Alice | $1,500 |
| Jan 2 | Bob | $2,500 |
On Jan 1, the total balance is $3,000. However, summing Jan 1 and Jan 2 balances to get $7,000 would be meaningless.
Non-Additive Facts
Non-additive facts cannot be meaningfully summed across dimensions. When aggregated, they usually must be recalculated from their underlying additive or semi-additive components.
Common examples include percentages, ratios, and unit prices.
| Product | Unit Price |
|---|---|
| Laptop | $1,000 |
| Phone | $500 |
| Tablet | $300 |
Adding the unit prices to get $1,800 has no meaningful business interpretation.
Textual Facts
Although fact tables primarily contain numeric measures, sometimes a fact table may contain only textual values associated with a specific business event.
A common example is an Employee Swipe Card log. The event may not have any numeric measures, but it records a specific action and its outcome.
| Date | Time | Employee | Card Reader | Swipe Status |
|---|---|---|---|---|
| Jan 1 | 08:00 | Alice | Front Door | Granted |
| Jan 1 | 08:05 | Bob | Front Door | Denied |
| Jan 1 | 08:06 | Bob | Front Door | Granted |
Here, Swipe Status is a textual fact because it represents a discrete outcome of a specific event. It can be analyzed by counting events (for example, the number of Granted or Denied swipes).
As a rule of thumb, if a text value provides descriptive context about an entity and is used for filtering or grouping, it belongs in a dimension rather than the fact table.
Factless Fact Tables
The name might sound like an oxymoron, but a factless fact table is a fact table that contains no explicit measures. Its rows consist primarily of foreign keys linking to dimension tables, with the existence of each row itself representing an event or condition.
Because there are no measures to aggregate, factless fact tables are typically analyzed by counting rows.
Textual Fact vs Factless Fact
- Textual fact → There is a fact or measurement, but its value happens to be text.
- Factless fact → There is no measure at all; the existence of the row itself represents an event or condition.
Factless fact tables are commonly used for two broad purposes: recording events and recording conditions or coverage.
Event Occurrence
These tables record that something happened. A few examples include:
- Student attended a class
- Customer visited a store
- Employee swiped a badge
- Patient checked into a clinic
- Web user clicked a link
- Package passed through a checkpoint
Example (Student Attendance):
| Date Key | Student Key | Class Key |
|---|---|---|
| 20240101 | S-105 | C-99 |
| 20240101 | S-106 | C-99 |
| 20240102 | S-105 | C-99 |
Total attendance can then be calculated by counting the rows.
Coverage (or Conditions)
These tables record that something was true, available, assigned, or applicable, regardless of whether an event subsequently occurred. A few examples include:
- Product was included in a promotion
- Employee was assigned to a department
- Course was offered to a student
- Product was available at a store
- Customer was eligible for an offer
- Machine was scheduled to operate during a period
Example (Promotion Coverage):
| Date Key | Product Key | Store Key | Promotion Key |
|---|---|---|---|
| 20240101 | P-10 | S-01 | PROM-50 |
| 20240101 | P-11 | S-01 | PROM-50 |
| 20240102 | P-10 | S-01 | PROM-50 |
By comparing this "coverage" table to the actual Sales fact table, business analysts can easily identify products that were heavily promoted but generated zero sales.
💡 Dimension or Fact?
It can sometimes be unclear whether to model something as a Fact or a Dimension. For example, Student Enrollment may be modeled as a Factless Fact when the enrollment relationship itself is the subject of analysis, or as a Dimension when Enrollment is treated as a descriptive entity with its own attributes.
To avoid confusion, always ask:
“Does the table primarily provide descriptive context, or does each row represent a business event, transaction, relationship, or state that we want to analyze?”