In most businesses, getting two different numbers for the same question is almost the rule. Ask how many tonnes were shipped last month and sales gives one figure, production another, accounting a third. Nobody is doing anything wrong; everyone is looking at a different source with a different definition. Sales takes the dispatch note date, accounting the invoice date, production the date the goods left the line. On top of that come retrospective corrections: when an old record is updated in the ERP, last month's report quietly changes and the number discussed in the previous meeting can never be found again. Half the meeting is spent arguing about which figure is right, and the decision slips to the following week. After a while everyone starts keeping their own spreadsheet in their own way; the company no longer has a single number, but several separate truths that never check each other. The problem is not the reporting tool — it is that the layer beneath the report was never built at all.
The second problem is running reports directly against the live system. ERP and production databases are designed for transactions; they are fast at creating records, updating them and querying them one at a time. Run a report at month end that scans twelve months and the same database slows down, and the user on the shop floor feels it at the terminal screen. More importantly, a transactional database is not obliged to keep history: when a customer's trading name changes, the old invoices appear under the new name too; when a product's price group is updated, last year's analysis is calculated with today's group. Then there is the fact that everyone who writes a new report rediscovers the same table joins from scratch; three people do the same job three different ways and the results do not agree. Each of these three problems looks bearable on its own; together they destroy any trust in reporting and management decisions revert to gut feel.
A data warehouse (DWH) moves this work into a separate layer. Data is pulled from the source systems at regular intervals and held in a separate store in layers: the raw layer keeps the data exactly as it stands in the source, the processed layer cleans it and aligns it to shared definitions, and the presentation layer models it in the form the report will use directly. Transforming data before writing it is called ETL; writing it first and transforming it inside the warehouse is called ELT, and which one fits is chosen according to the structure of the source and the volume of data. None of the source systems is changed; the ERP, the production tracking system and the accounting software carry on doing their own job, while the warehouse sits alongside them and only on the read side. The real value of the warehouse lies in two things: it keeps history, so last month's number stays last month's number; and it fixes metric definitions in one place, so the word revenue means the same calculation in every report.
Let nothing on this page go unsaid: a data warehouse does not clean dirty data, it only makes it visible. A customer account opened three times in the source is three records in the warehouse as well; the answer to that is master data management, and it is a separate job. Nor is a warehouse real-time on its own; how often each piece of data is refreshed is a deliberate decision, because keeping every metric to the minute produces unnecessary cost. A warehouse does not replace dashboards either; the Data, BI and Reporting page, which covers dashboards, KPI screens and report distribution, sits on top of this layer and the two pages should be read together. And not every company needs one: if you have a single ERP and a handful of standard reports, building a warehouse brings more maintenance load than it solves. The right question is not should we build a data warehouse; it is how many sources, how much history and how many differing definitions we have to produce reports from. In discovery, we discuss that first.
