“Data warehouse” sounds like something for banks and airlines. For many growing New Zealand businesses, it’s simply the answer to a familiar problem: the reports you need draw on several systems, and getting them out takes far longer than it should.

This guide explains what a data warehouse is, how to tell whether you need one, and what a sensible first version looks like for a business your size.

Signs you’ve outgrown reporting from each system

Most businesses start by reporting straight out of each system: sales from the inventory system, profit from Xero, hours from payroll. That works until it doesn’t. These are the usual signs:

  • Key reports pull from several systems, and someone stitches them together in Excel every month.
  • The numbers don’t reconcile. The sales report says one thing and the accounts say another, and nobody is sure which to trust.
  • A spreadsheet is the glue, and only one person really understands it.
  • Reports are slow, or running them slows down the system your team is working in.
  • History disappears. When you change systems, rename product codes or restructure your customer list, last year’s figures no longer line up with this year’s.
  • Good questions go unasked, because answering them would take a week of someone’s time.

If two or three of these sound familiar, a data warehouse is worth a serious look.

What a data warehouse actually is

A data warehouse is a separate database that holds a copy of the data from your business systems, cleaned up and organised for reporting. Three ideas matter.

It copies, it doesn’t replace. Your systems keep running exactly as they are. The warehouse reads from them on a schedule, so nobody changes how they work day to day.

It holds agreed definitions. “Revenue”, “gross margin” and “active customer” are defined once, in one place, so every report uses the same numbers.

It keeps history. The warehouse can keep snapshots over time, so you can compare this year with last year even after a system change or a restructure.

Your reports, and increasingly your AI tools, then work from one organised source rather than from five separate systems. That matters for AI too: a tool reading tidy, defined data has a much better chance of giving the right answer than one reading the raw tables of several systems.

What right-sized looks like for a growing business

A warehouse for a business with tens of staff is not a multi-year programme. A sensible first version usually has:

  • A database in your own Azure tenant, such as Azure SQL Database or a Microsoft Fabric warehouse. Azure SQL’s serverless tier, for example, scales compute with demand and pauses automatically when idle, when only storage is billed.
  • Scheduled loads from each system’s API, nightly or more often if the business needs it.
  • Two layers of tables: staging tables that mirror each source system, and clean tables for the things you report on, such as customers, products, sales, stock and jobs.
  • A handful of reports built on the clean tables, answering the questions that matter most.
  • Monitoring that tells a named person when a load fails, before anyone opens a report with stale figures.

Running costs depend on how much data you hold and how often you refresh it. Ask for an estimate based on your own volumes, and make sure everything runs in your own Azure account so you can see the bill and keep control.

Mistakes that sink warehouse projects

Most warehouse projects that disappoint fail for the same few reasons:

  • Trying to load everything. Bringing in every table from every system before answering a single question burns budget and goodwill.
  • No owner in the business. Someone outside IT needs to own the definitions and decide what “margin” means.
  • No agreed definitions. Three versions of the same measure in three reports is exactly the problem a warehouse should solve.
  • Copying the mess. Loading duplicate customers and inconsistent product codes without fixing them just moves the problem.
  • Building it somewhere you don’t control, such as a contractor’s own cloud account.
  • Treating it as finished. Your systems will change, and the warehouse needs care when they do.

Your first 90 days

A warehouse should prove its worth quickly. This is the plan we follow:

  1. Pick three questions that matter. For example: margin by customer and product, cash tied up in stock, and how debtors are trending. Write them down.
  2. List the systems that hold the answers, and only those systems.
  3. Agree the definitions for every measure involved, and name the person in the business who owns them.
  4. Set up the database in your own tenant, with access controlled through your existing Microsoft 365 accounts.
  5. Build the loads for those systems only, and keep a log of every run.
  6. Reconcile against the source. Sales for the month should match Xero to the cent before anyone builds a report on them.
  7. Build the reports for your three questions.
  8. Run them alongside the existing spreadsheets for a month, and investigate every difference.
  9. Retire the spreadsheets once the numbers match and people trust them.
  10. Pick the next three questions, and repeat.

By the end of the first 90 days you should have three questions answered from one trusted source, a pattern for adding more, and a clear sense of whether the investment is paying off.

Where to start

Start with the report that takes longest to produce each month, and write down every system it draws on. That’s usually the best place for a warehouse to begin. If you’d like help scoping a first version, see our data platforms and insight and reporting work, or get in touch.