OLAP e modellazione analitica avanzata

Le analisi OLAP sembrano naturali finché devi rispondere in fretta per tempo, prodotto, canale, paese e segmento. Senza un modello analitico ogni drill-down diventa una query fragile da riscrivere, e i totali smettono di quadrare. Il cubo multidimensionale è la guida che tiene insieme query e dashboard: in questa lezione impari a navigarlo con ROLLUP, CUBE e window function, senza unioni manuali.

L’idea in una frase

OLAP organizza metriche in dimensioni e gerarchie navigabili per rispondere a domande incrociate senza riscrivere la logica. Il modello del cubo è la guida per query e dashboard.

La sequenza di progettazione

1. Definisci dimensioni, gerarchie e grain prima di disegnare slice, dice e drill-down.
2. Classifica ogni misura come additiva, semi-additiva o non additiva per roll-up corretti.
3. Usa ROLLUP e CUBE per totali e subtotali invece di unioni manuali di query separate.
4. Copri ranking, cumulati e confronti anno su anno con window function standard e finestre dichiarate.
5. Verifica ogni navigazione contro baseline e segmento per impedire che le medie nascondano divergenze.

Il modello mentale del cubo

Le analisi OLAP sembrano naturali finché devi rispondere in fretta per tempo, prodotto, canale, paese e segmento. Senza modello analitico ogni drill-down diventa una query fragile da riscrivere. Tratta OLAP come progettazione di navigazione: drill-down, roll-up, slice, dice e gerarchie devono conservare significato.

Pensa a un cubo con tre dimensioni: Tempo per Prodotto per Paese. Ogni cella contiene il ricavo e ogni query è un’operazione sul cubo. Lo slice fissa una dimensione e taglia il cubo sul piano temporale. Il dice filtra su più dimensioni ed estrae un sotto-cubo. Il drill-down aumenta il dettaglio da anno a mese. Il roll-up lo diminuisce da città a regione. In SQL moderno bastano GROUP BY, WHERE e window function, ma il modello del cubo resta la guida per query e dashboard.

Passaggio	Domanda da fare	Output atteso
Decisione	Che cosa cambia se il modello analitico è più solido?	Scelta esplicita
Segnale	Quale dato osservabile riduce l’incertezza?	Metrica o evento
Baseline	Rispetto a cosa interpretiamo il risultato?	Confronto credibile
Vincolo	Che cosa può falsare la lettura?	Assunzione da dichiarare
Azione	Quale passo operativo segue?	Raccomandazione controllabile

ROLLUP, CUBE e GROUPING SETS

SQL supporta nativamente aggregazioni multidimensionali e conoscerle evita decine di query scritte a mano.

-- ROLLUP: gerarchia di aggregazioni
SELECT country, region, SUM(revenue)
FROM sales GROUP BY ROLLUP(country, region);
-- Produce: (country,region), (country, ALL), (ALL, ALL)

-- CUBE: tutte le combinazioni
SELECT country, product, SUM(revenue)
FROM sales GROUP BY CUBE(country, product);
-- Produce 4 combinazioni: country×product, country×ALL, ALL×product, ALL×ALL

Questi operatori sostituiscono le UNION ALL di query separate e sono ottimizzati dal query planner. Per dashboard con totali e subtotali sono essenziali.

Window function per OLAP

Le window function coprono la maggior parte dei casi avanzati: ranking per top N prodotti per paese, cumulati mensili con finestra, confronti anno su anno con LAG, medie mobili a sette giorni con RANGE BETWEEN. Senza window function servivano self-join o tool esterni. Oggi è SQL standard in ogni warehouse moderno.

In sintesi: usa ROLLUP per gerarchie naturali, CUBE per combinazioni libere e window function per ranking e confronti temporali, senza self-join artigianali.

Verdetto: ROLLUP vince per gerarchie naturali, CUBE per combinazioni libere e le window function per ranking e confronti temporali; niente self-join artigianali.

Esempio SQL: una vista di controllo

Il pattern seguente è eseguibile nella maggior parte dei warehouse moderni e crea una base con metrica, segmento e finestra temporale per confrontare periodi e gruppi senza riscrivere la logica.

WITH base_events AS (
  SELECT
    user_id,
    account_id,
    event_type,
    event_time,
    DATE_TRUNC('week', event_time) AS week,
    source,
    device_type
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '180 days'
    AND user_id IS NOT NULL
),
weekly_user_metrics AS (
  SELECT
    week,
    user_id,
    COALESCE(source, 'unknown') AS source,
    COALESCE(device_type, 'unknown') AS device_type,
    COUNT(*) AS total_events,
    COUNT(DISTINCT DATE(event_time)) AS active_days,
    COUNT(DISTINCT event_type) AS event_diversity,
    MAX(CASE WHEN event_type IN ('purchase', 'subscribe', 'activation') THEN 1 ELSE 0 END) AS reached_key_outcome
  FROM base_events
  GROUP BY week, user_id, source, device_type
)
SELECT
  week,
  source,
  device_type,
  COUNT(DISTINCT user_id) AS users,
  ROUND(AVG(active_days), 2) AS avg_active_days,
  ROUND(AVG(event_diversity), 2) AS avg_event_diversity,
  ROUND(AVG(reached_key_outcome) * 100, 2) AS key_outcome_rate
FROM weekly_user_metrics
GROUP BY week, source, device_type
ORDER BY week, source, device_type;

Esempio Python: controllare stabilità e anomalie

# df contiene: week, segment, users, key_outcome_rate
# key_outcome_rate espresso in percentuale, es. 12.4

df = df.sort_values(['segment', 'week']).copy()
df['previous_rate'] = df.groupby('segment')['key_outcome_rate'].shift(1)
df['wow_change_pp'] = df['key_outcome_rate'] - df['previous_rate']
df['rolling_mean'] = df.groupby('segment')['key_outcome_rate'].transform(
    lambda s: s.rolling(4, min_periods=2).mean()
)
df['rolling_std'] = df.groupby('segment')['key_outcome_rate'].transform(
    lambda s: s.rolling(4, min_periods=2).std()
)
df['z_score'] = (df['key_outcome_rate'] - df['rolling_mean']) / df['rolling_std']

anomalies = df[df['z_score'].abs() >= 2].sort_values('z_score')
print(anomalies[['week', 'segment', 'key_outcome_rate', 'wow_change_pp', 'z_score']])

Riferimento: Celko, J. (2014). Joe Celko’s SQL for Smarties, 5th ed. Morgan Kaufmann.

Il documento che ha fondato l’analisi multidimensionale

Nel 1993 Edgar F. Codd pubblica con Codd e Salley il white paper Providing OLAP to User-Analysts, il mandato che fonda l’analisi multidimensionale moderna. Il testo fissa 12 regole per i sistemi OLAP, tra cui viste multidimensionali, gerarchie, totali coerenti e performance interattiva su grandi volumi. Da lì nascono cubi, operatori di roll-up e drill-down e la distinzione tra misure additive e non additive ripresa in questa lezione. Il messaggio è diretto: senza dimensioni, gerarchie e regole di aggregazione scritte prima, ogni drill-down diventa una query fragile.

Domande per ripassare

1. Quali dimensioni e gerarchie rendono navigabile il tuo cubo senza riscritture?
2. Quale misura è additiva e quale richiede calcoli controllati nei roll-up?
3. Quando usi ROLLUP per gerarchie e quando CUBE per combinazioni?
4. Quale window function copre ranking, cumulati e confronti anno su anno?