Module 2: Data Warehouse Data Modeling
Course: Advanced Database & Data Warehousing
Learning Objectives
After completing this module, students will be able to:
1. Explain the principles of dimensional modeling and the rationale for modeling
choices.
2. Differentiate star, snowflake, fact‐constellation (galaxy) and data‐vault schemas.
3. Define the concept of grain and design slowly changing dimensions (SCDs).
4. Evaluate trade-offs in normalization vs. denormalization for analytic workloads.
5. Apply best-practice guidelines to select appropriate schema patterns for varied
business scenarios.
1 Overview of Dimensional Modeling
Dimensional modeling organises data around facts, the quantitative business events,
and dimensions, the business perspectives for analysis. Ralph Kimball’s approach
emphasises understandability and performance, whereas W. H. Inmon’s Corporate
Information Factory stresses integration and enterprise consistency.
2 Star Schema
A star schema comprises a single, large fact table in the centre, surrounded by wide,
denormalised dimension tables. Because each dimension is only one join away, star
schemas excel at query speed and are favoured by most BI tools.
Design checklist
Aspect Recommendation
Grain Declare the atomic grain first (e.g., one sales
transaction per line item).
Keys Use surrogate integer keys for dimensions; natural
keys live as attributes.
Indexes Bitmap or compressed columnar indexes
accelerate star joins.
SCDs Implement Type 2 for full history, Type 1 for
correction-only fields.
3 Snowflake Schema
In a snowflake schema, one or more dimensions are further normalised into sub-
dimensions to reduce redundancy. While storage is saved, additional joins can degrade
performance.
When to snowflake
Very high-cardinality attributes (e.g., product-SKU hierarchies).
Strict conformance to third-normal-form corporate data models.
Situations where storage costs trump query latency.
4 Fact-Constellation (Galaxy)
A fact-constellation shares conformed dimensions among multiple fact tables, enabling
cross-process analytics (e.g., Sales vs. Inventory). Galaxy schemas are a natural
evolution as enterprises mature and integrate several marts.
Example
Fact Table Conformed Dimensions
SALES_FACT Time, Product, Store
RETURNS_FACT Time, Product, Store
INVENTORY_FACT Time, Product, Warehouse
5 Data-Vault Modeling
Data-vault 2.0 introduces Hubs, Links and Satellites, separating keys, relationships and
descriptive attributes. The architecture supports agility for frequent source changes and
facilitates auditing by keeping all history.
Component Purpose
Hub Unique business key with surrogate.
Link Many-to-many relationships between hubs.
Satellite Contextual data, time-stamped for historisation.
Data-vault models are often loaded by event-driven ELT and published downstream as
dimensional marts.
6 Normalization vs. Denormalization for Analytics
Normalization reduces redundancy and update anomalies but introduces join overhead.
Denormalization speeds reads and simplifies queries at the cost of duplicate data and
potential inconsistency. In analytic databases—especially columnar MPP engines—the
space penalty of denormalisation is mitigated by compression, tilting the trade-off toward
query performance.
7 Choosing the Right Schema
Scenario Recommended Schema Rationale
Departmental dashboard Star Fast, intuitive, easy to build
requiring rapid prototyping
Corporate data integration with Data-Vault Agile evolution, audit trail
frequent source changes
Detailed financial ledger with Snowflake / 3NF Data integrity, minimal
strict referential rules redundancy
Cross-process enterprise Fact-Constellation Shared dimensions, scalability
reporting
8 Review Questions
1. What is the first step in designing a star schema and why is it critical?
2. List two business scenarios where a snowflake schema is advantageous.
3. How does a data-vault satellite differ from a dimension table in a star schema?
4. Explain how conformed dimensions support self-service BI.
5. Describe the trade-offs between Type 1 and Type 2 SCDs.
9 Practical Exercise
Using the Retail case study, model the following:
1. A star schema for daily sales.
2. A galaxy schema incorporating returns and inventory.
3. A data-vault core model.
Compare query plans for total_sales aggregated by product_category and quarter under
each schema.
10 Further Reading
Kimball, R. & Ross, M. (2019). The Data Warehouse Toolkit (4th ed.).
Linstedt, D. & Olschimke, M. (2016). Building a Scalable Data Warehouse with Data
Vault 2.0.
© 2025 Professor ______ — released under CC BY-NC-SA 4.0