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
Strumenti OLAP nell architettura di un DW
#
Architettura a 2 livelli (situazione simile a quella a 3 livelli )
Livello delle!
sorgenti!
Dati operazionali"
Dati esterni"
Strumenti ETL"
Data Warehouse!
Strumenti"
OLAP"
!
Livello di !
alimentazione!
Livello del!
warehouse!
Livello
di analisi
CUBO
Schema
Multidimensionale
Lo strumento OLAP analizza i dati del DW, ovvero dati che hanno
già una struttura multidimensionale (schema a stella/snowflake)
!
Ha una connessione dati con il DW
!
Non ha nessuna connessione con i Dati Operazionali
3
Cubi nelle architetture a 2 e 3 livelli
!
Dato uno schema di fatto ed il corrispondente schema logico
(stella/snowflake) si costruisce il cubo corrispondente, che è
praticamente definito in modo univoco essendo le scelte di
progettazione già state realizzate
!
Nel cubo
1.
Si aggiungono eventuali misure derivate e calcolate.
In particolare, per uno schema di fatto vuoto si introduce la
misura di conteggio degli eventi
2.
Si definiscono gli operatori di aggregazione
3.
Si definiscono alcuni aspetti specifici, quali il membro ALL per
le dimensioni
!
Dal punto di vista concettuale la definizione del cubo non richiede
altro; tuttavia nel costruire un cubo si possono gestire una serie di
altri concetti, in particolare quelli legati all efficienza del sistema
4
Architetture ad 1 livello
!
Questa architettura è caratterizzata dal fatto che al DW
non corrisponde un nuovo DB, ma lo schema logico del DW è
implementato attraverso delle viste nel DB Operazionale
Per la Fact Table si definirà
sicuramente una vista in
quanto occorre definire gli
eventi primari e le misure
!
Per le Dimension Table
1.
si può definire una vista
2.
si può usare una table del
DB Operazionale con
struttura simile
3.
Si può definire e costruire
direttamente nello
strumento OLAP
!
!
Dati operazionali"
Livello delle
sorgenti
Middleware" Livello del
warehouse
Strumenti"
OLAP"
Livello
di analisi
Lo strumento OLAP ha una connessione con il DB Operazionale
5
Architetture ad 1 livello
!
Problemi di efficienza
"
!
È ora evidente che le operazioni sul DB operazionali (OLTP, On-Line
Transactional Processing) e siano mischiate con quelle di tipo analitico
OLAP (OLAP, On-Line Analytical Processing)
Se si prescinde da questi aspetti di efficienza, un’architettura ad un
livello può essere utile per effettuare analisi semplici in modo
immediato, senza passare attraverso la realizzazione di un nuovo DB.
" Ad esempio, uno schema di fatto transazionale contenente solo
una dimensione data ed una o più dimensioni degenere può
essere implementato in modo immediata e semplice
6
Analysis Services : Editor del Cubo
!
Il sistema OLAP di Analysis Services, così come un qualsiasi
altro sistema OLAP, ha concetti e strumenti propri per definire
uno schema multidimensionale (un cubo), concetti e strumenti
più o meno sofisticati e spesso proprietari (cioè propri del
particolare sistema OLAP e differenti da sistema a sistema)
!
Ad esempio in Analysis Services lo strumento (automatico) per
definire le dimensioni consente di scegliere se usare uno
schema a stella oppure a fiocco di neve o altro, quale attributo
o espressione usare come nome di un livello e così via.
!
Vedere esempio DimensioneDistretto
$
In un’architettura a 2 o 3 livelli, dove è già stato progettato lo
schema di fatto e lo schema logico, di Analysis Services sono
sufficienti solo quei concetti/strumenti che ci consentono di
1.
Definire gli operatori di aggregazione per le misure
2.
Definire Misure Derivate e Calcolate
3.
Visualizzare i dati del fatto in modo multidimensionale,
effettuando operazioni di roll-up e drill-down
7
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
8
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:
!
!
!
Nel modello DFM la gerarchia degli attributi dimensionali di una
dimensione è un albero: un nodo può avere più figli
In AS la gerarchia degli livelli di una dimensione è un cammino
dell’albero : un nodo può avere al massimo un figlio
In AS 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)
9
DFM e AS : terminologie a confronto!
!
Negli schemi DFM, per gli attributi dimensionale si usa la terminologia
degli alberi: CITY è il padre di STATE
$
Un cammino dalla radice ad una foglia dell’albero individua un percorso
di navigazione sul quale effettuare roll-up e drill-down
!
In AS la relazione padre-figlio è riferita ai membri, e risulta “invertita”:
il membro EmiliaR (di STATE) è padre del membro RE (di CITY)
$
Nel linguaggio MDX sono definite diverse funzioni sulla
relazione padre-figlio : CHILDREN, DESCENDANT, !
!
I livelli descrivono la struttura gerarchica della dimensione dal livello
più alto – detto anche livello padre - (più aggregato)
al livello più basso – detto anche livello figlio - (più dettagliato)
dei dati.
Dalle Gerarchie del DFM ai Livelli di AS
!
Le dimensioni/livelli del cubo sono univocamente definite dalle
dimensioni/gerarchie dello schema di fatto
!
Data una gerarchia, per ogni cammino dalla dimensione (radice)
alle foglie si deve definire un dimensione nel cubo con un numero
di livelli pari alla lunghezza del cammino
" Se la gerarchia è un albero puro (no condivisioni/convergenze, no attributi
cross-dimensionali, no attributi multipli) nel cubo ci saranno un numero di
dimensioni pari al numero di foglie dell albero
Dimensione
VOLO-ORADIPARTENZA
Con livelli
VOLO
ORA_DI_PARTENZA
11
Dalle Gerarchie del DFM ai Livelli di AS!
gr up po di
mark eting
re pa rto
gerarchia
ca tego ria
tip o
città de lla mar ca
marc a
attributo
dimensionale
pr od otto
an no
gio rn o
va canz a
re spo nsa bile d elle vend ite
dist ret to d i ven dita
VE NDITA
da ta
tr ime stre mese
se ttima na
qu an tità v end uta
inca sso
nu m. client i
pr ezzo un itar io
!
!
Dimensione prodotto,
con tre cammini
1.
Prodotto % Tipo % GruppoDiMarketing
Prodotto % Tipo % Categoria % Reparto
Prodotto % Marca % CittaDellaMarca
2.
3.
ne go zio
città de l re gio ne sta to
ne go zio
Alla dimensione Prodotto corrispondono tre dimensioni di AS
(ovvero nel cubo VENDITA di AS si definiscono tre dimensioni)
1. [Prodotto-GruppoDiMarketing] o [GruppoDiMarketing]
Prodotto % Tipo % GruppoDiMarketing
2. [Prodotto-Reparto] o [Reparto]
Prodotto % Tipo % Categoria % Reparto
3. [Prodotto-CittaDellaMarca] o [CittaDellaMarca]
Prodotto % Marca % CittaDellaMarca
12
DFM e AS : terminologie a confronto!
!
Schema di Fatto DFM: relazione padre-figlio
Prodotto % Tipo % GruppoDiMarketing
Prodotto è padre di Tipo, Tipo è padre di GruppoDiMarketing
!
Cubo AS : relazione padre-figlio
[Prodotto-GruppoDiMarketing] o [GruppoDiMarketing]
Prodotto % Tipo % GruppoDiMarketing
GruppoDiMarketing è padre di Tipo, Tipo è padre di Prodotto
! GruppoDiMarketing
!!Tipo
!!!Prodotto
rappresentazione in AS
13
Dimensioni: livello (ALL) e membro ALL
!
Dimensione CITTA_ARRIVO con due livelli : Citta % Statto
CITTA
STATO
(ALL)
MARSIGLIA
FRANCIA
ALL
PARIGI
FRANCIA
ALL
LONDRA
INGHIL
ALL
...
...
!
!
Nella visualizzazione della dimensione, il
livello (ALL) è chiamato (Totale) ed il
membro ALL è totale CITTA_ARRIVO
!
Nelle proprietà della dimensione si può
eliminare il livello (ALL) (All level = No)
e cambiare il nome del membro ALL:
livelli
membri
14
Dimensioni: livello (ALL) e membro ALL
!
Per definizione, il membro ALL (e di conseguenza il livello (ALL)
che lo contiene) deve essere presente in ogni dimensione in
quanto consente di avere i totali per quella dimensione, ovvero di
visualizzare pattern senza la dimensione in questione
!
!
Non introdurre il membro ALL per una dimensione può comportare dei
vantaggi dal punto di vista dell efficienza: intuitivamente, si evita di
pre-calcolare i valori corrispondenti ad ALL
Se non si introduce il membro ALL, i totali per quella dimensioni
possono essere comunque calcolati attraverso espressioni MDX.
!
Nelle nostre dimensioni definiremo sempre il membro ALL!
!
Ricordiamo che è possibile ottenere
il membro ALL in SQL-OLAP,
attraverso group by with ROLLUP
Vendite
SELECT
case when(grouping(citta)=1)
then 'TOTALE' else Libro as Libro,
sum(numero) as numero,
sum(incasso) as incasso
FROM Vendite
group by Libro with ROLLUP
Dimensioni e livelli in AS!
!
Regola : Il numero dei membri di ogni livello deve essere sempre
maggiore o uguale al numero dei membri del livello padre superiore.
$ ogni livello deve determinare funzionalmente il livello padre
superiore
!
La regola può essere violata (genera un warning e non un errore)
Violare la regola significa realizzare una dimensione dove un livello
(esempio: Stato) non determina funzionalmente il livello padre
(esempio: Citta)
15
Dimensioni e livelli in AS!
PARTENZA
CodVolo% Da% Citta % Stato
Implementazione
errata
Implementazione
corretta
17
Nota!
!
!
Perché un warning e non un errore? Ha senso violare la regola?
Ha senso realizzare una dimensione dove un livello non determinare
funzionalmente il livello padre ?
genere
VENDITA
autore
libro
numero
incasso
data
mese anno
arco multiplo (AM)
!
Archi multipli: ha senso realizzare una dimensione dove un livello
(Libro) non determinare funzionalmente il livello padre (Autore) !
!
Ho più autori che libri
Il numero dei membri di Libro minore del numero dei membri del
livello padre autore % warning
altrimenti nessun warning!
18
Dalle Gerarchie del DFM ai Livelli di AS
!
Per costruire una dimensione/livelli di AS
1. Si considera lo schema logico
2. Si seleziona la Dimension table corrispodente ; nel caso di
snowflake schema si selezionano le dimension table
corrispondente e si verifica che siano correttamente legate
tramite join
3. Si definiscono i livelli (vedere esempi di dettaglio)
4. Si decide se tenere o meno un membro ALL
5. Si definiscono gli eventuali operatori di aggregazione particolari
per questa dimensione
!
Oltre alla struttura della dimensione si devono decidere altri aspetti,
in particolare se la dimensione può essere condivisa da più
dimensioni
19
Esempio : DimensioneDistretto
!
!
L esempio vuole evidenziare come l uso di AS su un DM già ben
progettato è molto semplice ed evita di dover utilizzare strumenti
propri di AS
Consideriamo un caso frequente, ovvero quello di un attributo
dimensionale derivante dalla composizione di più attributi
Esempio:
Nella Tabella Negozio, il distretto
di vendita è
STATO_DISTRETTO+NDISTRETTO
!
I valori di tale attributo
dimensionale vengono ricavati
tramite una vista e quindi
riportati in una Dimension_Table
del DM con struttura
DT_NEGOZIO(NOME,
NOMEDISTRETTO,
STATO_DISTRETTO)
20
Esempio : DimensioneDistretto
!
Realizzare tale dimensione in AS, partendo dalla DT_NEGOZIO è immediato:
1) si seleziona DT_NEGOZIO
2) si definiscono i tre livelli iniziando da StatoDistretto (2.a)
1)
!
2.a)
2.b)
2.c)
Visualizzazione della dimensione ottenuta:
21
Esempio : DimensioneDistretto
!
!
Realizzare tale dimensione in AS, senza usare DT_NEGOZIO ma direttamente su
NEGOZIO non è immediato in quanto occorre effettuare in AS quelle le
manipolazioni (ricavare il valore di NomeDistretto) effettuate invece in fase di
alimentazione del DM
In definitiva, molti strumenti propri di AS non sono indispensabili quando si parte
da un DM già ben progettato!
22
Operatore di aggregazione AVG in AS
!
CostoMedioBiglietto (CMB) aggregato tramite AVG
CodVolo
ALIT1
ALIT1
ALIT2
DATA
GEN1
GEN2
GEN2
Compagnia DATA
ALITALIA
GEN1
ALITALIA
GEN2
Compagnia Mese
ALITALIA
GEN
!
!
INCASSO NUM_BIG
20
2
40
4
30
6
SUM
SUM
INCASSO
20
70
INCASSO
90
CMB
10
10
5
AVG
NUM_BIG
2
10
NUM_BIG
12
CMB
10
7,5
CMB
8,33
In Analysis Services una misura con operatore di aggregazione
algebrico deve essere definito tramite una misura calcolata
Nel caso della media, essendo AVG(CM)=SUM(CMB)/count(*):
1. Si definisce la misura CMB_Base con oper. di aggregazione SUM
2. Si definisce la misura di supporto Conteggio, aggregata con COUNT
3. Si definisce CMB calcolata come CMB_Base/Conteggio
!
CMB_Base e Conteggio possono non essere visualizzate
23
Esempio di cubo: Ritardi_NEW!
!
Schema di Fatto Ritardi_New
!
Schema Logico snowFlake:
FACT TABLE
RITARDINEW(CODVOLO:VOLO,
ANNO,INIZIOMESE,
CITTAARRIVO:CITTA,
RITARDO,NUMRITARDI)
DIMENSION TABLEs
VOLO(CODVOLO,COMPAGNIA, AEROP_PART:AEROPORTO)
AEROPORTO(SIGLA, CITTA_PART:CITTA)
CITTA (CITTA,STATO)
!
In AS si definiscono 3 Dimensioni condivise (ad una gerarchia
corrispondono più dimensioni in AS, quindi il nome spesso
contiene il nome della foglia che si raggiunge)
1)
2)
3)
STATO_PARTENZA
STATO_ARRIVO oppure CITTA_ARRIVO (infatti c’è solo un
cammino)
COMPAGNIA
24
Database OLAP: Ritardi
!
Si crea un nuovo DB OLAP che conterrà tutti gli oggetti
multidimensionali (cubo, dimensioni condivise)
1)
Si definisce l’origine dei dati,
ovvero il collegamento al
DM
2)
Si seleziona la voce relativa
a SQL Server e quindi dall’elenco dei DB
disponibili - si seleziona il
DM
!
Se si apportano modifiche al DM, per
renderle visibili anche nel DB OLAP può
essere necessario aggiornare l’origine dati
25
Dimensione: STATO_PARTENZA
1)
Si seleziona la tabella
contenente la radice della
dimensione
2)
Si aggiungono le altre
tabelle che contengono gli
attributi dimensionale,
controllando le relazioni di
join
26
Dimensione: STATO_PARTENZA
1)
Si seleziona la tabella
contenente la radice della
dimensione
2)
Si aggiungono le altre
tabelle che contengono gli
attributi dimensionale,
controllando le relazioni di
join
27
Cubo Ritardi
!
Dopo aver generato tutte le dimensioni condivise, si passa alla
generazione del cubo
1)
Si userà l’editor (la
procedura guidata infatti è
basata su concetti propri di
AS !)
2)
Si seleziona la fact table; un
cubo è basato su una ed
una sola fact table
3)
Se la Fact Table è vuota si
ha un warning del tipo:
28
Cubo Ritardi
!
Dopo aver generato tutte le dimensioni condivise, si passa alla
generazione del cubo
4)
Si definiscono le dimensioni
degenerei ANNO e
INIZIOMESE
5)
Si inseriscono le dimensioni
condivise definite in
precedenza, una alla volta.
6)
Inserimento CITTA_ARRIVO: il
join con la fact table viene
automaticamente definito
considerando il nome, quindi
essendo nomi diversi non
viene automaticamente
generato
In ogni caso, meglio generare
manualmente i Join!
29
Cubo Ritardi
7)
Si genera il join. A questo
punto la dimensione
CITTA_ARRIVO per il cubo è
definita.
8)
Inserimento
STATO_PARTENZA.
Il sistema introduce tutte le
tabelle che servono per definire
la dimensione.
PROBLEMA: la tabella CITTA
viene usata due volte, in due
dimensioni.
Questo non è corretto, è come
se si stesse definendo una
convergenza.
Il sistema non consente questo
ciclo:
30
Cubo Ritardi
!
9)
Per evitare il problema
precedente, ogni
qualvolta c’è una tabella
che viene usata in più
dimensioni tramite
condivisione, si assegna
un alias a ciascuna
occorrenza
Inserimento dimensione
COMPAGNIA:
in questo caso COMPAGNIA è
nella stessa tabella VOLO,
quindi non è necessario
introdurre un alias per volo
31
Cubo Ritardi
!
9)
!
Per evitare il problema
precedente, ogni
qualvolta c’è una tabella
che viene usata in più
dimensioni tramite
condivisione, si assegna
un alias a ciascuna
occorrenza
Inserimento dimensione
COMPAGNIA:
in questo caso COMPAGNIA è
nella stessa tabella VOLO,
quindi non è necessario
introdurre un alias per volo
Tutte le dimensioni sono state inserite:
si noti il differente simbolo per quelle
condivise
32
Cubo Ritardi : misure
!
Misura addittiva NUMRITARDI,
la definizione è immediata
!
Per la misura Misura RITARDO, aggregata tramite AVG, si deve
usare una misura calcolata come spiegato a pagina 17.
Il procedimento è mostrato nella slide che segue
33
Cubo Ritardi : Misura Ritardo con AVG
34
Esempio : DimensioneDistretto
!
Consideriamo il pattern
STATO_PARTENZA.CITTA, CITTA_ARRIVO.STATO
e visualizziamo limitandoci a “CITTA_ARRIVO.STATO = ‘ITALIA’)
35
Esempio : DimensioneDistretto
!
Verifica dei risultati, calcolando le misure direttamente sugli eventi primari tramite
SQL:
36
Calcolo delle misure: ottimizzazione
!
!
!
Un significativo aumento delle prestazioni può essere
ottenuto precalcolando i dati aggregati di uso più comune
Misure definite con operatori Distributivi ed Algebrici
permettono di calcolare dati aggregati a partire direttamente
da dati parzialmente aggregati
L ottimizzazione usa il concetto di vista materializzata:
" Ogni pattern secondario corrisponde ad una vista sul pattern primario
" Vengono materializzate le viste (ovvero pre-calcolate e memorizzate in
tabelle) corrispondenti ad alcuni pattern secondari
" La scelta delle viste da materializzare è basata sul compromesso tra diversi
vincoli, i principali dei quali sono
• Tempo di costruzione ed aggiornamento delle viste materializzate
• Spazio a disposizione
$
Varie tecniche di ottimizzazione sono generalmente già
implementate nei sistemi OLAP e l utente può configurare
alcuni parametri, quali lo spazio a disposizione per
memorizzare i dati aggregati
$
Nel seguito vedremo velocemente questi concetti in AS
37
Archiviazione del cubo
!
Il calcolo delle viste materializzate avviene in fase di archiviazione del cubo
!
Noi abbiamo già scelto come modello di progettazione logica il modello ROLAP,
infatti stiamo considerando un DM implementato in un DB relazionale
Con ROLAP i dati restano nello star/snowflake schema e anche le aggregazioni
verranno messe in tabelle ..
!
38
Archiviazione del cubo
Questa interfaccia
consente di calcolare le a
!
Trascurare eventuali warning
!
39
Archiviazione del cubo
Le aggregazioni vengono
salvate in tabelle nel DM
!
!
!
Prendiamo una
di queste
tabelle
CON MOLAP le tabelle non vengono generate
40
