Data warehousing con SQL Server

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) • 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

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

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

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

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

9. Misure Calcolate (concetto di AS) • Una misura calcolata è una misura il cui valore è calcolato a partire da altre misure dopoaver 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. SUM SUM AVG Esempio di Misure Calcolate

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)

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: • 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. ? Sum AVG 22.20 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

14. ESEMPIO: Implementazione in AS • Prezzo ha come operatore di aggregazione AVG, allora si definisce come Misura Calcolata SUM(prezzo)/count(). Allora • Si usa l’attributo Prezzo per definire PrezzoBase aggregata con SUM; • Definisco la misura di supporto Conteggio, aggregata con COUNT • 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. SUM SUM AVG Misura Derivata: esempio dei biglietti • CostoMedioBiglietto (CMB) calcolato come INCASSO/NUM_BIG. • Implementazione in Analysis Services • Si definisce la misura derivata CMB_Base • Definisco la misura di supporto Conteggio, aggregata con COUNT • Definisco CMB calcolata come CMB_Base/Conteggio

16. Misura Derivata: esempio dei biglietti • Implementazione in Analysis Services • 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: • Definisco la misura di supporto Conteggio, aggregata con COUNT

17. Misura Derivata: esempio dei biglietti 3. Definisco CMB calcolata come CMB_Base/ConteggioPer 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 • 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 • 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.

19. Misura aggregabile con differenti operatori • Per una misura si possono definire differenti operatori di aggregazione per le differenti dimensioni • La misura Livello è addittiva sulle dimensioni Prodotto e Magazzino, mentre rispetto alla dimensione Data si possono usare gli operatori AVG e MIN

20. {Città, Data} {Magazzino, Mese} Misura aggregabile con differenti operatori • Livello aggregato tramite SUM su Magazzino e tramite MIN su Data • {Magazzino, Data} • Per il Pattern {Città, Mese} ho due possibilità: • 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

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: