Every finance team eventually builds a report that runs against the live ERP database. It starts as a single query written by someone helpful in IT, it gets scheduled because people want it every morning, and within two years there are three hundred of them. Then month-end slows down, the warehouse scanning app times out during a stock count, and nobody can say which of the three hundred queries is responsible. Reporting from production is the most common analytics architecture in the mid-market, and it is not an architecture. It is a sequence of individually reasonable decisions that produces a system where the reporting workload and the transaction workload compete for the same resources, and the transactions lose.
Why it breaks, specifically
The two workloads are opposites. A transactional system is optimised for small reads and writes with strict consistency — post a journal, update a stock level, confirm an order. An analytical query wants to scan millions of rows across several years and multiple tables, aggregate them, and return a single number. Running the second against a database tuned for the first produces four distinct failure modes. Contention. Long-running analytical queries hold locks and consume I/O and memory that transactional work needs. Users experience this as intermittent slowness that nobody can reproduce, which is the worst kind of performance problem to diagnose. No history. Production systems hold current state. They tell you what a customer's credit limit is, not what it was in March. Any question about how something changed over time — which is most interesting business questions — cannot be answered from a transactional database, because it was never designed to remember. Structural hostility. ERP schemas are normalised for integrity and, in many packaged products, deliberately opaque: cryptic table names, codes instead of values, business logic embedded in application layers rather than in the data. Writing a correct report requires knowing which of four date fields means what, and that knowledge lives with two people. No reconciliation point. When two reports disagree — and they always disagree — there is no canonical definition to appeal to, because each query embeds its own filters and assumptions. Meetings then become arguments about whose number is right, which is the single most expensive symptom of this pattern.
What to build instead
The answer is separation: a dedicated analytical store fed from the ERP on a defined schedule, modelled for reporting, holding history, and carrying agreed definitions. The important thing is that this need not be a large programme. The version that works for most mid-market organisations is modest:
- A nightly or intraday extract of the tables that matter, not the whole database.
- A modelled layer that translates ERP structures into business language — customer, product, order, invoice, period — with codes resolved to names and dates disambiguated.
- Historical snapshots of the dimensions that change: customer attributes, product hierarchies, cost centres, employee records.
- A published set of metric definitions, each with an owner, so that "revenue" means one thing.
- Reporting tools pointed exclusively at this layer, with production access removed rather than discouraged. That last point is where most attempts fail. If the warehouse exists but production access remains available, people keep using production — because their old query still works and the new model does not have their field yet. The cut-over has to be enforced, and the transition needs someone responsible for closing the gaps quickly enough that the workaround stays unattractive. The second common failure is modelling the warehouse as a copy of the ERP. A replica of a normalised transactional schema is not easier to report from; it is the same problem on different hardware. The value comes from the translation layer, and skipping it saves the wrong cost.
| Problem | Reporting-layer response |
|---|---|
| Contention | Separate analytical workloads from transaction processing |
| Missing history | Capture historical snapshots of changing dimensions |
| Opaque ERP structures | Translate codes, fields and dates into a business-language model |
| Disagreeing metrics | Publish agreed definitions with named owners and reconciliation responsibility |
Qualitative summary of this article's source text, not a measured outcome or performance estimate.
Practical Guidance for Analytics Architecture Review
- Inventory what currently queries production and who owns each report. The list is always longer than expected and roughly a third of it is unused.
- Start with the ten reports that matter, not the three hundred that exist. Retiring reports is part of the project, not a follow-up phase.
- Model in business language with codes resolved and dates disambiguated. A warehouse that reproduces ERP table structures delivers almost no benefit.
- Snapshot slowly changing dimensions from day one. History you did not capture cannot be recovered later, and this is the most regretted omission.
- Publish metric definitions with named owners. The reconciliation problem is organisational; the warehouse only provides somewhere to put the answer.
- Remove production reporting access on a defined date. Optional migration is not migration.
- Match refresh frequency to the decision, not to ambition. Most management reporting needs daily data; real-time requirements are usually operational queries that belong in the application.
- Assign an owner for reconciliation between warehouse and ERP. Numbers will diverge, and the credibility of the platform depends on how quickly that gets explained.
The Regional Dimension
Several things make this harder — and more valuable — for Gulf organisations. Multi-entity structure is the first. A regional group typically runs several legal entities across mainland and free zone jurisdictions and multiple countries, frequently on separate ERP instances or at least separate company codes with divergent chart-of-accounts usage. Consolidated reporting therefore requires not just extraction but harmonisation: mapping different account structures to a group hierarchy, handling intercompany elimination, and dealing with entities that use different fiscal calendars or reporting currencies. That mapping is the real work, it is rarely documented, and it usually exists as a spreadsheet maintained by one person in group finance. Moving it into the warehouse as versioned, owned logic is often the single highest-value outcome of the whole exercise. Bilingual master data is the second, and it is specific to this region. Customer, supplier and employee names exist in Arabic and English, transliteration is inconsistent, and the same counterparty appears three times under different spellings. Reporting from production means those duplicates are invisible; a warehouse gives you somewhere to resolve them properly — ideally against identifiers rather than names, using trade licence number, tax registration number, establishment card or bank account details as the matching key. Name-based deduplication across scripts does not work reliably and should not be attempted as the primary method. Third, the regulatory reporting layer has grown quickly. VAT return preparation, Saudi e-invoicing clearance records, economic substance and country-by-country reporting for qualifying groups, wage protection system submissions, and Emiratisation or Saudisation ratio tracking all require period-accurate, auditable data pulled across entities. Assembling those from live production queries at deadline is where errors and late filings come from. These are exactly the reports that benefit from a stable, snapshotted, reconciled source. Two further notes. Data residency now constrains where the analytical store can sit — a group with Saudi entities may need the warehouse or at least the entity-level detail in-country, which argues for designing the platform so that country partitions can be separated later without a rebuild. And the integrator dependency that characterises regional ERP estates applies here too: if the partner who built your extracts holds the only understanding of the source mappings, you have replaced one single point of failure with another. Insist on documented lineage as a deliverable.
The objection worth taking seriously
The legitimate counter-argument is that a data warehouse is a large answer to a problem that sometimes has a small one. Modern ERP platforms — particularly in-memory and cloud-hosted ones — handle mixed workloads far better than the systems that made this rule necessary, and several offer read replicas, dedicated reporting nodes or embedded analytics that remove contention without a separate platform. For an organisation with one entity, a few hundred users and modest history requirements, standing up a warehouse can cost more than the problem it solves and introduces a new failure mode: a nightly pipeline that breaks at 3am and leaves everyone without reports. There is also a fair criticism of warehouse projects specifically. They have a poor completion record. The pattern is familiar: an eighteen-month programme to build a comprehensive model of everything, delivered after the business questions have changed, adopted by nobody because the original spreadsheet still works. The projects that succeed are narrow, iterative and driven by a small number of named reports with named owners. "Build the warehouse" is not a business case; "produce a consolidated group P&L that group finance trusts without a weekend of spreadsheet work" is. And a caution about what separation does not fix. A warehouse makes bad data faster to aggregate. If the underlying master data is duplicated, the cost centres are misassigned and the transaction coding is inconsistent, the reporting layer will present those errors with more authority than the spreadsheet ever did. The data quality work is a prerequisite, not a by-product — and it is the part with no technology answer.
Common Questions
How do we know it is time to stop reporting from production?
Three signals: performance complaints that correlate with reporting schedules, questions about history that cannot be answered, and two reports that disagree with no way to adjudicate. Any one justifies the conversation; all three make it urgent.
Is a read replica sufficient?
It solves contention, which is valuable and often the immediate pain. It does not give you history, business-language modelling or agreed definitions. Treat it as a fast fix that buys time, not as the destination.
How much history should we keep?
Enough for the comparisons people actually make — typically three to five years for financial trend analysis, plus whatever statutory retention applies in each jurisdiction you operate in. Capture dimension history from the start even if you do not need it yet, because it cannot be recreated.
Does AI change the case for a warehouse?
It strengthens it considerably. Natural-language querying, automated anomaly detection and forecasting all depend on a modelled layer with clear semantics — asking a model to interpret raw ERP tables with cryptic names and four ambiguous date fields produces confident, wrong answers. The organisations getting real value from conversational analytics are the ones that invested in the semantic layer first: defined metrics, resolved codes, documented lineage. There is also a governance dimension: an AI tool connected to your data needs the same access boundaries as a human analyst, and pointing one at production is worse than letting a person write a query, because it will generate more of them and faster. The practical sequence has not changed — separate the workload, model the data, define the metrics, and then add the AI layer on top of something that means what it says.
Analytics Architecture Review — the warehouse is worth building only if it translates ERP structures into business language and someone removes production access on a fixed date.