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 typically contain foreign keys linking to dimension tables, with the existence of each row itself representing an event or condition.
The key is that "factless" means there are no explicit measures to aggregate, not that the table contains exclusively foreign keys. In fact, the Employee Swipe Card log from the previous section is a perfect example of a factless fact table.
Because there are no measures to aggregate, factless fact tables are typically analyzed by counting rows.
Textual Fact vs Factless Fact
These terms describe different aspects of a fact table and are not mutually exclusive:
- Textual fact → Describes a fact or measurement whose value happens to be text.
- Factless fact → Describes a fact table that contains no explicit measures, where the existence of each row 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?”