Data warehousing con SQL Server
•
SQL Server è un RDBMS (Relational DataBase Management System)
•
Analysis Services è un componente di SQL Server che offre un insieme di
funzionalità di supporto al data warehousing
•
Componenti per il data warehousing
– OLAP Server: è il server analitico dei dati
rappresenta i dati analitici del DW in forma multidimensionale, usando i concetti
di cubo, dimensione e misura
– OLAP Manager : strumento di amministrazione dei dati analitici
1
Analysis Services
•
Punto di partenza :
– DW relazionale organizzato secondo uno schema dimensionale (star schema,
snowflake schema)
– Il DW relazionale non deve essere necessariamente un DB gestito con SQL
Server
•
Obiettivo :
– I dati del DW relazionale vengono rappresentati ed analizzati in forma
multidimensionale usando la nozione di cubo (data cube)
•
I cubi sono contenuti in un OLAP database gestiti dall’OLAP Server
•
Un cubo recupera i dati dal DW relazionale che è definito come sorgente
dati (data source) all’interno dell’OLAP Database
•
Un OLAP database può avere varie data source
•
Un cubo può recuperare dati da una singola data source
•
Diversi cubi (di uno stesso OLAP database) possono recuperare dati da
data source differenti
2
OLAP Server: Modalità di memorizzazione dei dati
• OLAP multidimensionale (MOLAP)
– rappresentazione dei dati in formato multi-dimensionale proprietario
– i dati sono estratti dal DW e memorizzati nel server OLAP
– le interrogazioni analitiche vengono eseguite in modo efficiente, perchè
il formato è ottimizzato in tal senso
• OLAP relazionale (ROLAP)
– rappresentazione dei dati in formato relazionale
– i dati di base sono acceduti dal DW memorizzato su un RDBMS
– l’RDBMS viene anche usato anche per la memorizzazione di dati preaggregati, in tabelle separate
– modalità adeguata per DW acceduti poco frequentemente
• OLAP ibrido (HOLAP)
– combina elementi da MOLAP e da ROLAP
– i dati di base sono acceduti dal DW memorizzato su un RDBMS
– i dati pre-aggregati vengono invece memorizzati dal server OLAP, in
formato multi-dimensionale proprietario
– modalità adeguata per DW grandi dimensioni
3
Schemi multi-dimensionali in Analysis Services
• Dimensioni e attributi dimensionali si chiamano livelli
• I valori delle dimensioni e degli attributi dimensionali si dicono membri
livelli
dimensione STORE
(ALL)
membri
STORE
CITY
STATE
COUNTRY
(ALL)
Ditutto
RE
EmiliaR
Italia
ALL
NonSoloX
RE
EmiliaR
Italia
ALL
NonSoloY
MO
EmiliaR
Italia
ALL
NonSoloZ
RM
Lazio
Italia
ALL
...
...
...
...
ALL
4
Schemi multi-dimensionali in Analysis Services
Membri e Livelli:
• le dimensioni contengono solitamente il livello speciale (ALL) che
contiene il solo membro All che denota tutti i membri della
dimensione
Organizzazione in Livelli:
• Nel modello DFM gli attributi dimensionali sono organizzati in una
gerarchia che è un albero direzionato (un nodo può avere più figli)
• In Analysis Services i livelli formano una successione lineare (un
nodo può avere al massimo un figlio)
• L’organizzazione in livelli corrisponde alla definizione di una
relazione padre-figlio tra i membri di livelli successivi (ogni membro
di un livello si raggruppa nel membro padre)
– il membro All è padre dei membri Italia, Francia, ...
– il membro Italia è padre dei membri EmiliaR, Lazio, ..
– …
Misure : corrispondono al concetto di misura del modello DFM
– Le misure sono considerate come membri di una dimensione speciale
chiamata Measures (presente in tutti i cubi)
5
Esempio di Gerarchie e Livelli
•
Consideriamo lo Schema di Fatto BIGLIETTI e la (parte di) dimensione dei
voli, con la relativa gerarchia
•
In Analysis Server definiremo un cubo BIGLIETTI e le dimensioni rispetto
alle quali si vogliono analizzare i dati
– Dimensione VOLO-COMPAGNIA
– Dimensione VOLO-ORADIPARTENZA
6
Misure ed operatori di aggregazione
• Classificazione degli operatori di aggregazione :
– Distributivi: permettono di calcolare dati aggregati a partire direttamente da
dati parzialmente aggregati (es. somma, massimo, minimo)
– Algebrici: richiedono un numero finito di informazioni aggiuntive (misure di
supporto) per calcolare dati aggregati a partire da dati parzialmente
aggregati (es. media – richiede il numero dei dati elementari che hanno
contribuito a formare un singolo dato parzialmente aggregato)
– Olistici: non permettono di calcolare dati aggregati a partire da dati
parzialmente aggregati utilizzando un numero finito di informazioni
aggiuntive (es. mediana, moda)
•
Nel seguito analizzeremo come questi concetti generali siano riportati ed
implementati in Analysis Services e quali sono alcuni dei concetti
particolari di Analysis Services
7
Misure Derivate
•
Ottenute applicando operatori matematici a due o più valori appartenenti
alla stessa tupla: nell’espressione si usano solo attributi della Fact Table
•
Una misura derivata viene calcolata sugli eventi primari, ovvero prima di
effettuare l’aggregazione dei dati; quindi, al pari delle altre misure, anche
per le misure derivate si deve definire un operatore di aggregazione
•
In Analysis Service c’è il concetto di Misura Derivata
8
Misure Derivate : Esempio
•
Schema di Fatto ESAMI
– Dimensioni
• STUD (con in gerarchia FACOLTA) e DATA (con in gerarchia MESE)
– Misure
• BASE (Crediti di tipo Base) e ALTRO (Crediti di tipo ALTRO)
– Misure derivate
• RAPPORTO = BASE/ALTRO
• TOTALE = BASE + ALTRO
STUD
ING1
ING1
ING2
DATA
GEN1
GEN2
GEN2
BASE
20
40
30
ALTRO
SUM
SUM
2
4
6
FACOLTA
ING
ING
DATA
GEN1
GEN2
BASE
20
70
ALTRO
FACOLTA
ING
MESE
GEN
BASE
90
RAPPORTO
10
10
5
TOTALE
22
44
36
AVG
SUM
2
10
RAPPORTO
10
7,5
TOTALE
22
70
ALTRO
12
RAPPORTO
8,33
TOTALE
102
9
Misure Calcolate (concetto di Analysis Services)
•
Una misura calcolata è una misura il cui valore è calcolato a partire
da altre misure dopo aver aggregato i dati (quindi per una misura
calcolata non si definisce un operatore di aggregazione)
•
Esempio: Consideriamo lo schema di Fatto ESAMI
– Misure derivate
•
RAP_DER = BASE/ALTRO
– Misure calcolate
•
•
RAP_CALC = BASE/ALTRO
TOTALE = BASE + ALTRO
•
se la misura e’ distributiva (TOTALE), otteniamo lo stesso risultato sia come
misura derivata che come calcolata: la scelta si basa sull’efficienza del
calcolo (una misura calcolata “è più efficiente”)
•
se la misura non è distributiva (RAPPORTO), non si ottiene lo stesso
risultato e quindi si deve scegliere il modo opportuno
10
Esempio di Misure Calcolate
STUD
ING1
ING1
ING2
DATA
GEN1
GEN2
GEN2
BASE ALTRO
20
2
40
4
30
6
SUM
SUM
RAP_DER TOTALE RAP_CALC
10
22
10
10
44
10
5
36
5
AVG
FACOLTA
ING
ING
DATA
GEN1
GEN2
BASE
20
70
ALTRO
2
10
RAP_DER
10
7,5
TOTALE
22
80
FACOLTA
ING
MESE
GEN
BASE
90
ALTRO
12
RAP_DER
8,33
TOTALE
102
RAP_CALC
10
7
RAP_CALC
7,5
11
Operatore di Aggregazione Algebrico : AVG
•
In Analysis Services una misura con operatore di aggregazione
algebrico deve essere definito tramite una misura calcolata
•
Esempio : Prezzo Unitario (PU)
anno
trim.
categoria
pulizia
casa
tipo
detersivo
sapone
prodotto
Brillo
Sbianco
Lucido
Manipulite
Scent
2
1,5
–
1
1,5
anno
trim.
categoria
pulizia
casa
tipo
detersivo
sapone
media:
anno
trim.
categ oria
pulizia casa
I’99
1999
II’99 III’99
2
1,5
3
1,2
1,5
2,2
2
3
1,5
2
IV’99
2,5
2,5
3
1,5
–
I’99
1999
II’99 III’99 IV’99
1,75
1,25
1,50
2,17
1,35
1,76
2,40
1,75
2,08
2,67
1,50
2,09
I’99
1999
II’99 III’99 IV’99
1,50
1,84
2,14
2,38
12
Implementazione in Analysis Services
•
Una misura quale PU con operatore di aggregazione AVG deve essere
necessariamente definita come Misura Calcolata in quanto la devo calcolare
come SUM(PU)/count().
Allora:
1.
2.
3.
•
Si usa l’attributo PU per definire la misura PUBase aggregata con SUM;
Definisco la misura di supporto Conteggio, aggregata con COUNT; per definire il
conteggio posso usare un qualsiasi attributo della fact table
Definisco PU calcolata come PrezzoBase/Conteggio
Le due misure utilizzate per calcolare PU possono non essere visualizzate
13
ESEMPIO (pag 20 delle dispense)
Tipo
T1
T1
T2
Prodotto
P1
P2
P3
Tipo
T1
T2
•
•
Quantità
5
7
9
Sum
Prezzo
Guadagno
1,00
5,00
1,50
10,50
0,80
7,20
22,70
AVG
Quantitˆ
12
9
Prezzo
Guadagno
1,25
15,00
7,20
0,80
22.20
?
Non è possibile calcolare l’aggregazione a partire dalle aggregazioni
componente: il guadagno per il tipo T1 non lo posso ottenere
moltiplicando la quantità per il prezzo unitario
La soluzione corretta è sempre quella che si ottiene aggregando i dati
direttamente dalla vista primaria : definisco Guadagno come Misura
Derivata aggregata tramite la SUM
14
ESEMPIO: Implementazione in Analysis Services
•
Prezzo ha come operatore di aggregazione AVG, allora si definisce
come Misura Calcolata SUM(prezzo)/count(). Allora
1. Si usa l’attributo Prezzo per definire PrezzoBase aggregata con SUM;
2. Definisco la misura di supporto Conteggio, aggregata con COUNT
3. Definisco PREZZO calcolata come PrezzoBase/Conteggio
•
Si definisce la misura Guadagno come Misura Derivata
Prezzo*Quantità, con operatore di aggregazione Sum
•
Per confrontare (e verificare l’errore che si otterrebbe calcolare
l’aggregazione a partire dalle aggregazioni componente ) si
definisce anche una misura GuadagnoCalc come Misura Calcolata
Prezzo*Quantità
15
Misura Derivata: esempio dei biglietti
•
CostoMedioBiglietto (CMB) calcolato come INCASSO/NUM_BIG.
CodVolo
ALIT1
ALIT1
ALIT2

DATA
GEN1
GEN2
GEN2
Compagnia
ALITALIA
ALITALIA
DATA
GEN1
GEN2
Compagnia
ALITALIA
Mese
GEN
INCA SSO NUM_ BIG
20
2
40
4
30
6
SUM
SUM
INCA SSO
20
70
CMB
10
10
5
AVG
NUM_ BIG
2
10
CMB
10
7,5
INCA SSO NUM_ BIG CMB
90
12
8,33
Implementazione in Analysis Services
1.
2.
3.
Si definisce la misura derivata CMB_Base
Definisco la misura di supporto Conteggio, aggregata con COUNT
Definisco CMB calcolata come CMB_Base/Conteggio
16