Dimensional Modeling in Data Warehousing: Concepts, Theory, and Practical Example

0x00 Preface

The following content is the author’s summary of learning and work, with conceptual material mainly from books and practical insights from personal experience; errors may exist.

Overview

Data warehouses encompass architecture, modeling, and methodology, including Hadoop, Spark, Hive components, dimensional modeling, and auxiliary systems such as scheduling, metadata, ETL, and visualization.

The core of a data warehouse is the data model, especially dimensional modeling, which the article examines in depth.

Article Structure

The article proceeds in four parts:

Introduce classic data warehouse models and analyze their pros and cons.

Detail the basic concepts and theory of dimensional modeling.

Model a familiar e‑commerce scenario using the discussed theory.

Share practical trade‑offs observed in real‑world implementations.

0x01 Classic Data Warehouse Models

Four models are presented, each corresponding to a reference book.

1. Entity‑Relationship (ER) Model

Immon’s 3NF‑based model designs a whole‑enterprise schema focusing on subject‑oriented abstraction, aiming for a “single version of the truth”. Challenges include extensive business knowledge, long implementation cycles, and high skill requirements.

2. Dimensional Model

Ralph Kimball’s approach, described in *The Data Warehouse Toolkit*, builds models from analytical decision needs, yielding star and snowflake schemas that support fast analysis and large‑scale query performance.

3. Data Vault

Dan Linstedt’s method extends ER modeling to provide an auditable, historical data layer, emphasizing traceability and extensibility via Hub, Link, and Satellite components.

4. Anchor Model

Lars Rönnbäck’s highly extensible model normalizes to 6NF, using Anchors, Attributes, Ties, and Knots, resembling a key‑value structure.

0x02 Dimensional Modeling

1. What Is Dimensional Modeling

It creates schemas (e.g., star schema) where a central fact table records measurable events and surrounding dimension tables store descriptive attributes.

2. Core Elements

Fact Table

Stores measurable, transactional events; each row represents a single measurement.

Example: an order table records each purchase.

Dimension Table

Contains a single primary key and descriptive attributes; serves as a foreign key source for fact tables.

Examples include user, merchant, and time tables.

0x03 Practice

1. Business Scenario

Model an e‑commerce platform focusing on user purchase behavior, involving buyer, merchant, product, purchase time, and order amount.

2. Model Design

The proposed dimensional model separates facts and dimensions, reducing redundancy, clarifying structure, and facilitating OLAP analysis, though it adds join complexity and potential data inconsistency.

In contrast, a single wide table is intuitive and easy to query but suffers from massive redundancy and low reusability at large scale.

3. Usage Example

Query to calculate total price of LV brand purchases by male users in Beijing in 2016:

SELECT SUM(order.money)
FROM order, product, date, address, user
WHERE date.year = '2016'
  AND user.sex = 'male'
  AND address.province = '帝都'
  AND product.name = 'LV'

0xFF Summary

Dimensional modeling offers many advantages such as reduced redundancy, clear structure, and OLAP friendliness, but real‑world constraints often require compromises, including the occasional use of wide tables despite their drawbacks.

·END·