Adding days to show open to your date dimension

Somebody asks for the registration curve of this year's edition against last year's, and gets a chart where one line stops halfway across. The show moved from March to May, so the two campaigns occupy different parts of the calendar and the comparison has nothing to stand on.

The fix everyone reaches for is days to show open, an integer counting the distance between any date and the day the doors open. It is the right instinct. It is also the point where a lot of models put the column in the wrong table and spend a year finding out.

Why can't the offset live on the date dimension?

The date dimension is shared. The Kimball Group's technique page on calendar date dimensions describes it as "attached to virtually every fact table to allow navigation of the fact table through familiar dates, months, fiscal periods, and special days", which means one row per calendar date, used by every process and every show in the portfolio.

Now try to add a days_to_open column to it.

Your kitchen and bath show opened on 14 March 2025. Your hospitality design show opens on 3 May 2026. Take the row for 12 March. Relative to the March show that date is minus 2. Relative to the May show it is minus 52, because 19 days remain in March, April contributes 30 and May contributes 3, which sums to 52.

One row, two correct answers, and a portfolio with eight brands wants eight of them. There is no column that holds all eight, and a column that holds one of them is a bug waiting for the first person who filters by the wrong show.

Teams work around this by creating a date dimension per show, which is how you end up with eight date dimensions that drift apart on fiscal period definitions and stop conforming. The Kimball Group's guidance on the date dimension points the other way, including its instruction that filtering and grouping belong on the dimension's own attributes and never on a smart key built out of the date value. A shared date dimension holding genuinely universal attributes is one of the few pieces of a data platform that never needs to be rebuilt, and edition-relative offsets do not belong in it.

The offset that two editions share

The reason to want the integer at all is worth demonstrating with real dates.

The kitchen and bath show opened on 14 March 2025 and its 2026 edition opens on 3 May, a shift of 50 days. A registration taken on 21 January 2025 is 52 days before that year's opening: 10 days left in January, 28 in February, and 14 in March gives 52. A registration taken on 12 March 2026 is also 52 days before opening.

Those two dates are 50 days apart in the calendar and identical in campaign position. The offset is what makes them comparable, and the pacing comparison built on top of it is a separate subject with its own traps, including whether the campaign window itself changed length.

The same logic runs after the doors open. Plus zero is opening day. A three-day show opening 3 May has plus zero, plus one and plus two as its show days, and a badge scan on 4 May carries plus one whichever year you are looking at. Comparing attendance by position in the run is what makes a moved show honest, and the attendance side of that correction has enough in it to be its own post.

An edition calendar as an outrigger

The design that works is a small table keyed on edition and date.

One row per edition per relevant date, carrying the edition key, the date key, the days-to-open integer and the edition day number. Cover a window from 365 days before opening to 30 days after closing and each edition needs about 400 rows. Eight brands with five years of history is 40 editions, so the whole table is roughly 16,000 rows.

Sixteen thousand rows is nothing. It fits in memory on any database made this century, and it turns every edition-relative question into a join rather than a calculation.

Facts join to it on both keys. A scan fact already carries an edition key and a date key, so the join is a two-column equality and the query planner handles it without help. Registration facts do the same.

The table is also the natural home for anything else that is edition-relative and calendar-shaped: whether the date falls inside the early bird window, whether it is a build-up day, whether it is a public holiday in the host country for that edition. Those attributes are all specific to one staging, and holding them here keeps the edition dimension at one row per edition where it belongs.

Should the integer live on the fact row instead?

Sometimes, and the trade is worth being explicit about.

Writing days_to_open directly onto the fact row at load time costs four bytes per row. On an edition producing 38,900 badge scans that is 155,600 bytes, about a sixth of a megabyte. The value is then available with no join at all, which matters for the dashboards that filter on it constantly.

The condition is that the value has to be fixed at load. It is fixed if the edition of a row never changes and the open date is final when the row lands. It is not fixed if your open date can move after loading, which happens more than anyone admits: a hall handover slips, a build day is added, and the published open date changes in February for a show in May.

I would do both. Store the integer on the fact for speed, keep the edition calendar as the definition of record, and add a test that recomputes the fact column from the calendar and fails if any row disagrees. That test costs a query a month and it catches the case where an open date moved and 60,000 registration rows kept the old offset.

Where a show has genuinely never moved its dates after loading, the fact column alone is fine and the calendar table is still worth having for the attributes that are not a simple subtraction.

Role playing, and the naming trap

A single fact table usually needs more than one date. A registration fact has a registration date, a payment date and possibly a cancellation date. A scan fact has an event date and an ingest date.

The Kimball Group's technique page on role-playing dimensions covers this pattern: "A single physical dimension can be referenced multiple times in a fact table, with each reference linking to a logically distinct role for the dimension." The mechanism is separate views over one physical table, and the page is specific about what those views must do: "These separate dimension views (with unique attribute column names) are called roles."

Unique attribute column names is the part teams skip, and it is the part that causes the outage. If both the registration date view and the payment date view expose a column called days_to_open, a report that joins both and groups by days_to_open will silently pick one, and which one depends on the tool. Name them registration_days_to_open and payment_days_to_open and the ambiguity cannot arise.

The same discipline applies to the edition calendar when a fact needs two edition-relative offsets, which happens on cross-show meeting facts where two parties are registered for different editions.

What the edition day number adds

Days to open handles the campaign. The edition day number handles the show itself, and they answer different questions.

For a three-day show, edition day 1, 2 and 3 lets you compare Wednesday of this year against Wednesday of last year even when the run moved from a Tuesday start to a Wednesday start. Build-up and breakdown days can carry 0 and negative numbers or a separate day type attribute, and the choice matters less than making it once.

The Kimball Group's date dimension page also notes that the dimension "needs a special row to represent unknown or to-be-determined dates". The edition calendar needs the same thing for a different reason. Scans arrive with unparseable timestamps, registrations arrive with null dates from a broken import, and a fact row with no date has to point somewhere. A row with a sentinel offset that no report treats as a real value keeps those rows loadable and countable, which is better than dropping them and wondering later why a total is short.

The limit

The offset is a calendar subtraction, so it inherits everything wrong with calendars.

A registration timestamped in UTC and a show opening in Singapore can land a day apart, and at minus 1 against plus 0 that single day is the difference between a pre-show registration and an on-site one. Decide whose clock defines the boundary, write it on the edition row, and apply it at load.

The offset also flattens a real difference between shows. Minus 52 for an annual show with a nine-month campaign is a different commercial position from minus 52 for a show that opens registration eight weeks out. Two editions of the same brand compare cleanly. Two different brands at the same offset do not, and the number tempts people into that comparison because it looks universal.

Nothing here fixes a missing open date. If your edition rows are populated from a spreadsheet somebody maintains by hand, the offsets are exactly as reliable as that spreadsheet, and one transposed date silently reshapes an entire pacing chart.

Build the edition calendar this week. Forty editions, a 400-day window each, four columns. Then take the last edition you closed, join your registration fact to it, and check that the earliest registration lands where you expect on the axis. If it does not, the open date on that edition row is wrong, and you have just found it before anyone published from it.