A session attendance fact table needs both a scan and a capacity

The conference director opens the post-show deck on the Monday and stops on slide nine. Session 14 had 214 entries. She asks whether that is good, and nobody in the room can answer, because the warehouse knows how many people walked in and has no idea how many seats were waiting for them.

That gap is the design question behind a session attendance fact table. The entry event and the number it gets divided by belong in different tables, and the instinct to put them in the same table breaks something specific.

What does one row actually record?

Ralph Kimball's 2008 article on fact tables names the three shapes worth choosing between: "All of your fact tables can be grouped into just three types: transaction grain, periodic snapshot grain and accumulating snapshot grain." A session entry is a transaction grain. One row per person per entry, stamped with the second the reader fired.

The keys are the usual set, and the argument for keeping the wide version of that set has already been made for the stand capture table, so take date, time of day, person, session, room, edition, device and entry type as given here.

What is worth dwelling on is the column that does not exist. The Kimball Group's technique page on factless fact tables uses almost this exact case as its illustration, describing "an event of a student attending a class on a given day" that "may not have a recorded numeric fact", where "a fact row with foreign keys for calendar day, student, teacher, location, and class is well-defined". Swap student for delegate and class for breakout session and the table is the same table. The measurement is that the row exists.

Why capacity cannot live on the fact table

The temptation is obvious. Every report wants fill rate, fill rate needs capacity, so put capacity on the row and let the report divide.

Kimball's 2008 piece states the rule that stops this: "We always strive to make the facts additive across the dimensions and exactly consistent with the grain." His own example is a retail price. "We don't store the price of the product being scanned because the price is nonadditive. Rather, we store the extended price, which can be added freely."

Room capacity behaves like that price. A session held in a room set for 300 produces 214 entry rows, each carrying the value 300. Sum that column and you get 64,200, a number with no meaning that will nonetheless appear in a report the first time somebody drags the field into a total row. Average it and you get 300 back, which is correct and useless, because the average conceals that it was the same 300 repeated 214 times.

The deeper problem is that capacity does not vary at the grain of the fact. It varies at the grain of the session. A column that is constant within every group is a dimension attribute wearing a fact's clothing.

Where a numeric attribute belongs

The Kimball Group's technique page on numeric values as attributes or facts, drawn from the third edition of The Data Warehouse Toolkit by Ralph Kimball and Margy Ross (Wiley, 2013), gives the test in two sentences. "If the numeric value is used primarily for calculation purposes, it likely belongs in the fact table." And: "If a stable numeric value is used predominantly for filtering and grouping, it should be treated as a dimension attribute."

Capacity is used for both, which is why the question comes up at all. It gets divided into, which sounds like calculation. It also gets grouped by, because somebody always wants to know how the sessions in rooms under 150 seats performed against the big halls. Stability breaks the tie. A session's capacity is fixed from the moment the room set is signed off until the session ends, so it goes on the session dimension and the division happens in the query.

That placement has a second benefit. The session dimension row already holds the track, the start time, the format and the speaker, so the capacity sits next to the other things a person filters on, and a report writer looking for it finds it where they would look.

Why not store one row per session with a headcount?

Every few years a team proposes the smaller table. One row per session, holding the entry count and the capacity together, computed once at load time. It is 180 rows for a programme of 180 sessions instead of the 12,200 entry rows the same programme generates, so it loads in a second and every fill rate is already sitting there.

That table is a periodic snapshot in Kimball's 2008 taxonomy, and building it as the only table costs more than it saves. 180 rows against 12,200 is 1.5 per cent of the storage, which at these volumes is a saving of nothing, and the questions it can no longer answer arrive within one edition.

It cannot tell you how many distinct people attended any part of the conference programme, because summing 180 session counts double counts everybody who went to two things. It cannot tell you which sessions the same person attended in sequence, which is the question the programme team asks when they are deciding whether two tracks clash. It cannot tell you when in the ninety minutes people arrived. And it cannot be recomputed under a corrected definition of attendance, because the entries it was built from are gone.

Build the summary if the reports are slow. Build it from the entry rows, keep the entry rows, and treat the summary the way you treat an index, which is the general form of the argument and belongs to L2.

Doing the fill rate arithmetic

Three sessions from a fixture edition, worked by hand.

Session A took 214 entries in a room set theatre style for 300. That is 214 divided by 300, or 71.3 per cent. Session B took 96 entries in a cabaret set for 120, which is 80 per cent. Session C took 318 entries in a room set for 300, which is 106 per cent, and the moment a fill rate goes above 100 you have learned something real about the door.

Now roll the three up. Total entries are 214 plus 96 plus 318, which is 628. Total capacity is 300 plus 120 plus 300, which is 720. The programme fill rate is 628 over 720, or 87.2 per cent.

Take the mean of the three individual percentages instead and you get 71.3 plus 80 plus 106, divided by three, which is 85.8 per cent. The two answers differ by 1.4 points on three sessions, and the gap widens with the spread of room sizes, because averaging ratios silently weights a 40 seat room the same as a 900 seat hall. Sum the numerators and sum the denominators. The star schema makes that the easy query and the other one the awkward one, which is the right way round.

Which capacity are you dividing by?

There are at least three numbers that all get called capacity, and choosing between them is a business decision that the data team should force rather than absorb.

The occupant load is what the building allows, set by the fire authority against floor area and exit width. The room set is how many chairs were actually put out, which for the same room might be 300 theatre, 160 classroom or 120 cabaret. And the sellable capacity is whatever the programme team decided to cap registration at, often below the chair count so the front two rows stay empty for the panel.

Fill rate against the occupant load flatters nobody and tells operations whether the building is being used. Fill rate against the room set is what the conference team means when they ask the question. Fill rate against the cap is the only one that tells you whether demand exceeded what you offered.

Hold whichever one you pick on the session dimension, name the column for the definition rather than for the word capacity, and if you need more than one, hold more than one. Three integer columns cost twelve bytes on a table with a few hundred rows. Where the room itself changes between editions, that history belongs with the venue and hall dimension and the session row should carry the number that applied on the day.

What the ratio still will not tell you

Fill rate computed this way measures cumulative entries against seats, and a room is not filled cumulatively.

A 90 minute session with 214 entries and a steady trickle of people leaving for the coffee break might never have held more than 140 bodies at once. The entry fact records arrivals, so peak occupancy needs exit scans, and almost nobody scans the way out. Where re-entry is possible the gap widens further: those 214 entries came from 198 distinct badges, so 16 of them were somebody coming back in, and the distinct badge fill rate is 198 over 300, or 66 per cent, five points below the entry figure.

Publish both. An entry count and a distinct badge count side by side is honest, and the difference between them is itself a signal about how porous the room was.

The larger limit is coverage. Scanning at session doors is patchy at most shows, run by a temporary staff member who may or may not have caught everyone during the surge in the first four minutes. A session fill rate of 71.3 per cent where the door was scanned for the first ten minutes only is a scan coverage figure dressed up as an attendance figure, and no schema fixes that. The fix is procedural, and reporting session attendance alongside the scan window it was collected over is the minimum honesty.

Sessions that recorded nothing at all are a different problem again, because a transaction fact has no row to show you. Those need a coverage table holding every session on the programme.

Start by pulling last edition's session list and checking whether a capacity number exists anywhere in a system you control, as opposed to in a room set spreadsheet an operations manager keeps locally. If it lives only in that spreadsheet, getting it into the session dimension is a one afternoon job and it converts every entry count you already hold into a fill rate. Write down which of the three definitions you loaded, in the data platform conventions, before anyone builds a report on it.