How to create a factless fact table in a Data Warehouse

João Barros 12 de July de 2026 5 min read

A factless fact table is a fact table with no numeric measures: it only stores the dimension keys involved in an event. It looks odd at first, but it is the cleanest way to count things that happen — classes attended, applications, clicks — and the only practical way to answer the hardest question in a Data Warehouse: what did not happen. Here is the step by step to create a factless fact table in a Data Warehouse, load it and use it in real queries.

Prerequisites

A Data Warehouse with a star schema already started (SQL Server, Azure SQL, Fabric Warehouse, Synapse or any other SQL engine).
At least two dimensions with surrogate keys (for example DimStudent and DimCourse) plus a DimDate.
A staging table holding the raw events (stg_Attendance).
Permissions to create tables and run INSERT, and basic SQL (CREATE TABLE, JOIN, GROUP BY).

Step 1: Know when you need a factless fact table

There are two classic scenarios, and it pays to tell them apart before writing any code:

Events: something happened and there is nothing to add up. A student attended a class. There is no "value" for the attendance — the attendance is the fact. The measure is simply the row count.
Coverage: you record what could have happened. Which students are enrolled in which courses, which products are on promotion in which stores. On its own it says little; crossed with the event table, it reveals the absences.

A common mistake is inventing a Quantity column always equal to 1 just so "the table has a fact". You do not need it: COUNT(*) does the same job and saves space.

Step 2: Define the business process and the grain

The grain is the sentence that describes exactly what one row represents. Write it before the DDL — if you cannot put it in a single sentence, the model is not ready.

Grain: one row per student, per course, per day on which the student attended the class.

This grain immediately tells you three things: the dimensions involved (student, course, date), the primary key of the table, and what counts as a duplicate.

Step 3: Create the table

The table has foreign keys only. The primary key is made up of the three keys — that is what enforces the grain and prevents duplicates.

CREATE TABLE FactAttendance (
    StudentKey INT NOT NULL,
    CourseKey  INT NOT NULL,
    DateKey    INT NOT NULL,
    CONSTRAINT PK_FactAttendance
        PRIMARY KEY (StudentKey, CourseKey, DateKey),
    CONSTRAINT FK_FactAttendance_Student
        FOREIGN KEY (StudentKey) REFERENCES DimStudent(StudentKey),
    CONSTRAINT FK_FactAttendance_Course
        FOREIGN KEY (CourseKey) REFERENCES DimCourse(CourseKey),
    CONSTRAINT FK_FactAttendance_Date
        FOREIGN KEY (DateKey) REFERENCES DimDate(DateKey)
);

Notice: not a single measure column. That is normal, and it is exactly the point.

Step 4: Load the data

The load looks up the surrogate keys in the dimensions and inserts only what is not there yet, so you can re-run it without duplicating rows (idempotent load).

INSERT INTO FactAttendance (StudentKey, CourseKey, DateKey)
SELECT DISTINCT s.StudentKey, c.CourseKey, d.DateKey
FROM stg_Attendance a
JOIN DimStudent s ON s.StudentCode = a.StudentCode
JOIN DimCourse  c ON c.CourseCode  = a.CourseCode
JOIN DimDate    d ON d.FullDate    = CAST(a.AttendedAt AS date)
WHERE NOT EXISTS (
    SELECT 1
    FROM FactAttendance f
    WHERE f.StudentKey = s.StudentKey
      AND f.CourseKey  = c.CourseKey
      AND f.DateKey    = d.DateKey
);

If a JOIN fails (a student who does not exist yet in DimStudent), the row is simply dropped. To avoid losing events silently, switch to a LEFT JOIN and send those rows to a rejects table, or use an "Unknown" row in the dimension.

Step 5: Count events

With no measures, the analysis is done with counts. This is where the table shows its value:

SELECT c.CourseName,
       d.MonthName,
       COUNT(*)                    AS Attendances,
       COUNT(DISTINCT f.StudentKey) AS DistinctStudents
FROM FactAttendance f
JOIN DimCourse c ON c.CourseKey = f.CourseKey
JOIN DimDate   d ON d.DateKey   = f.DateKey
WHERE d.CalendarYear = 2026
GROUP BY c.CourseName, d.MonthName
ORDER BY Attendances DESC;

Step 6: Find out what did not happen

This is where the second factless table comes in: FactEnrollment, with the grain "one row per student enrolled in a course". Crossing coverage (enrolments) with events (attendances) surfaces the students who never attended a single class — a question you cannot answer with the attendance table alone, because an absence generates no rows.

SELECT s.StudentName, c.CourseName
FROM FactEnrollment e
JOIN DimStudent s ON s.StudentKey = e.StudentKey
JOIN DimCourse  c ON c.CourseKey  = e.CourseKey
LEFT JOIN FactAttendance f
       ON f.StudentKey = e.StudentKey
      AND f.CourseKey  = e.CourseKey
WHERE f.StudentKey IS NULL
ORDER BY c.CourseName, s.StudentName;

Check the result

Three quick checks before calling the table done:

Grain respected: the row count must equal the number of distinct combinations. If you created the PRIMARY KEY, the engine already guarantees it — a second load cannot insert duplicates.
No orphan keys: SELECT COUNT(*) FROM FactAttendance f LEFT JOIN DimStudent s ON s.StudentKey = f.StudentKey WHERE s.StudentKey IS NULL; must return 0.
Totals match: compare COUNT(*) on the factless table with the number of distinct events in staging. If they differ, the cause is almost always a JOIN that failed in Step 4.

Run the load twice in a row: the total must stay exactly the same. If it grows, the bug is in the NOT EXISTS or in the grain.

Conclusion

With half a dozen columns you now have a table that counts events cheaply and that, together with the coverage table, answers absence questions that usually go unanswered. The natural next step is to connect both to Power BI and create two DAX measures — attendances and attendance rate — on top of this same model. And in your business, which event can nobody count today simply because it has no value attached to it?