Introduction to Dimensional Modeling

The theory of dimensional modeling was proposed by Ralph Kimball, who suggested dividing the tables in a data warehouse into two types: fact tables and dimension tables. Dimensional modeling is designed for analytical scenarios, constructing data warehouse models tailored to analysis needs. It focuses on quickly and flexibly addressing analytical requirements while providing rapid response performance for large-scale data.

A "fact table" is used to store measures of facts and foreign key values pointing to various dimensions. A "dimension table," often referred to as a dim table, is used to store metadata of the dimension, including descriptive information such as the dimension's hierarchy and member categories. Simply put, a dimension table represents the perspective from which you observe the subject, while a fact table represents the content you focus on.

Three Modes of Dimensional Modeling

Star Schema

The Star Schema consists of a central subject table and multiple dimension tables. The central subject table is also known as the fact table, while the dimension tables are used to describe the attributes of the fact table. The core idea of the Star Schema is that all dimension attributes should be directly connected to the fact table, rather than being connected through other dimension tables.

Snowflake Schema

The Snowflake Schema is an extension of the Star Schema, where dimension tables are further normalized. Each dimension table can connect to multiple sub-dimension tables.

Constellation Schema

The Constellation Schema (Fact Constellations Schema) is an extension of the star schema, featuring multiple fact tables that can share the same dimension tables. This model is often applied in scenarios where data relationships are more complex than those in star schemas and snowflake schemas. The fact constellation schema requires multiple fact tables to share dimension tables, and thus can be regarded as a collection of star schemas, which is why it is also referred to as the galaxy schema.