Data warehouse and business intelligence: why two reports never agree

When two reports on the same thing show different numbers, the problem is usually not that one of them is wrong. Both are right — each is counting something else.

Sales reports March revenue, finance reports a different figure. An hour of digging shows sales counts by order date and finance by invoice date, one includes VAT and the other does not, and cancellations are deducted in one place but not the other. Nobody lied. Nobody ever agreed what the word "revenue" means.

Why a tool does not fix this

This is the moment a BI tool usually gets bought. A dashboard goes up, it looks good, and a month later there are three dashboards contradicting each other exactly as three spreadsheets did before.

A tool solves presentation, not definition. While "revenue" means something different in every system, a handsome chart only spreads the discrepancy faster.

What a data warehouse does

A warehouse is a place where data from individual systems is stored in one agreed shape. Not a copy of everything — a selection of what decisions rest on, translated into a shared vocabulary.

It brings three things you cannot have directly on production databases:

• One definition. "Revenue" is written down once, including what counts towards it and what does not. A report does not reinvent it.
• History. A production system knows the state now. When a customer changes address, the old one is gone. A warehouse remembers how it looked then, so last year's report gives the same number a year later.
• Peace for the live system. A heavy analytical query does not run on the database customers are buying from.

The source of truth is a decision, not a technicality

Every field needs one system that owns it. The ERP owns price, the shop owns order state, the CRM owns contacts — or the other way round, it does not matter. What matters is that it is decided and written down.

It is the same decision that keeps integrations between systems sane; the warehouse is simply where its absence becomes visible. Without it, two branches of data never meet on the same number.

Start from the question, not the data

The most common mistake when building reporting is starting from what data happens to be available. The result is a dashboard full of charts nobody opens, because it answers nothing anyone actually needed to know.

The reverse order works better. Write down five questions you need answered when deciding — what it costs to win a customer, which products come back most often, where an order got stuck. What data you need, and in what shape, falls out of those. Everything else can be added later.

Data you can trust

A warehouse is useful exactly as long as usable data flows into it. So it comes with checks that run on their own: did the load finish, does the record count match expectations, have duplicates or missing mandatory fields appeared.

The point is catching an error on the way in. An error found in a report is expensive twice — once to fix, and once for the decision somebody made on it in the meantime.

How we do it

We design the data model and the warehouse so that data arriving from integrations is stored consistently and can be compared over time, and we name the source of truth for every field. Reports read straight from it, so numbers match across departments and nobody reconciles them by hand before the meeting.

If your reports disagree today and nobody knows why, get in touch — the gap can usually be traced faster than people expect. More about this area is on our services page.