On Monday you compared sales in the North region with the previous quarter. Today the previous quarter shows a different figure. No invoices have changed. A customer has moved region and the report looks up their current account record.
A data warehouse gives you a place outside your operational systems to retain, combine and prepare data for analysis. The buying decision starts with what you'll need to reproduce. Your existing reporting model or a direct connection to the source may already be enough.
When a direct connection is enough
For today's open orders in one system, a direct query or a regular import into the reporting tool may be all you need. If the source already keeps the history you need and extraction works under normal load, another storage environment may add little value.
A warehouse gets easier to justify when several reports rebuild the same account mapping, when an old system is about to be retired or when tonight's import overwrites information you'll need next month. Include the ongoing work of monitoring loads, handling corrections and managing access in the comparison.
Decide whether the region follows the account or the sale
Suppose a customer buys SEK 30,000 in June while the North region owns the account. South takes over on 1 July. When someone runs the June report in August, where should the sale appear? Two readers can reasonably need different answers.
| Reader's question | Where the sale appears | Data required |
|---|---|---|
| What did each region sell while it owned the account? | June shows SEK 30,000 in North. | Region on the sale, or a dated history of account ownership. |
| What did South's current accounts buy? | June shows SEK 30,000 in South. | Current account ownership linked to past sales. |
Use snapshots for questions about a particular point in time
If management wants to know the order backlog every Friday, save a snapshot at an agreed cut-off. Compare one Friday with another. Adding all the Fridays into a monthly total would count a standing order more than once.
A snapshot can't show every change between two extracts. If you need to know how long an order spent at a particular stage, you'll need the times of the stage changes, for instance from a source event log. Agree what happens to a published report when a late correction arrives.
Test the smallest piece of warehouse that answers the question
Start with one report and one historical question your current setup can't answer. For a regional sales report, keep the fields and dates needed to attribute each sale to its region at the time. Define what one row represents, then ask a regional manager to check a real account that moved.
Test with the same input twice, a late correction and an interrupted load. The number of sales should hold steady on a rerun. Readers should be able to tell which version they're seeing after a correction, and someone should be told when fresh data is missing. That's a first delivery you can evaluate before connecting more sources.
Common questions
Do we have to replace our BI tool to build a data warehouse?
Usually you can keep it, provided it can read from the warehouse. Check which calculations and access controls already work. Move only the definitions or history that need to be shared and checked centrally.
What drives the cost of a data warehouse?
The source count alone tells you surprisingly little. How far history must go back, whether changes can be detected and which users need which detail all affect the work. Ask for implementation, ongoing storage and operational responsibilities to be itemised separately.