data warehousing con sql server
DESCRIPTION
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 - PowerPoint PPT PresentationTRANSCRIPT
1
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
2
Analysis Services (AS)
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
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)
3
Schemi multi-dimensionali in AS
Dimensioni e attributi dimensionali si chiamano livelli
I valori delle dimensioni e degli attributi dimensionali si dicono membri
dimensione STORE
livelli
(ALL)
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
membri
4
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)
Schemi multi-dimensionali in AS
5
Confronto tra DFM e AS
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
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
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 DATA BASE ALTRO RAPPORTO TOTALEING1 GEN1 20 2 10 22ING1 GEN2 40 4 10 44ING2 GEN2 30 6 5 36
FACOLTA DATA BASE ALTRO RAPPORTO TOTALEING GEN1 20 2 10 22ING GEN2 70 10 7,5 70
SUM SUM SUMAVG
FACOLTA MESE BASE ALTRO RAPPORTO TOTALEING GEN 90 12 8,33 102
9
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
10
Esempio di Misure Calcolate
STUD DATA BASE ALTRO RAP_DER TOTALE RAP_CALC ING1 GEN1 20 2 10 22 10 ING1 GEN2 40 4 10 44 10 ING2 GEN2 30 6 5 36 5
FACOLTA DATA BASE ALTRO RAP_DER TOTALE RAP_CALCING GEN1 20 2 10 22 10ING GEN2 70 10 7,5 80 7
SUM SUM
FACOLTA MESE BASE ALTRO RAP_DER TOTALE RAP_CALCING GEN 90 12 8,33 102 7,5
AVG
11
Operatore di Aggregazione Algebrico : AVG
anno 1999trim. I’99 II’99 III’99 IV’99
categ oria tipo prodottoBrillo 2 2 2,2 2,5
Sbianco 1,5 1,5 2 2,5detersivoLucido – 3 3 3
Manipulite 1 1,2 1,5 1,5
puliziacasa
saponeScent 1,5 1,5 2 –
anno 1999trim. I’99 II’99 III’99 IV’99
cat e goria tipodetersivo 1,75 2,17 2,40 2,67pulizia
casa sapone 1,25 1,35 1,75 1,50media: 1,50 1,76 2,08 2,09
anno 1999trim. I’99 II’99 III’99 IV’99
categ oriapulizia casa 1,50 1,84 2,14 2,38
In Analysis Services una misura con operatore di aggregazione algebrico deve essere definito tramite una misura calcolata
Esempio : Prezzo Unitario (PU)
12
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:1. Si usa l’attributo PU per definire la misura PUBase aggregata con SUM;2. 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
Le due misure utilizzate per calcolare PU possono non essere visualizzate
13
ESEMPIO (pag 20 delle dispense originali)
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
Tipo Prodotto Quantità Prezzo Guadagno T1 P1 5 1,00 5,00 T1 P2 7 1,50 10,50 T2 P3 9 0,80 7,20 22,70
SumSum AVGAVG ??
Guadagno 15,00
7,20
22.2022.20
Tipo Quantità PrezzoT1 12 1,25T2 9 0,80
14
ESEMPIO: Implementazione in AS
Prezzo ha come operatore di aggregazione AVG, allora si definisce come Misura Calcolata SUM(prezzo)/count(). Allora1. Si usa l’attributo Prezzo per definire PrezzoBase aggregata con
SUM;2. Definisco la misura di supporto Conteggio, aggregata con
COUNT3. 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 DATA INCASSO NUM_BIG CMB ALIT1 GEN1 20 2 10 ALIT1 GEN2 40 4 10 ALIT2 GEN2 30 6 5
Compagnia DATA INCASSO NUM_BIG CMB ALITALIA GEN1 20 2 10 ALITALIA GEN2 70 10 7,5
Compagnia Mese INCASSO NUM_BIG CMB ALITALIA GEN 90 12 8,33
SUM SUM AVG
Implementazione in Analysis Services1. Si definisce la misura derivata CMB_Base2. Definisco la misura di supporto Conteggio, aggregata con COUNT3. Definisco CMB calcolata come CMB_Base/Conteggio
16
Misura Derivata: esempio dei biglietti
Implementazione in Analysis Services1. 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
17
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
18
Misura Derivata: esempio dei biglietti
Il risultato che si ottiene è
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 CMBFROM BIGLIETTI INNER JOIN VOLO ON BIGLIETTI.COD_VOLO = VOLO.CODVOLO INNER JOIN AEROPORTO ARRIVO ON VOLO.A = ARRIVO.SIGLAGROUP BY ARRIVO.STATO
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.
19
Misura aggregabile con differenti operatori
Per una misura si possono definire differenti operatori di aggregazione per le differenti dimensioni
stato
INVENTARIO
livello
quantità ingresso
magazzino città
indirizzo
data
AVG,
MIN
unità per pallet
prodotto
tipo
reparto
peso
confezione
marca
categoria
meseanno
La misura Livello è addittiva sulle dimensioni Prodotto e Magazzino, mentre rispetto alla dimensione Data si possono usare gli operatori AVG e MIN
20
Misura aggregabile con differenti operatori
Livello aggregato tramite SUM su Magazzino e tramite MIN su Data
3 Città Magazzino 1 / 3 2 /3
Mese Data
M1 10 25 RE M2 40 30
{Magazzino, Data}
Città Magazzino 3 M1 10 RE
M2 30
Mese
{Magazzino, Mese} 3
Città 1 / 3 2 /3 RE 50 55
Mese Data
{Città, Data}
Per il Pattern {Città, Mese} ho due possibilità:1. Minimo della Somma = 50
2. 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
21
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!
22
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: