Fundamentals and Architectural Models of Data Warehousing

Introduction

Data warehousing bridges the gap between operational systems that run day‑to‑day business processes and informational systems that support managerial decision making. As described in the introductory source, organizations need both a current view of business state and historical snapshots for trend analysis and forecasting. A data warehouse (DW) therefore serves as a subject‑oriented, integrated, time‑variant, and non‑volatile collection of data (Inmon definition) that enables analysts to query across multiple dimensions without interfering with transactional workloads.

Core Characteristics of Data Warehousing

The four defining attributes of a DW are grounded in the source material:

• Subject‑oriented – Data is organized around major business subjects such as customers, products, or sales, providing a concise view for decision makers while excluding irrelevant operational details (Source 3).
• Integrated – Heterogeneous sources—relational databases, flat files, and online transaction records—are cleansed and harmonized. Consistent naming, encoding, and measurement conventions are enforced to ensure that, for example, hotel‑price data from different systems share a common currency and tax representation (Source 6).
• Time‑variant – Unlike operational databases that store only the current value, a DW retains historical records over long horizons (often 5‑10 years), embedding a time element in every key to support trend and period‑over‑period analysis (Source 8).
• Non‑volatile – Once loaded, the warehouse is read‑only for analytical queries; it does not support transaction processing, recovery, or concurrency control. Access is limited to the initial load and subsequent reads (Source 4).

These characteristics are explored in depth in the sub‑article Core Characteristics and Architectural Models of Data Warehousing.

Architectural Models

Several canonical architectures illustrate how the core characteristics are realized in practice.

Generic Two‑Level Architecture

The simplest model consists of a single, company‑wide warehouse populated by periodic extraction. Because extraction occurs in batches, the warehouse is not completely up‑to‑date, but it provides a unified repository for all subjects (Source 11).

Three‑Layer Architecture

A more elaborate design adds staging, integration, and presentation layers. The staging layer receives raw extracts, the integration layer applies cleaning and transformation, and the presentation layer exposes subject‑oriented schemas to users. All layers rely on the ETL process.

Independent Data Mart

Independent marts are miniature warehouses focused on a specific business line. Each mart has its own ETL pipeline, which can increase complexity when many marts coexist (Source 7). This approach is useful when departments require rapid, localized analytics without waiting for enterprise‑wide integration.

Dependent Data Mart with Operational Data Store (ODS)

A dependent mart draws data from a central Enterprise Data Warehouse (EDW) and an ODS that holds current operational data. This architecture simplifies data access, enables a single ETL process for all dependent marts, and ensures that the EDW remains the authoritative source (Source 2).

Logical Data Mart and Active Data Warehouse

Logical marts are not separate physical databases; they are virtual views over an active data warehouse that supports near‑real‑time ETL. Because the underlying warehouse is continuously refreshed, creating new logical marts is straightforward and does not require additional storage (Source 5).

ETL as the Unifying Process

Regardless of the chosen architecture, Extraction, Transformation, and Loading (ETL) is the backbone of data warehousing. It extracts data from operational sources, transforms it to meet integration standards, and loads it into the warehouse or marts. In batch‑oriented designs, ETL runs on a schedule; in active warehouses, it may operate in near‑real‑time to satisfy low‑latency reporting needs.

Relation to Operational vs Informational Systems

The sub‑article Operational vs Informational Systems in Data Warehousing details how operational systems focus on transaction processing, whereas informational systems—realized through the DW—focus on analytical processing. By separating these concerns, organizations avoid performance contention and can tailor each environment to its specific workload.

Conceptual Modeling and Fact Definition

A solid conceptual model, often expressed as star or snowflake schemas, defines facts (quantitative measures) and dimensions (contextual attributes). While the present article does not delve into schema design, readers are directed to the sub‑article Conceptual Modeling and Fact Definition in Data Warehousing for a deeper treatment.

In sum, the fundamental principles and architectural variants described above provide a flexible yet disciplined framework for building data warehouses that empower organizations to turn raw operational data into strategic insight.