Designing Factless Fact Tables

In this lesson, you will learn how to model business events that track "what happened" without needing a numerical measurement like revenue or quantity.

What is a Factless Fact Table?

In a standard star schema, a fact table usually contains a quantitative measure—like sales_amount or units_sold. However, not every business event involves a calculation. Sometimes, the mere existence of an event is the data you need to capture. A Factless Fact Table records the association between dimension keys to signify that an occurrence took place.

The Attendance Example

Imagine you are tracking student attendance in a school. You aren't adding up "attendance points" for every row; you simply need to know that a specific student was present in a specific class on a specific date.

A factless fact table for this scenario would look like this:

CREATE TABLE fact_student_attendance (
    date_key INT,         -- FK to DimDate
    student_key INT,      -- FK to DimStudent
    class_key INT,        -- FK to DimClass
    teacher_key INT       -- FK to DimTeacher
);

In this table, there is no numerical "fact" column. The "fact" is the row itself. By joining this table against your dimensions, you can easily answer questions like, "Which students missed class on Monday?" or "How many students attended the Physics seminar?"

Why Use Them?

You use factless fact tables when you need to track:

1. Events/Occurrences: Like student attendance, medical checkups, or customer registrations.
2. Coverage/Inventory Snapshots: Recording what was "in stock" even if no sales occurred that day (to ensure you can report on days where inventory existed but moved zero units).

By stripping away the requirement for a numeric measurement, you gain the flexibility to model complex business relationships that are based entirely on time, place, and participants. When you query these tables, you typically perform a COUNT on the rows to get your answers, rather than a SUM.

Key Takeaway: Factless fact tables model business events by storing dimension keys without numerical measures, allowing you to track occurrences and relationships simply by counting the rows.