Data Warehouse
A data warehouse is a central, analytics-optimized database that cleans, historizes and consolidates data from multiple source systems such as ERP, shop or CRM in a subject-oriented way – serving as a reliable foundation for reporting and business intelligence.
A data warehouse (DWH) is a central, analytics-oriented database that extracts, cleans, unifies and permanently stores data from several operational source systems – such as ERP, online shop, CRM or financial accounting. Unlike an operational database, which is designed for the fast capture and modification of individual transactions, the data warehouse serves to read and aggregate large volumes of data over long periods. It is therefore the shared, historized foundation on which reports, dashboards and business intelligence analyses are built.
The term goes back to Bill Inmon, who defined a data warehouse as a subject-oriented, integrated, time-variant and non-volatile collection of data. These four properties describe its core: the data is organized by business subjects such as customer, product or revenue, consolidated across systems, given a timestamp and not overwritten but carried forward historically. This creates a consistent "single source of truth" that every department can rely on.
At a glance
- A central, analytics-optimized database rather than day-to-day operations
- Consolidates cleaned data from ERP, shop, CRM and other systems
- Historizes data: past values are retained and remain analyzable
- Loaded via ETL/ELT and used through OLAP, reporting and dashboards
- Relieves the operational ERP and enables analysis across system boundaries
How does a data warehouse work?
A data warehouse sits at the intersection between the operational systems and the analytics tools. Data is extracted from the source systems at regular intervals – often nightly or in short cycles – transformed and loaded. This process is known as ETL (Extract, Transform, Load); in modern cloud architectures, data is frequently loaded first and transformed afterwards (ELT). Within the warehouse itself, the data resides in a model optimized for read access and aggregation, which reporting, OLAP and dashboard tools query.
The layers of a DWH architecture
A typical data warehouse is built in multiple layers. In a staging layer, the raw data from the source systems first lands unchanged. From there it is cleaned, deduplicated and moved into a consolidated core model that forms the integrated, historized truth. On this basis, subject-specific data marts are created – smaller extracts for individual areas such as sales or purchasing. This separation keeps analyses fast and consistent without burdening the productive systems.
Star schema, facts and dimensions
For analysis, data in the warehouse is usually modeled dimensionally, classically as a star schema. At the center sits a fact table with the measurable quantities – for example revenue, quantity or contribution margin per sales transaction. Around it are grouped dimension tables with the descriptive attributes such as time, product, customer or region. This structure makes it possible to filter metrics flexibly along any dimension and to navigate by drill-down from the grand total down to the individual line item.
Why a data warehouse matters
The value of a data warehouse lies in consistency, history and relief. If analyses are run directly on the operational systems, compute-intensive queries compete with day-to-day operations and slow them down. In addition, each system delivers its own figures, often with differing definitions – the warehouse brings them together in a coordinated model and creates a solid factual basis.
Historization is decisive: operational systems overwrite values as soon as, for instance, a price, a customer address or a stock level changes. A data warehouse preserves each respective state with a time reference and can therefore trace developments over months and years. Only this makes it possible to reliably analyze trends, seasonality and year-over-year comparisons. A prerequisite for meaningful results is high data quality – faulty or inconsistent source data leads to wrong metrics in the warehouse as well.
The data warehouse and the ERP system
For most mid-sized companies, the ERP system is the most important data source for a data warehouse, because orders, invoices, stock, purchases and master data all come together there. The connection is usually made via an API or an ETL connector that transfers the relevant transaction and master data into the warehouse.
Many modern ERP systems already come with built-in reporting and dashboard features that deliver standard metrics straight from live operations – for many everyday questions, that is enough. A dedicated data warehouse becomes worthwhile as soon as several sources need to be consolidated, such as ERP, online shop and marketplace settlements, or when large histories and complex models are to be analyzed. The warehouse then becomes the cross-system analytics layer, while the ERP remains responsible for operational business.
Distinction: data warehouse, data lake and data mart
The terms around central data storage overlap but mean different things. A data warehouse stores structured, already prepared and modeled data – the schema is fixed before loading (schema-on-write). It is designed for defined, recurring analyses.
Data lake and lakehouse
A data lake, by contrast, stores large volumes of raw, even unstructured data in its original format and defines the structure only at analysis time (schema-on-read). It is more flexible and cheaper for mass data, but requires more preparation at the analysis step. Modern "lakehouse" approaches combine both worlds. A data mart, in turn, is not a counterpart but a thematic extract of a warehouse for a single business area. And while OLAP and reporting describe the analysis, the data warehouse is the underlying storage and integration layer.
The data warehouse in DACH mid-market companies
For a long time, the data warehouse was seen as a large-enterprise topic with high infrastructure and project effort. However, cloud-based warehouse services and BI tools integrated into ERP systems have significantly lowered the entry barrier, so that today even smaller retail and manufacturing businesses in the DACH region operate a warehouse – often as a lean cloud solution without their own servers.
Data protection must be considered: as soon as personal data such as customer or employee data is stored in the warehouse in historized form, the requirements of the GDPR apply – in particular purpose limitation, data minimization and deletion concepts. The permanent historization typical of a warehouse in particular is at odds with the right to erasure, which is why a well-thought-out concept for retention and anonymization is needed. With cloud services outside the EU, the requirements for data transfers to third countries must additionally be checked.
Example
A data warehouse at a multichannel retailer
A mid-sized retailer sells through its own online shop, several marketplaces and brick-and-mortar stores. The revenue data is scattered across ERP, shop system and marketplace settlements; each department analyzes it with its own Excel lists, and the figures regularly diverge. A reliable overall view of revenue and margin per channel is missing, and analyses run directly in the ERP slow down day-to-day operations at peak times.
The retailer sets up a cloud-based data warehouse. A nightly ETL process loads orders, invoices and stock from all systems into a star schema with fact and dimension tables. A dashboard now shows revenue, gross profit and return rate per channel, product group and month – with drill-down to the individual product and a year-over-year comparison across the historized data. This makes it visible that a high-revenue marketplace delivers only a thin margin after returns and fees.
Frequently asked questions
Related services
Questions about Data Warehouse in your ERP project?
We advise vendor-neutrally – and implement it ourselves on request.