How to create a factless fact table in a data warehouse

João Barros 11 de July de 2026 4 min read

A factless fact table is a fact table that stores no numeric measures — only the foreign keys that link to the dimensions. At first glance an "empty" fact table sounds like a contradiction, but it is the most elegant way to record that an event happened (an attendance, a click, an enrollment) and to answer questions about counts and, above all, about what did not happen.

Prerequisites

A data warehouse or relational database (the examples use SQL Server, but the pattern is the same in any engine).
Basic knowledge of dimensional modeling: what dimensions, facts, and the star schema are.
Permissions to create tables and insert data.

Step 1: Understand when to use it

There are two classic scenarios for this pattern. The first is event tracking: each row states that something happened at a given moment — a student attended a class, a user viewed a page, a badge was scanned at the door. The second is coverage (or eligibility): the table records relationships that could happen so you can measure what did not happen. Without those rows it is impossible to answer "who was absent?", because an absence, by its very nature, produces no record.

Step 2: Model the dimensions

We will use a class attendance example. We need three dimensions: the student, the class, and the date. We assume they already exist in the model, each with its own surrogate key.

-- Dimensions (simplified)
CREATE TABLE DimAluno (
    AlunoKey   INT PRIMARY KEY,
    Nome       NVARCHAR(100)
);

CREATE TABLE DimTurma (
    TurmaKey   INT PRIMARY KEY,
    Disciplina NVARCHAR(100)
);

CREATE TABLE DimData (
    DataKey    INT PRIMARY KEY,   -- e.g., 20260711
    Data       DATE
);

Step 3: Create the factless fact table

The attendance table holds keys only. There are no quantity or amount columns — hence the name "factless". The grain (the level of detail of each row) is clear: one row per student, per class, per day. Each row therefore represents one confirmed attendance.

CREATE TABLE FactPresenca (
    AlunoKey INT  NOT NULL REFERENCES DimAluno(AlunoKey),
    TurmaKey INT  NOT NULL REFERENCES DimTurma(TurmaKey),
    DataKey  INT  NOT NULL REFERENCES DimData(DataKey),
    CONSTRAINT PK_FactPresenca PRIMARY KEY (AlunoKey, TurmaKey, DataKey)
);

Tip: some modelers add a constant column with the value 1 (for example Presencas) to simplify sums. It is optional; counting rows gives exactly the same result.

Step 4: Load some data

INSERT INTO FactPresenca (AlunoKey, TurmaKey, DataKey) VALUES
    (1, 10, 20260711),
    (2, 10, 20260711),
    (3, 10, 20260711);

These three rows state that students 1, 2, and 3 attended class 10 on July 11, 2026.

Step 5: Count events

To find out how many students attended each class, we count the rows. Since there is no measure to add up, we use COUNT(*) instead of SUM — that is the fundamental difference from a traditional fact table.

SELECT t.Disciplina,
       d.Data,
       COUNT(*) AS TotalPresencas
FROM   FactPresenca f
JOIN   DimTurma t ON t.TurmaKey = f.TurmaKey
JOIN   DimData  d ON d.DataKey  = f.DataKey
GROUP BY t.Disciplina, d.Data;

Step 6: Answer absences with coverage

Here is the real power of the pattern. To learn who was absent, we need to know who should have attended. A second factless table — the coverage table — records enrollments: which student is enrolled in which class.

-- Coverage: enrollments (who is expected to attend)
CREATE TABLE FactInscricao (
    AlunoKey INT NOT NULL,
    TurmaKey INT NOT NULL,
    CONSTRAINT PK_FactInscricao PRIMARY KEY (AlunoKey, TurmaKey)
);

With a LEFT JOIN between the enrolled and the present, the rows with no match reveal a day's absences:

SELECT i.AlunoKey
FROM   FactInscricao i
LEFT   JOIN FactPresenca p
       ON p.AlunoKey = i.AlunoKey
      AND p.TurmaKey = i.TurmaKey
      AND p.DataKey  = 20260711
WHERE  i.TurmaKey = 10
  AND  p.AlunoKey IS NULL;

Verify the result

If student 4 is enrolled in class 10 but has no row in FactPresenca for the 11th, the last query returns their AlunoKey. Also confirm that the count from Step 5 matches the number of rows you inserted — three attendances for class 10. If both numbers line up, the model is correct.

Conclusion

A factless fact table solves, in a simple way, two problems that would otherwise be tricky: counting events and measuring what did not happen. The next step is to apply the same pattern to other domains — campaign clicks, active promotions per store, or page visits. Here is a question to take away: which important event in your business is not being recorded yet, just because it "has no measure to store"?