Designing Factless Fact Tables For Events

In this lesson, you will learn how to design factless fact tables to capture business events that record "when" and "who" without needing a specific numeric measurement like price or quantity.

The Concept of a Factless Fact Table

In traditional fact tables, we usually look for a numeric metric—like sales_amount or discount_rate—to aggregate. However, many important business processes are purely event-based. When a student attends a class, a patient visits a clinic, or a user logs into a system, the existence of the event itself is the data point.

Because there is no "amount" to sum up, we call these Factless Fact Tables. Instead of metrics, these tables consist primarily of foreign keys pointing to your dimension tables (e.g., Date_Key, Student_Key, Class_Key) and perhaps a degenerate dimension like Attendance_ID.

Practical Illustration

Imagine a university system that tracks student attendance. We don't need a numeric value to know someone was present; the presence of a record in the table represents the event.

CREATE TABLE fact_attendance (
    attendance_date_key INT,
    student_key INT,
    class_key INT,
    teacher_key INT,
    attendance_status_key INT, -- e.g., Present, Tardy, Absent
    CONSTRAINT fk_date FOREIGN KEY (attendance_date_key) REFERENCES dim_date(date_key),
    CONSTRAINT fk_student FOREIGN KEY (student_key) REFERENCES dim_student(student_key),
    CONSTRAINT fk_class FOREIGN KEY (class_key) REFERENCES dim_class(class_key)
);

Why Use This?

1. Counting Occurrences: You can easily answer questions like, "How many times did Student A attend Class B?" by performing a COUNT(*) on this table, grouped by the dimension attributes.
2. Coverage Analysis: You can identify "negative" events (what didn't happen) by performing an outer join between your dimension (e.g., all students) and your factless fact table to find who is missing from a specific event record.
3. Simplicity: It avoids the clutter of "dummy" metrics (like a 1 value) that developers often mistakenly insert into tables to force them to behave like standard fact tables.

By focusing on the event itself, you create a lightweight, high-performance table that serves as the backbone for reporting on activity frequency and participation trends.

Key Takeaway: Factless fact tables are essential for modeling event-driven processes where the occurrence of the event itself is the primary value, rather than a numeric measurement.