1

Introduzione al data warehousing

2

In genereIn genere::

abbondanza di dati

ma anche abbondanza di ridondanza ed inconsistenza che non permette di utilizzare i dati in modo utile a fini decisionali

DB4

Il problema

DB1

DB3

DB2

3

Tipiche richieste a cui spesso è difficile dare una

risposta• Qual è il volume delle vendite per regione e

categorie di prodotto durante l’ultimo anno?• Come si correlano i prezzi delle azioni delle società

produttrici di hardware con i profitti trimestrali degli ultimi 10 anni?

• Quali sono stati i volumi di vendita dello scorso anno per regione e categoria di prodotto?

• In che modo i dividendi di aziende di hardware sono correlatiai porfitti trimestrali negli ultimi 10 anni?

• Quali ordini dovremmo soddisfare per massimizzare le entrate?

4

Possibili applicazioni

•gestione dei rischi•analisi finanziaria•programmi di marketing•analisi statistica•integrazione DB clienti•integrazione relazioni clienti•analisi temporale

•telecomunicazioni•banking•università•assicurazioni•beni di consumo•salute•produzione

contesti

problematiche

5

Sistemi informatici: una classificazione

• Transaction processing systems: – per i processi operativi

• Decision support systems: – fortemente integrati, di supporto ai processi direzionali– Richiedono operazioni non previste a priori– Coinvolgono spesso grandi quantità di dati, anche storici e

aggregati – Coinvolgono dati provenienti da varie fonti operative,

anche esterne

6

In sintesi ...

daticonoscenza utile all’azienda

sistemi di supporto alle decisioni (DSS)

DSS: Tecnologia che supporta la dirigenza aziendale nel prendere decisioni tattico-strategiche in modo migliore e più veloce

7

Perché i sistemi tradizionali non sono

sufficienti?

• no dati storici

• sistemi eterogenei

• basse prestazioni

• DBMS non adeguati al supporto

decisionale

• problemi di sicurezza

8

• Sistemi tradizionali– On-Line Transaction Processing (OLTP)

• Sistemi di data warehousing– On-Line Analytical Processing (OLAP)

Profondamente diversi

Più formalmente ...

9

In dettaglio ...OLTP OLAP

funzione gestione giornaliera

supporto alle decisioni

progettazione orientata alle applicazioni

orientata al soggetto

frequenza gironaliera sporadicadati recenti, dettagliati storici, riassuntivi,

multidimensionalisorgente singola DB DB multipleuso ripetitivo ad hocaccesso read/write readflessibilità accesso uso di programmi

precompilati generatori di query

# record acceduti decine migliaiatipo utenti operatori manager# utenti migliaia centinaiatipo DB singola multiple, eterogeneeperformance alta bassadimensione DB 100 MB - GB 100 GB - TB

10

Evoluzione dei DSS

• Anni ‘60: rapporti batch– difficile trovare ed analizzare i dati– costo, ogni richiesta richiede un nuovo programma

• Anni ‘70: DSS basato su terminale– non integrato con strumenti di automazione d’ufficio

• Anni ‘80: strumento d’automazione d’ufficio– strumenti di interrogazione, fogli elettronici,

interfacce grafiche– accesso ai dati operazionali

• Anni ‘90: data warehousing, con strumenti integrati OLAP

11

I sistemi di data warehousing

Il Data Warehousing si può definire come il processo di integrazione di basi di dati indipendenti in un singolo repository (il data warehouse) dal quale gli utenti finali possano facilmente ed efficientemente eseguire query, generare report ed effettuare analisi

12

Client Client

Warehouse

Source Source Source

Query & Analysis

Integration

Metadata

I sistemi di data warehousing

13

Il data warehouseCollezione di dati che soddisfa le seguenti proprieta`:• usata per il supporto alle decisioni• orientata ai soggetti• integrata: livello aziendale e non dipartimentale• correlata alla variabile tempo: ampio orizzonte

temporale• con dati tipicamente aggregati: per effettuare

stime• fuori linea: dati aggiornati periodicamente

14

Il data warehouse

• Orientata ai soggetti: considera i dati di interesse ai soggetti dell’organizzazione e non quelli rilevanti ai processi organizzativi

– basi di dati operazionali dipartimentali:• vendita, produzione, marketing

– data warehouse: prodotti, clienti, fornitori

15

Il data warehouse

• Integrata: i dati provengono da tutte le sorgenti informative

• il data warehouse rappresenta i dati in modo univoco, riconciliando le eterogeneita` delle diverse rappresentazioni:– nomi– struttura– codifica– rappresentazione multipla

16

Il data warehouse

• Correlata alla variabile tempo: presenza di dati storici per eseguire confronti, previsioni e per individuare tendenze

• Le basi di dati operazionali mantengono il valore corrente delle informazioni L’orizzonte temporale di interesse è dell’ordine dei pochi mesi

• Nel data warehouse è di interesse l’evoluzione storica delle informazioni L’orizzonte temporale di interesse è dell’ordine degli anni

17

Il data warehouse

• Dati aggregati: nell’attivita` di analisi dei dati per il supporto alle decisioni:

– non interessa “chi” ma “quanti”

– non interessa un dato ma la somma, la media, il minimo, il massimo di un insieme di dati

18

Il data warehouse

• Fuori linea: – base di dati operazionale: i dati venono

acceduti, inseriti, modificati, cancellati pochi record alla volta

– data warehouse:• operazioni di accesso e interrogazione

diurne• operazioni di caricamento e

aggiornamento notturne

che riguardano milioni di record

19

Il data warehouse

• Un DW rappresenta spesso l’unione di più data mart

• Data mart: restrizione data warehouse ad un singolo processo o ad un gruppo di processi aziendali (es. Marketing)

DWDatamart#1

Datamart#2

Datamart#3

DW

20

... una base di dati separata ...• Per tanti motivi

– non esiste un’unica base di dati operazionale che contiene tutti i dati di interesse

– la base di dati deve essere integrata– non è tecnicamente possibile fare l’integrazione in linea – i dati di interesse sarebbero comunque diversi

• devono essere mantenuti dati storici• devono essere mantenuti dati aggregati

– l’analisi dei dati richiede per i dati organizzazioni speciali e metodi di accesso specifici

– degrado generale delle prestazioni senza la separazione

21

Architettura di riferimento

22

Caratteristiche architetturali irrinunciabili

• Separazione: l’elaborazione analitica e quella transazionale devono essere il più possibile separate

• Scalabilità: l’architettura hw e sw deve essere facilmente ridimensionabile

• Estendibilità: deve essere possibile accogliere nuove applicazioni e tecnologie

• Sicurezza: il controllo sugli accessi è essenziale (dati strategici)

• Amministabilità: l’attività di amministrazione non deve essere troppo complessa

23

dw

Back room Front room catalogo dei metadati

acquisizionememorizzazione accesso

Architettura di riferimento (a due livelli)

24

Dwvirtuale

acquisizione middleware accesso

Architettura ad un livello

Back room Front room catalogo dei metadati

25

Architettura a tre livelli

dw

Back room Front room catalogo dei metadati

acquisizionememorizzazione accesso

Dati riconciliati

26

Sistemi sorgente

• Ogni sorgente di informazioni aziendali• Spesso rappresentate da dati

operazionali: insieme di record la cui funzione è quella di catturare le transazioni del sistema organizzativo

• tipico accesso OLTP• uso di production keys (non vengono

usate nel DW)

27

Dati riconciliati

• Integrazione dati sorgente– simile ad integrazione schemi relazionali

• Risiedono su data staging area– Area di memorizzazione – i dati sorgente vengono trasformati – tecnologia relazionale ma anche flat files

28

Data Warehouse

• Risiede su Presentation Server– Componente che permette la

memorizzazione e la gestione del data warehouse, secondo un approccio dimensionale

• Può essere basato su:– tecnologia relazionale (ROLAP)– tecnologia multidimensionale (MOLAP)

29

End-user data access tools

• Client del DW, di facile utilizzo

• tools per interrogare, analizzare e presentare l’informazione contenuta del DW a supporto di un particolare bisogno aziendale

• invio specifiche richieste al presentation server in formato SQL

31

Due ritmi diversi ...

• Uso bimodale:– 16-22 ore al giorno usati per attività di

interrogazione• funzionalità front room

– 2-8 ore al giorno per caricamento, indicizzazione, controllo qualità e pubblicazione• funzionalità back room

32

Servizi principali back room• Processo ETL: Extraction,Transformation, Loading• Extraction

– Estrazione dei dati dalle sorgenti informative operazionali– Opzioni: tutti i dati / solo dati modificati (incrementale)

• Transformation – Pulizia, per migliorare la qualità dei dati– Trasformazione di formato, da formato sorgente a quello

del DW– Correlazione con oggetti provenienti da altre sorgenti

• Loading– Caricamento (refresh o update) con aggiunta di

informazioni temporali e generazione di dati aggregati

33

Servizi principali back room

• Il ruolo degli strumenti ETL è quello di alimentare una sorgente dati singola, dettagliata, esauriente e di alta qualità che possa a sua volta alimentare il DW

• in caso di architettura a tre livelli questi strumenti alimentano il livello dei dati riconciliati

• la riconciliazione avviene quando il DW viene popolato la prima volta e periodicamente quando il DW viene aggiornato

34

Servizi principali front room

• Supporto di tool di accesso: tool che permettono all’utente di accedere in modo intuitivo ed altamente espressivo ai dati contenuti nel DW:– capacità di effettuare confronti– presentazione dati avanzata– risposte alla domanda: perche?

35

Tool di accesso

• Ad hoc – permettono all’utente di specificare le proprie query

attraverso interfaccie user-friendly

• tools per la generazione di reportistica

• applicazioni avanzate– applicazioni che permettono di applicare operazioni molto

sofisticate al DW• previsione• DATA MINING• ...

36

Tool di accesso

DBMS

Traduzionein SQL

Presentazione

ODBC, JDBC

Aggregatenavigator

37

Progettazione di un data warehouse

38

Fattori di rischio• Tipiche ragioni di fallimento dei progetti di data

warehousing:• Rischi legati alla gestione del progetto

– necessità di condivisione di informazione tra i reparti

– definizione dell’ambito e delle finalità del sistema

• Rischi legati alle tecnologie (rapida evoluzione)• Rischi legati ai dati e alla progettazione

– qualità dei dati e del progetto realizzato• Rischi legati all’organizzazione

– difficoltà di trasformare la cultura aziendale, inerzia organizzativa

39

Metodologie di progettazione• Approccio top-down

+ visione globale dell’obiettivo+ DW consistente e ben integrato– costi onerosi e lunghi tempi di realizzazione

(rischio di scoraggiare la direzione)– complessità dell’analisi e riconciliazione

contemporanea di tutte le sorgenti– impossibilità di prevedere a priori nel dettaglio le

esigenze delle diverse aree aziendali– impossibilità di prevedere la consegna a breve

termine di un prototipo

40

Metodologie di progettazione

• Approccio bottom-up

– il DW viene costruito in modo incrementale assemblando iterativamente più data mart

– rischio: determina una visione parziale del dominio di interesse

– il primo data mart da prototipare deve essere quello che gioca il ruolo più strategico per l’azienda e deve ricoprire un ruolo centrale per l’intero DW

41

Business Dimensional Lifecycle [Kimball]Pianificazione

Definizione dei requisiti

Attuazione

Manutenzione

Progetto dell’architettura

Selezione e installazione prodotti

Modellazionedimensionale

Specificaapplicazioni

Progettazione fisica

Progetto dell’alimentazione

Sviluppo applicazioni

Tecnologia DatiApplicazioni

42

La progettazione di un data mart

• Analisi e riconciliazione delle fonti dati– input: schema delle sorgenti– output: schema riconciliato

• Analisi dei requisiti– input: schema riconciliato– output: fatti, carico di lavoro preliminare

• Progettazione concettuale– input: schema riconciliato, fatti, carico di lavoro

preliminare– ouput: schemi di fatto

• Raffinamento del carico di lavoro, validazione dello schema concettuale– input: schemi di fatto, carico di lavoro preliminare– ouput: carico di lavoro, schemi di fatto validati

43

La progettazione di un data mart

• Progettazione logica– input: schema di fatto, modello logico target,

carico di lavoro– output: schema logico del data mart

• Progettazione dell’alimentazione– input: schemi delle sorgenti, schema riconciliato,

schema logico del data mart– output: procedure di alimentazione

• Progettazione fisica– input: schema logico del data mart, DBMS target,

carico di lavoro– output: schema fisico del data mart

44

La progettazione di un data mart

• Aspetto chiave: – basare la modellazione dei data mart sugli schemi

operazionali– uno schema concettuale di massima per il data mart

può essere derivato dal livello dei dati riconciliati– per questo motivo la fase di analisi e riconciliazione

delle fonti avviene prima della fase di analisi dei requisiti utente

• se queste due fasi sono invertite – lo schema viene ricavato dalle specifiche utente e

solo a posteriori si verifica che le informazioni richieste siano effettivamente disponibili nei database operazionali

– rischio di minare la fiducia del cliente verso il progettista

45

Analisi e riconciliazione delle fonti dati

Metadati

Analisi ericonciliazione

Progettazionedel cleaning

Progettazione della

trasformazione

StrumentiETL

Schemi sorgentioperazionali

Schema riconciliato,Mapping sorgentioperazionali

Schema riconciliato,Mapping sorgentioperazionali

Campioni deidati

Procedure perstrumenti ETL

46

Analisi e riconciliazione delle fonti dati

Metadati

Ricognizione enormalizzazione

Integrazione degli schemi

Schema logico(locale)

Schema concettuale(locale) riconciliato

Schema logico(locale)

Sorgente 1 Sorgente 2

Ricognizione enormalizzazione

Schema concettuale(locale) riconciliato

Schema concettuale(globale) riconciliato

Definizionecorrispondenza con le sorgenti

Schema concettuale(globale) riconciliato

Schema logico(globale) riconciliatoe corrispondenza

47

Analisi e riconciliazione delle fonti dati

• Ricognizione: Esame approfondito degli schemi locali mirato alla piena comprensione del dominio applicativo

• normalizzazione: correzione degli schemi locali per modellare in modo più accurato il dominio applicativo(Fasi da svolgere anche se sorgente dati unica)

• integrazione: v. quanto detto su integrazione di schemi concettuali

• definizione delle corrispondenze: il risultato finale è lo schema riconciliato in cui sono risolti i conflitti e l’insieme delle corrispondenze tra gli elementi degli schemi sorgenti e quelli dello schema riconciliato

48

Le fasi della progettazione di un data

mart• Progettazione concettuale:– fornisce una rappresentazione formale del

contenuto informativo del data mart– indipendente dal sistema che verrà utilizzato per la

sua implementazione

• progettazione logica:– lo schema concettuale viene tradotto nel modello

dei dati del sistema prescelto

• progettazione fisica:– fase in cui vengono scelte le caratteristiche legate

allo schema fisico del DW (indici, partizionamento)• non la vediamo

49

Le fasi della progettazione

di un data martSchemariconciliato

Requisiti utente

PROGETTAZIONECONCETTUALE

PROGETTAZIONELOGICA

PROGETTAZIONEFISICA

Schema difatto

Carico di lavorovalori dei datimodello logico

Schema logico

Carico di lavorovolume dei datiDBMS

Schema fisico

50

Progettazione concettuale di un data warehouse

Analisi multidimensionale• L’analisi richiede normalmente dimensioni multiple:

– “quanti items ho venduto– per regione– per mese– per tipo di cliente?”

• Dimensioni normalmente utilizzate per l’analisi:– Tempo– Prodotto– Cliente– Area geografica– Dipartimento/settore

52

Progettazione concettuale

• modello entità-relazione

• si cerca di eliminare il più possibile la ridondanza– maggiore efficienza delle operazioni di

aggiornamento

• schema simmetrico

• ci possono essere molti modi per connettere (mediante un’operazione di join) due tabelle

• la rappresentazione dipende dalla struttura dei dati

OLTP

53

• Un data warehouse si basa su un modello dei dati multidimensionale che rappresenta i dati sotto forma di data cube

• Un data cube permette di modellare e creare viste dei dati rispetto a molteplici dimensioni

• Modello dati multidimensionale• Detto “Star Schema”• Implementabile su un DB relazionale• Consente volumi di dati molto grandi

– volumi dell’ordine di 100 gbytes forniscono tempi di risposta sotto i 10 sec

Progettazione concettualeOLAP

54

Progettazione concettualeOLAP

prodotto

magazzino

tempo

vino acquacoca cola

mag

apr

feb

set

CB

A

15 121

42

10

9

25

2

7

11

23

3

Processo:vendite in unacatena di supermercati

55

Progettazione concettuale

prodotto

magazzino

tempo

Il manager regionale esamina la vendita dei prodotti in tutti i periodi relativamenteai propri mercati

Il manager di prodotto esamina la vendita di un prodotto in tutti i periodo e in tutti i mercati

Il manager finanziario esamina la vendita dei prodotti in tutti i mercati relativamente al periodo corrente e quelloprecedente

Il manager strategico si concentra su una categoria di prodotti, un’area regionale e un orizzonte temporale medio

56

Progettazione concettuale

• Ogni parametro puo` essere organizzato in una gerarchia che ne rappresenta i possibili livelli di aggregazione:

– negozio, citta`, provincia, regione– giorno, mese, trimestre, anno

OLAP

57

Progettazione concettuale

• L’eliminazione della ridondanza non è un obiettivo– non si devono eseguire operazioni di aggiornamento – schemi denormalizzati

• schemi asimmetrici

• un solo modo per connettere (mediante un’operazione di join) due tabelle– minore numero dijoin– maggiore efficienza

• la rappresentazione dipende dalla struttura dei dati

OLAP

58

Concetti usati per definire un data cube

• Fatto un tema di interesse per l’organizzazione (vendite, spedizioni, acquisti)

• Misura una proprietà di un fatto da analizzare (numero di unità vendute, prezzo unitario)

• Dimensione descrive una prospettiva lungo la quale un’organizzazione vuole mantenere i dati (prodotto, negozio, data)

59

Progettazione concettuale

• Utilizza modelli multidimensionali– schemi di fatto

• ogni schema di fatto mette in evidenza– le dimensioni (spigoli del cubo)– le misure (contenuto di ogni cubetto)– Fatti e dimensioni collegati attraverso

associazioni uno-a-molti – lo schema complessivo rappresenta una

relazione molti-a-molti

60

Schemi di fatto

VENDITA

UnitàIncasso

cliente

prodotto

ora

negozio

fatto

misure

dimensioni

61

Le dimensioni

• Devono essere scelte solo le entità rilevanti per le analisi che si intendono effettuare

• Le dimensioni sono tipicamente caratterizzate da attributi:– testuali– discreti

ma possono anche essere numeriche– dimensione di un prodotto

• esiste sempre una dimensione temporale

62

Dimensioni: esempi

• Attività: vendita in una catena di supermercati– dimensioni: tempo, prodotti, magazzino

• Attività: ordini– dimensioni: tempo, prodotti, clienti, spedizioni

• Attività: iscrizioni universitarie– dimensioni: tempo, facoltà, tipologia studenti

• Attività : vendita automobili– dimensioni: clienti, venditori, concorrenti,

automobili, concessionarie

63

Le dimensioni

• Problema: come si può identificare se un attributo numerico è un fatto o un attributo di una dimensione?

• Se è una misura che varia continuamente nel tempo – fatto

• analisi costo di un prodotto nel tempo

• se è una descrizione discreta di qualcosa che è ragionevolmente costante– attributo di una dimensione

• costo di un prodotto visto come informazione descrittiva

64

Le dimensioni

• Le dimensioni utilizzate sono spesso le stesse in vari contesti applicativi:– tempo– collocazione geografica– organizzazione– clienti

• il numero di attributi per ogni dimensione è in genere molto elevato (anche nell’ordine del centinaio)

65

La dimensione tempo

• È presente in ogni DW in quanto virtualmente ogni DW rappresenta una serie temporale

• Domanda: perché non campo di tipo DATE nella tabella dei fatti?

• Risposta: la dimensione tempo permette di descrivere il tempo in modi diversi da quelli che si possono desumere da un campo date in SQL (giorni lavorativi-vacanze, periodi fiscali, stagioni, ecc.)

66

La dimensione tempo

• Alcuni tipici attributi della dimensione tempo:– tempo-k (può essere un campo di tipo data in

SQL)– giorno-della-settimana– n-giorno-nel-mese– n-giorno-in-anno– n-settimana-in-anno– mese– stagione– periodo fiscale– ...

67

I fatti

• I fatti hanno delle proporietà che sono dette misure

• Le propretà dei fatti sono tipicamente:– numeriche– additive

• possono essere aggregati rispetto agli attributi delle dimensioni, utilizzando l’operazione di addizione

68

Fatti e misure: esempi

• Attività (fatti): vendite in una catena di supermercati– misure: n. prodotti venduti, incassi, costi, ...

• Attività (fatti): ordini– misure: n. spedizioni, n. clienti, importi, ...

• Attività (fatti): iscrizioni universitarie– misure: n. studenti, …

• Attività (fatti): chiamate gestite da compagnia telefonica– misure: costo, durata

69

Additività delle misure

• Incasso, unità vendute: sono additive in quanto si possono aggregare sommando rispetto ad ogni dimensione:

– somma incassi/unità su tempo– somma incassi/unità su prodotti– somma incassi/unità su dipartimenti

70

Semiadditività delle misure

• Numero clienti non è una misura additiva:– somma n. clienti su tempo OK– somma n. clienti su dipartimenti OKMA:

•somma n. clienti su prodotto genera problemi

•si supponga che – clienti che hanno comprato carne 20– clienti che hanno comprato pesce 30

•il numero di clienti che hanno comprato carne o pesce è un qualunque numero tra 30 e 50

71

Semiadditività delle misure

• Il numero clienti è una misura semiadditiva, poiché può essere sommata solo rispetto ad alcune dimensioni

• Soluzione: cambiare la granularità del database, portandola a livello singola transazione

72

Semiadditività delle misure

• Tutte le misure che memorizzano una informazione statica, quali:– bilanci finanziari– misure di intensità (temperatura di una

stanza)

sono semiadditive rispetto al tempo

• ciò che comunque si può fare è calcolare la media su un certo periodo di tempo

73

Non addittività delle misure

• Le misure non additive sono misure che non possono essere sommate

• Esempi:

– misure: costo unitario e quantità nel contesto di un ordine

– dimensioni: clienti, spedizioni, tempo, …– i costi unitari non possono essere sommati se

prima non sono moltiplicati per le rispettive quantità, quindi tali costi sono misure non additive

74

Schemi di fatto

UnitàIncassoNumClientiPrezzoUnitario (AVG)

misure non additive

VENDITA

prodotto

75

Fatti anomali• In alcuni contesti applicativi, puo` capitare di avere fatti

senza misure– fatti anomali

• in questo caso i fatti rappresentano semplicemente una relazione molti-a-molti, senza aggiungere alcuna nuova informazione

• Esempi:– Attivita` principale: corsi universitari

• dimensioni: corsi, professori, studenti, tempo

– attivita` principale: assegnazione cure negli ospedali• dimensioni: ospedali, dottori, diagnosi, tempo, pazienti,

assistenti, procedure

76

Gerarchie• Ciascuna dimensione è spesso organizzata in

una gerarchia che rappresenta i possibili livelli di aggregazione per i dati

• ogni livello della gerarchia rappresenta una relazione molti-a-uno

giornonegozio

prodottocittà

provincia

regione

mese

trimestre

anno

categoriamarca

77

Esempio di DW con gerarchie

customer id name address city53 joe 10 main sfo81 fred 12 main sfo

111 sally 80 willow lacity cityId pop regId

sfo 1M northla 5M south

region regId namenorth cold regionsouth warm region

sType tId size locationt1 small downtownt2 large suburbs

storesType

city region

78

Gerarchie

• Gli attributi della gerarchia vengono associati alle dimensioni a cui si riferiscono e chiaramente indicati

• gli attributi della dimensione devono essere associati al livello della gerarchia a cui si riferiscono

79

Schemi di fatto

VENDITA

UnitàIncasso

cliente

prodotto

ora

negozio

attributi descrittivi

gerarchia

professione

età

nomecognome

indirizzo

categoria

descrizione

coloremodello

indirizzo

città

regione

stato

giorno

settimana mese

trimestre

anno

80

Aggregazione • In alcune situazioni, non si hanno vincoli su tutte

le dimensioni ma solo per alcune• Esempio:

– qual’e` il rapporto tra vendite effettuate nei week-end e vendite effettuate nei giorni lavorativi in ogni magazzino?

– Quale prodotto e` stato maggiormente venduto negli ultimi 3 mesi?

• L’esecuzione di queste interrogazioni e` molto costosa se viene effettuata sui dati di base– Idea: precalcolare aggregati

81

Aggregazione • Un aggregato e` un insieme di misure ottenute come

sintesi di varie misure che caratterizzano i fatti di base

• una misura aggregata è spesso associata a dimensioni aggregate

• è utile considerare gli aggregati a livello concettuale per capire– se lo schema di base permette il calcolo degli aggregati– rientra nell’analisi del carico di lavoro

82

Aggregazione

• un aggregato viene utilizzato per due motivi:– efficienza– impossibilita` di rappresentare gli stessi

dati al livello di dettaglio– Esempio: costi di promozione possono

essere espressi a livello categoria e non a livello di singolo prodotto

83

Esempio

vendite

aggregati(livello 1)

aggregati(livello 2)

Categoria perprodotto per giorno

Vendite mensili per prodotto per giorno

Categoria per mese

84

Due problemi

• Quali dati aggregare?

• Come rappresentare i dati aggregati?

85

Quali dati aggregare?• È importante considerare:

– tipiche richieste aziendali• distribuzione geografica, linee di prodotti, periodicità

generazione reportistica• per ogni dimensione, identificare gli attributi e le

combinazioni di attributi che può essere utile aggregare

– distribuzione statistica dei dati• stimare la dimensione delle tabelle aggregate• se la dimensione della tabella aggregata non riduce di

molto la dimensione della tabella di partenza, forse non conviene aggregare

• aggregazioni non molto usate possono essere utili come punto di partenza per effettuare altre aggregazioni più significative

86

Come e dove memorizzare i dati aggregati?

• Esistono due approcci di base:– nuovi fatti

• vengono create nuove tabelle per i fatti e le dimensioni aggregate

– nuovi campi • vengono aggiunti nuovi attributi nei fatti

e nelle dimensioni

• vediamo solo il primo approccio

87

Nuove tabelle dei fatti

• Per ogni aggregato di interesse viene generato un nuovo fatto

• si generano nuove dimensioni derivate da quelle di base ma contenenti solo i dati di interesse per i fatti aggregati

88

Esempio

VENDITA

UnitàIncasso

cliente

negozio

professione

età

nomecognome

indirizzo

categoria

indirizzo

città

regione

stato

mese

trimestre

anno

89

Composizione degli schemi

• Lo schema risultante da ogni processo aziendale può essere visto come lo schema associato ad uno specifico data mart

• problema: combinare i fatti e le dimensioni contenuti negli schemi associati a ciascun processo, cioe’ contenuti in ciascun data mart

90

Composizione degli schemi

• Gli schemi associati ai vari processi possono avere dimensioni a comune– Una singola dimensione puo` essere usata in relazione a

diversi fatti

• per potere passare dalle informazioni contenute in uno schema alle informazioni contenute in un altro (drill-across): le dimensioni con lo stesso nome devono avere lo stesso significato e contenere gli stessi attributi (o sottoinsiemi di attributi)– dimensioni conformate

• Conseguenza: i vincoli su attributi delle dimensioni a comune devono restituire le stesse entità per ogni schema considerato

91

Fatti conformati

• Anche le misure devono essere conformati– misure con lo stesso nome in fatti

diversi hanno la stessa granularita` e le stesse unita` di misura

– stesso periodo temporale– stesso riferimento geografico

92

Costellazione di fatti

• Schema risultante:– costellazione di fatti

93

Progettazione logica di un data warehouse

94

Scelta sistema di gestione dei dati

• DBMS operazionale: in genere relazionale

• DBMS informativo: – relazionale (Oracle 8/8i, RedBrick-

Informix,…)– multidimensionale (Oracle Express

Server)

95

DBMS relazionali

• Tecnologia consolidata• molto efficienti su dati di dettaglio• estesi in modo da permettere la

materializzazione degli aggregati – (Oracle 9i)

• performance• scalabilità• general-purposes

96

DBMS multidimensionalivendite prodotto mese magazzino

1 vino febbraio A2 acqua febbraio B3 coca cola aprile A4 acqua maggio A5 acqua settembre C

… … … ...

prodotto

magazzino

tempo

vino acquacoca cola

mag

apr

feb

set

CB

A

15 121

42

10

9

25

2

7

11

23

3

97

DBMS multidimensionali

• Modello dei dati basato su hypercubi (vettori multidimensionali)

• precalcolo aggregazioni• aumento prestazioni per le query utente ma

– … sparsità (in genere meno del 20% delle celle contiene informazioni)

– … no join– … no interfaccia SQL (API) --> no standard– … necessità sistema relazionale per dati dettaglio– … file molto grandi– … limitazioni a circa 10GB (problemi scalabilità)

• Per superare questi problemi:– aggiunta capacità di navigare da un MDBMS ad un RDBMS

98

Sistemi ROLAP & MOLAP• ROLAP:

– sistema di data warehouse in grado di supportare le interrogazioni tipiche (roll-up, drill-down,…)

– presentation server relazionale• Oracle 9i + Discoverer

• MOLAP: – sistema di data warehouse in grado di supportare le

interrogazioni tipiche (roll-up, drill-down,…)– presentation server multidimensionale

• Express Server

• DOLAP (Desktop OLAP):– i dati vengono recuperati da un DW relazionale o

multidimensionale e copiati localmente • Business Objects

99

ROLAP & MOLAP

• Performance– Query: MOLAP– Caricamento: ROLAP

• Analisi: MOLAP• Dimensione DW: ROLAP

– MOLAP: problema sparsità

• Flessibilità nello schema: ROLAP– MOLAP: minor numero di dimensioni

ammesse

100

Progettazione logica• Durante questa fase, lo schema concettuale del

DW viene tradotto in uno schema logico, implementabile sullo strumento scelto

• Il modello logico deve essere il più possibile vicino al modello concettuale, anche se alcune variazioni possono essere rese necessarie dal particolare tool prescelto

• supponiamo che il sistema prescelto sia ROLAP

101

Impatto dell’architettura sullo schema logico

• Architettura a due livelli:– ogni tabella = una relazione

• architettura a un livello:– ogni tabella = una vista

• nel seguito ipotizziamo architettura a due-tre livelli

102

Progettazione logica

• Modelli logici per data mart in ROLAP:– modello a stella– modello snowflake

103

Modello a stella

• Si interpretano fatti e dimensioni come entità del modello entità-relazione

• si mappa lo schema entità-relazione in uno schema relazionale– fatti e dimensioni diventano tabelle a cui si

aggiunge una chiave artificiale– le tabelle delle dimensioni contengono tutti

gli attributi per tutti i livelli della gerarchia– poiché le associazioni sono tutte uno-a-molti,

si modellano con chiavi esterne

104

Chiavi

• Le chiavi aggiunte devono essere chiavi artificiali (numeriche, progressive)– non sono le chiavi semantiche

eventualmente utilizzate nella base di dati operazionale

• si ottimizzano le operazioni di join• le chiavi semantiche possono essere

comunque presenti come attributi comuni

105

Esempio di schema

Vendite

Codice orarioCodice luogoCodice prodottoCodice clienteUnitàIncasso

Tempo

Codice orarioOraGiornoSettimanaMeseTrimestreAnno

Luogo

Codice luogoNegozioIndirizzoCodice CittàCittà

Codice RegioneRegioneCodice StatoStato

Prodotto

Codice prodottoDescrizioneColoreModelloCodice categoriaCategoria

Cliente

Codice clienteNomeCognomeIndirizzoEtà

Codice professioneProfessione

106

Esempio di instanza

customer custId name address city53 joe 10 main sfo81 fred 12 main sfo

111 sally 80 willow la

product prodId name pricep1 bolt 10p2 nut 5

sale oderId date custId prodId storeId qty amto100 1/7/97 53 p1 c1 1 12o102 2/7/97 53 p2 c1 2 11105 3/8/97 111 p1 c3 5 50

store storeId cityc1 nycc2 sfoc3 la

107

Osservazioni sulla normalizzazione dello

schema• La tabella dei fatti è completamente normalizzata• le tabelle delle dimensioni possono non essere

normalizzate, ma:– la dimensione delle tabelle delle dimensioni è in genere

irrilevante rispetto alla dimensione della tabella dei fatti– quindi, ogni sforzo per normalizzare queste tabelle ai

fini del DW è una perdita di tempo– lo spazio guadagnato è in genere meno dell’1% dello

spazio richiesto dallo schema complessivo

• la normalizzazione delle tabelle delle dimensioni può ridurre la capacità di browsing (navigazione) dello schema (si veda oltre)

108

Schemi snowflake• In presenza di gerarchie, una dimensione può

essere facilmente normalizzata introducendo una nuova relazione per ogni livello della schema snowflake

Prodotto

Codice prodottoDescrizioneColoreCod Modello

Modello

Codice modelloModellocodice categoria Categoria

Codice categoriacategoria

109

Schemi snowflake

• Uno schema snowflake rende meno efficienti le operazioni di ricerca, anche se la tabella e` grande (+ join)

• e` conveniente utilizzare uno schema snowflake solo se questo approccio aumenta la leggibilita` dello schema e le prestazioni globali

110

Schemi aggregati

• Approccio A– lo schema logico aggregato viene creato

utilizzando le stesse regole utilizzate per lo schema di base• lo schema di base e gli schemi aggregati

dovranno essere alimentati dalle procedure ETL

• si aumenta il carico di lavoro della back room• non si altera il carico di lavoro del

presentation server

111

Schemi aggregati

• Approccio B– lo schema aggregato viene creato in modo

virtuale, come insieme di viste, eventualmente materializzate• solo lo schema di base deve essere

alimentato• si aumenta il carico di lavoro del presentation

server• non si altera il carico di lavoro della back

room (si semplificano le procedure di alimentazione)

112

Esempio

• Fatti: unità, incasso• Dimensioni: prodotti, tempo• si vogliono analizzare unità e incasso per categoria di

prodotto

CREATE VIEW vendite_per_cat(categoria,tempo_k,unità_cat,incasso_cat) AS

SELECT categoria, tempo_k, SUM(unità),SUM(incasso)FROM Vendite,prodottiWHERE vendite.prodotto_k = prodotti.prodotto_kGROUP BY categoria, tempo_k

113

Vantaggi e svantaggi nell’uso degli aggregati

• Svantaggi:– L’uso degli aggregati aumenta di molto la

dimensione del DB (anche del 300%!)– usare aggregazione nel caso in cui ogni

aggregato sintetizza almeno 10-20 record di base

• Vantaggi:– Miglioramento delle prestazioni– possono essere utilizzati in modo

trasparente all’utente

114

Influenza aggregati sul codice SQL

• Se gli aggregati sono presenti, per poterli utilizzare bisogna ovviamente scrivere codice SQL opportuno

• partendo da una query sulle tabelle di base, le tabelle aggregate possono essere utilizzate sostituendole alle corrispondenti tabelle di base

115

Esempio query di base

SELECT categoria, SUM(unità_cat)FROM vendite, prodotti, tempoWHERE vendite.prodotto-k = prodotti.prodotto-k

ANDvendite.tempo-k = tempo.tempo-k ANDtempo.giorno = ‘1 Gennaio, 1996’

GROUP BY categoria

116

Esempio query aggregata

SELECT categoria, unità_catFROM vendite-per-cat, tempoWHERE vendite-aggreg-per-cat.tempo-k =

tempo.tempo-k AND tempo.giorno = 1 Gennaio, 1996’

117

Influenza sul codice SQL

• Gli utenti finali e i tool di accesso devono generare codice differente in relazione che esistano o meno le tabelle agrgegate

– discontinuità delle applicazioni

• Soluzione: aggregate navigator

118

Aggregate navigator• Livello software il cui obiettivo è quello di

intercettare le richieste SQL e tradurle utilizzando nel modo migliore le tabelle aggregate– si scelgono le più piccole

• le richieste SQL si assumono utilizzare le tabelle di base

• si rende trasparente l’uso degli aggregati all’utente finale

119

Progettazione logica in Oracle 9i

• Oltre a creare una relazione per ogni tabella, è possibile rappresentare esplicitamente le gerarchie, utilizzando il concetto di DIMENSIONE– nuovo oggetto della base di dati

• possibilità di materializzare le query

120

Dimensioni in Oracle 9i

• Oggetti che permettono di descrivere gerarchie esistenti all’interno delle tabelle

• vengono utilizzate per:– riscrivere le query – suggerire la creazione di view materializzate

• non contengono nuovi dati ma specificano:– gli attributi coinvolti nelle gerarchie (livelli)– le gerarchie (anche >= 1 per una stessa tabella)– dipendenze funzionali tra livelli ed altri attributi

delle tabelle sottostanti

121

Dimensioni in Oracle 8iCREATE DIMENSION <nome>LEVEL <nome_l1> IS <nome tabella>.<attr>LEVEL <nome_l2> IS <nome tabella>.<attr>…HIERARCHY <nome gerarchia> (

<nome_livello> CHILD OF<nome_livello> CHILD OF…)

ATTRIBUTE <nome livello> DETERMINES <nome<tabella>.<attr>

...

122

Esempio

UnitàIncassoNumClientiPrezzoUnitario (AVG)

prodottocategoria

descrizione

coloremodello

VENDITA

Prodotto_kProdottoModelloColoreDescrizioneCategoria

Prodotti

123

Dimensioni in Oracle 8i

CREATE DIMENSION Prodotti_DLEVEL prod_l IS Prodotti.prodottoLEVEL categ_l IS Prodotti. categoria HIERARCHY Prodotti_H (

prod_l CHILD OFcateg_l)

ATTRIBUTE prod_l DETERMINES descrizioneATTRIBUTE prod_l DETERMINES modelloATTRIBUTE prod_l DETERMINES colore;

124

View materializzate

• Materializzo la vista, cioe` la calcolo una sola volta, la memorizzo e la uso durante l’esecuzione delle query

• Necessità di specificare:– Politiche di caricamento– Politiche di aggiornamento (refresh)– Utilizzo/non utilizzo da parte dell’aggregate

navigator

125

View materializzate in Oracle 9i

• Caricamento:

– Immediate: all’atto della definizione (default)

– Deferred: popolata alla successiva operazione di refresh (che deve essere completo)

126

View materializzate in Oracle 9i

• Refresh:– Come:

• Fast: incrementale (molte restrizioni)• Complete: totale• Force: incrementale quando possibile, totale altrimenti

– Quando:• On Commit: fast refresh al commit delle transazioni

sulle tabelle di definizione della view (solo per join view e single-table view)

• On Demand: invocando specifiche procedure• Start with <date> Next <date expression>• ….

127

View materializzate in Oracle 9i

• Query Rewrite:– Enable: utilizzata dall’aggregate

navigator in fase di riscrittura delle query

– Disable: non utilizzata dall’aggregate navigator in fase di riscrittura delle query

128

View materializzate in Oracle 9i

CREATE MATERIALIZED VIEW nomeBUILD <tipo caricamento>REFRESH <tipo refresh>[ENABLE QUERY REWRITE]AS <sottoquery di definizione>

DROP MATERIALIZED VIEW nome

ALTER MATERIALIZED VIEW ...

129

View materializzate in Oracle 9i

CREATE MATERIALIZED VIEW vendite_catBUILD immediateREFRESH complete on commitENABLE QUERY REWRITEAS SELECT categoria, tempo_k, SUM(unità),SUM(incasso)FROM Vendite,prodottiWHERE vendite.prodotto_k = prodotti.prodotto_kGROUP BY categoria, tempo_k

130

Interrogazione di un data warehouse

131

Tipologie

• Reportistica• On-Line Analytical Processing• Data mining

132

Reportistica• Approccio orientato ad utenti che hanno necessità di

accedere a intervalli di tempo predefiniti a informazioni strutturate in modo pressochè invariabile

• di questi rapporti è nota a priori la forma• un rapporto è definito da un’interrogazione e da una

presentazione• l’interrogazione comporta in genere la selezione e

l’aggregazione di dati multidimensionali• la presentazione può essere in forma tabellare o grafica• la reportistica non è nata con il DW, ma ha acquisito

con il DW benefici in termini di affidabilità e tempestività dei risultati

133

OLAP: On-Line Analytical Processing

• Una visione multidimensionale, logica, dei dati• Analisi interattiva dei dati• Modellazione analitica: derivazione delle proporzioni,

delle varianze, etc • Aggregazioni per ogni sottoinsieme delle dimensioni• Previsione, trend analysis, e statistical analysis• Calcola e visualizza i dati in 2D o 3D crosstabs, charts,

e grafi, con semplici operazioni di rotazione degli assi

134

OLAP su data cubes

Prodotti

Periodi di tempo

Mercati

Quantità

Vendite

135

Progettazione concettuale

prodotto

magazzino

tempo

Il manager regionale esamina la vendita dei prodotti in tutti i periodi relativamenteai propri mercati

Il manager di prodotto esamina la vendita di un prodotto in tutti i periodo e in tutti i mercati

Il manager finanziario esamina la vendita dei prodotti in tutti i mercati relativamente al periodo corrente e quelloprecedente

Il manager strategico si concentra su una categoria di prodotti, un’area regionale e un orizzonte temporale medio

136

I nuovi tipi di query

• Dipendono dai tool di accesso• influenzano l’implementazione delle

query• Operazioni di base:

– drill-down/roll-up– pivoting– slicing– dicing– top-n

137

Operazioni tipiche• Roll up: riassumi i dati, salendo nella gerarchia dei

concetti per una dimensione o attraverso una riduzione di una dimensione– il volume totale di vendite per categoria di prodotto e

per regione per anno– si rimuove per esempio la dimensione tempo

• Roll down or drill down: passa da un livello di dettaglio basso ad un livello di dettaglio alto, scendendo nella gerarchia o introducendo una nuova dimensione.– per un particolare prodotto, trova le vendite dettagliate

per ogni venditore e per ogni data

138

Operazioni tipiche (cont.)

• Slice and dice: select & project– L’operazione di Slice esegue una selezione su

una dimensione del cubo.– L’operazione di Dice definisce un sottocubo

eseguendo una selezione su due o più dimensioni

Vendite delle bevande nel West negli ultimi 6 mesi

• Pivot (rotate): riorienta il cubo• Top-n:

– Esempio: determinare i 10 prodotti piu` venduti ad una certa data e in un certo magazzino, ordinati per vendite

139

Operazioni tipiche: Roll-Up

ProductStore

Month

ProductStore

Year

Roll-up

Drill-Down

ProductRegion

Year

Roll-up

Drill-Down

140

Operazioni tipiche: drill-down e roll-up

Dipartimento Incassi Unità vendute Panificio Lit. 12100000 5088Cibo surgelato Lit. 23000000 15000…

Dipartimento Marca Incassi Unità vendute Panificio Barilla 6000000 2600Panificio Agnesi 6100000 2488 Cibo surgelato Findus 15000000 6500Cibo surgelato Orogel 8000000 8500…

down up

141

Operazioni tipiche: Slice and Dice

ProductStore

Month

Slice

ProductStore

Month

142

Data mining• Attività orientata a scoprire informazioni nascoste nei

dati• le tecniche di data mining sono utilizzate da anni in

applicazioni scientifiche specialistiche (ricerca geologica, medica, astronomica, metereologica, …)

• con il DW il data mining viene trasportato dall’analisi scientifica all’analisi commerciale (ricerche di mercato, segmentazione di mercato, analisi delle abitudini di acquisto, …)

• permette di analizzare automaticamente grosse quantità di dati

• tipologie di pattern estraibili con regole di data mining: regole associative, clustering, alberi di decisione, serie temporali

143

Impatto sul codice SQL• Tipiche query OLAP richiedono molte

aggregazioni

1995

1996

1997

Totale

GE MI Totale

63 81 144

107 14538

35

223

110

388

75

176

SELECT SUM (vendite)FROM vendite S, Tempo T, Magazzini MWHERE S.TId = T.TId AND S.Mid = M.MidGROUP BY T.anno, M.citta`

SELECT SUM (vendite)FROM vendite S, Magazzini MWHERE S.MId = M.MId GROUP BY M.citta`

SELECT SUM (vendite)FROM vendite S, Tempo TWHERE S.TId = T.TId GROUP BY T.anno

144

Impatto sul codice SQL

• In genere:– fatti con k dimensioni– 2k query SQL aggregate

• Nuovo operatore SQL CUBE per calcolare tutte le possibili aggregazioni rispetto ad un insieme di attributi

CUBE Pid, Mid, Tid BY SUM Vendite

equivalente ad un insieme di query:

SELECT SUM (vendite)

FROM vendite S

GROUP BY grouping list

• Presente in molti DBMS

{ }

{PId} {MId} {TId}

{PId, MId} {PId, TId} {MId, TId}

{PId, MId,TId}

145

Impatto sul codice SQL

• Necessita` di determinare “i primi n elementi” rispetto ad un certo ordinamento

• Esempio: determinare i 10 prodotti piu` venduti in un certo magazzino, ordinati per entita` delle vendit

• Presente in molti DBMS

146

Operatori aggregati in Oracle 9i

• SQL viene esteso con nuovi operatori di aggregazione. Tra i vari operatori:

– ROLLUP– CUBE– RANK/TOP-N

147

Roll-up

• SELECT ….GROUP BY ROLLUP (elenco colonne)

• calcola l’aggregato standard rispetto all’elenco di colonne specificato

• calcola subtotali di livello più alto, riducendo ad uno ad uno le colonne da aggregare, procedendo da destra a sinistra nella lista

148

Roll-up• Esempio:

SELECT città, mese, prodotto, SUM(vendite) FROM Vendite v, Magazzini m, Tempo t,

Prodotti p WHERE m.Magazzino_k = v.Magazzino_k AND p.Prodotto_k = v.Prodotto_k AND

t.Tempo_k = v.Tempo_k GROUP BY ROLLUP(città,mese,prodotto)

149

Roll-up Città Mese Prodotto Vendite

genova marzo p1 120genova marzo p2 320genova marzo 440genova luglio p1 220genova luglio p2 110genova luglio 330genova 770milano marzo p1 430milano marzo p2 143milano marzo 573milano luglio p1 340milano luglio p2 100milano luglio 440milano 1013

150

Cube

• SELECT ….GROUP BY CUBE (elenco colonne)

• calcola l’aggregato standard rispetto all’elenco di colonne specificato e rispetto ad ogni sottoinsieme dell’elenco specificato

151

Cube• Esempio:

SELECT città, mese, prodotto, SUM(vendite) FROM Vendite v, Magazzini m, Tempo t,

Prodotti p WHERE m.Magazzino_k = v.Magazzino_k AND p.Prodotto_k = v.Prodotto_k AND

t.Tempo_k = v.Tempo_k GROUP BY CUBE(città,mese,prodotto)

152

Cube Città Mese Prodotto Vendite

genova marzo p1 120genova marzo p2 320genova marzo 440genova luglio p1 220genova luglio p2 110genova luglio 330genova p1 340genova p2 440genova 770milano marzo p1 430milano marzo p2 143milano marzo 573milano luglio p1 340milano luglio p2 100

153

Top-N• SELECT A1,…,An FROM

(SELECT B1,…,Bm, RANK() OVER(ORDER BY Ai ASC,

ORDER BY Aj DESC) AS rankFROM …WHERE ...GROUP BY A1,…,An)

WHERE rank <= N;

• permette di ordinare i risultati e restituire solo i primi N rispetto all’ordinamento prescelto

154

Top-N

• Esempio:

SELECT città, mese, prodotto, sum_vendite FROM (SELECT città,mese,prodotto, SUM(vendite) AS sum_vendite, RANK() OVER (ORDER by SUM(vendite) DESC) AS rank FROM Vendite v, Magazzini m, Tempo t, Prodotti p WHERE m.Magazzino_k = v.Magazzino_k AND p.Prodotto_k = v.Prodotto_k AND

t.Tempo_k = v.Tempo_k GROUP BY (città,mese,prodotto)) WHERE rank <= 3;

155

Top-N

Città Mese Prodotto Vendite

milano marzo p1 430milano luglio p1 340genova marzo p2 320