Data warehouse for SMEs: when you need one and when not

What is a data warehouse, really?

A data warehouse is a separate database where you bring together, clean and store data from your various systems, specifically for reporting. Your accounting, CRM and time tracking keep doing their job. The data warehouse pulls data from them at fixed times and shapes it so you can build reports on it.

The term sounds big, but the principle is simple. The difference with an export to Excel is that it runs automatically, the logic lives in one place and you build up history. In our article what is a data foundation you can see how this fits into the bigger picture of sources, connections, a data layer and fixed definitions. In that picture, the data warehouse is the data layer.

When does an SME not need a data warehouse?

To be fair: plenty of companies would gain nothing from one right now. You can leave it for the time being if most of the following is true:

• You report from one system. All your key figures come from your accounting software, and its reporting or a direct Power BI connection does what you need.
• You mainly look at the current position. You have no need for year-on-year comparisons that the software cannot produce itself.
• There is one report and one user. The logic sits in that single report, and it is easy to oversee.
• The numbers are right and nobody argues about them. There are not two versions of revenue going around.

In that situation a direct connection between your software and your dashboard is often enough. How that works is explained in connect Power BI to your accounting. Adding a data warehouse would then mean extra maintenance without a clear gain.

When do you need a data warehouse?

The tipping point is rarely the volume of data. It is the questions you want answered. These are the signs we see most often:

1. You combine data from several systems. Margin per customer needs revenue from accounting, hours from planning and opportunities from your CRM. Each system identifies a customer differently. That data has to be merged somewhere, and separate reports are a poor place to do it.
2. You want history your software does not keep. Many systems only show the current state. How big was the pipeline three months ago? How many invoices were outstanding at the end of last quarter? If you do not record it yourself, it is gone.
3. Several reports use the same figures. Finance, sales and management all look at revenue. If the definition sits in each report separately, sooner or later you get three different numbers.
4. Your reports get slow or break with every change. When a dashboard has to fetch and recalculate all raw data on every refresh, you notice it in the speed. A change in a source system then breaks your report straight away.
5. You want to use the data for something else too. For example an automation or an AI application that needs to run on the same clean customer data as your reports.

If you recognise two or more of these, a central data layer is probably worth it.

How heavy should an SME data warehouse be?

This is where it often goes wrong. "Data warehouse" makes people think of a large project with expensive licences and an in-house IT department. Most SMEs do not need that. There are roughly three levels:

• Direct connection. The dashboard pulls data straight from the source. Quick to set up, but no history and the logic lives in the report.
• Light central database. A simple database that connections load your source data into, with a few fixed tables for customers, revenue and other core concepts. You build up history and the definitions live in one place. For many SMEs this is the right starting point.
• Full data warehouse. Several layers (raw, cleaned, report-ready), extensive checks and many sources. Worth it as your organisation grows, when you have many sources or strict requirements for traceability.

The good news is that the second level can grow into the third. You do not have to choose today for something you might need in five years. Start with one question and build from there.

An example: from three separate reports to one data layer

A fictional but recognisable example. A technical services company with around forty employees has three Power BI reports: one for finance on the accounting system, one for sales on the CRM and one for planning on the time tracking. Each report pulls its data directly from its own system.

That works, until management wants to know which customers are most profitable after hours. It turns out that customer numbers in the CRM do not match account numbers in accounting, and that time tracking uses projects rather than customers. Someone starts stitching it together in Excel.

The solution is not a big project, but a light central database:

1. Connections fetch customers, invoices, opportunities and hours from the three systems every night.
2. One mapping table records which CRM account belongs to which accounting customer and which projects.
3. Revenue, hours and margin are defined once, in the database.
4. The three existing reports are switched to this single source, and a fourth report is added with margin per customer.

There is still no enormous data warehouse afterwards, but there is one place where the numbers add up and history is being built. How to set up such a project in four weeks is described in from Excel to a data foundation in 30 days.

What mistakes do SMEs often make with a data warehouse?

• Starting with the technology. Picking a platform first and then working out what to do with it. Start with the question you want answered; the technology follows from that.
• Loading everything at once. Fetching every table from every system "just in case". That makes it slow, costly to maintain and hard to follow. Fetch what you need.
• Skipping definitions. A data warehouse with three versions of revenue solves nothing. Agreeing what a number means matters as much as the technology.
• No owner and no monitoring. Connections can stop when a source system changes something. Agree who gets alerted and who fixes it.

How do you start if you think you need one?

Write down the one or two questions that currently take the most time or cause the most debate. Add which systems the data comes from and whether you need history. With that list you can quickly judge which level fits.

Building the connections and the central database is what we mean by data engineering. For the reports on top, see dashboarding, and the systems we can already connect are listed under integrations. How a project runs in phases is described in our approach.