Basi di Dati – Complementi Esercitazione su Data Warehouse

Sommario
1. Riassunto concetti principali dalle slide
della lezione di teoria
2.Studio di caso : progettazione di un Data
Warehouse di una catena di
supermercati
3.Progettazione di un Data Warehouse dei
voli di un insieme di compagnie aeree
Basi di Dati – Complementi
Esercitazione su Data Warehouse
1/24/2006
2
Data warehouse
Una base di dati
– di tipo On Line Analytical Processing
– utilizzata principalmente per il supporto ai
processi decisionali
1 - Riassunto concetti principali dalle slide
della lezione di teoria
– integrata — aziendale e non dipartimentale
– orientata ai dati — non alle applicazioni
– orientata a dati storici — con un ampio orizzonte
temporale
– non volatile — i dati sono caricati e acceduti fuori
linea
– mantenuta separatamente dalle basi di dati
operazionali
1/24/2006
On Line Transaction Processing
On Line Analytical Processing
• Tradizionale elaborazione di transazioni, che realizzano
i processi operativi dell’azienda-ente
• Operazioni predefinite e relativamente semplici
• Ogni operazione coinvolge “pochi” dati
• Queries senza aggregazioni o con aggregazioni semplici
• Es. Prenotazioni online, ricerche per chiave
• Dati elementari, aggiornati
• Frequenti, molti utenti
• Le proprietà ACID (atomicità, correttezza, isolamento,
durabilità) delle transazioni sono essenziali
• Ottimizzano il throughput di transazioni di lettura e
scrittura in presenza di concorrenza
1/24/2006
4
5
• Elaborazione di operazioni per i processi decisionali
• Operazioni complesse e casuali
• Queries con aggregazioni contemporanee su piu’
dimensioni
• Es.: totale posti prenotati aggregati per regione e per
tipo di cliente, oppure totale posti prenotati per
periodo e per agenzia
• Ogni operazione può coinvolgere molti dati
• Dati aggregati, storici, anche non attualissimi
• Utenti selezionati
• Le proprietà ACID non sono rilevanti, perché le
operazioni sono di sola lettura
1/24/2006
6
1
Integrata
• I dati di interesse provengono da tutte le sorgenti
informative — ciascun dato proviene da una o più di
esse
Processo di costruzione
di un data warehouse
• Il data warehouse rappresenta i dati in modo univoco
— riconciliando le eterogeneità dalle diverse
rappresentazioni
– nomi
– codifiche
– formati
– significato
1/24/2006
7
Fonti e fasi di: costruzione, aggiornamento e
elaborazione di un Data Warehouse
1. Estrazione
2. Esportazione
3. Allineamento
Fonti
Sorgenti
esterne
4. Accesso
Basi di dati
operazionali
8
DW e data mart
Strumenti
di analisi
Data
Warehouse
1/24/2006
I data mart sono sottoinsiemi logici dell’intero
datawarehouse, cioe’ restrizioni del data
warehouse a un particolare processo di supporto
alle decisioni
Fonti
Analisi
dimensionale
Visualizzazione
Data mining
Sorgenti
esterne
Strumenti di analisi
Data
Warehouse
Basi di dati
operazionali
Analisi
dimensionale
Visualizzazione
Data mining
Data Mart
1/24/2006
9
1/24/2006
Fatti, Misure e Dimensioni
10
Fatti, Misure e Dimensioni
• Concetti rilevanti nella definizione di un DW
sono:
– Fatto — un concetto sul quale centrare
l’analisi
– Misura/e — una/piu’
proprietà atomica di
un fatto che si vuole analizzare
– Dimensione — una prospettiva secondo la
• Esempio di individuazione di Dimensioni, Fatti e
Misure nelle specifiche:
– “quanto ho incassato
MISURA
a seguito di vendite di automobili FATTO
–
per regione
DIMENSIONI
–
per mese
–
per tipo di cliente?”
quale effettuare l’analisi
1/24/2006
11
1/24/2006
12
2
Due modelli per DW
Esempi di fatti/misure/dimensioni
• Modello logico: Star Schema
– Per rappresentare fatti, misure, dimensioni rispetto al
modello Entita’ Relazione si dimostra piu’ espressivo il
modello detto Star Schema, che corrisponde a uno
schema relazionale di forma particolare
– Direttamente esprimibile in un DB relazionale
– Chiamato anche Relational OLAP (ROLAP)
• Modello operazionale: Data Cube
– Un Data Cube, che descrive tutte le possibili
aggregazioni che possono essere effettuate partendo
dalle dimensioni scelte
– Implementabile su un DB relazionale
– Chiamato anche Multidimensional OLAP (MOLAP)
• Catena di negozi
– Fatto: vendita di prodotti
– Misure: unità vendute, incasso
– Dimensione: prodotto, tempo, zona
• Compagnia telefonica
– Fatto: telefonata
– Misure: costo, durata
– Dimensione: chiamante, chiamato, tempo,
zona.
1/24/2006
13
1/24/2006
14
Due tipi di tabelle per lo Star Schema
• Tabella dei fatti
• Tabelle delle dimensioni
• Definiamole formalmente utilizzando anche un’ esempio,
riguardante una catena di negozi di prodotti alimentari
– Fatti: vendite dei singoli prodotti (es bottiglia di olio Spremi)
nei diversi negozi ai diversi clienti
– Misure
• Unita’ vendute
• Incassi
– Dimensioni
• Orario, ad esempio ogni ora di ogni giorno di un insieme di
anni
• Luogo, dove e’ localizzato ogni negozio della catena
• Prodotto venduto, ad esempio una certa bottliglia di olio
• Cliente che ha una carta fedelta’, e di cui e’ noto cognome,
1/24/2006
15
ecc
Dimensioni
Teoria Star Schema e Snowflake Schema
Esempio Modello star schema
Tempo
Codice orario
Ora
Giorno
Settimana
Mese
Trimestre
Anno
Luogo
Codice luogo
Negozio
Indirizzo
Codice Città
Città
Codice Regione
Regione
Codice
1/24/2006 Stato
Stato
Fatti
Vendite
Codice orario
Codice luogo
Codice prodotto
Codice cliente
Unità
Incasso
Misure
Modello snowflake schema
(a fiocco di neve)
Prodotto
• Le tabelle sono normalizzate in Boyce Codd
Normal form
• Ha piu’ tabelle rispetto allo schema star
Codice prodotto
Descrizione
Colore
Modello
Codice categoria
Categoria
Luogo
Codice luogo
Negozio
Indirizzo
Codice Città
Città
Codice Regione
Regione
Cliente
Codice cliente
Nome
Cognome
Indirizzo
Età
Codice professione
Professione
17
1/24/2006
Dipendenze funzionali
Cod. luogo
Citta’
Citta’
Cod. Regione
Cod Regione
Regione
18
3
Esempio Modello snowflake schema
Categoria
Prodotto
Codice orario
Ora
Giorno
Settimana
Mese
Trimestre
Anno
Codice prodotto
Descrizione
Colore
Modello
Codice categoria
Citta’
CodiceCitta’
Citta’
Codice Regione
Codice categoria
Categoria
Tempo
Luogo
Codice luogo
Negozio
Indirizzo
Codice Città
Interrogazioni su Star Schema
Vendite
Codice orario
Codice luogo
Codice prodotto
Codice cliente
Unità
Incasso
Professione
Codice professione
Professione
Cliente
Codice cliente
Nome
Cognome
Indirizzo
Età
Codice professione
Regione
Codice Regione
Regione
1/24/2006
19
Forma generale delle aggregazioni - 1
La forma generale delle query
per il modello star schema usa la clausola
GROUP BY gia’ vista nel corso di Elementi
di Basi di dati
1/24/2006
21
Forma generale delle aggregazioni - 2
SELECT insieme degli attributi di raggruppamento
e delle aggregazioni (SUM, etc)
FROM Tabella dei fatti insieme a zero o piu’
tabelle delle dimensioni in join con la tabella dei
fatti
WHERE condizioni di join tra le tabelle citate
nella FROM piu’ condizioni di selezione sugli
attributi (in genere ATTR = “Valore” oppure
ATTR compreso in un intervallo)
GROUP BY insieme degli attributi di
raggruppamento
1/24/2006
Rappresentazione Star Schema su cui effettuare
un esempio di interrogazione
Periodo
Temporale
#Mese
Anno
1/24/2006
Area di mercato
#Regione
#Zona Geografica
Vendita
#Regione
#Prodotto
#Mese
Quantita’
22
Esempio di interrogazione
• Il manager regionale e’ interessato alla vendita
dei prodotti in tutti i periodi temporali
relativamente alla propria regione
Prodotto
#Prodotto
Nome
Tipo
Settore
23
1/24/2006
24
4
La precedente analisi si puo’ effettuare
nel modello star schema con la query
Schema coinvolto
Vendite(Regione, NomeP, Mese-di-anno, Quantita’)
Schema coinvolto
Vendite (Regione, NomeP, Mese-di-anno, Quantita’)
Aree di mercato (Regione, Zona goegrafica)
SELECT NomeP, Mese-di-Anno, SUM (Quantita’)
From VENDITE
WHERE REGIONE = “Lombardia”
GROUP BY NomeP, Mese-di-Anno
SELECT NomeP, Zona geografica, Mese-di-anno
SUM (Quantita’)
From VENDITE, AREE_DI_MERCATO
WHERE VENDITE. Regione.=
AREE_DI_MERCATO.Regione
GROUP BY NomeP, Zona geografica, Mese-di-anno
In questo caso non dobbiamo fare join
1/24/2006
Se si vuole modificare la precedente
aggregando per area geografica ……
25
1/24/2006
26
Progettazione di data warehouse
• La progettazione di un data warehouse è diversa dalla
progettazione di una base di dati operazionale
– i dati da memorizzare hanno caratteristiche
eterogenee
– vincolata dalle basi di dati esistenti
– guidata da criteri progettuali diversi
• Attività principali
Progettazione di data wharehouse
– analisi — delle sorgenti informative esistenti
– integrazione
– progettazione — concettuale, logica e fisica
1/24/2006
27
Fasi della progettazione di un DW
Input: Requisiti degli utenti, basi di dati aziendali, altre fonti
informative esterne
Fase 1: Analisi
• 1.1. Selezione e analisi delle sorgenti informative
• 1.2. Traduzione delle sorgenti informative in un modello
concettuale comune
Fase 2: Integrazione
• 2.1 INTENSIONALE - Produzione dello schema concettuale
integrato
• 2.2 ESTENSIONALE - Integrazioni delle sorgenti
informative
Fase 3 Progettazione logico fisica
• 3.1 Identificazione di fatti e dimensioni
• 3.2 Progettazione logico fisica
1/24/2006
29
1/24/2006
28
Una metodologia di integrazione
• Passo 1 - Trova i conflitti tra i concetti degli schemi
– Omonimie
– Sinonimie
– Conflitti di tipo
• Risolvi i conflitti
• Passo 2 - Fondi gli schemi ed evidenzia le parti comune
degli schemi
• Passo 3. Cerca le proprieta’ interschema, definite cioe’
su concetti nelle parti “non in comune”
1/24/2006
30
5
Una semplice metodologia di progetto
• Scopi:
– Mettere in evidenza gli aspetti legati
alla scelta delle dimensioni
– Confrontare la soluzione star schema
con la soluzione snowflake schema
2 - Studio di caso : progettazione di un
Data Warehouse di una catena di
supermercati
1/24/2006
31
1/24/2006
Case study: progetto di un DW
per un supermercato
Case study: progetto di un DW
per un supermercato
• Scenario: Una catena di supermercati ha 100 negozi
sparsi su un’era geografica che comprende 5 zone
• Ogni supermercato consiste di un insieme di
dipartimenti e gestisce circa 60.000 prodotti sugli
scaffali
– I prodotti sono chiamati SKU (stock keeping
units)
– Sono circa 60.000
1/24/2006
32
33
• I dati vengono raccolti:
– Alla cassa, tramite scan dei bar codes
– All’ingresso in magazzino
• Il sistema di supporto alle decisioni ha come
problema principale decidere prezzi e
promozioni sui prodotti
1/24/2006
34
Passo di design 1: scelta del processo business
su cui prendere decisioni
Passo di design 2: scelta della granularita’ dei
fatti e delle loro dimensioni
• Linea guida 1: Un DW o Data Mart dovrebbe cogliere le
esigenze di uno o piu’ processi aziendali
– Il DW va progettato in funzione del processo da
supportare, piuttosto che in funzione dei soli dati di
partenza disponibili
• Linea guida 2: il modello dimensionale deve gestire
l’informazione piu’ granulare possibile richiesta dal
processo di business
– I dati “atomici” sono quelli che non possono essere
ulteriormente suddivisi
• Nel nostro esempio: il dato atomico e’ una singola voce
di spesa di una transazione di cassa
– Transazione = carrello che attraversa la cassa
– Voce di spesa = singolo tipo prodotto sul carrello (es.
Bottiglia di olio Spremi, che il cliente puo’ aver
acquistato in quantita’ pari a una o piu’)
• Nel nostro esempio, scegliamo di modellare il processo
di vendita:
– Quali prodotti vengono venduti in quale negozio, in
quali giorni e secondo quali promozioni
1/24/2006
35
1/24/2006
36
6
Passo di design 3: Scelta delle dimensioni
N.B. TBD significa to be done, ancora da fare, da espandere
• Le dimensioni primarie seguono la granularita’ dei fatti:
– Data, prodotto, negozio
• Altre dimensioni di interesse:
– Promozione associata alla vendita
1/24/2006
37
Misure additive e non-additive - 1
• Le quantita’ individuate sono in genere additive:
– La somma di quantita’ additive e’ valida per
qualunque selezione dei valori delle
dimensioni
– Ad es le quantita’ vendute (Sales quantity) su
ogni negozio, o su determinati prodotti per
determinati negozi, ecc.
1/24/2006
39
Passo di design 4: scelta delle misure (nei fatti)
• Le quantita’ misurabili seguono la definizione dei fatti
– Quantita’ venduta della voce
– Prezzo unitario della voce venduta
– Prezzo totale della voce = quantita’ x prezzo unitario
– Costo unitario al venditore
1/24/2006
Misure additive e non-additive - 2
• Non sempre le quantita’ sono additive:
– Es il margine lordo (Gross profit Dollar Amount)
non e’ additivo perche’ e’ una funzione di altre
quantita’ (rapporto tra prezzo e costo)
– Dato il margine lordo su due insiemi di negozi,
non si puo’ calcolare il margine lordo sulla loro
unione
1/24/2006
Dimensionamento delle tabelle - 1
40
Dimensionamento delle tabelle - 2
• Dimensione temporale: Date - Data
– Se un record della dimensione Date rappresenta un
giorno, possiamo rappresentare 10 anni di vendite con
circa 3.650 record
– Una dimensione accettabile della tabella
1/24/2006
38
41
• Dimensione Product - Prodotto: al min 60.000 record,
spesso molti di piu’
– Deve contenere attributi descrittivi di ogni SKU
– La gerarchia delle merci, per es.:
• SKU Æ marca Æ categoria Æ dipartimento
– Normalmente, circa 50 attributi descrittivi
1/24/2006
42
7
Esempio di tabelle Date e Prodotto
Dimensionamento delle tabelle - 3
• Rappresentazione delle promozioni in corso
– La meno ovvia e forse la piu’ interessante delle
dimensioni
– L’analisi serve infatti a chiarire se la promozione e’
efficace
– Possiamo scegliere, ad esempio:
• Media type, mezzo di comunicazione utilizzato
• Begin date
• End date
• Ecc.
1/24/2006
43
1/24/2006
44
Lo schema proposto (vista parziale)
3 - Progettazione di un Data
Warehouse dei voli di un insieme
di compagnie aeree
1/24/2006
45
Esercizio 1
• Costruire il Data Warehouse dei voli di un insieme di compagnie
aeree. Lo scopo e’ confrontare le compagnie dal punto di vista
della loro capacita’ di non lasciare posti vuoti e di fare profitti.
Ogni volo e’ caratterizzato da una compagnia, una citta’ di
partenza e di arrivo, un orario di partenza (ora, giorno, mese,
anno), classe (economica, business, prima), numero di posti vuoti
in ogni classe, profitti effettuati in ogni classe. Alle citta’ sono
associate nazioni e continenti.
• 1a. Costruire lo star schema e lo schema snowflake.
• 1b. Costruire l’interrogazione che fornisce per ogni compagnia e
mese e per la sola classe business la somma dei posti vuot
• 1c. Costruire l’interrogazione che fornisce per ogni
compagnia,Anno e Città di Partenza la somma dei posti vuoti
• 1d. Costruire l’interrogazione che fornisce per la compagnia
“Alitalia”e per ogni mese la somma dei profitti
1/24/2006
47
Partiamo dalla metodologia semplificata
descritta nel corso
• Passo di design 1: scelta del processo
business su cui prendere decisioni
• Passo di design 2: scelta della granularita’
dei fatti e delle loro dimensioni
• Passo di design 3: Scelta delle dimensioni
• Passo di design 4: scelta delle misure
(nei fatti)
1/24/2006
48
8
Passo di design 1: scelta del processo business su
cui prendere decisioni
Passo di design 2: scelta della granularita’ dei
fatti e delle loro dimensioni - 1
• In questo caso il processo e’ gia’ scelto
• Costruire il Data Warehouse dei voli di un insieme di
compagnie aeree. Lo scopo e’ confrontare le
compagnie dal punto di vista della loro
capacita’ di non lasciare posti vuoti e di fare
profitti. Ogni volo e’ caratterizzato da una compagnia, con il
nome, l’indicazione se la compagnia sia di bandiera o privata e il
capitale, una citta’ di partenza e di arrivo, un orario di partenza
(ora, giorno, mese, anno), classe (economica, business, prima),
numero di posti vuoti in ogni classe, profitti effettuati in ogni
classe. Alle citta’ sono associate nazioni e continenti.
1/24/2006
49
Passo di design 2: scelta della granularita’ dei
fatti e delle loro dimensioni - 2
Rileggiamo le specifiche
• Costruire il Data Warehouse dei voli di un insieme
di compagnie aeree. Lo scopo e’ confrontare le
compagnie dal punto di vista della loro capacita’ di
non lasciare posti vuoti e di fare profitti. Ogni
volo e’ caratterizzato da una compagnia, con il
nome, l’indicazione se la compagnia sia di bandiera
o privata e il capitale, una citta’ di partenza e di
arrivo, un orario di partenza (ora, giorno, mese,
anno), classe (economica, business, prima), numero
di posti vuoti in ogni classe, profitti effettuati in
ogni classe. Alle citta’ sono associate nazioni e 51
1/24/2006
continenti.
• Leggendo le specifiche, apparentemente
abbiamo due scelte:
• 1. I fatti sono i singoli viaggi dei singoli
passeggeri
• 2. I fatti sono i gruppi di viaggi di posti
relativi alla stessa classe
• La scelta corretta e’ la seconda
1/24/2006
50
Passo di design 2: scelta della granularita’ dei
fatti e delle loro dimensioni - 3
Decisione
• L’interpretazione corretta e’ la 2:
• I fatti sono i gruppi di viaggi di posti
relativi alla stessa classe
1/24/2006
52
Passo di design 3: Scelta delle dimensioni
• Costruire il Data Warehouse dei voli di un insieme di
compagnie aeree. Lo scopo e’ confrontare le compagnie dal
punto di vista della loro capacita’ di non lasciare posti vuoti e
di fare profitti. Ogni volo e’ caratterizzato da una
compagnia, con il nome, l’indicazione se la compagnia sia di
bandiera o privata e il capitale, una citta’ di partenza e di
arrivo, un orario di partenza (ora, giorno, mese, anno), classe
(economica, business, prima), numero di posti vuoti in ogni
classe, profitti effettuati in ogni classe. Alle citta’ sono
associate nazioni e continenti.
Dimensioni
Compagnia – CodiceCo, Nome, Capitale
Citta’ di partenza – CodiceCitta’, Nome, Nazione, Continente
Citta’ di arrivo – CodiceCitta’, Nome, Nazione, Continente
Classe –NomeClasse
1/24/2006
OrarioPartenza
– codiceOr, Ora, Giorno, Mese, Anno
53
Passo di design 4: scelta delle misure (nei fatti)
• Costruire il Data Warehouse dei voli di un insieme
di compagnie aeree. Lo scopo e’ confrontare le
compagnie dal punto di vista della loro capacita’ di
non lasciare posti vuoti e di fare profitti. Ogni
volo e’ caratterizzato da una compagnia, con il
nome, l’indicazione se la compagnia sia di bandiera
o privata e il capitale, una citta’ di partenza e di
arrivo, un orario di partenza (ora, giorno, mese,
anno), classe (economica, business, prima), numero
di posti vuoti in ogni classe, profitti effettuati in
ogni classe. Alle citta’ sono associate nazioni e
continenti.
1/24/2006
54
9
Organizzazione star schema
Orario di partenza
Codice
Ora
Giorno
Mese
Anno
Compagnia
Cod compagnia
Nome compagnia
Tipo
Capitale
1/24/2006
Soluzione sbagliata
Citta’
Volo
Codice citta’
Partenza
Nome citta’
Cod volo
Cod. Nazione
Cod compagnia
Nome Nazione
Cod citta’ partenza
Arrivo Cod. Continente
Cod citta’ arrivo
Continente
Cod orario di partenza
Classe
Numero posti vuoti
Profitto
Attenzione: la dimensione classe
La rappresentaimo all’interno della
Tabella dei fatti perche’ consiste
di un solo attributo
55
• Ha un livello di normalizzazione delle
tabelle dimensione maggiore rispetto allo
schema star
• Esempio per la dimensione citta’
Citta’
Dipendenze funzionali
Citta’
Nazione
Compagnia
Cod compagnia
Nome compagnia
Tipo
Capitale
E’ inutile duplicare
Le relazioni relative alle
Citta’ perche’ coincidono
Citta’ Arrivo
Codice citta’
Nome citta’
Cod. Nazione
Nome Nazione
Cod. Continente
Continente
1/24/2006
Nazione
Continente
Orario
CodOrario
Ora
CodGiorno
CodGiorno
Giorno
CodMese
CodMese
Mese
Codanno
Anno
57
1/24/2006
Domanda 1.b
56
Partenza
Volo
Cod volo
Cod compagnia
Cod citta’ partenza
Cod citta’ arrivo
Cod orario di partenza
Cod classe
Numero posti vuoti
Profitto
Arrivo
Citta’
Codice citta’
Nome citta’
Cod. Nazione
Nazione
Cod. Nazione
Nazione
Cod. Continente
Continente
Cod compagnia
Nome compagnia
Tipo
Capitale
58
Domanda 1.c
• Costruire l’interrogazione che fornisce per
ogni compagnia e mese e per la sola classe
business la somma dei posti vuoti
Select CodCompagnia, Mese, Classe, Sum(PostiVuoti)
From VOLO, ORARIO
Where VOLO.CodOrariodiPartenza = ORARIO.Cod orario
and Classe = “business”
Group By Cod.Compagnia, Mese, Classe
1/24/2006
Cod volo
Cod compagnia
Cod citta’ partenza
Cod citta’ arrivo
Cod orario di partenza
Classe
Numero posti vuoti
Profitto
Compagnia
1/24/2006
Citta’ Partenza
Codice citta’
Nome citta’
Cod. Nazione
Nome Nazione
Cod. Continente
Continente
Volo
Codice
Ora
Giorno
Mese
Anno
Organizzazione snowflake schema
Schema snowflake (a fiocco di neve)
Citta’
Codice citta’
Nome citta’
Cod. Nazione
Nome Nazione
Cod. Continente
Continente
Orario
• Costruire l’interrogazione che fornisce per
ogni compagnia,Anno e Città di Partenza la
somma dei posti vuoti
Select CodCompagnia, Anno, CittàDiPartenza,
Sum(PostiVuoti)
From VOLO, ORARIO, Città
Where VOLO.CodOrariodiPartenza = ORARIO.Cod orario
and VOLO.CodCittàDiPartenza = Città.CodCittà
Group By Cod.Compagnia, Anno, CittàDiPartenza
59
1/24/2006
60
10
Domanda 1.d
• Costruire l’interrogazione che fornisce per
la compagnia “Alitalia”e per ogni mese la
somma dei profitti
Select CodCompagnia, Mese, Sum(Profitti)
From VOLO, ORARIO, COMPAGNIA
Where VOLO.CodOrariodiPartenza = ORARIO.Cod orario
and VOLO.CodCompagnia= Compagnia.CodCompagnia AND
NomeCompagnia = “Alitalia”
Group By CodCompagnia, Mese
1/24/2006
61
11