What is a data warehouse and do you need one?

A data warehouse is a central database in which data from your source systems, such as ERP, CRM and accounting, is collected every night, cleaned and kept with its history, so that every report draws on the same figures. For a mid-sized company it usually becomes necessary from three or four source systems onwards, or as soon as Power BI on its own gets too slow, too large or too hard to keep straight.

That last sentence is missing from almost every explanation of the term, and that is no accident. The explanations mostly come from the companies that sell the storage, and they have little interest in telling you that you do not need it yet.

Yet for many organisations of fifty to five hundred people, that is the honest answer. So below comes first what a data warehouse is and how the architecture works, then how it relates to a data lake and a lakehouse, and after that the question that actually matters: which signals tell you it is time, and what setting one up then costs.

What is a data warehouse?

The meaning is in the name: a warehouse for data. Except it is not a warehouse where you put things to be rid of them, but one where you put them to find them again, in the same form, three years from now. The data warehouse pulls data from the systems where the work happens, converts it to one structure with one set of definitions, and keeps every version.

The difference from an ordinary database is not the technology but the purpose. The database under your ERP is built to find and change one order quickly. A data warehouse is built to add up three years of orders in one go, per customer group, per month, without anyone in the ERP noticing. The concept was described in the late 1980s by Bill Inmon and comes down to four properties that still hold:

• Subject-oriented. The data is organised by the subjects you steer on, such as customers, orders and products, not by the application it came from.
• Integrated. A customer from the CRM and a debtor from the accounting system are the same customer in the data warehouse, with the same number.
• Time-variant. Every value belongs to a period. What stock was on 31 March stays retrievable, even when the source system only knows today’s position.
• Non-volatile. What is in it is not overwritten. A new version is added.

Data warehousing, for the record, is the verb: the process of collecting, converting and loading.

What is a data warehouse used for?

For steering information, first of all. Business intelligence and the data warehouse are about the same age and arose for the same reason: the board’s question could not be answered from one system. In practice a data warehouse delivers three things you do not get without one.

First, a single source of truth. The definition of revenue sits once in the data warehouse and every report, every pivot table and every export uses it. Without a data warehouse that definition sits in every Power BI file again, slightly differently, and the monthly meeting is about which figure is right instead of what to do about it.

Second, history. Most source systems only know the present. Ask what the pipeline looked like three quarters ago and the CRM has no answer, because those deals have since been won, lost or moved. The data warehouse keeps each day’s position, and that is exactly what you need to see whether anything is improving.

Third, peace in the source systems. A heavy reporting query straight on the ERP database slows down the colleagues entering orders at that moment. In the data warehouse a query may take a minute, because nobody is waiting for it at the counter.

That also means a dashboard without this layer underneath does not keep for long. It looks fine the first month, and then it drifts from the accounts, the history drops away or margin in the sales report turns out to be calculated differently from the finance one. The foundation decides how long the screen above it holds its value, not the design.

How does a data warehouse work? The architecture in layers

Almost every data warehouse architecture consists of the same layers, whatever the vendor calls them. From source to screen:

1. The source systems. ERP, CRM, accounting, time registration, web shop, HR system. In mid-sized companies often AFAS, Exact Online, Dynamics 365 Business Central, HubSpot or an industry package. How to connect them is covered in our articles on AFAS and Power BI and Exact Online and Power BI.
2. The loading layer. Here data is fetched, usually once a night, and written away raw. That is ETL: extract, transform, load. In the cloud the order has often become ELT: load raw first, transform afterwards, because transforming inside the data warehouse is faster and cheaper than before it.
3. The history layer. Every change in the source is kept as a new version. This is the layer that can answer “what did it look like on 31 March”.
4. The data marts. One star schema per subject: one fact table with the figures, such as order lines, and dimension tables around it with customers, products, dates and employees. This is the shape Power BI works on fastest and most predictably, and Microsoft explains in its own guidance why a star schema is the norm.
5. The semantic model and the reports. On top of the data mart sits a semantic model with the measures and definitions, and on that the dashboards. From here on, the user sees it.

The whole process runs at night. That makes the data warehouse the one colleague who updates all the figures at three in the morning and does not complain about it the next day.

What you do not see in this list is the choice that matters most: how you model. Kimball with star schemas, Inmon with a normalised model, Data Vault for large landscapes with a lot of change. For mid-sized companies the answer is almost always Kimball: star schemas per subject, because Power BI handles them best and because the next developer can understand them.

Data warehouse, data lake, lakehouse and data mart: what is the difference?

The terms get used interchangeably, while they are four different things:

Data warehouse, data lake, lakehouse and data mart compared on contents, users and when to choose each

	What is in it	For whom	When
Data warehouse	Structured, cleaned tables with fixed definitions and history	Reporting and BI for the whole organisation	As soon as several sources and several reports have to share the same figures
Data lake	Raw files in any format: exports, log files, documents, sensor data	Data analysts and scientists who add structure themselves	When you want to keep data whose use you do not know yet
Lakehouse	Storage like a data lake, with tables, transactions and governance like a data warehouse on top	Organisations that want reporting and analysis on one platform	When you need both and do not want to maintain two environments
Data mart	A part of the data warehouse for one department or subject	One team, such as finance or sales	Always, as the top layer of a data warehouse or lakehouse

For most mid-sized companies the choice is simpler than the table suggests. There is rarely unstructured data in quantities that justify a data lake. What there is, is five systems with tables that do not talk to each other. That is a data warehouse question, and the medallion architecture with its bronze, silver and gold layers that you meet in Fabric is nothing but the three layers above under a new name.

Cloud data warehouse at Microsoft: Azure, Fabric and Power BI

Ten years ago a data warehouse sat on a server in the basement, with a licence and an administrator. That still happens, especially where data may not leave the building, but for mid-sized companies the cloud has become the default. Not because it is fashionable, but because you pay for what you use and nobody has to patch anything.

In the Microsoft landscape there are three routes to a cloud data warehouse, and they fit three sizes:

• Azure SQL Database. An ordinary relational database in Azure, with the layers from the previous section as schemas inside it. Cheap, familiar ground for any developer, and for most organisations ample up to a few hundred gigabytes. This is the classic mid-market data warehouse.
• Fabric Warehouse or Lakehouse. Microsoft Fabric bundles storage, processing and Power BI on one capacity, with OneLake as shared storage. The warehouse simply speaks SQL and Microsoft describes how it relates to the lakehouse within the same platform. This is what is now called a modern data warehouse: storage and compute separated, and reporting straight on top. What such a capacity costs and when it pays for itself is in our article on Power BI licensing.
• Power BI without a data warehouse. For small landscapes Power BI can load, transform and combine the sources itself. That is not a data warehouse, but for an organisation with two sources and one developer it does the same job for a while.

With all three, the name on the invoice matters more than the technology. A data warehouse that runs in your BI partner’s Azure tenant belongs to your partner. Where it sits and in whose name is exactly the subject of our piece on data sovereignty.

Does a mid-sized company need a data warehouse?

Not from day one. The first dashboard is usually built straight on the sources, and that is sensible: you do not yet know which figures matter. The trouble starts six months later. By then there are four reports, built by two people, and margin is calculated slightly differently in each of them. The pipeline history is gone, because the CRM overwrites a deal’s stage. And the refresh now takes forty-five minutes, because every report fetches all the sources itself.

Sound familiar? Then this is the list to test against. Two or more of these signals means a data warehouse pays for itself:

• Three or more source systems that come together in one report, with customers or products that have a different number in each system.
• The same logic in several reports. As soon as the margin calculation sits in two Power BI files, they drift apart. Not maybe, but certainly.
• History that is overwritten in the source. Stock levels, deal stages, headcount: if you want to know how it was last quarter, someone has to record it, and that is the data warehouse.
• A model that gets too large or too slow. Above the limit of a Pro licence, or with a refresh that no longer fits its window, the cheapest fix is not a heavier licence but moving the work to a layer underneath.
• More than one builder. Two developers without a shared layer build two truths. A data warehouse is the place where they have to agree.
• Questions that do not come from one system. Revenue per employee, margin per project, absence against capacity: each of those needs two sources that have to find each other on one key.

What is not a reason: the wish to be “future-proof”, an offer from a platform vendor, or the fact that a bigger competitor has one. A data warehouse without a concrete question behind it is a moving box nobody unpacks.

Setting up a data warehouse: steps, costs and who builds it

Think big and start small. You draw the architecture from the previous sections once in full, and then build it one subject at a time, starting with the subject that hurts most. In four steps:

1. Choose one steering question and two or three sources. Usually revenue and margin, from the accounts and the ERP. Not the whole landscape. Which question comes first belongs in your data strategy; if it is not there, this is the moment to put it on one page after all.
2. Fix the definitions before you load. What is revenue, when does an order count, which source leads when two systems disagree. This is an afternoon with finance and it prevents months of rework.
3. Build the first data mart and the first screen together. One star schema, one semantic model, one dashboard that gets used in the next monthly meeting. What such a first screen looks like and what it costs is in our article on getting a Power BI dashboard built.
4. Add one subject per quarter. Sales, operations, HR: each on the same data warehouse, with the same customer and date dimensions. That is the moment the investment pays back, because the second data mart costs a fraction of the first.

The costs fall into two parts, and they relate differently from what most people expect. For a mid-sized company the cloud costs are the smaller part: an Azure SQL Database or a small Fabric capacity costs tens to a few hundred euros a month. The larger part is building and maintaining: the connections, the model, the definitions and checking that last night’s load actually succeeded.

Who builds it decides whether it is still worth anything to you in three years. An in-house data warehouse developer or data engineer is, for most mid-sized companies, too expensive a permanent hire for too little work. A freelance data warehouse specialist is fast and good, as long as the knowledge does not leave with the assignment. A data warehouse consultant or BI partner brings the experience of ten landscapes, but should then build in your tenant, with documentation a next party can read. The question to ask is not who builds it cheapest, but who builds it so that you do not need them to understand it.

---

Do your figures sit in one data warehouse that every report draws from, or in five Power BI files that each calculate their own truth? Want to know whether a data warehouse already pays for itself in your situation? Get in touch and we will go through the signals above together.

Frequently asked questions