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 event occurrences and tracking conditions, relationships or coverage.
Event Occurrence
These tables record actual activity to answer "What happened?". Each row represents an event that occurred at a particular time. A few examples include:
- Student attended a class
- Employee swiped a badge
- Patient checked into a clinic
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, Conditions and Relationships
These tables record what was applicable, available, or in effect on a given date or during a period. They establish a baseline for "What could or should have happened?" Comparing this baseline with actual activity helps answer "What didn’t happen?". A few examples include:
- A product is covered by a promotion at a store on a given date
- An employee’s assignment to a department is active during a specified period
- A student is eligible to enroll in a course during a particular term
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?”