Data Warehouse Fundamentals

Understanding the foundation of enterprise analytics

What is a Data Warehouse?

A Data Warehouse (DWH) is a centralized repository that stores integrated data from multiple sources, optimized for query and analysis rather than transaction processing. It serves as the foundation for business intelligence, reporting, and data analytics.

Unlike operational databases designed for day-to-day transactions (OLTP - Online Transaction Processing), data warehouses are built for analytical queries (OLAP - Online Analytical Processing), enabling organizations to gain insights from historical and current data.

Key Characteristics

Subject-Oriented

Data is organized around major subjects (customers, products, sales) rather than applications, providing a comprehensive view of each subject area.

Integrated

Data from various sources is consolidated and standardized, resolving inconsistencies in naming conventions, formats, and coding structures.

Time-Variant

Historical data is preserved, allowing analysis of trends over time. Every record includes a time dimension for temporal analysis.

Non-Volatile

Once data enters the warehouse, it remains stable and unchanging. Updates create new records rather than modifying existing ones.

Data Warehouse Architecture

Three-Tier Architecture

1. Bottom Tier - Data Sources

The foundation layer containing source systems: operational databases, CRM systems, ERP applications, flat files, APIs, and external data sources. ETL processes extract data from these sources.

2. Middle Tier - Data Warehouse Server

The core layer where data is stored, typically using a relational database optimized for OLAP. Includes staging areas, integration layers, and the actual warehouse database with dimensional or normalized schemas.

3. Top Tier - Presentation Layer

The analysis and reporting layer containing BI tools, dashboards, OLAP cubes, data marts, and reporting applications. Users interact with data through this layer.

Benefits of Data Warehousing

Better Business Intelligence
Centralized data enables comprehensive analysis, better decision-making, and strategic insights across the organization.
Improved Query Performance
Optimized for analytical queries with pre-aggregated data, indexes, and query optimization techniques, delivering fast response times.
Historical Analysis
Maintains historical data for trend analysis, forecasting, and understanding business evolution over time.
Data Quality & Consistency
ETL processes clean, standardize, and validate data, ensuring high-quality information for analysis and reporting.

OLTP vs OLAP

AspectOLTP (Operational)OLAP (Analytical)
PurposeDay-to-day operationsAnalysis and reporting
Data SourceOperational dataConsolidated from multiple sources
DesignNormalized (3NF)Denormalized (Star/Snowflake)
Query TypeSimple, frequent transactionsComplex, ad-hoc queries
Data VolumeCurrent data (GB)Historical data (TB)
Response TimeMillisecondsSeconds to minutes
UsersThousandsHundreds
FocusData integrity, availabilityQuery performance, flexibility