0Pricing
SQL Academy · Lezione

Tabelle dei fatti e delle dimensioni

Gli elementi fondamentali di un data warehouse.

Tabelle dei fatti e delle dimensioni è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 2 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cos'è un data warehouse?

Un data warehouse è un repository centrale progettato per il reporting e le query analitiche. A differenza di un database transazionale, ottimizzato per scritture rapide, un data warehouse è ottimizzato per letture rapide su grandi volumi di dati storici.

Il modo più comune di organizzare un data warehouse consiste nell'utilizzare uno schema a stella, che suddivide i dati in due tipi di tabelle: tabelle dei fatti e tabelle delle dimensioni.

Definizione delle tabelle dei fatti

Una tabella dei fatti archivia eventi misurabili e quantitativi, ovvero gli elementi che si desidera analizzare. Ogni riga rappresenta il verificarsi di un evento aziendale, come una vendita, una visualizzazione di una pagina web o un ticket di supporto.

Le tabelle dei fatti sono in genere estese (molte righe) e strette (poche colonne); la maggior parte delle colonne contiene chiavi esterne verso le tabelle delle dimensioni oppure misure numeriche come quantity o revenue.

CREATE TABLE fact_sales (
  sale_id      SERIAL PRIMARY KEY,
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  store_key    INT NOT NULL,
  quantity     INT NOT NULL,
  unit_price   NUMERIC(10, 2) NOT NULL,
  total_amount NUMERIC(12, 2) NOT NULL
);

Definizione delle tabelle delle dimensioni

Una tabella delle dimensioni archivia attributi descrittivi che forniscono il contesto per ogni fatto. Tra gli esempi figurano la dimensione prodotto (nome, categoria, marca) e la dimensione data (giorno, mese, trimestre, anno).

Le tabelle delle dimensioni sono solitamente corte (meno righe), ma estese (molte colonne descrittive). Vengono collegate alla tabella dei fatti tramite chiavi intere surrogate.

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL,
  category     VARCHAR(100),
  brand        VARCHAR(100),
  unit_cost    NUMERIC(10, 2)
);

CREATE TABLE dim_customer (
  customer_key SERIAL PRIMARY KEY,
  full_name    VARCHAR(200) NOT NULL,
  email        VARCHAR(200),
  country      VARCHAR(100),
  segment      VARCHAR(50)
);

La dimensione data

La dimensione data è la dimensione più comune in qualsiasi data warehouse. Anziché archiviare un TIMESTAMP grezzo nella tabella dei fatti, si archivia una chiave intera che fa riferimento a una tabella calendario predefinita.

In questo modo le query possono filtrare o raggruppare i dati per trimestre fiscale, giorno della settimana, indicatori dei giorni festivi e altri attributi del calendario senza eseguire calcoli sulle date al momento dell'interrogazione.

CREATE TABLE dim_date (
  date_key       INT PRIMARY KEY,  -- e.g. 20240315
  full_date      DATE NOT NULL,
  day_of_week    VARCHAR(10),
  day_of_month   INT,
  month_num      INT,
  month_name     VARCHAR(20),
  quarter        INT,
  year           INT,
  is_holiday     BOOLEAN DEFAULT FALSE,
  fiscal_quarter INT
);

-- Sample row
INSERT INTO dim_date VALUES
  (20240315, '2024-03-15', 'Friday', 15, 3, 'March', 1, 2024, FALSE, 2);

Il pattern dello schema a stella

Quando si disegna un diagramma con una tabella dei fatti al centro e le tabelle delle dimensioni disposte radialmente intorno, il risultato ricorda una stella, da cui deriva il nome schema a stella.

Le chiavi esterne nella tabella dei fatti puntano alle chiavi primarie di ciascuna dimensione. In genere, le query collegano la tabella dei fatti a una o più dimensioni per aggiungere un contesto descrittivo ai numeri grezzi.

-- Join fact to two dimensions to enrich a sales report
SELECT
  dp.product_name,
  dp.category,
  SUM(fs.quantity)     AS total_units_sold,
  SUM(fs.total_amount) AS total_revenue
FROM fact_sales fs
JOIN dim_product  dp ON dp.product_key  = fs.product_key
JOIN dim_date     dd ON dd.date_key     = fs.date_key
WHERE dd.year = 2024
GROUP BY dp.product_name, dp.category
ORDER BY total_revenue DESC;

Chiavi surrogate e chiavi naturali

Le tabelle delle dimensioni utilizzano chiavi surrogate: interi sintetici generati dal database, indipendenti da qualsiasi significato aziendale. Le chiavi naturali (come lo SKU di un prodotto o l'e-mail di un cliente) possono cambiare nel tempo, mentre le chiavi surrogate non cambiano mai.

L'uso delle chiavi surrogate isola la tabella dei fatti dalle modifiche ai sistemi a monte e rende i join più veloci, poiché i confronti tra interi sono meno costosi di quelli tra stringhe.

-- Surrogate key approach: integer join is fast
SELECT fs.sale_id, dc.full_name, fs.total_amount
FROM fact_sales fs
JOIN dim_customer dc ON dc.customer_key = fs.customer_key
WHERE dc.country = 'Germany'
LIMIT 10;

-- Natural key approach (avoid in warehouses): slower string join
-- JOIN dim_customer dc ON dc.email = fs.customer_email

Granularità: il livello di dettaglio di una tabella dei fatti

La granularità di una tabella dei fatti descrive esattamente ciò che rappresenta una singola riga. Prima di creare un data warehouse, è necessario dichiarare la granularità, ad esempio una riga per ogni singola riga di prodotto di un ordine di vendita.

Una granularità ben definita evita aggregazioni ambigue. Se righe diverse rappresentano eventi diversi, i risultati di SUM e COUNT non avranno alcun significato.

-- Grain: one row per product per order line
-- Each row = one line item sold in one transaction
SELECT
  sale_id,
  date_key,
  product_key,
  quantity,
  unit_price,
  total_amount
FROM fact_sales
WHERE date_key = 20240315
ORDER BY sale_id;

Misure additive, semi-additive e non additive

I fatti si suddividono in tre tipi in base al modo in cui è possibile aggregarli:

  • Additivi: possono essere sommati su tutte le dimensioni (ad esempio revenue, quantity).
  • Semi-additivi: possono essere sommati su alcune dimensioni, ma non su tutte (ad esempio il balance di un conto può essere sommato tra i clienti, ma non nel tempo).
  • Non additivi: non possono essere sommati in modo significativo (ad esempio unit_price, ratio). Utilizzi AVG o altre funzioni di aggregazione.
SELECT
  dd.month_name,
  SUM(fs.total_amount)         AS total_revenue,   -- additive
  AVG(fs.unit_price)           AS avg_unit_price,   -- non-additive: use AVG
  SUM(fs.quantity)             AS total_units       -- additive
FROM fact_sales fs
JOIN dim_date dd ON dd.date_key = fs.date_key
WHERE dd.year = 2024
GROUP BY dd.month_name, dd.month_num
ORDER BY dd.month_num;

Dimensioni a cambiamento lento (SCD di tipo 1 e 2)

Gli attributi delle dimensioni cambiano nel tempo: un cliente cambia Paese, un prodotto cambia categoria. Le dimensioni a cambiamento lento (SCD) gestiscono questi cambiamenti:

  • Tipo 1: sovrascrive il valore precedente. È semplice, ma la cronologia va persa.
  • Tipo 2: aggiunge una nuova riga con una nuova chiave surrogate e le date di validità. Conserva l'intera cronologia, così i fatti storici continuano a fare riferimento alla versione corretta della dimensione.
-- SCD Type 2: add a new version of the row
ALTER TABLE dim_customer ADD COLUMN valid_from DATE;
ALTER TABLE dim_customer ADD COLUMN valid_to   DATE;
ALTER TABLE dim_customer ADD COLUMN is_current BOOLEAN DEFAULT TRUE;

-- Expire the old row
UPDATE dim_customer
SET is_current = FALSE,
    valid_to   = CURRENT_DATE - INTERVAL '1 day'
WHERE email = 'anna@example.com' AND is_current = TRUE;

-- Insert the updated version
INSERT INTO dim_customer (full_name, email, country, segment, valid_from, valid_to, is_current)
VALUES ('Anna Muller', 'anna@example.com', 'Austria', 'Premium', CURRENT_DATE, '9999-12-31', TRUE);

Dimensioni degeneri

A volte un attributo di una dimensione non necessita di una tabella propria. Una dimensione degenere è una chiave di dimensione che risiede direttamente nella tabella dei fatti, senza una tabella delle dimensioni corrispondente.

Gli esempi classici sono i numeri d'ordine, i numeri di fattura o gli ID dei ticket. Forniscono il contesto per eseguire il drill-down, ma non hanno altre colonne descrittive che valga la pena archiviare in una tabella separata.

-- order_number is a degenerate dimension:
-- it lives in the fact table, no dim_order table needed
CREATE TABLE fact_order_lines (
  line_id      SERIAL PRIMARY KEY,
  order_number VARCHAR(20) NOT NULL,  -- degenerate dimension
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  quantity     INT NOT NULL,
  line_total   NUMERIC(12, 2) NOT NULL
);

SELECT order_number, SUM(line_total) AS order_total
FROM fact_order_lines
GROUP BY order_number
ORDER BY order_total DESC
LIMIT 5;

Interrogare lo schema a stella completo

Riassumendo: una tipica query di un data warehouse collega la tabella dei fatti a diverse dimensioni, applica filtri sugli attributi delle dimensioni e aggrega le misure della tabella dei fatti.

L'ottimizzatore può gestire in modo efficiente questi join multipli, perché le chiavi esterne della tabella dei fatti sono indicizzate e le tabelle delle dimensioni sono relativamente piccole.

SELECT
  dd.year,
  dd.quarter,
  dp.category,
  dc.country,
  SUM(fs.quantity)     AS units_sold,
  SUM(fs.total_amount) AS revenue
FROM fact_sales fs
JOIN dim_date     dd ON dd.date_key     = fs.date_key
JOIN dim_product  dp ON dp.product_key  = fs.product_key
JOIN dim_customer dc ON dc.customer_key = fs.customer_key
WHERE dd.year IN (2023, 2024)
  AND dp.category = 'Electronics'
GROUP BY dd.year, dd.quarter, dp.category, dc.country
ORDER BY dd.year, dd.quarter, revenue DESC;

Verifica rapida: fatti e dimensioni

Verifichi la Sua comprensione delle differenze tra le tabelle dei fatti e quelle delle dimensioni in uno schema a stella.

Riepilogo della lezione

In questa lezione ha appreso gli elementi fondamentali di uno schema a stella per data warehouse:

  • Le tabelle dei fatti contengono eventi misurabili (vendite, clic, transazioni), con misure numeriche e chiavi esterne.
  • Le tabelle delle dimensioni forniscono il contesto descrittivo (chi, cosa, dove, quando) utilizzando chiavi surrogate.
  • La granularità definisce esattamente ciò che rappresenta una singola riga dei fatti: va dichiarata prima della creazione del data warehouse.
  • Le misure sono additive, semi-additive o non additive; questa caratteristica determina come aggregarle.
  • Le SCD di tipo 2 conservano i valori storici delle dimensioni aggiungendo nuove righe con date di validità.
  • Le dimensioni degeneri risiedono nella tabella dei fatti quando non hanno attributi aggiuntivi da descrivere.

Comprendere le tabelle dei fatti e delle dimensioni è il fondamento per creare data warehouse veloci, scalabili e potenti dal punto di vista analitico.

Domande Frequenti

La lezione «Tabelle dei fatti e delle dimensioni» è gratuita?

Sì — il testo completo di «Tabelle dei fatti e delle dimensioni» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Tabelle dei fatti e delle dimensioni»?

Gli elementi fondamentali di un data warehouse. Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 2 di 4.

Quanto tempo richiede la lezione «Tabelle dei fatti e delle dimensioni»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. OLTP vs OLAP
  2. Tabelle dei fatti e delle dimensioni
  3. Schemi a stella e a fiocco di neve
  4. Scrivere query analitiche
← Torna a SQL Academy