Managing Factless Fact Tables for Occurrences

In this lesson, you will learn how to design fact tables that capture the "when" and "where" of events, even when there is no numeric metric to measure.

The Concept of Occurrence

Most fact tables you have built so far focus on quantitative data, like sales_amount or quantity_sold. However, many business questions don't involve money or counts. Instead, they ask about the existence of an event. Examples include: "Which students attended class on a specific day?" or "Which warehouse locations held a specific product during a monthly inventory audit?"

When you need to track these activities, you use a Factless Fact Table for Occurrences. These tables contain no measures; they consist entirely of foreign keys pointing to your dimensions. Their primary value lies in the intersection of those dimensions.

Designing for Coverage and Attendance

Imagine you want to track employee attendance at a training seminar. Your fact table, fact_attendance, doesn't need a "sales" column. Instead, it tracks the occurrence of the relationship between the employee and the event.

CREATE TABLE fact_attendance (
    date_key INT,
    employee_key INT,
    event_key INT,
    location_key INT
);

By querying this table, you can answer questions like:

• "How many employees attended this training?" (Count the rows)
• "Which employees were absent?" (Perform a LEFT JOIN from your dim_employee table to the fact_attendance table and look for NULL matches)

Why Use Them?

If you tried to force these scenarios into a traditional fact table, you might be tempted to create a dummy 1 as a "count" column. While that works, true "factless" design signals to other developers that the table's purpose is to track the existence of a relationship or event. It makes your schema intent clear: this table is for analyzing participation, coverage, and activity patterns rather than financial performance. By focusing on the keys, you build a lean, efficient table that serves as the bridge between your dimensions.

Key Takeaway: Factless fact tables for occurrences enable you to analyze patterns and coverage by tracking the existence of events using only foreign key relationships, without the need for numeric measures.