When a factless fact table gives you the coverage denominator

The exhibitor scan report runs on the Tuesday after the show and lists 351 stands. The floor plan had 380. The 29 missing stands are the ones that took no scans at all, and they are missing from the report for a reason that is structural: the report groups a table of scans, and a stand with no scans contributed no rows to group.

Nobody spots it. The report looks complete, the totals reconcile against the scan count, and the only people who know are the 29 exhibitors deciding whether to rebook. Factless fact table coverage is the fix, and it is a small table that pays for itself in the first week it exists.

Why do the zeros disappear?

A transaction fact table is a record of things that happened. That is its entire contract, and it is the reason the design works: no row is written speculatively, so every row is evidence.

The consequence is that any query over it is conditioned on activity. Group scans by exhibitor and you get the exhibitors with scans. Average scans per exhibitor and you get the mean over the exhibitors with scans, which is 38,900 divided by 351, or 110.8. The number a commercial director actually wants is 38,900 divided by 380, or 102.4, and the two differ by 8.4 scans per stand because the denominator quietly changed under them.

An outer join against the exhibitor dimension gets you part of the way, and it is what most teams reach for first. It breaks as soon as eligibility is not the same as existence. The dimension holds every exhibitor who has ever booked with you across five years and eight shows. Joining to it returns hundreds of companies that were not at this edition and never could have been scanned, which is a different kind of wrong answer.

What Kimball calls this, and why the name is unhelpful

The Kimball Group's technique page on factless fact tables, summarising the third edition of The Data Warehouse Toolkit by Ralph Kimball and Margy Ross (Wiley, 2013), describes the structure directly. It calls for "a factless coverage table that contains all the possibilities of events that might happen and an activity table that contains the events that did happen", and then states the mechanic in one sentence: "When the activity is subtracted from the coverage, the result is the set of events that did not happen."

Bob Becker's Kimball Group design tip of April 2011 gives the general definition, calling factless fact tables "fact tables that have no facts but captures the many-to-many relationship between dimension keys", and lists coverage as one of the standard uses, with retail promotions as the worked case: a row for every item being promoted, so somebody can ask which promoted items did not sell.

The name is the obstacle to adoption. Factless sounds like a table that failed to have a purpose, and it reliably gets challenged in design review by somebody who wants to know why a fact table with no facts is being built. Call the physical table what it does. Name it coverage, or eligibility, and the review goes differently.

What one coverage row asserts

A coverage row is a claim: this pairing of dimensions was possible during this window. Nothing about it is a measurement, which is why there is no measure to store.

For the exhibitor case the grain is one row per exhibitor per edition, sourced from the signed contract list rather than from the floor plan or the dimension. Keys to exhibitor, edition, hall and stand number, plus an effective date range so a company that cancelled three weeks out is on the table for the period it was actually expected.

For the conference case the grain is one row per session per edition, sourced from the published programme. A show with 180 sessions across 5 tracks produces exactly 180 coverage rows. Subtract the session entry fact and 12 of them come back with nothing, which is 6.7 per cent of the programme running to an empty room, a figure the conference team has never seen because it has never been computable.

Both tables are tiny. 180 rows and 380 rows, at eight bytes of keys each, are measured in kilobytes. The cost is entirely in deciding what eligible means, which is a conversation with the commercial team rather than a modelling exercise.

How large should a coverage table be?

The failure mode is building it as a cross product and then defending the result.

Take exhibitors and halls. 380 exhibitors across 5 halls, multiplied out, gives 1,900 rows. Only 380 of those are real, because an exhibitor occupies one hall, so 1,520 rows, or 80 per cent of the table, assert pairings that were never possible. Every rate computed against that denominator is wrong by a factor of five, and the error is invisible because the arithmetic is correct.

The rule that holds up is that coverage comes from a source system that already knows the answer. The contract list knows which exhibitors were expected. The programme knows which sessions were scheduled. The room booking knows which rooms were in use on which day. Where no source system knows, the cross product is a signal that the eligibility question has not been answered yet, and building the table anyway just moves the argument downstream to whoever reads the report.

One useful exception is the deliberately dense case. A coverage table of hosted buyer by published meeting slot genuinely is a cross product, because every buyer was free in every slot before the schedule was built, and 240 buyers by 6 slots is 1,440 rows that all mean something. The test is whether you can say out loud why each individual row was possible.

Does every fact table need a coverage twin?

No, and building them by reflex is how a warehouse acquires tables nobody queries.

The test worth applying is whether absence is actionable. An exhibitor with no scans is actionable: somebody calls them. A session with no entries is actionable: it comes off next year's programme or moves out of the 08:30 slot. A registration channel that produced nothing is actionable, because somebody is still paying for it.

Against that, a coverage table over date by device tells you which scanners were idle on which day, which is real information that operations already has from the kit return sheet and does not need a fact table for. And a coverage table over person by session, meaning every delegate who could have attended every breakout, is 12,400 registrations multiplied by 180 sessions, which is 2,232,000 rows asserting a possibility so weak it carries no information. Nobody was ever going to attend all 180.

Where absence is not actionable, the reporting question is usually satisfied by a fixed denominator held as a single number, and a coverage table is the wrong amount of machinery.

What the subtraction actually buys

Three questions become answerable, and they are questions that get asked every year.

The first is the coverage rate itself. 351 of 380 exhibitors recorded at least one scan, which is 92.4 per cent, and the 7.6 per cent who recorded nothing is a renewal risk list with names on it. That list is worth more to a sales director than any ranking of the top performers, because the top performers were going to rebook anyway.

The second is the honest denominator for every average in the report. Scans per exhibitor, entries per session, meetings per buyer. Each of those has been silently computed over the participating subset for as long as anyone can remember, and each moves when the denominator is corrected.

The third is the trend in absence. 29 silent exhibitors this year against 41 last year is a real improvement in either lead capture adoption or floor plan placement, and neither number exists without the coverage table. This is the one that takes discipline, because it needs the coverage table to have been built the same way in both editions, and a written grain statement is how that survives a change of analyst.

Where a coverage table misleads you

A coverage row is somebody's assertion about what was possible, and that assertion can be wrong in a direction that produces confident nonsense.

The clearest case is the exhibitor who paid and never turned up. If coverage is built from the contract list, that company appears as a zero-scan exhibitor and lands on the renewal risk list next to companies that were present all week and captured nothing. Those two need different phone calls. The fix is a status attribute on the coverage row, populated from whatever the operations team actually knows about who occupied their space, and the honest position is that at most shows this is recorded on paper or not at all.

Cancelled sessions have the same shape. A breakout dropped on the Monday morning is on the published programme, so it is on the coverage table, and it will report as an empty room forever unless somebody writes the cancellation back.

The subtler limit is that coverage tells you an event did not happen and says nothing about whether it could have been observed. A session with no entries in a room where the scanner was never switched on is indistinguishable from a session nobody attended. That distinction needs the scan window recorded per door, which is why the entry fact and its denominator has to carry collection metadata, and it is the same failure that makes a stand with a broken lead retrieval unit look like a stand nobody visited on the scan fact.

Build the exhibitor one first, because it takes an hour. Pull the contracted exhibitor list for the last edition, load it as one row per exhibitor per edition, left join the scan fact and count the nulls. The number that comes back is the size of the hole in every exhibitor average you have published, and it belongs in the data platform conventions as a standing table rather than a one-off query.