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 (AS)
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
Schemi multi-dimensionali in AS
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
3
Schemi multi-dimensionali in AS
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:
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 :
Le misure sono considerate come membri di una dimensione
speciale chiamata Measures (presente in tutti i cubi)
4
Confronto tra DFM e AS
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)
Misure : corrispondono al concetto di misura del modello DFM
Consideriamo lo Schema di Fatto BIGLIETTI e la (parte di) dimensione dei
voli, con la relativa gerarchia. In AS avremo un cubo BIGLIETTI e le
dimensioni rispetto alle quali si analizzaranno i dati
Dimensione VOLO-COMPAGNIA
Dimensione VOLO-ORADIPARTENZA
5
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
6
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
7
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
8
Misure Calcolate (concetto di AS)
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
9
Esempio di Misure Calcolate
STUD
ING1
ING1
ING2
DATA
GEN1
GEN2
GEN2
BASE
20
40
30
SUM
ALTRO
2
4
6
SUM
RAP_DER TOTALE
10
22
10
44
5
36
RAP_CALC
10
10
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
10
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.
categoria
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
11
Implementazione di AVG in AS
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:
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
3. Definisco PU calcolata come PrezzoBase/Conteggio
1.
2.
Le due misure utilizzate per calcolare PU possono non essere
visualizzate
12
ESEMPIO (pag 20 delle dispense originali)
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
15,00
1,25
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
13
ESEMPIO: Implementazione in AS
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à
14
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
15
Misura Derivata: esempio dei biglietti
Implementazione in Analysis Services
1.
Si definisce la misura derivata CMB_Base come INCASSO/NUM_BIG.
Importante: CMB_BASE deve essere definita come real (ad esempio
double) in modo da fare la divisione tra reali e non tra interi:
2.
Definisco la misura di supporto Conteggio, aggregata con COUNT
16
Misura Derivata: esempio dei biglietti
3. Definisco CMB calcolata
come CMB_Base/Conteggio
Per visualizzare anche le
cifre decimali, impostare il
format string come in figura
In questo modo la visualizzazione del cubo risulterà essere
17
Misura Derivata: esempio dei biglietti
Come facciamo a verificare che il risultato sia corretto?
Si può procedere valutando il calcolo in alcuni casi, ma la verifica
teorica effettiva è quella di calcolare la misura direttamente sugli eventi
primari nel DM!
Nell’esempio, raggruppiamo sullo stato di arrivo e calcoliamo
AVG(INCASSO/NUM_BIGLIETTI) con la seguente query SQL
SELECT
ARRIVO.STATO AS ARRIVAA,
AVG(INCASSO/NUM_BIGLIETTI) AS CMB
FROM
BIGLIETTI INNER JOIN
VOLO ON BIGLIETTI.COD_VOLO = VOLO.CODVOLO INNER JOIN
AEROPORTO ARRIVO ON VOLO.A = ARRIVO.SIGLA
GROUP BY ARRIVO.STATO
Il risultato che si ottiene è
IMPORTANTE: nella tabella BIGLIETTI occorre definire gli attributi
INCASSO e NUM_BIGLIETTI come decimal e non come integer altrimenti il
rapporto INCASSO/NUM_BIGLIETTI in AGV(INCASSO/NUM_BIGLIETTI)
viene calcolato come divisione tra interi e quindi viene approssimato.
18
Misura aggregabile con differenti operatori
Per una misura si possono definire differenti operatori di
aggregazione per le differenti dimensioni
reparto
categoria
tipo
peso
confezione
marca
unità per pallet
prodotto
indirizzo
INVENTARIO
anno mese data
AVG,
MIN
livello
magazzino città
quantità ingresso
stato
La misura Livello è
addittiva sulle dimensioni
Prodotto e Magazzino,
mentre rispetto alla
dimensione Data si
possono usare gli
operatori AVG e MIN
19
Misura aggregabile con differenti operatori
Livello aggregato tramite SUM su Magazzino e tramite MIN su Data
3
{Magazzino, Data}
Citt
RE
Magazzino
M1
M2
{Magazzino, Mese}
Citt
RE
Magazzino
M1
M2
2 /3
25
30
{Città, Data}
3
Mese
Citt
RE
1/3
50
2 /3
55
Mese
Data
Per il Pattern {Città, Mese} ho due possibilità:
1.
2.
3
10
30
1/3
10
40
Mese
Data
Minimo della Somma = 50
Somma dei Minimi = 40
In AS (tramite il linguaggio MDX) è possibile definire
formule personalizzate di rollup per i vari livelli di una dimensione
l’ordine di priorità per stabilire a quale aggregazione dare la precedenza
20
In AS: Livello aggregato con SUM su Magazzino e MIN su Data
Definisco Livello aggregato tramite SUM : tale aggregazione si applica
per tutte le dimensioni a meno di non Personalizzare il rollup
Per la dimensione Data si personalizza il rollup per il livello Mese usando
l’operatore MIN : Min([Data].CurrentMember.Children)
Nota: Le dimensioni contengono il livello speciale (ALL) che contiene il solo
membro All (che viene mostrato come Totale <NomeDimensione>) che
denota tutti i membri della dimensione: è possibile non visualizzare tale
membro usando “No” nella proprietà All Level della dimensione!
21
Ordine di valutazione
Nell’esempio precedente, utilizzando l’ordine di valutazione di default,
per il pattern {Città, Mese} viene riportato il minimo della somma in
quanto, una formula personalizzata di rollup ha un ordine di valutazione
più alto, quindi viene valutato alla fine
Vedremo che tale ordine di valutazione potrà essere modificato utilizzando la
clausola SOLVE_ORDER di MDX
Per concludere, in MDX sono disponibili altri operatori per aggregare i dati.
Ad esempio, si può aggregare su Data usando la Mediana:
22
