Somebody asks how much revenue came from customers acquired in the last quarter. Three exports later there are two answers, and an afternoon has gone into deciding which one to put in the board pack. This happens in businesses with good analysts and expensive BI tools, which is the clue that the problem is not analysis or tooling.
The argument of this post is narrow: the quality of your reporting is decided by your data architecture, and the reporting tool inherits whatever that architecture already settled. When a number computed from three exports disagrees with itself, it is usually one of four mismatches, and none of them are fixable in the chart.
Mismatch one: grain
Grain is what a single row represents. An orders table has one row per order. A line-items table has one row per product on an order. A payments table has one row per payment, and a partially paid order has several.
Join two tables of different grain and the rows multiply. Sum anything afterwards and the total is too high by a factor nobody can name. The reason this survives review is that the wrong number is still in the right order of magnitude, so it passes the only check most people apply.
The tell is a total that changes when you add an unrelated filter. If filtering by region changes your national total by more than the region’s share, you have a fan-out.
Mismatch two: as-of time
Every export has a moment at which it was true. Pull orders at 09:00, payments at 14:00, and a refund issued at 11:00 exists in one file and not the other. Reconciliation now shows a payment with no matching order state, and somebody writes a rule to handle the anomaly, which quietly becomes permanent.
Nightly syncs make this worse by formalising it. A nightly pipeline is a promise that your systems will agree tomorrow, made by a process that is allowed to fail overnight. When it fails, the failure mode is not an empty dashboard. It is yesterday’s dashboard, which looks exactly like a working one.
Reporting against live rows removes this mismatch by construction rather than by discipline: there is one as-of time, and it is the moment you pressed run.
Mismatch three: identity
The hardest one. Your CRM knows a customer as a company record. Your billing system knows a billing account. Your support tool knows a contact with an email address. Your store knows a guest checkout with no account at all.
Now count “customers who bought twice”. The answer depends entirely on which identity you counted, and every system will give you a defensible, different number. Email address as a join key fails on shared inboxes and address changes. Company name fails on “Acme Ltd” versus “Acme Limited”. The correct fix is a durable identifier issued once and carried everywhere, which is a decision about how records are created, not a transformation you can apply later.
Mismatch four: units, currency and timezone
Three small ones that behave identically. A system storing amounts in minor units next to one storing decimals differs by a factor of one hundred. Multi-currency figures summed without a stated conversion date produce a number that no exchange rate reproduces. Timestamps stored in UTC and bucketed into local days move every transaction near midnight into the adjacent day, which shifts month boundaries and makes month-end comparisons unreproducible.
Daylight saving deserves its own sentence, because it is the bug that ends trust in a recurring report. A schedule built by adding a fixed number of hours drifts by an hour twice a year, so the Tuesday 9am report becomes the Tuesday 8am report and the “today” window it covers is silently wrong.
| Mismatch | Symptom in the meeting | Where it is actually settled |
|---|---|---|
| Grain | Totals too high; filters move the total oddly | Table design and join keys |
| As-of time | Two dashboards, two answers, same question | Whether reports read live rows or copies |
| Identity | Customer counts differ by system | How records are created and linked |
| Units, currency, timezone | Month-end figures never reproduce | Storage format and conversion policy |
The failure case: a churn number that was never wrong
A twenty-person SaaS company reports monthly churn from a spreadsheet joining a CRM export, a Stripe export and a support export. Churn runs near 3% for eight months. In month nine it prints 11%, the leadership team spends two weeks on retention interventions, and the number returns to 3% the following month with no explanation.
What happened: the CRM export was taken on the 1st, the Stripe export on the 3rd. A batch of annual renewals processed on the 2nd. Those accounts were active in Stripe and not yet updated in the CRM, so the join dropped them, and dropped accounts were being counted as churned because the spreadsheet treated a missing match as a loss.
No query was wrong. No tool malfunctioned. The join had two as-of times and a silent null-handling rule, and a team burned two weeks on it. The fix was not a better dashboard; it was taking both extracts at the same moment, which is a thing the architecture permits or does not.
Why the BI tool cannot save you
A BI tool executes definitions. It has no access to the facts that would let it detect any of the four mismatches, because those facts live in the systems that produced the data. Semantic layers genuinely help with the fifth problem, definitional drift, by making “active customer” a single written definition. They do nothing about a customer identifier that means different things in two systems.
This is why reporting is downstream of architecture. Consolidating what counts as one record is the intervention; buying a visualisation layer over unconsolidated data is buying a faster way to produce disagreeing numbers. The same argument drives why one system of record beats five integrated ones.
What reporting on the system of record looks like
When the modules share one database, three of the four mismatches stop being problems rather than being solved.
Oneop’s reporting engine queries 33 datasets across eleven modules, with 68 reports that already exist before you build one. Reports read the same tables the application writes to, so there is no extract step and nothing can be six hours behind: an invoice raised two minutes ago is in the invoice report. A dashboard tile is a saved report pinned to a grid rather than a second query path, so a tile and the report it came from cannot disagree about a number. Schedules step through calendar dates in your chosen timezone rather than adding a fixed offset, which is the daylight-saving bug above, closed.
The request-to-query boundary is worth understanding if you care about correctness. A report configuration names dataset, dimension, measure and filter keys from a catalogue the server defines; the server owns every column, expression, join and predicate those keys turn into. The workspace filter is never part of the configuration, and it is re-applied to each joined table rather than trusted to travel through the relationship. Results cap at 5,000 rows, and a capped result says it was capped instead of being served as if complete.
The honest limit: this is operational reporting against live data, with up to two groupings and that row ceiling. It is not a modelling layer over years of historical snapshots. If you need that, you need a warehouse and should have one. The related argument about why software that acts on your data should sit in the same place is in why your AI agent should live inside your system of record, and the inventory version of the same consistency problem is in available to promise.
The question to ask before buying another dashboard
Ask where the number comes from, once, in full: which table, which timestamp column, which identity key, which currency, taken at what moment. If that sentence cannot be spoken about your most important metric, more reporting tooling will not help. Consolidating the records will. A shorter version of the same test, applied to AI claims rather than metrics, is in what is an AI-first customer platform.