Drawing an event bus matrix before you model a single fact table

Three months into a data platform build, an event business usually has two good marts and an argument. Registration reporting works. Exhibitor lead reporting works. Nobody can produce a single view of a company across both, because each mart built its own company table and they disagree about which records are the same firm.

An event bus matrix is a morning of work that prevents this, and it is one of the few planning artefacts that pays for itself before lunch. It is a grid: processes down the side, dimensions across the top, a mark where the process needs the dimension.

What the matrix is and what it decides

Ralph Kimball described the device in December 1999, and his summary is still the shortest one available: "The matrix is simply a vertical list of data marts and a horizontal list of dimensions." The Kimball Group's later technique page on the enterprise data warehouse bus matrix uses the vocabulary most teams now use: "The rows of the matrix are business processes and the columns are dimensions." The shaded cells mark which dimensions each process needs.

What it decides is build order and ownership, which are the two things that actually go wrong on a warehouse programme.

Build order, because a dimension shaded in six rows should exist before any of those six facts are loaded. Ownership, because a dimension used by six processes cannot belong to whoever happened to need it first.

The matrix does not decide grain, columns, or storage. It sits earlier than all of those, and it is deliberately coarse enough that a show director can read it without a glossary.

Six processes, nine dimensions

Here is a matrix for a B2B exhibition business. Six processes that produce measurable events, nine dimensions those events are analysed by.

Six rows by nine columns is 54 cells. Thirty-seven of them are marked, which is 68.5 per cent, and that density is typical. Event data is heavily shared, which is the whole reason conformity pays.

The row counts are worth reading on their own. Badge scan needs 8 of the 9 dimensions, which tells you it is the most demanding fact to build and the one most likely to expose a missing dimension. Exhibitor service order needs 4, which makes it the cheapest row on the grid and a reasonable place to prove the pipeline end to end.

Which dimensions do you build first?

Read down the columns and count.

Date is marked in all six rows. Edition is marked in all six. Company is marked in all six. Venue and hall is marked in four, exhibitor category in four, person in three, time of day in three, stand in three, and marketing channel in two.

Date is shared by everything and needs almost no negotiation, so it is built in an afternoon and nobody argues. That leaves edition and company as the first two real builds, and they are real builds because both require decisions that different teams will answer differently if asked separately.

Edition needs a durable key for the show brand alongside a surrogate key per staging, which is a design with genuine choices in it and no obvious default.

Company needs a decision about what counts as one firm, and every process on the grid depends on that answer. Building it once, before six facts have each invented their own, is the difference between a portfolio roll up that works and a reconciliation project that runs every quarter.

The columns with two or three marks tell you something too. Marketing channel appears on registration and booth contract only, so it can be built later by whichever team needs it first without much risk. A dimension used by one process is a local concern, and treating it with the same governance as company wastes the governance.

What the rows tell you about scope

Read across a row and you get a specification sketch for one fact table.

Badge scan carries marks under date, time of day, edition, venue, person, company, category and stand. That is eight foreign keys, and the row has just told you that a scan fact without a stand key cannot answer stand-level questions, however good the rest of it is.

A row with a suspiciously small number of marks usually means the process is under-specified rather than simple. If your meeting row has no company mark, somebody has assumed meetings are between people, and the first commercial question about meetings will be about which exhibitors got them.

A row with marks under dimensions nobody has built is a dependency, and it belongs on a plan. Sponsorship deliverable needing venue and hall is easy to miss until you discover that half of sponsorship inventory is physical and located.

Once the row is agreed, the next step is writing what one row of that fact table means, which is the grain statement and the point where the matrix hands over.

Why draw it before you model anything?

Because the alternative is discovering the shared dimensions by collision, and collisions cost about ten times what the matrix costs.

The failure sequence is consistent. Registration reporting is urgent, so a mart gets built with a company table derived from registration records. Exhibitor reporting is urgent six weeks later, so a second mart gets built with a company table derived from contract records. Both are correct locally. Neither can be joined to the other, because the registration version keys on the company name a registrant typed and the contract version keys on the legal entity on the invoice.

Now the group wants a portfolio view. The work to merge those two company tables is larger than building one properly at the start, and it has to be done while both marts are in production and feeding reports somebody depends on.

The matrix makes the collision visible in advance, on a single sheet, in a form a sponsor can read. That is its real function: it turns an architectural argument into a picture that a person who does not model data can still audit.

It also exposes the processes nobody had listed. Teams routinely draw the matrix, get to four rows, and then remember that sponsorship deliverables and exhibitor service orders produce facts too, and that both are commercially material. Those rows change the dimension list, which changes the build plan, and finding them on a whiteboard is considerably cheaper than finding them in month five.

The implementation matrix, once you start building

The planning grid has a more detailed sibling, and the Kimball Group's page describes it as the implementation bus matrix, where each business process row is separated into its individual fact tables or cubes so that precise grain statements and fact lists can be documented.

That is the version to maintain as the build proceeds. Badge scan stops being one row and becomes two: hall entry scans and exhibitor lead captures, each with its own grain statement and its own measure list, because they come from different devices with different guarantees.

Keeping the implementation matrix current gives you a second useful artefact, which is a live map of what a change to a dimension would break. Change the company dimension and six rows are affected. Change marketing channel and two are. That count is the review requirement, and it is a better basis for a change process than seniority.

Keep both versions. The planning grid stays coarse and goes on the wall. The implementation grid lives with the model code in version control, next to the transformations, so it changes in the same commit as the thing it describes. Conformity across the shaded columns is what the whole exercise is for, and what conformity actually demands is stricter than shared column names.

Where this stops

A matrix is a plan, and plans about data are only as good as the sources behind them.

Marking a cell says the process should be analysed by that dimension. It says nothing about whether the source system emits the key. A booth contract row marked under exhibitor category is a statement of intent, and if your contract system holds category as free text typed by a salesperson, the cell is a wish. Draw the grid, then walk each marked cell back to a field in a real extract, and mark the ones that fail.

The matrix is also silent about time. It shows which dimensions a process needs and not whether those attributes need history. Company appears in six rows, and whether the company dimension keeps the exhibitor's name as at contract date is a separate decision with real consequences for how old contracts report.

And a matrix drawn once and framed is worse than no matrix, because it acquires authority while going stale. A show that adds a hosted buyer programme adds a process row. An acquisition adds dimensions. Redraw it when the business changes, and treat the grid as part of the data platform rather than as documentation.

Get six people in a room for ninety minutes this week, list every process in your show that produces a measurable event, list the dimensions you report by, and mark the cells. Then count the marks in each column. If any column outside date is shaded in every row and does not yet exist as a single shared table, you have found what to build next.