Data & BI

Data warehouse: does your business need one?

What a data warehouse is, how it differs from a database or a data lake, the signs a small business needs one, a lean set-up and what drives its running costs.

Your monthly report takes three days. Sales come from the customer relationship management (CRM) tool, invoices from the accounting software, stock from the enterprise resource planning (ERP) system, and someone glues it all together in Excel. When two people present the same figure, they rarely agree.

This is usually when someone mentions a data warehouse for small business. Is it a real answer or an enterprise toy? Below, we explain what a data warehouse is and is not, the signs you need one, a lean set-up for a small or medium-sized enterprise (SME), the basics of history, and what drives the running costs.

What is a data warehouse?

A data warehouse is a database built for one job: answering questions about your business. It collects copies of the data from your operational tools, cleans them, gives them a shared shape and keeps their history. Reports and dashboards then read from that one place instead of from each tool.

The key word is copies. Your CRM, ERP and accounting software keep doing their work. The warehouse sits beside them and is refreshed on a schedule, often every night.

Data warehouse vs database

Every application already has a database. It is designed to record transactions quickly and safely: one order, one invoice, one change of address. It usually keeps only the current state, and its structure follows the application’s screens, not your questions.

A warehouse is designed to read large volumes and to compare: this quarter against last, one region against another. It joins data from several tools and remembers what things looked like in the past.

Data warehouse vs data lake

A data lake stores raw files of any kind as they arrive: exports, logs, documents. It is cheap to fill, but someone has to make sense of the files each time they are used. A warehouse stores checked, structured data with agreed definitions. For most SMEs, whose questions are about sales, margins, stock and cash, the warehouse is the useful half.

And the Excel extract?

The extract is where most businesses start, and it is not a mistake. Its limits appear with growth: manual steps only one person knows, formulas that break when a column moves, no history beyond the files someone kept, and several versions of the truth.

Application database Excel extract Data lake Data warehouse
Purpose Run daily operations Ad hoc analysis Store raw data Reporting and analysis
Data from One application A few exports Many sources, raw Many sources, cleaned
History Current state only Files that were kept Whatever was stored Designed in
Definitions Per application Per person None by default Agreed and documented

Decision checklist: do you need a data warehouse?

Tick the statements that are true for your business today.

  • Your key reports combine data from three or more tools.
  • Someone spends days each month exporting, pasting and checking figures.
  • Two departments give different numbers for the same indicator.
  • You cannot answer a question about the past, because your tools keep only the current state.
  • Heavy reports slow down a tool while people are working in it.
  • You are about to replace a tool and want to keep its history.
  • Management wants a dashboard with definitions everyone accepts.

How to read it: none or one ticked, a warehouse is probably not worth it yet. Two or three, start small with one subject, such as sales. Four or more, a warehouse is likely the right investment, provided someone owns it.

When not to build one: if most of your activity lives in one application, its own reports may already answer your daily questions. In a project led by our founder, Anass Chabbab, in his previous roles, a Sydney law firm received a custom CRM/ERP, used by more than 100 people, that cut processing time by 40%. When your data lives in one well-designed system, improve that system first. A warehouse earns its place when your questions cross several tools.

A lean data warehouse for small business

An SME needs neither a large platform nor a dedicated team. A lean set-up has four parts, and each can stay modest.

1. Sources

List the tools that feed your ten most important reports, and nothing more. For each one, note how its data can be read: an application programming interface (API), a database connection or a scheduled export. A tool with none of these is a risk to know about before you start.

2. Loading

The data is copied on a schedule. You will hear of ETL (extract, transform, load) and ELT (extract, load, then transform inside the warehouse). For an SME, ELT is often simpler: copy first, then shape the data with readable Structured Query Language (SQL) scripts kept under version control. Ready-made connectors exist for many common tools; a small custom script covers the rest.

3. The model

This is where the value lies: turning raw tables into clear subjects such as orders, customers and products, with one agreed definition of each indicator.

4. Reports

Your dashboards and business intelligence (BI) tool read from the warehouse. To choose that tool, see our criteria for choosing a BI tool; to decide what management should see, see the KPIs of an executive dashboard.

Start with the subject that hurts most, prove that the figures match what finance trusts, then add the next. The warehouse can run on a cloud database service or on a server you already manage; if other tools are moving too, our cloud migration plan helps you sequence both. And from day one, copy only the personal data your reports really need. Our data and BI expertise page shows how we approach these projects.

History and the data model, in plain terms

Two ideas make a warehouse more useful than a pile of extracts.

Facts and dimensions

Most SME warehouses use a simple shape called a star schema. In the middle sits a fact table: one line per event you measure, such as an order line, with quantities and amounts. Around it sit dimensions: who (customer), what (product), when (date), where (branch). Reports then slice the facts by any dimension, and a finance team can follow the logic.

Keeping history

Operational tools often overwrite. When a customer is moved to another region, the old region disappears, and last year’s regional sales change with it. A warehouse can keep both versions, each with its dates of validity, and take monthly snapshots of things like stock or unpaid invoices. Decide early which subjects need this: history that was never captured cannot be rebuilt.

Illustrative example (fictitious figures): a distributor with 20 sales representatives reassigns 200 customers between regions in January. Without history, the previous year’s regional sales change overnight. With history, they stay as reported, and the change shows from January.

What drives the running costs

We do not quote platform prices: they change often and depend on your volumes. The cost drivers, however, are stable.

  • Compute: most cloud warehouses charge for the processing used by loads and queries. Frequent refreshes and dashboards that recalculate everything cost the most.
  • Storage: usually the smallest item for an SME, even with years of history.
  • Connectors: ready-made ones are often priced by volume or number of sources; custom scripts cost time instead.
  • BI licences: often priced per user who builds or reads reports.
  • People: someone watches the loads, answers questions and adjusts the model when a tool changes. This is often the largest cost, and the most often forgotten.

Three habits keep costs in check: refresh only as often as decisions need, archive what nobody reads, and review query consumption every month.

Frequently asked questions

Is a data warehouse the same as a BI tool?

No. The warehouse stores and prepares the data; the BI tool displays it in reports and dashboards. You can use a BI tool without a warehouse, reading directly from a few sources, and that is often fine at the start. The warehouse becomes useful when sources multiply and definitions must be shared. Our guide to choosing a BI tool covers the display side.

Does a data warehouse have to be in the cloud?

No. It is a database designed for analysis, and it can run on a server you already manage. Cloud services make it easier to start small and grow, and they remove hardware maintenance. The right choice depends on where your other tools are, any constraints on where your data must be stored, and your team’s skills.

Who maintains a data warehouse once it is built?

Someone must own it: check the nightly loads, fix a broken connector and update the model when a tool changes. In an SME, this can be someone in finance or operations, with technical support from inside or outside. Without an owner, a warehouse slowly drifts from reality, and people return to their spreadsheets.

In short

A data warehouse collects copies of your tools’ data, cleans them, gives them shared definitions and keeps their history, so that reports read from one trusted place. It is not needed when one application holds most of your activity, but it pays off when key reports cross several tools and take days to build. Start lean, with one subject, a star schema and the history you will need. Watch compute, connectors, licences and, above all, the time of the person who owns it.

Are your monthly reports still assembled by hand from several tools? Book a free 30-minute discovery call: we look at your sources and reports together, and tell you honestly whether a warehouse is worth it now.

Related servicesData & BI

Have a project in mind?

Let’s talk about it on a 30-minute discovery call, free and with no obligation.

Book a discovery call

Further reading

All articles