Understanding the foundation of enterprise analytics
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.
Data is organized around major subjects (customers, products, sales) rather than applications, providing a comprehensive view of each subject area.
Data from various sources is consolidated and standardized, resolving inconsistencies in naming conventions, formats, and coding structures.
Historical data is preserved, allowing analysis of trends over time. Every record includes a time dimension for temporal analysis.
Once data enters the warehouse, it remains stable and unchanging. Updates create new records rather than modifying existing ones.
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.
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.
The analysis and reporting layer containing BI tools, dashboards, OLAP cubes, data marts, and reporting applications. Users interact with data through this layer.
| Aspect | OLTP (Operational) | OLAP (Analytical) |
|---|---|---|
| Purpose | Day-to-day operations | Analysis and reporting |
| Data Source | Operational data | Consolidated from multiple sources |
| Design | Normalized (3NF) | Denormalized (Star/Snowflake) |
| Query Type | Simple, frequent transactions | Complex, ad-hoc queries |
| Data Volume | Current data (GB) | Historical data (TB) |
| Response Time | Milliseconds | Seconds to minutes |
| Users | Thousands | Hundreds |
| Focus | Data integrity, availability | Query performance, flexibility |