Data Warehouse Architecture and Construction Methodology

Data Warehouse Overview

Enterprise data is often scattered across departmental systems, creating four key problems: diverse data sources, lack of standards, inconsistent metrics, and weak data governance. These issues lead to low data credibility, low productivity, and difficulty turning data into actionable information.

Credibility issues : no unified dimensions, algorithmic differences, multi‑level extraction, external data problems, and no common source.

Low productivity : generating enterprise reports requires browsing many files; custom extraction programs face technical obstacles.

Information infeasibility : data is not integrated and lacks the historical context needed for analysis.

Historical Development

The concept originated at MIT in the 1970s, separating business processing from analytical processing. IBM introduced the "Information Warehouse" in 1988 as a marketing concept. Bill Inmon formalized the data warehouse in 1991 with *Building the Data Warehouse*, defining its purpose and construction approach.

Definition and Characteristics

Subject‑oriented : data is organized by business subjects (e.g., customer, product, finance) rather than by transactions.

Integrated : ETL processes ensure consistent coding and eliminate redundancy.

Stable : data reflects a historical period and is primarily queried, not updated.

Time‑variant : timestamps capture historical changes, enabling trend analysis.

Advantages

Simplified information flow after integration.

Higher data reuse and sharing.

Single source of truth.

Unified business view and standardized data.

Data governance ensures quality.

Comparison with Other Systems

Database vs. Data Warehouse : Operational databases are transaction‑oriented, real‑time, and store current data for many users. Data warehouses are analytical, store large historical volumes, have lower query frequency, and serve a smaller group of decision makers.

ODS vs. DW : ODS holds current, updatable data for near‑real‑time OLTP/OLAP and feeds the warehouse. DW stores historical, immutable data for strategic analysis. Differences include data content, scale, technology support, and primary users.

Data Warehouse Architecture

Design Methods

Top‑down : extensive planning and design before implementation; early decisions on sources, security, structure, quality, and standards.

Bottom‑up : start with a small warehouse and expand gradually; early results can prove value.

Hybrid : combine both, often integrating data marts first and then consolidating them into a unified warehouse.

Architecture Debate

The long‑standing debate pits Bill Inmon’s hub‑and‑spoke (Corporate Information Factory) against Ralph Kimball’s data‑mart bus architecture. Inmon advocates a centralized, highly consistent warehouse; Kimball promotes a bus of conformed dimensions linking independent data marts.

Architecture Selection

Choose Inmon for mature, stable enterprises; choose Kimball for fast‑growing, complex businesses.

Evolution in Enterprise Development

Early stages use Inmon’s DataSource → ODS → EDW → DM → APP flow. Rapid growth may require a hybrid Inmon+Kimball model, where data marts pull from ODS rather than directly from the enterprise data warehouse.

Data Modeling in Data Warehouse

Modeling Stages

Business modeling – decompose and formalize business processes.

Domain (subject‑area) modeling – abstract business models into thematic concepts.

Logical modeling – translate domain concepts into relational structures.

Physical modeling – map logical entities to tables, columns, data types, and storage.

Why Modeling Matters

Enables comprehensive business analysis.

Eliminates information silos.

Improves data consistency.

Supports flexible changes.

Clarifies project scope.

Modeling Steps

Business modeling : define boundaries, understand workflows, propose improvements, and set scope.

Domain modeling : extract key subjects, detail each domain, and create conceptual models.

Logical modeling : list requirements, choose dimensions, add timestamps, decide grain, and define relational schemas.

Physical modeling : convert entities to tables, attributes to columns, define data types, and allocate storage.

Modeling Methods

Entity‑modeling : philosophical approach dividing business into entities, events, and explanations; useful for early stages.

Normalization (3NF) : Inmon’s relational method ensuring lossless decomposition and strong data integrity; may limit performance for analytical queries.

Dimensional modeling (Kimball): star schema with fact tables surrounded by dimension tables; emphasizes query performance and intuitive business view.

Dimensional Modeling

Fact Tables

Grain : the level of detail each row represents; atomic grain is recommended for flexibility.

Additivity : facts may be fully additive, semi‑additive, or non‑additive, affecting aggregation.

Text measures : rarely stored in facts; better placed in dimensions.

Key selection : surrogate keys give better performance; natural keys improve maintainability; the choice must be documented.

Consistent facts : facts shared across tables must have identical definitions.

Fact table types: transaction facts, periodic snapshot facts, and accumulating snapshot facts.

Dimension Tables

Contain descriptive attributes, use a single surrogate primary key, and are often denormalized for simplicity. Attributes should be textual and discrete. Hierarchies (e.g., product → brand → category) are stored flat rather than normalized to improve query speed.

Dimensional Modeling Process

Select business process : focus on a concrete activity rather than an entire department.

Define grain : specify the exact fact each row will capture; prefer atomic grain for flexibility.

Choose dimensions : identify descriptive attributes such as date, product, customer, account, etc.

Determine facts : decide which measurements to record, ensuring they match the defined grain; separate facts of different grains into different tables.

Physical Layering

Detail layer : fine‑grained data similar to ODS, cleaned and integrated.

Aggregation layer : summarized data based on common analytical needs.

Topic (wide‑table) layer : combines aggregated facts and shared dimensions for specific business topics.

Dimension tables : sourced directly from business systems, derived from facts, or generated.

These layers support both detailed analysis and high‑performance reporting.