Factless fact table

What is a factless fact table?

A factless fact table is a fact table without numeric measures. It contains keys to dimensions and records that something happened, or that a relationship or condition existed, but it has no amount, quantity, duration, or balance to sum.

That sounds contradictory, but the row itself is the fact. A student attended a lesson. A customer received a campaign. A product was on promotion in a store on a certain day. A user logged in. There is no sales amount to add up. The useful measurement is the count of rows.

In a star schema, a normal fact table stores measures plus dimension keys. A factless fact table leaves out the measures and keeps the keys.

The two main types

Kimball-style dimensional modelling usually distinguishes two uses.

Event tracking
One row is recorded each time an event occurs. Attendance is the classic example: student, class, teacher, room, date. No numeric measure is needed. The row says the attendance event happened.

Other examples include a login, a support contact, a webpage view, a training session attended, a quality incident, or a customer campaign touch. You count the rows by date, customer, channel, or another dimension.

Coverage or conditions
One row records that a relationship or condition was possible or valid, even if no activity followed. A product was on promotion in a store on a date. A sales rep was assigned to a customer for a month. A member was eligible for a programme.

Coverage tables are powerful because they show the denominator: what could have happened. Without that, you can only count activity and cannot see the missing cases.

How do you measure without measures?

You count rows. COUNT replaces SUM.

How many lessons did a student attend? Count rows in the attendance table for that student. How many days was a product on promotion? Count rows in the promotion coverage table. How many customers were contacted? Count contact-event rows.

This is why the grain still matters. One row per student per lesson is not the same as one row per student per day. One row per product-store-day promotion is not the same as one row per campaign-product-store. If the grain is vague, the count is vague.

Counting what did not happen

The coverage pattern is the interesting one.

A sales fact table only contains sales. If a product did not sell, there is no row. That makes it hard to ask which promoted products failed to sell, because absence is not stored in the activity table.

A coverage table stores all product-store-day combinations where a promotion was active. Compare it with the sales fact table. The rows in coverage with no matching sale are exactly the promotions that produced no sales. Kimball describes this as subtracting activity from coverage: one table says what could happen, the other says what did happen.

This is useful for absence questions: customers not contacted, eligible members who did not apply, products promoted but not sold, required inspections not performed, scheduled appointments not attended.

Factless fact table versus bridge table

A factless fact table and a bridge table can look similar because both may contain only keys.

A bridge table solves a many-to-many relationship, such as products to categories or accounts to account holders. If it contains only relationship keys, it behaves like a factless fact table: it records that the relationship exists. If it also carries an allocation factor or weighting rule, the bridge has extra modelling work to do.

The difference is mostly the modelling question. If you are counting events or valid conditions, you will probably call it a factless fact table. If you are resolving a many-to-many relationship between dimensions, you will probably call it a bridge table.

Common examples

Attendance
Student, course, teacher, room, date. Count attendances by student, course, week, or teacher.

Promotion coverage
Product, store, promotion, date. Compare with sales to find promoted products that did not sell.

Customer contact
Customer, campaign, channel, employee, date. Count touches and compare them with conversion facts.

Eligibility
Customer, programme, period, segment. Compare with activity to find eligible people who did not participate.

What to watch out for

Declare the grain
The table may be factless, but it still needs a precise grain. Otherwise the row count will not mean anything.

Do not hide measures that should exist
If duration, quantity, or amount matters, add it. A table should be factless because the event has no numeric measure, not because nobody bothered to model one.

Coverage can get large
All possible product-store-day combinations can grow quickly. Generate coverage only at the level needed for real questions.

Absence needs a reference table
You cannot report what did not happen unless you also store what could have happened. That is the point of coverage.

Counts are still business definitions
Counting rows sounds objective, but a row depends on rules. Does a login count after a failed password? Does attendance count after five minutes? Agree before building the table.