Dimensional Modeling Guide

Exemple : commande e-commerce

-- Table de faits : une ligne par ligne de commande
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,
    -- Mesures (agrégables)
    quantity            INT,
    unit_price          DECIMAL(10,2),
    discount_amount     DECIMAL(10,2),
    line_total          DECIMAL(10,2)
);

-- Table de dimension : une ligne par client
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
);

Exemple : dimension date

CREATE TABLE dim_date (
    date_sk             INT PRIMARY KEY,       -- Format YYYYMMDD
    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)
);

-- Usage : filtrer/grouper facilement par tout attribut date
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;

Exemple : accumulating snapshot (fulfillment commande)

CREATE TABLE fct_order_fulfillment (
    order_sk                BIGINT PRIMARY KEY,
    order_id                VARCHAR(50),
    customer_sk             BIGINT,
    -- Plusieurs clés dates (jalons)
    order_date_sk           INT,
    payment_date_sk         INT,
    ship_date_sk            INT,
    delivery_date_sk        INT,
    -- Mesures de délai (calculées)
    days_to_payment         INT,
    days_to_ship            INT,
    days_to_delivery        INT,
    -- Mesures
    order_total             DECIMAL(10,2),
    current_status          VARCHAR(50)
);

-- La ligne est mise à jour au fur et à mesure
-- Au début : seulement order_date_sk
-- Après paiement : payment_date_sk rempli, days_to_payment calculé
-- Après expédition : ship_date_sk rempli, days_to_ship calculé
-- Après livraison : delivery_date_sk rempli, days_to_delivery calculé