Dimensional Modeling Guide

Esempio: ordine e-commerce

-- Tabella dei fatti: una riga per linea d'ordine
CREATE TABLE fct_order_lines (
    order_line_sk       BIGINT PRIMARY KEY,    -- Surrogate key
    order_id            VARCHAR(50),           -- Natural key (dim. degenere)
    customer_sk         BIGINT REFERENCES dim_customers,
    product_sk          BIGINT REFERENCES dim_products,
    date_sk             INT REFERENCES dim_date,
    -- Misure (aggregabili)
    quantity            INT,
    unit_price          DECIMAL(10,2),
    discount_amount     DECIMAL(10,2),
    line_total          DECIMAL(10,2)
);

-- Tabella dimensione: una riga per cliente
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
);

Esempio: dimensione data

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

-- Uso: filtrare/raggruppare facilmente per ogni attributo data
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;

Esempio: accumulating snapshot (evasione ordine)

CREATE TABLE fct_order_fulfillment (
    order_sk                BIGINT PRIMARY KEY,
    order_id                VARCHAR(50),
    customer_sk             BIGINT,
    -- Più chiavi data (milestone)
    order_date_sk           INT,
    payment_date_sk         INT,
    ship_date_sk            INT,
    delivery_date_sk        INT,
    -- Lag calcolati
    days_to_payment         INT,
    days_to_ship            INT,
    days_to_delivery        INT,
    -- Misure
    order_total             DECIMAL(10,2),
    current_status          VARCHAR(50)
);

-- La riga si aggiorna quando l'ordine avanza
-- Inizialmente: solo order_date_sk
-- Dopo il pagamento: payment_date_sk compilato, days_to_payment calcolato
-- Dopo la spedizione: ship_date_sk compilato, days_to_ship calcolato
-- Dopo la consegna: delivery_date_sk compilato, days_to_delivery calcolato