Data Warehouse Architecture
The architecture describes how data flows from sources, through ETL and storage, to the tools users query. The standard reference model is the three-tier architecture.
"Draw and explain the three-tier data warehouse architecture" is an extremely frequent 10-mark question. Also learn the ROLAP / MOLAP / HOLAP comparison table.
1. Three-Tier Architecture
| Tier | Contents |
|---|---|
| Tier 1: Data sources | Operational (OLTP) systems, legacy databases, external data, unstructured feeds |
| Tier 2: Warehouse (integration layer) | Staging area, ETL engine, central EDW (detail and summary), data marts, metadata repository |
| Tier 3: Front end | OLAP servers, query and reporting tools, dashboards, data mining and EIS applications |
Monitoring, refresh, backup and recovery tools surround the warehouse tier.
2. Two-Tier Architecture
Source systems feed the warehouse, and analytical tools query it directly, with no separate OLAP or presentation server.
| Pros | Cons |
|---|---|
| Simple and cheap for small warehouses | Heavy queries can slow the source systems |
| No extra data duplication | Poor scalability and limited analysis power |
3. Other Architecture Patterns
| Pattern | Idea |
|---|---|
| Hub-and-spoke (Inmon) | Normalized EDW is the hub; dependent data marts (spokes) are fed from it. One version of the truth |
| Bus architecture (Kimball) | Fact tables with conformed dimensions shared by all marts, tied by a bus matrix |
| Independent data marts | Each department builds its own mart. Quick, but inconsistent |
| Federated / virtual warehouse | No physical copy; data stays in sources and queries are joined virtually |
4. OLAP Server Types
| Type | Storage | Speed | Scalability | Notes |
|---|---|---|---|---|
| ROLAP | Relational (star or snowflake) | Slower | Very high | SQL generated on the fly, huge volumes |
| MOLAP | Pre-computed multidimensional cube | Fastest | Limited | Sparse data and cube explosion |
| HOLAP | Detail relational, aggregates in a cube | Fast | High | Best of both, common in practice |
5. Layer Responsibilities
| Layer | Function | Typical tools |
|---|---|---|
| Source | Run the business | Oracle, MySQL, SAP, SQL Server |
| ETL and staging | Extract, clean, transform | Informatica, DataStage, SSIS, Talend |
| Storage | EDW and marts | Teradata, Redshift, Snowflake, Hive |
| OLAP | Multidimensional analysis | Essbase, MS Analysis Services |
| Presentation | Reports, dashboards, mining | Power BI, Tableau, Looker, R, Python |
6. Data Flow Equations
Rows loaded in one refresh cycle:
where is the set of fact tables, the rows inserted and the rows updated.
Pre-aggregation helps because a summary table built from detail rows with reduction factor has only about rows to scan:
- Draw and explain the three-tier architecture with all components (10 marks, extremely frequent).
- ROLAP vs MOLAP vs HOLAP (5 marks).
- Two-tier vs three-tier architecture (short note).
Frequently Asked Questions
QWhat are the three tiers of a data warehouse?
Bottom tier: data sources plus the warehouse with ETL and metadata. Middle tier: the OLAP server (ROLAP, MOLAP or HOLAP). Top tier: front-end tools such as reports, dashboards and data mining.
QWhat is the difference between ROLAP and MOLAP?
ROLAP keeps data in relational tables and generates SQL at query time (very scalable, slower). MOLAP stores pre-computed multidimensional cubes (fastest, but limited size and sparse-data problems).
QWhat is HOLAP?
Hybrid OLAP: detail data stays in relational tables and aggregates are stored in a cube, balancing speed and scalability.
QWhat is a virtual (federated) warehouse?
A warehouse with no physical copy of data; queries are rewritten and run against the source systems.
QWhat are hub-and-spoke and bus architectures?
Hub-and-spoke (Inmon) feeds dependent data marts from a central normalized warehouse. Bus (Kimball) connects dimensional data marts through shared conformed dimensions.