Data Warehouse — Definition & Architecture

Definition

A Data Warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data organized to support management decision-making (Bill Inmon's definition). It consolidates data from multiple heterogeneous operational sources into one consistent store optimized for analysis rather than transactions.

Architecture Diagram

┌───────────────┐   ┌───────────────┐   ┌───────────────┐
│  Source: RDBMS│   │ Source: Files │   │ Source: ERP   │
└───────┬───────┘   └───────┬───────┘   └───────┬───────┘
        │                   │                   │
        └────────┬──────────┴──────────┬────────┘
                  ▼                     
        ┌─────────────────────┐
        │   ETL / Data Staging │  Extract, clean, transform,
        │        Area          │  integrate, load
        └──────────┬───────────┘
                    ▼
        ┌─────────────────────┐
        │   Metadata           │◀── describes structure,
        │   Repository          │    source mappings, lineage
        └──────────┬───────────┘
                    ▼
        ┌─────────────────────┐
        │   Data Warehouse      │  (central repository —
        │   (Fact + Dimension   │   star/snowflake schema)
        │    tables)             │
        └──────────┬───────────┘
                    ▼
     ┌──────────────┼──────────────┐
     ▼              ▼              ▼
┌─────────┐   ┌───────────┐  ┌───────────┐
│ Data     │   │  OLAP      │  │  Reporting/│
│ Marts    │   │  Server    │  │  Mining   │
│ (dept-   │   │ (ROLAP/    │  │  Tools    │
│ specific)│   │  MOLAP)    │  │           │
└─────────┘   └───────────┘  └───────────┘
                    │
                    ▼
             End Users / Analysts

Key Layers

1. Source layer – operational systems (RDBMS, flat files, ERP, external feeds).
2. ETL/Staging layer – extraction, cleaning, transformation, integration, and loading of data.
3. Metadata repository – records schema definitions, source-to-target mappings, and data lineage.
4. Warehouse storage layer – central repository modeled as fact tables (measures) linked to dimension tables (context), often broken into smaller data marts for individual departments.
5. OLAP/access layer – ROLAP, MOLAP, or HOLAP servers that let users slice, dice, drill down, and roll up.
6. Front-end tools – reporting, dashboards, ad-hoc query tools, and data mining applications used by decision-makers.

This layered design keeps the warehouse decoupled from source systems (so analytics never slows down production databases) while giving analysts a single, consistent, historical view of the business.