Data Warehouse Architecture (with diagram) + ROLAP, MOLAP, HOLAP

Architecture Diagram

┌───────────┐ ┌───────────┐ ┌───────────┐
│ Source DB1│ │ Source DB2│ │ Ext. Files│
└─────┬─────┘ └─────┬─────┘ └─────┬─────┘
      └──────────────┼──────────────┘
                      ▼
            ┌───────────────────┐
            │   ETL: Extract,     │
            │   Clean, Transform, │
            │   Load               │
            └──────────┬──────────┘
                        ▼
            ┌───────────────────┐
            │  Data Warehouse     │◀──▶ Metadata Repository
            │ (fact + dimension   │
            │  tables)            │
            └──────────┬──────────┘
                        │
        ┌───────────────┼───────────────┐
        ▼                ▼                ▼
  ┌───────────┐   ┌────────────┐   ┌────────────┐
  │ Data Marts │   │ OLAP Server │   │  Mining    │
  │ (dept-wise)│   │(ROLAP/MOLAP/│   │  Engine    │
  │            │   │  HOLAP)     │   │            │
  └───────────┘   └────────────┘   └────────────┘
                        │
                        ▼
              Front-end Tools (Query,
              Reporting, Dashboards)

Layers

1. Source systems — operational databases, files, external feeds.
2. ETL layer — extracts, cleans, transforms, and loads data into the warehouse.
3. Metadata repository — describes schema, source mappings, and lineage.
4. Central warehouse — stores integrated, subject-oriented, time-variant data as fact/dimension tables.
5. Data marts / OLAP servers / mining engine — provide focused or multidimensional access.
6. Front-end tools — dashboards, reports, ad-hoc query tools for end users.

ROLAP, MOLAP, HOLAP (OLAP server types)

Type	Storage	Speed	Scalability
ROLAP (Relational OLAP)	Star/snowflake tables in an RDBMS; multidimensional queries translated to SQL	Slower (query-time computation)	Very high — handles huge data volumes
MOLAP (Multidimensional OLAP)	Pre-computed proprietary cube (array structure)	Fastest (pre-aggregated lookups)	Limited — cube can explode in size for sparse/high-cardinality data
HOLAP (Hybrid OLAP)	Detail data in relational tables, summary data in cubes	Balanced	High — combines both strengths

In short: the architecture moves data from many raw sources through ETL into one integrated warehouse, which is then served up via an OLAP layer — and ROLAP/MOLAP/HOLAP are simply three different engineering trade-offs (storage vs speed vs scale) for implementing that OLAP layer.