Conformed dimensions across shows are what make a portfolio roll up work

The board asks how many companies exhibit with the group. Somebody sums the exhibitor counts of eight shows, gets a number, and someone else points out that at least one firm they can name appears in four of those shows.

Nobody can say by how much the total is wrong, because answering that needs the eight lists compared, and the lists cannot be compared. Conformed dimensions across shows are the thing that would have made the question answerable in a query instead of a project, and the reason most portfolios cannot answer it is that each show built its own dimensions in isolation and every one of them was reasonable on its own terms.

What does it take for two dimensions to conform?

The bar is higher than "both shows track industry sector".

The Kimball Group's technique page on conformed dimensions, drawn from the third edition of The Data Warehouse Toolkit by Ralph Kimball and Margy Ross (Wiley, 2013), sets the condition precisely. Dimension tables "conform when attributes in separate dimension tables have the same column names and domain contents", and the same page calls this "the essence of integration in an enterprise DW/BI system".

Two requirements sit in that sentence and teams usually satisfy one.

Same column names is the easy half. Rename stand_sector to exhibitor_category in one model and the schemas look compatible.

Same domain contents is the half that takes the work. The permitted values have to be the same set, with the same meanings, so that grouping by the attribute produces the same buckets whichever fact you started from. A column called exhibitor_category holding 41 values in one show and 62 different values in another has conformed names and unconformed contents, which is worse than no conformity at all because the query now runs and returns something wrong.

The portfolio roll up that reports 103 sectors

Put numbers on it.

Show A classifies exhibitors into 41 categories, built up over a decade by the sales team and matched to how that market talks about itself. Show B uses 62, inherited from an acquisition and mapped to its own directory.

Union the two dimension tables without a mapping and the portfolio category dimension has 103 rows. Look at the underlying concepts and only 74 distinct sectors exist, because 29 of the 103 are the same sector under two labels. Commercial vehicles in one list and light trucks in the other. Contract furnishings in one and hospitality furniture in the other.

The roll up therefore reports 103 sectors where 74 exist, inflating the category count by 29 on 103, which is 28.2 per cent. Worse than the count, every sector split by revenue or square metres is now spread across two rows, so no sector reaches the top ten that its true size deserves, and the portfolio's largest verticals disappear from the chart that exists to find them.

The company dimension does the same thing with higher stakes. Show A holds 1,900 exhibitor company records and Show B holds 1,240. If 310 firms exhibit at both, the true count of distinct companies is 2,830 and the naive union reports 3,140. That is an 11.0 per cent overstatement of how many customers the group has, sitting in a slide that gets used to argue about cross-sell.

Getting from 3,140 to 2,830 needs the two company records recognised as one firm, which is a matching problem with its own methods and its own failure modes, and it belongs to the identity resolution cluster. What conformity adds is the destination: one company dimension, one key per firm, used by every fact in the data platform, so the match is done once instead of being re-derived in every report.

Conformed facts, and the measure that means two things

Dimensions get the attention and measures cause as much damage.

The Kimball Group's page on conformed facts states the requirement: "If the same measurement appears in separate fact tables, care must be taken to make sure the technical definitions of the facts are identical if they are to be compared or computed together." The instruction that follows is the one worth adopting as policy: conformed facts "should be identically named; but if they are incompatible, they should be differently named to alert the business users and BI applications."

In an event portfolio the standing example is verified attendance. Show A counts a person as attended on any badge scan. Show B requires a hall entry scan and excludes exhibitor staff. Both call the measure verified_attendance. Both are defensible. Summed together they produce a portfolio attendance figure that is a mixture of two definitions and describes nothing.

The rule gives you two exits. Conform the definitions, agree one rule, and keep the shared name. Or accept that the two shows measure different things and name them differently, verified_attendance_hall_entry and verified_attendance_any_scan, so the portfolio report either picks one or shows both.

I would take the second exit far more often than teams do. Forcing a definitional merge when the two shows genuinely operate differently produces a conformed measure that neither show director recognises, and they will both go back to computing their own, which is where you started with an extra table.

Which dimensions to conform first?

Conformity is expensive per dimension, so the order matters more than the ambition.

Two dimensions earn the work immediately in almost every portfolio. Company, because it is the axis of every commercial question worth asking across shows. Edition, because every question is asked per staging and because the edition key is what carries a fact back to a show, a year and a venue.

Date is usually free, since a shared calendar needs no negotiation about meaning, with the caveat that anything edition-relative belongs elsewhere.

Conforming those three is also what makes a cross-process query safe to write, because each fact can then be summarised to shared attributes and merged, which is the escape route from a grain mismatch between two fact tables.

After those three, the honest answer is that it depends on which processes you actually report across, and that is a question a matrix answers. Laying out processes against dimensions and shading the cells shows which dimensions are used by every row, and those are the ones to build first. Drawing that matrix takes a morning and it converts an argument about priorities into a count of shaded cells.

Person is the dimension teams reach for first and it is rarely the right starting point, because resolving people across shows is the hardest match in the portfolio and the payoff arrives later than the company work.

Why do conformity programmes stall after the kickoff meeting?

Because the cost lands on the show teams and the benefit lands on the group.

A show director whose sales team has used the same 41 categories for eleven years gets asked to renumber into a 74-value master list so a portfolio report can be produced. Her exhibitors recognise the old labels. Her rate card is structured around them. Her floorplan zones are named after them. The renumbering gains her nothing and costs her a season of confusion, so she agrees in the meeting and quietly keeps using the old list in the operational system.

Six months later the warehouse has a conformed dimension nobody populates correctly and a set of local values arriving as free text in a notes field.

The design that survives keeps both. A conformed master list for portfolio grouping, the local list on every screen the show team touches, and a mapping table between them maintained as a real object with an owner. Nobody is asked to give up their vocabulary, and the roll up still works.

The same principle applies to company. Show teams keep their account names. The warehouse assigns a durable company key and holds the local names as aliases.

What a conformed dimension costs to run

It costs a person, part time, permanently, and pretending otherwise is how these initiatives fail in year two.

The recurring work is small and it never stops. New exhibitor categories appear each edition and need mapping. Companies merge and the master needs a decision about which key survives. A new show joins the portfolio with its own 38-value list and someone maps it. Two master values turn out to be the same sector and need merging, which changes historical grouping and needs a note.

Budget an afternoon a month, plus a day per new edition loaded, and name the person. A conformed dimension with no owner degrades into an unconformed one within about four editions, and the degradation is invisible until a roll up produces a number somebody notices is wrong.

The other cost is a review gate on schema changes. Any change to a conformed dimension affects every fact that uses it, so it needs the same treatment a breaking API change gets: a diff, a named approver and a rebuild of the affected reports before it ships.

Where this stops

Conformed dimensions make a portfolio roll up computable. They do not make it meaningful.

Eight shows across different verticals, geographies and business models produce measures that can be summed and often should not be. Total exhibiting companies across the group is a real number. Average leads per exhibitor across the group mixes a machine tools show where a stand takes 40 leads with a consumer gift fair where it takes 900, and the average describes neither.

Conformity also cannot fix a source that never captured the attribute. If Show C has no industry category on its exhibitor records at all, mapping cannot invent one, and the portfolio report has to carry an unclassified bucket with a real count in it. Publishing that bucket honestly is better than distributing its rows across the known categories in proportion, which is a fabrication with a plausible shape.

And a conformed dimension is a snapshot of agreement at one moment. Markets reorganise, a vertical splits, and the master list that fitted in 2024 is wrong by 2028. Version the master list, date each version, and accept that a five-year comparison across a version change needs a footnote.

Take the exhibitor category lists from your two largest shows this week, put them side by side in a spreadsheet, and mark every pair that means the same thing. The count of pairs you find is the amount your current portfolio roll up is double counting, and it takes about two hours to discover.