Star Schema — Dimensional Modeling for Analytical Warehouse

Star schema — central fact table surrounded by dimension tables. Intuitive structure optimized for BI.

Dimensional Modeling¶

Ralph Kimball designed star schema for maximum analytical query performance.

Fact Tables¶

CREATE TABLE fct_sales (
    sale_id       BIGINT PRIMARY KEY,
    date_key      INT REFERENCES dim_date(date_key),
    product_key   INT REFERENCES dim_product(product_key),
    customer_key  INT REFERENCES dim_customer(customer_key),
    quantity      INT NOT NULL,
    total_czk     DECIMAL(12,2)
);

Dimension Tables¶

CREATE TABLE dim_date (
    date_key    INT PRIMARY KEY,
    full_date   DATE,
    day_of_week VARCHAR(10),
    quarter     INT,
    year        INT,
    is_weekend  BOOLEAN
);

Advantages¶

• Simplicity — understandable for business
• Performance — fewer JOINs
• BI compatibility — optimized for BI tools

Summary¶

Star schema is proven approach for analytics. Fact table with metrics and dimensions with descriptive attributes.