A fact table grain mismatch is why your two reports disagree

Two people open the same dashboard and read two different registration totals off it. One filters by exhibitor, one does not, and the exhibitor-filtered version is bigger. Nobody can explain how a filter made a number grow.

The cause is almost always a fact table grain mismatch, and the mechanism is arithmetic rather than anything subtle about the tool. Somewhere in the model, a registration fact holding one row per person has been joined to a scan fact holding one row per scan, and the join has quietly multiplied one side by the other.

Where the extra rows come from

Take a slice of one edition: a single exhibitor category with 900 completed registrations attached to it, and 3,400 badge scans recorded against stands in that category.

Join those two tables on the person key. The registration fact contributes one row per person. The scan fact contributes one row per scan. For a registrant who scanned four times, the join emits four rows, each carrying a complete copy of her registration record.

Suppose 720 of the 900 registrants scanned at least once, and between them they produced the 3,400 scans. The inner join returns 3,400 rows. Count registrations off that result and you get 3,400, which is 3.8 times the true figure of 900.

Two errors are running at once, in opposite directions, which is what makes this hard to spot by eye.

The fan-out inflates. Every registrant appears once per scan, so anyone who worked the floor hard is now weighted by how much they walked.

The inner join deletes. The 180 registrants who never scanned anything have vanished from the result entirely. If you were measuring registration by source or by country, those 180 people are simply absent, and their absence is not random. People who register and never scan are disproportionately the ones who did not attend, which is exactly the population a no-show analysis is about.

What the join does to the money

Counts are the visible version. Sums are the expensive one.

Give each of those 900 registrations a paid conference fee of 145. The true revenue for the segment is 900 times 145, which is 130,500.

Run it through the joined result and the fee column is summed across 3,400 rows. That gives 3,400 times 145, or 493,000. The reported figure is 362,500 too high, and it is high by precisely the factor by which scans exceed registrations for the people who scanned.

What makes this dangerous is that the inflated number is stable. Run the query again next week and you get 493,000 again. Run it on a different segment and it is inflated by a different factor, because the scans-per-registrant ratio varies by hall, by category and by day. So the two segments cannot be compared, and nothing in the output looks broken. There is no null, no error, no obviously silly total. There is a revenue figure that is wrong by a multiplier nobody has computed.

The version of this that reaches a board pack is worse still, because a percentage computed from two inflated figures can come out roughly right, which builds confidence in a model that is producing nonsense one layer down.

Why does the mismatch survive code review?

Because the join is syntactically correct and semantically catastrophic, and reviewers read syntax.

The query says join registration to scan on person. That is a true statement about the world. The person in the registration row is the person in the scan row. Nothing about the SQL announces that the two tables are counted in different units, because SQL has no concept of a grain. It has a concept of a key, and the key is fine.

The second reason is that the model usually starts correct and drifts. Somebody builds a clean registration mart. Somebody else builds a clean scan mart. Six months later a third person needs a single view for a BI tool that prefers one wide table, and flattens them together into an obliging model called something like registration_scan_detail. Each build step was reasonable. The composite is not.

The third reason is that dimensional models make the failure look like a normal star join. Fact-to-dimension joins never fan out, because the dimension has one row per key, and teams generalise from that experience to fact-to-fact joins, where the guarantee does not hold. The Kimball Group's technique summary, drawn from the third edition of The Data Warehouse Toolkit by Ralph Kimball and Margy Ross (Wiley, 2013), states the underlying rule in the grain step: "Different grains must not be mixed in the same fact table." A join creates a result set, and the result set is a table with a mixed grain.

Drilling across, which is the actual fix

The correct pattern has a name and it is older than most of the tools people hit this with.

The Kimball Group's technique page on drilling across puts it in one sentence: "Drilling across simply means making separate queries against two or more fact tables where the row headers of each query consist of identical conformed attributes. The answer sets from the two queries are aligned by performing a sort-merge operation on the common dimension attribute row headers." The same page notes that reporting vendors sell this under names including "stitch and multipass query".

Applied to the example, it works like this.

Query one runs against the registration fact alone, grouped by exhibitor category, and returns 900 registrations and 130,500 of fee revenue.

Query two runs against the scan fact alone, grouped by exhibitor category, and returns 3,400 scans and 720 distinct scanning badges.

Then the two result sets, each one row per category, are merged on the category attribute. The output row reads: 900 registrations, 130,500 revenue, 3,400 scans, 720 distinct scanners. Every measure was computed at its own grain before anything was combined, so nothing multiplied anything.

The derived measures now work too. Scans per registrant is 3,400 divided by 900, which is 3.8. Scan coverage is 720 divided by 900, which is 80 per cent. Both are honest, and neither could be computed from the fanned-out table without a distinct count and a lot of care.

This is the pattern to reach for whenever a report needs measures from two processes side by side, which for an event business is most reports worth publishing.

What conformity has to mean for this to work

Drilling across only works if both queries can group by the same thing.

Kimball's condition is that dimensions conform "when attributes in separate dimension tables have the same column names and domain contents". Same column names, same values, not merely the same idea. If the registration mart calls it exhibitor_category with 41 values and the scan mart calls it stand_sector with 62, there is nothing to merge on, and the team will reach for the fan-out join because it appears to work.

That is why the mismatch is often a symptom of a missing shared dimension. Building one company and category dimension used by every process removes the temptation, because the correct query becomes the easy one.

Edition is the second attribute worth conforming early. Almost every event question is asked per edition, and if the registration fact carries a text field like "2026 Spring" while the scan fact carries an integer edition key, every cross-process report starts with a string comparison somebody will get wrong. Conformity of that kind is a property of the data platform as a whole, and it cannot be retrofitted inside a single report.

How do you find the mismatches you already have?

Three checks, and each takes minutes.

Compute every headline measure twice. Once from its own fact table with no joins, and once from whatever combined model your reports actually use. Any measure that comes out larger in the combined version is being multiplied. Equal values are not proof of safety, but a difference is proof of a defect.

Compare a count of rows against a count of distinct grain keys in every model that feeds a report. If your registration model has 3,400 rows and 900 distinct registration ids, the grain is no longer one row per registration, whatever the model is called.

Read the from clause of your widest reporting model and count the fact tables in it. More than one fact table joined directly, without an aggregation step in between, is where to look first.

Where the disagreement has already surfaced between two teams, the same evidence settles it. A line-by-line diff of the two definitions usually ends at a join, and it is faster than arguing about which number feels right.

Where this stops

Drilling across assumes both facts can be summarised to a common set of attributes, and some questions genuinely need row-level pairing.

Attributing a specific scan to a specific registration source, or measuring the gap between a person's registration timestamp and her first scan, requires the two records side by side on one row. That analysis is legitimate. What makes it safe is declaring the grain of the result before writing it: one row per registration with the first scan attached, at registration grain, with scan measures collapsed to that grain by a minimum or a count. The output is a new fact table with its own grain statement, built deliberately and documented, and it is a different object from an ad hoc join in a dashboard.

The other limit is that drilling across gives you correct measures side by side and no opinion about whether they belong side by side. A category with 3,400 scans and 900 registrations might have drawn most of those scans from people registered under a different category. The merge will not tell you that, and treating the columns as though they describe the same population is a reasoning error the query cannot catch.

Open the model behind your most-used report and count the fact tables joined in it. If there is more than one, run the same measure directly against its own source table and compare the two totals this week. The gap between them is the size of the error you have been publishing.