Factless Fact Tables

Data architectural patterns often define fact tables as containers for numerical measurements. We look for columns representing revenue, quantity, duration, or balances. However, you will encounter business processes where the primary event does not produce a measurable metric. The significance lies solely in the event itself or the relationship between dimensions.

These scenarios require a factless fact table. Despite the contradictory name, this structure is a standard and necessary component of dimensional modeling. It captures the intersection of dimensions to record that an event occurred or to define a domain of coverage.

Event Tracking Tables

The most common variation of a factless fact table records the occurrence of a discrete event. In these cases, the "fact" is simply that the row exists. There is no magnitude to measure, only the convergence of dimensions at a specific point in time.

Consider a student attendance system. If a student attends a class, an event is generated. There is no "amount" of attendance; the student was simply present. The grain of this table is one row per student per class per day.

The schema for this event looks like a standard Star Schema, but the fact table contains only foreign keys.

Schema design for an event tracking factless fact table. The central table consists entirely of foreign keys linking to the surrounding dimensions.

To analyze this data, you count the rows. If you need to calculate the total attendance for a specific course, you perform a count of the rows grouped by the course dimension.

$\text{Total Attendance} = \sum_{\text{rows}} 1$

In some implementations, data engineers add a dummy column, often named measure or occurrence, containing the constant value $1$. This allows Business Intelligence (BI) tools to perform a standard SUM operation rather than a COUNT, which can simplify report generation in tools that expect additive metrics by default.

Coverage Tables

The second, and often more critical, application of factless fact tables is modeling coverage or conditions. Standard transactional fact tables are sparse; they only contain rows when an activity occurs. If a product does not sell on a specific day, no row exists in the FactSales table.

This sparsity makes it difficult to answer negative questions, such as:

• "Which products on promotion did not sell today?"
• "Which stores had zero movement on authorized inventory?"

You cannot query the sales table for items that are not there. To answer these questions, you need a table that defines the universe of possibilities, what should have happened or what was eligible to happen.

A coverage factless fact table sits between dimensions to define a relationship. For example, a FactStorePromotion table might link DimProduct, DimStore, DimPromotion, and DimDate. A row in this table indicates that a specific product was on promotion in a specific store on a specific day, regardless of whether it sold.

By joining this coverage table with the sales table, you can identify gaps.

1. Select all expected rows from the Coverage Table (Product A, Store B, Date 1).
2. Left join to the Sales Fact Table on the corresponding keys.
3. Filter where the Sales metric is NULL.

This technique is essential for inventory analysis, insurance policy coverage (modeling active policies even if no claims are made), and sales territory assignments.

Primary Approaches

In a standard fact table, the primary key is often a composite of all the foreign keys. In a factless fact table, this rule usually holds true, but you must ensure the grain is unique.

For the attendance example, the combination of Student_SK, Course_SK, and Date_SK guarantees uniqueness because a student cannot attend the same class instance twice in one day. However, if you are tracking system logins (an event factless table), a user might log in multiple times a day. In this scenario, the composite of User and Date is not unique. You would either need to add a time dimension to the grain or introduce a surrogate key for the fact table itself to uniquely identify each event.

When to Use Factless Fact Tables

You should introduce this pattern when the business process generates no natural metrics, yet the intersection of dimensions provides analytical value.

• Tracking Workflow Transitions: A record indicating a document moved from "Draft" to "Review."
• Assignments: Linking a salesperson to a territory or a customer to a segmentation group for a specific period.
• Eligibility: Recording which employees are eligible for specific benefits plans.

Understanding that facts do not strictly require numbers allows you to model the complete reality of the business environment, capturing not just what happened (transactions), but also the context and conditions in which those events occurred.