Designing the event and edition dimension for a show that repeats yearly

The brief that arrives is deceptively small. We need an events table. Eight shows, five years, put them in a dimension and move on.

Then somebody asks for a five-year trend on a show that changed its name in 2024, moved venue in 2025 and ran twice in 2023 because of a calendar shift. Event and edition dimension design is the part of the model where those three facts either fit or force a rebuild, and the decision is made in the first hour by whoever picks the key.

What actually changes between one edition and the next?

Two things are being described here and they move at different speeds.

The show brand persists. It has a vertical, an owning business unit, a launch year, an audience proposition and a name that changes occasionally. It exists whether or not an edition is running.

The edition is one staging. It has an open date, a close date, a number of show days, a venue, a hall configuration, a floorplan, a rate card and a set of commercial targets. Every one of those attributes belongs to that staging alone, and the next edition may differ on all of them.

Almost every reporting failure in this area comes from modelling one of those and pretending it covers the other. A dimension holding only brands cannot answer what net square metres were sold in 2024. A dimension holding only editions cannot answer whether the show has grown over five years without somebody matching names in a spreadsheet.

Two keys on one row

The design that holds up is a single dimension at edition grain carrying two keys.

The surrogate edition key is what fact tables store. The Kimball Group's technique page on dimension surrogate keys is direct about the form these take: "These dimension surrogate keys are simple integers, assigned in sequence, starting with the value 1, every time a new key is needed." No meaning in the value, no source-system identifier embedded in it, no date stuffed into the last four digits.

The durable event key identifies the brand across all its stagings. The Kimball Group's page on natural, durable and supernatural keys explains why this cannot be the operational identifier: "Natural keys created by operational source systems are subject to business rules outside the control of the DW/BI system." When the registration platform is replaced, or when the show is sold and gets a new code, the natural key changes and every historical row that used it becomes an orphan. The durable key is assigned by the warehouse, is persistent, and takes the same form the same page recommends for keys generally, a simple integer assigned in sequence.

So the row for the 2026 staging of your kitchen and bath show carries edition_key 27 and event_key 3, and the 2022 staging of the same show carries edition_key 11 and event_key 3. Group by event_key and you have a brand trend. Group by edition_key and you have one staging. Nothing had to be parsed out of a name.

Both keys sit on the same dimension row, which keeps the fact tables narrow. A scan fact stores the edition key and reaches the durable key through the dimension, so a rename never touches a fact table.

Forty rows, and what they let you ask

The scale of this dimension is worth stating because it changes how much care it deserves.

Eight show brands with five years of history is 40 edition rows. A portfolio scan history over the same period runs to something over a million and a half rows. So the edition dimension is roughly two thousandths of one per cent of the data in the data platform, and it determines the grouping of nearly every published figure.

Put the commercial attributes on it and the arithmetic that show directors ask for stops needing a join to anywhere else. Give each edition row an open date, a close date, a show day count, a hall count, gross square metres and net square metres sold.

Take one brand's five editions with net square metres of 11,200, 12,050, 12,900, 12,400 and 13,100. Year-on-year movement is a single query over the dimension: up 850, up 850, down 500, up 700. The 2025 fall of 500 on 12,900 is 3.9 per cent. Divide the 2026 figure of 13,100 by that edition's three halls and you get 4,367 net square metres a hall, which is the number a floorplan conversation actually starts from.

None of that touched a fact table. A well-built edition dimension answers a surprising share of executive questions on its own, and it does so in milliseconds because there are 40 rows.

The edition sequence number is worth adding while you are there. Edition 1 through edition 5 for each brand, so a query can ask about first editions across the portfolio without anyone hard-coding years.

Show day count deserves the same treatment. A brand that ran four days in 2022 and three days from 2023 onwards carries a 25 per cent reduction in trading time into every comparison after it, and holding the day count on the row means any per-day figure can be computed without somebody remembering the change. Sum that column across the portfolio and 40 editions averaging 3.2 days gives 128 show days of trading in five years, which is the denominator behind every staffing and stand-service conversation you will have.

Why does the show that ran twice break everything?

Because most models assume edition and year are the same thing, and they encode that assumption in a column called year that reports quietly group by.

A show that moves from March to November will occasionally run twice in one calendar year, or skip one. A launch edition in October 2023 followed by the regular slot in March 2025 leaves 2024 empty. Grouping by calendar year then produces a chart with a hole in it and a doubled bar, and the show director will assume the data is broken.

The edition sequence number solves the comparison, because edition 4 against edition 3 is a meaningful pairing whatever the calendar did. The open date on the row solves the timeline, because a chart can plot editions along real dates and show the gap honestly.

What the edition row must never do is carry a single year integer that reporting treats as the identity of the staging. Keep the year as a derived attribute for filtering and let the sequence number carry the ordinal meaning. Making registrations comparable across a moved show needs a further step on the date dimension, which is the days-to-open integer and belongs to that post.

What belongs on the edition row and what does not

The test is whether the attribute varies by staging.

Open date, close date, show day count, edition sequence number, venue, hall count, gross square metres, net square metres sold, rate card version, currency and the commercial target set all vary by staging, so they go on the edition row.

Vertical, owning business unit, launch year and the brand name as currently traded describe the show rather than the staging. You can hold these as durable event attributes on the same row, repeated across editions, which is standard practice for a dimension at the lower grain. The repetition costs nothing at 40 rows and it saves a join on every query.

Hall layout is the awkward case, because it changes every edition while the building does not. Keeping the venue in its own slowly changing dimension and the per-edition hall configuration on the edition row is the split that works, and the venue side of that has enough in it to be its own subject.

Anything that varies within an edition stays off this dimension entirely. Daily opening hours, hall-by-hall square metres and stand-level pricing all belong in facts or in their own dimensions, because putting them here would break the one row per edition grain.

Where this stops

The two-key design survives renames and venue moves. It does not survive a merger of two show brands into one, and no key design does.

When your two regional shows combine into a single national edition, the durable event key has to make a choice. Either the merged show inherits one predecessor's key, and its trend line silently absorbs the other's history, or it gets a new key and both predecessors end. Both answers are wrong for somebody in the room. What works is recording the decision on the dimension with a predecessor event key column, so a report can reconstruct either view and the choice is visible in the data.

The same limit applies to a show that splits into two verticals. There is no arrangement of keys that makes a five-year comparison honest across a split, and the useful move is a footnote on the chart rather than a cleverer model.

The dimension is also only as good as the operational discipline behind it. Net square metres sold on an edition row has to come from somewhere, and if it is typed in by whoever remembers, it will be wrong for at least one edition and nobody will know which. Source it from the space contract fact where you can, and where you cannot, record the source and the date on the row itself. Company-level history has the same problem in a harder form, which is why exhibitor identity needs type 2 rows with effective and expiry stamps.

Build the dimension this week with 40 rows and eight columns. Open date, close date, show days, venue, halls, gross square metres, net square metres and edition sequence, with a durable event key you assign yourself. Then run one query grouping net square metres by durable event key across five years, and see whether the trend it produces matches the one in your last board pack.