Dimensional Modeling Guide

Beispiel: E-Commerce Bestellung

-- Faktentabelle: eine Zeile pro Order-Position
CREATE TABLE fct_order_lines (
    order_line_sk       BIGINT PRIMARY KEY,    -- Surrogate Key
    order_id            VARCHAR(50),           -- Natural Key (degenerate dimension)
    customer_sk         BIGINT REFERENCES dim_customers,
    product_sk          BIGINT REFERENCES dim_products,
    date_sk             INT REFERENCES dim_date,
    -- Kennzahlen (aggregierbar)
    quantity            INT,
    unit_price          DECIMAL(10,2),
    discount_amount     DECIMAL(10,2),
    line_total          DECIMAL(10,2)
);

-- Dimensionstabelle: eine Zeile pro Kunde
CREATE TABLE dim_customers (
    customer_sk         BIGINT PRIMARY KEY,    -- Surrogate Key
    customer_id         VARCHAR(50),           -- Natural Key
    customer_name       VARCHAR(255),
    email               VARCHAR(255),
    segment             VARCHAR(50),           -- 'Enterprise', 'SMB', 'Consumer'
    acquisition_channel VARCHAR(50),
    created_at          TIMESTAMP
);

Beispiel: Date Dimension

CREATE TABLE dim_date (
    date_sk             INT PRIMARY KEY,       -- YYYYMMDD Format
    date_actual         DATE NOT NULL,
    day_of_week         VARCHAR(10),           -- 'Monday', 'Tuesday', ...
    day_of_week_num     INT,                   -- 1-7
    day_of_month        INT,
    day_of_year         INT,
    week_of_year        INT,
    month_num           INT,
    month_name          VARCHAR(10),
    quarter_num         INT,
    quarter_name        VARCHAR(10),           -- 'Q1', 'Q2', ...
    year_num            INT,
    fiscal_year         INT,
    fiscal_quarter      INT,
    is_weekend          BOOLEAN,
    is_holiday          BOOLEAN,
    holiday_name        VARCHAR(50)
);

-- Nutzung: einfach nach beliebigem Datumsattribut filtern/gruppieren
SELECT 
    d.month_name,
    d.year_num,
    SUM(f.order_total) as revenue
FROM fct_orders f
JOIN dim_date d ON f.order_date_sk = d.date_sk
WHERE d.is_weekend = FALSE
GROUP BY d.month_name, d.year_num;

Beispiel: Accumulating Snapshot (Order Fulfillment)

CREATE TABLE fct_order_fulfillment (
    order_sk                BIGINT PRIMARY KEY,
    order_id                VARCHAR(50),
    customer_sk             BIGINT,
    -- Mehrere Datums-FKs (Meilensteine)
    order_date_sk           INT,
    payment_date_sk         INT,
    ship_date_sk            INT,
    delivery_date_sk        INT,
    -- Lag-Kennzahlen (berechnet)
    days_to_payment         INT,
    days_to_ship            INT,
    days_to_delivery        INT,
    -- Kennzahlen
    order_total             DECIMAL(10,2),
    current_status          VARCHAR(50)
);

-- Zeile wird aktualisiert, wenn Bestellung fortschreitet
-- Initial: nur order_date_sk
-- Nach Zahlung: payment_date_sk gefüllt, days_to_payment berechnet
-- Nach Versand: ship_date_sk gefüllt, days_to_ship berechnet
-- Nach Lieferung: delivery_date_sk gefüllt, days_to_delivery berechnet