A Systematic Overview of Data Warehouse and Data Mart Modeling Techniques

Introduction

Data warehouse modeling includes several techniques: ER modeling, relational modeling, and dimensional modeling specifically designed for analytical databases.

The article will detail dimensional modeling and focus on three overall modeling systems based on ER/relational/dimensional approaches: normalized data warehouse, dimensional data warehouse, and independent data marts.

Basic Concepts of Dimensional Modeling

Dimensional modeling is a relational modeling method for analytical databases, data warehouses, and data marts. It adds two concepts:

Dimension table : describes the type of an analysis subject (e.g., time, location, product). Dimension tables are relatively static and contain a small amount of data.

Fact table : stores measurements of the subject (e.g., sales amount). Fact tables contain foreign keys to dimension tables, are numeric, and grow rapidly.

Note: Strict normalization is not required in a data warehouse (see previous article).

Three Dimensional Modeling Schemas

1. Star Schema : The most common form, consisting of a single fact table surrounded by dimension tables. Characteristics:

Dimension tables connect only to the fact table.

Each dimension’s primary key is a single column placed as a foreign key in the fact table.

Dimensions radiate around the central fact table like a star.

2. Snowflake Schema : An extension of the star schema where dimension tables are further normalized into sub‑dimensions.

Snowflake dimensions are smaller and normalized, but increase query complexity; therefore they are rarely used in practice.

3. Fact Constellation Schema (Star‑Constellation): Extends the star schema to allow multiple fact tables sharing dimensions.

When business evolves, a single dimension may be linked to several fact tables, making the constellation schema common in later stages.

Example: Retail Sales Dimensional Modeling

After gathering user requirements, an ER diagram for a retail company is created (image omitted). The ER diagram is mapped to a relational diagram.

The chosen model is a star schema. Four key questions guide the dimensional design:

Which dimensions are useful? For sales analysis: PRODUCT, CUSTOMER, STORE, DATE.

How to generate dimension tables from existing data?

PRODUCT dimension from PRODUCT, VENDOR, CATEGORY tables.

CUSTOMER dimension directly from CUSTOMER table.

STORE dimension from STORE and REGION tables.

CALENDAR dimension derived from the TDate column of SALESTRANSACTION.

What metrics measure the subject? Sales quantity and sales amount.

How to build the fact table? Join SALESTRANSACTION, SOLDVIA, and PRODUCT to obtain quantity and amount.

Observations: dimension tables and fact tables do not fully satisfy normalization (3NF and 1NF), but because the warehouse is read‑only, strict normalization is unnecessary.

Additional Fact Attributes

Fact tables often include transaction identifier (TID) and transaction time. TID is placed in the fact table as a degenerate dimension to avoid costly joins. Transaction time is also stored in the fact table for partitioning in distributed warehouses (Hive, Spark SQL).

Classic Constellation Model

Multiple fact tables sharing dimensions enable two scenarios:

Shared dimensions : Adding a new fact table (e.g., defective product analysis) without redesigning dimensions.

Detail vs. aggregate fact tables : Detail tables store individual facts (include TID), while aggregate tables store pre‑summarized data (no TID). Combining both in a constellation provides flexibility and performance.

Slowly Changing Dimensions

Dimensions may change over time, requiring surrogate keys (***Key) and row indicators to preserve history. Example: CUSTOMER table tracks TaxBracket changes using surrogate keys and timestamps, allowing queries for current, historical, or specific attribute states.

Three Data Warehouse Modeling Systems

Normalized Data Warehouse (Inmon): Build a fully normalized analytical database, then create data marts for each department. Central database is not accessed directly by developers.

Pros: high data quality, less redundancy; Cons: longer time to deliver, higher modeling effort.

Dimensional Data Warehouse (Kimball): Use conformed dimensions and a large star‑constellation model to serve all analytical needs. New data marts can be added on top if needed.

Independent Data Marts : Each business unit builds and maintains its own ETL and data mart.

Cons: data silos, duplicated effort, harder to achieve enterprise‑wide analytics.

Comparison of the Three Systems

Normalized vs. dimensional approaches trace back to Bill Inmon and Ralph Kimball. Normalized warehouses require extensive global modeling, leading to higher upfront effort but better long‑term manageability and data quality. Dimensional warehouses are more agile, easier to adapt to frequent business changes, and demand less modeling expertise. Independent data marts are the least recommended due to fragmentation, though they may be chosen for organizational or budget constraints.

Conclusion

Data warehouse modeling is a comprehensive discipline that combines ER, relational, and dimensional techniques. Complex enterprises need dedicated modeling teams with strong technical skills and deep business understanding to design effective warehouses.