What a Data Warehouse Is Really For

Finance reports recognised revenue. Sales reports the value of orders booked. Operations reports the orders that have been fulfilled.

All three numbers may be correct. The trouble starts when a leadership meeting treats them as though they describe the same thing.

The same problem appears outside sales: a lender may have competing definitions of an account in arrears, an insurer may count claims differently across operational and regulatory reporting, and a support organisation may disagree about when a case is resolved.

This kind of disagreement can look like a dashboard problem. Sometimes it is. More often, the dashboards are exposing differences that already exist underneath them: separate source systems, different definitions, inconsistent time periods, or business rules recreated independently by several teams.

A data warehouse can help with those problems, but not simply because it puts more data in one place.

The more useful question is what analytical context the organisation needs to create, preserve, and operate.

The Reporting Problem Is Rarely Just a Reporting Problem

Suppose several analysts need monthly revenue by customer and product.

One starts with invoices because finance considers revenue recognised when an invoice is issued. Another starts with sales orders because the commercial team wants to understand demand. A third excludes cancelled and returned lines using logic maintained in a spreadsheet.

They may also answer the same underlying questions differently:

Moving the three reports into one tool won’t settle those questions. Nor will placing the source tables in one database.

The organisation needs agreed definitions, traceable rules, an appropriate treatment of history, and a dependable way to apply those decisions repeatedly.

What a Data Warehouse Contributes

At a practical level, a data warehouse is an analytical data store designed to bring selected organisational data together, apply controlled definitions and quality rules, retain useful historical context, and make the result suitable for analysis.

That description contains several distinct jobs.

These are intended outcomes, not features that appear automatically when warehouse technology is installed.

A warehouse can contain perfectly structured tables and still fail if nobody owns the definitions, important transformations can’t be explained, or users don’t trust the result.

Why Not Query Operational Systems Directly?

Operational systems are built to run business processes. They record orders, update customer accounts, allocate stock, approve claims, and perform many other small and frequent transactions.

Analytical work asks different questions. It may scan several years of activity, combine multiple processes, group results by several dimensions, or compare what happened under definitions that have since changed.

This isn’t an absolute technical divide. Modern databases can support mixed workloads, and a small organisation may report safely from a well-designed operational source.

The distinction becomes more important as the analytical questions, data volumes, historical requirements, users, and source systems grow.

Direct access also transfers complexity to every consumer. Analysts need to know which tables to join, which records to exclude, which date to use, and which calculation reflects the accepted business meaning. If five reports each solve that problem independently, the organisation now has five implementations to test and maintain.

The Warehouse Sits Inside a Wider System

The warehouse doesn’t replace the systems that operate the business, and it isn’t necessarily the interface report authors use directly.

Operational systems continue to record business activity. Relevant data is then integrated, validated, historised where necessary, and organised in the warehouse. A presentation or semantic layer makes the result easier for reports, analysts, and planning teams to use consistently.

Several related structures may appear around it.

Data marts

A data mart concentrates analytical data on a particular subject area, business process, department, or group of users. It may be supplied from an enterprise warehouse, or it may have been developed more independently.

The distinction is important. A focused mart can deliver useful results quickly, but several independently defined marts can reproduce the integration problem at a different level.

Data lakes

A data lake stores structured, semi-structured, and unstructured data, often retaining it closer to its source representation. This might include database extracts, CSV or JSON files, application logs, documents, images, audio, and video.

It can support data engineering, data science, archival, and exploratory needs that don’t fit naturally into a relational warehouse.

The lake and warehouse are not necessarily competing destinations. Lake storage may sit upstream of curated warehouse structures or serve other workloads alongside them.

Presentation and semantic layers

A warehouse can be technically sound and still be awkward for a report author or analyst to use directly. Another layer can present readable names, agreed measures, useful hierarchies, relationships, formatting, and access behaviour over the curated data.

This responsibility is often called a presentation layer or semantic layer. Modern platforms commonly use the term semantic model. Microsoft renamed Power BI datasets to semantic models in 2023, but the broader architectural idea predates that terminology change.

Readers may also encounter names such as OLAP cube, multidimensional model, presentation server, or vendor-specific business layer. These describe earlier or product-specific ways of performing some of the same work; recognising their purpose is more important here than memorising the terminology.

The warehouse and semantic model still serve different purposes. The warehouse organises and retains trustworthy analytical data. The semantic model builds on that foundation for a particular reporting or analytical experience.

Microsoft’s OLAP architecture guidance still describes this traditional flow from source data to warehouse, semantic layer, reporting, and analysis.

What the Warehouse Does Not Solve by Itself

It’s easy to draw a clean architecture in which data moves from several systems into one trusted analytical store. The difficult parts are often hidden inside the arrows.

A warehouse doesn’t automatically:

These limitations don’t make the warehouse less useful. They help define the work required to make it useful.

The Microsoft modern data warehouse guidance describes ingestion, transformation, modelling, serving, orchestration, security, and governance as connected responsibilities. The exact implementation will vary, but treating the warehouse as an isolated database leaves important parts of the service unplanned.

When a Warehouse May Be Justified

Several symptoms can suggest that a more deliberate analytical foundation is needed:

None of these creates an automatic business case, but they are evidence to investigate.

A credible proposal still needs to identify the affected decisions, users, processes, risks, expected improvement, delivery cost, and operating responsibility.

When It May Be Premature

Some organisations need a smaller intervention.

One stable source may already support the required analysis safely. A modest reporting database or governed semantic model may address the immediate problem without introducing an enterprise warehouse. A warehouse programme may also be premature when the organisation has not agreed which decisions matter or who owns the resulting definitions.

The warning signs include proposals that begin with a preferred platform, promise to bring in all available data, and leave the first useful business outcome undefined.

This isn’t an argument for underinvestment. It’s about matching the solution to the problem and the organisation’s ability to operate it.

A Better First Conversation

Before discussing warehouse products or table designs, I would want to ask:

Those questions won’t produce a complete architecture, but they’ll establish whether there’s a useful problem to solve.

The next article takes that problem into planning. It looks at requirements, business processes, ownership, scope, and the different emphases associated with Inmon and Kimball before any team starts designing tables.