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 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
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.
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
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
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
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