Il capitolo 13 ha chiuso la progettazione fisica del data mart. Con il capitolo 14 si apre il Modulo 2 del corso, dedicato alla costruzione e all'interrogazione del data warehouse con strumenti commerciali: il data mart di esempio è quello progettato in Indyco Builder sul benchmark TPC-D (capitolo 15), e l'interrogazione avviene con Power BI. Le slide del modulo spiegano un'idea centrale: una query OLAP si formula scegliendo una visualizzazione e trascinando attributi nelle sue proprietà — ma dietro ogni visualizzazione c'è una query SQL, con misure da aggregare, attributi dimensionali per creare i gruppi e filtri. Su questo nucleo si innestano la navigazione delle gerarchie, lo slice, l'ordinamento, i gruppi e il binning, le colonne virtuali e le misure definite con CALCULATE e REMOVEFILTERS.
Le query OLAP in Power BI si formulano in due passi: si sceglie una visualizzazione e si trascinano gli attributi (drag&drop) nelle proprietà della visualizzazione. La stessa query può essere emessa in modi diversi, cambiando solo la visualizzazione: i dati sono gli stessi. La slide mostra l'SQL equivalente, che interroga la fact table ft e le dimension table del modello TPC-D con i prefissi p_ (part), s_ (supplier), o_ (order) e d_ (date):
La corrispondenza fra gesti e clausole SQL è diretta: gli attributi trascinati come assi o righe/colonne finiscono nella SELECT e nella GROUP BY; le misure vengono aggregate (qui sum(ft.quantity)); i filtri finiscono nella WHERE. I join fra fact table e dimension table sono impliciti nel modello, non vanno scritti a mano.
Le slide definiscono esplicitamente i tre elementi della query OLAP:
l_quantity, l_extendedprice, l_tax;d.year, s.region, p.mfgr;p.type = 'ECONOMY ...'.Nel modello multidimensionale del Modulo 2, alcuni attributi sono descrittivi: servono a descrivere, non a raggruppare. La regola delle slide è netta: gli attributi descrittivi possono essere usati solo in combinazione con l'attributo che descrivono. Nell'albero degli attributi del capitolo 15, p_name descrive p_partkey, s_name descrive s_suppkey, c_name descrive c_custkey: possono comparire in una visualizzazione (per esempio come etichetta in hover) solo quando l'attributo che descrivono è già presente nella query.
Un attributo descrittivo usato da solo come gruppo non avrebbe alcun significato di aggregazione: p_name identifica un singolo prodotto, ma senza p_partkey non si capisce a quale livello di dettaglio raggruppare — e il risultato dipenderebbe dal nome, che non è una chiave. La coppia chiave + descrizione è invece informativa: la chiave definisce il gruppo, il nome lo rende leggibile.
La navigazione delle gerarchie è abilitata quando le gerarchie sono usate in alcune (non tutte) visualizzazioni. Le slide definiscono quattro operazioni:
| Operazione | Significato |
|---|---|
| Drill-up | corrisponde al roll-up: sale al livello più aggregato della gerarchia |
| Drill-down | modalità interattiva per slice-and-drill: scende nel dettaglio, livello per livello |
| Next-level | scende di un livello e sostituisce il livello corrente con quello più fine della gerarchia |
| Expand-all | scende di un livello e mantiene il livello corrente |
La differenza fra le ultime due è il punto delicato: next-level rimpiazza il livello corrente (la visualizzazione mostra solo il nuovo livello, più fine); expand-all lo mantiene (il nuovo livello si aggiunge al precedente, che resta visibile). Entrambi eseguono un drill-down, ma con un comportamento diverso della visualizzazione.
Avanzate le operazioni nell'ordine corretto e osservate che cosa succede al livello mostrato dalla visualizzazione.
Lo slice si esegue aprendo il pannello dei filtri: si può aggiungere un attributo o una misura (ad esempio mfgr), oppure espandere un attributo o una misura già usati nella query (ad esempio region). Sugli attributi, lo slice consiste nel selezionare uno o più valori categorici, in due modi:
Sulle misure, lo slice seleziona un intervallo di valori, e qui sta la distinzione fondamentale fra i due momenti in cui il filtro può agire:
| Filtro | Quando agisce | SQL |
|---|---|---|
| Post-aggregazione | sul risultato aggregato, come una HAVING | SELECT [..] GROUP BY [..] HAVING SUM(quantity) > 12M |
| Pre-aggregazione | sulle singole tuple, come una WHERE | SELECT [..] WHERE quantity > 10 [..] |
La differenza pratica è netta. Il filtro post-aggregazione si applica quando si filtra sull'attributo che è già nel pannello: l'intervallo selezionato viene confrontato con il valore aggregato (una HAVING). Il filtro pre-aggregazione richiede di trascinare di nuovo la misura dal pannello Fields: solo così il filtro può agire sulle tuple prima dell'aggregazione (una WHERE).
L'ordinamento si ottiene in due modi: cliccando su un attributo nella visualizzazione, oppure cliccando i tre puntini nell'angolo in alto a destra della visualizzazione stessa.
Le slide presentano poi quattro funzionalità di visualizzazione:
Dal clic destro su un attributo (Create > ...) si possono creare nuovi campi:
Da Table tools > New column si crea una colonna virtuale basata su una formula personalizzata. Gli esempi delle slide:
profit = 'dw ft'[quantity] * RELATED('dw dt_part'[retailprice]) — la funzione RELATED() attraversa la relazione per leggere il prezzo dalla dimension table;taxed = IF('dw ft'[tax]>0, "taxed", "not taxed") — una classificazione condizionale;order_age = DATEDIFF('dw dt_order'[rel_orderdate_date], TODAY(), YEAR) — l'età dell'ordine in anni.Da Table tools > New measure si definisce una funzione per aggregare i dati, da usare al posto di SUM(), AVG() ecc. Gli esempi delle slide, tutti nella fact table:
sum_of_profit = SUM('dw ft'[profit]);avg_profit_by_customer = SUM('dw ft'[profit]) / DISTINCTCOUNT('dw dt_customer'[custkey]);AvgNationSales = SUM('dw ft'[quantity]) / DISTINCTCOUNT('dw dt_customer'[nation]);DaysWith25UnitsBought = CALCULATE(DISTINCTCOUNT('dw dt_order'[id_date]), 'dw ft'[quantity] > 25). CALCULATE permette di specificare un calcolo (qui il conteggio di date distinte) associato a una condizione di filtro;PercWithinRegion = SUM('dw ft'[quantity]) / CALCULATE(SUM('dw ft'[quantity]), REMOVEFILTERS('dw dt_customer'[nation])). Il filtro in CALCULATE può essere usato per manipolare la clausola group-by: REMOVEFILTERS elimina il filtro sulla nazione imposto dal group-by, dando il totale di regione.Le misure avg_profit_by_customer e AvgNationSales mostrano due modi di fare la stessa cosa: dividere un aggregato per un conteggio di valori distinti. La differenza è nel denominatore: custkey conta i clienti, nation conta le nazioni. In entrambi i casi il risultato dipende dal contesto di filtro corrente — che CALCULATE + REMOVEFILTERS permette di modificare esplicitamente.
Le slide chiudono con tre esercizi guidati, che riassumono tutte le funzionalità del capitolo.
Quattro visualizzazioni da costruire:
Tre sessioni (3.1–3.3), ciascuna con più passi marcati [W] (un nuovo foglio di lavoro per ogni passo):
Sales = Extendedprice * (1-Discount) * (1+Tax) (una «misura» nella terminologia DFM, non Power BI); visualizzare il trend annuale delle vendite con la gerarchia year sull'asse X e spiegare il calo del 1998 con slice e drill; visualizzare i trend mensili (una linea per anno), escludendo agosto 1998.Quattro query (4.1–4.5), con obiettivo primario rispondere alla query e obiettivo secondario riprodurre il grafico dato:
L'esercizio 4.3 contiene la trappola tipica dei quesiti d'esame: «Make sure that you count the customers who made orders, not just those in the DT». Il denominatore va calcolato sulla fact table (clienti che compaiono negli ordini), non sulla dimension table (tutti i clienti). È lo stesso problema del DISTINCTCOUNT discusso nella sezione 9.
In due passi: scelta di una visualizzazione e drag&drop degli attributi nelle proprietà della visualizzazione. Dietro c'è una query SQL che combina fact table e dimension table, aggrega le misure e raggruppa per gli attributi dimensionali; la stessa query può essere emessa in modi diversi cambiando solo la visualizzazione.
Misure (da aggregare), attributi dimensionali (per creare i gruppi), filtri (condizioni di selezione). Nella query SQL corrispondono rispettivamente alla lista aggregata della SELECT, alla GROUP BY e alla WHERE.
Solo in combinazione con l'attributo che descrivono (ad esempio p_name con p_partkey, s_name con s_suppkey): da soli non definiscono un livello di aggregazione significativo, come coppia chiave+descrizione rendono leggibile il gruppo.
Drill-up (roll-up, sale al livello aggregato); drill-down (modalità interattiva slice-and-drill); next-level (scende di un livello e sostituisce il livello corrente); expand-all (scende di un livello e mantiene il livello corrente). La navigazione è abilitata quando le gerarchie sono usate in alcune, non tutte, le visualizzazioni.
Si apre il pannello dei filtri (aggiungendo un attributo o espandendone uno già usato) e si selezionano uno o più valori categorici: manualmente, spuntando i valori, oppure con Top/Bottom N in base a un criterio.
Il post-aggregazione filtra il risultato aggregato (una HAVING: GROUP BY [..] HAVING SUM(quantity) > 12M) e si applica quando l'attributo è già nel pannello. Il pre-aggregazione filtra le tuple (una WHERE: WHERE quantity > 10) e richiede di riscaricare la misura dal pannello Fields.
L'ordinamento: click su un attributo nella visualizzazione oppure sui tre puntini in alto a destra. Le funzionalità: Legend (spezza i mark in sotto-mark), Small multiples (spezza il grafico in sotto-grafici), Visual table (il grafico come tabella), Data point table (i valori originali prima dell'aggregazione).
Group (discreto): si prendono manualmente alcuni membri di un attributo e li si mette in un nuovo attributo. Group (numerico): si discretizza un valore numerico in bin, con solo binning equi-width supportato (es. il saldo del conto dei clienti, bin size 1000 nell'esercizio 4.1).
Attraversa la relazione fra tabelle per leggere un valore dalla tabella correlata: ad esempio profit = 'dw ft'[quantity] * RELATED('dw dt_part'[retailprice]) legge il prezzo dalla dimension table partendo da una riga della fact table.
CALCULATE associa un calcolo a una condizione di filtro (es. DaysWith25UnitsBought = CALCULATE(DISTINCTCOUNT('dw dt_order'[id_date]), 'dw ft'[quantity] > 25)). Il filtro in CALCULATE può manipolare la clausola group-by: REMOVEFILTERS('dw dt_customer'[nation]) elimina il filtro imposto dal group-by sulla nazione, dando il totale di regione per il calcolo delle percentuali.
Contare i clienti che hanno fatto ordini, non tutti quelli nella dimension table: il denominatore di AvgOfTotSalesPerCustomer va calcolato sulla fact table, con DISTINCTCOUNT sui clienti presenti negli ordini.