Esercizi Svolti Datawarehouse
date post
14-Apr-2016Category
Documents
view
60download
7
Embed Size (px)
description
Transcript of Esercizi Svolti Datawarehouse
1
ESERCIZI Data Warehousing 1 ESERCIZI - PROGETTAZIONE DI UN DW ................................................................................................... 2
1.1 Esercizio: Spedizione ............................................................................................................................. 2 1.1.1 Soluzione .......................................................................................................................................... 4 1.1.2 Variante 1 ....................................................................................................................................... 11 1.1.3 Variante 2 (misura derivata) .......................................................................................................... 11 1.1.4 Variante 3: (schema transazionale) ................................................................................................ 12 1.1.5 Soluzioni possibili per la misura NUMERO (conteggio eventi primari) ........................................... 14 1.1.6 Soluzioni possibili per la misura COSTO (misura con AVG in schema transazionale) .................. 15
1.2 Esercizio: Dettaglio Ordine .................................................................................................................. 19 1.2.1 Soluzione ........................................................................................................................................ 19
1.3 Esercizio: Biglietto ................................................................................................................................. 22 1.3.1 Soluzione ........................................................................................................................................ 24 1.3.2 Variante .......................................................................................................................................... 26 1.3.3 Variante (dimensione derivante da discretizzazione) .................................................................... 27
1.4 Esercizio : Esame ................................................................................................................................. 29 1.4.1 Soluzione ........................................................................................................................................ 30
1.5 Esercizio: Vendita ................................................................................................................................. 32 1.5.1 Soluzione ........................................................................................................................................ 32
1.6 Esercizio (19/12/2012) .......................................................................................................................... 34 1.7 Esercizio (14/01/2013 ) ......................................................................................................................... 35 1.8 Esercizio (16/04/2013 ) ......................................................................................................................... 36 1.9 Esercizio (10/09/2013 ) ......................................................................................................................... 37
Sistemi Informativi Avanzati Anno Accademico 2013/2014 Prof. Domenico Beneventano
2
1 ESERCIZI - Progettazione di un DW Per alcuni esercizi viene anche riportato il codice SQL per provare le soluzioni date. La Fact Table e le dimension table sono definite come viste nel DBO dato (architettura ad un livello)
1.1 Esercizio: Spedizione Consideriamo un DBO con il seguente schema E/R ed il corrispondente schema relazionale (nello schema relazionale ci possono essere vincoli di integrit aggiuntivi):
CITTA(CITTA,REGIONE,STATO) DF: REGIONE STATO DATA(DATA,MESE,ANNO) DF: MESE ANNO ORDINE(ORDINE,CLIENTE:CLIENTE,DATA:DATA) REPARTO(REPARTO,MAGAZZINO:MAGAZZINO) MAGAZZINO(MAGAZZINO,CITTA:CITTA) CLIENTE(CLIENTE,CITTA:CITTA) SPEDIZIONE(NRIGA,ORDINE:ORDINE, PRODOTTO, REPARTO:REPARTO DATASPEDIZIONE:DATA, COSTO) AK : { ORDINE,PRODOTTO, REPARTO,DATASPEDIZIONE }
Viene richiesto di:
A) Progettazione concettuale : Progettazione dello schema di fatto SPEDIZIONE con dimensioni {PRODOTTO,MAGAZZINO,CLIENTE,DATASPED} e misure COSTO: il costo medio di DETTAGLIO.COSTO
NUMERO: il numero complessivo di spedizioni
NUMERO_ORDINI: il numero complessivo di ordini
B) Progettazione logica : Progettazione dello STAR SCHEMA e SNOWFLAKE SCHEMA
C) Alimentazione : Scrivere in SQL lalimentazione della fact-table.
ORDINE
DATA (0,N)
(1,1)
DETTAGLIO (1,N)(1,1)
NRIGA
DA
(1,N)
(1,1)
REPARTO
IN
(1,N)
(1,1)
MAGAZZINO
DEL
(1,N)
(1,1)
CLIENTE
CITTAIN (0,N)(1,1)
INDIRIZZO
(0,N)
(1,1)
DATA
REPARTO
MAGAZZINO
COSTO
MESE
ANNO
CITTA
STATO
PRODOTTO
(0,N)
(1,1)
DATASPEDIZIONE
CLIENTE
REGIONE
ORDINEDATA
ORDINE
!
SPEDIZIONE
3
Esempio di istanza del DBO e corrispondente istanza del fatto (eventi primari)
Eventi Primari del Fatto SPEDIZIONE con dimensioni
PRODOTTO, MAGAZZINO, CLIENTE, DATASPED
4
1.1.1 Soluzione Progettazione Concettuale
Nella progettazione concettuale occorre riportare uno schema di fatto con tutte le dimensioni richieste e, per ciascuna dimensione, con tutta la gerarchia derivante dallo schema del DBO.
Albero degli attributi iniziale
5
INNESTO su REPARTO ed ORDINE
Anche DATA dellORDINE viene POTATA
Si aggiungono le seguenti dipendenze funzionali, non presenti nello schema ER:
REGIONE STATO
MESE ANNO
6
Si ottiene
Viene POTATA anche NRIGA
7
Scelta delle dimensioni
Viene quindi creato lo schema di fatto, considerando una condivisione su CITTA.
8
Fatto: SPEDIZIONE(NRIGA,ORDINE,PRODOTTO,REPARTO, DATASPEDIZIONE,COSTO) AK : { ORDINE,PRODOTTO, REPARTO,DATASPEDIZIONE }
Dimensioni: {PRODOTTO,MAGAZZINO,CLIENTE,DATASPED}
Granularit: Temporale Dipendenze funzionale tra le dimensioni: Nessuna Misure normali
NUMERO= COUNT(*), additiva NUMERO_ORDINI = count(distinct ORDINE), additiva rispetto a CLIENTE, in quanto ORDINE CLIENTE Quindi NUMERO_ORDINI aggregabile solo rispetto a CLIENTE, pertanto linsieme delle dimensioni rispetto alle quali non aggregabile NA: {PRODOTTO,MAGAZZINO,DATASPED}
Misure calcolate COSTO = COSTO_SUM/COSTO_COUNT dove COSTO_SUM = SUM(COSTO), additiva COSTO_COUNT = COUNT(COSTO), additiva Fact Table FACT_TABLE(PRODOTTO,MAGAZZINO,CLIENTE,DATASPED,
NUMERO,COSTO_SUM,COSTO_COUNT, NUMERO_ORDINI) In questa fase viene anche indicata una Fact Table con struttura semplificata (senza riferimenti alle Dimension Table) per riassumere quali sono le dimensioni del fatto e le misure che si devono considerare. Riassumere quali sono tutte le misure utile anche per controllare se tra tutte queste misure ce ne sono alcune equivalenti. Ad esempio, in questo caso sono state introdotte e riportate nella Fact Table due misure
NUMERO= COUNT(*), additiva COSTO_COUNT = COUNT(COSTO), additiva Sono queste due misure equivalenti ? Se la risposta positiva, nella Fact Table verr inserita una sola misura.
CLIENTE
SPEDIZIONENUMERO(C) COSTO (AVG)NUMERO_ORDINI
CITTAREGIONE
DATASPED
MAGAZZINO
STATO
MESE
PRODOTTOANNO
9
Progettazione Logica
Nella Progettazione logica viene richiesto di delineare sia lo STAR SCHEMA che lo SNOWFLAKE SCHEMA; La Fact Table in questi due tipologie di schemi coincide (mentre quelle che cambiano sono ovviamente le Dimension Table) quindi possiamo riportare la Fact Table una sola volta.
La Fact Table ha la seguente struttura
FACT_TABLE(PRODOTTO,MAGAZZINO,CLIENTE,DATASPED, NUMERO,COSTO_SUM,COSTO_COUNT, NUMERO_ORDINI)
Come detto in precedenza, dobbiamo chiederci se le due misure
NUMERO= COUNT(*), additiva COSTO_COUNT = COUNT(COSTO), additiva
sono equivalenti, ovvero se il loro valore coincide. Quindi si dovrebbe controllare se COUNT(*) e COUNT(COSTO) restituiscono lo stesso valore: la risposta negativa se nel DBO il campo COSTO pu assumere dei valori NULL, in quanto per definizione un valore NULL viene conteggiato da COUNT(*) ma non viene conteggiato da COUNT(COSTO). Nei nostri esercizi si suppone il caso generale della presenza di NULL pertanto nella Fact Table verranno sempre tenute entrambe le misure.
La Fact Table sar quindi
FACT_TABLE(CLIENTE:dtCLIENTE,MAGAZZINO:dtMAGAZZINO,DATASPED:dtDATA,PRODOTTO, NUMERO,COSTO_SUM,COSTO_COUNT, NUMERO_ORDINI)
Si noti che la dimensione PRODOTTO degenere, quindi non viene inserita la relativa Dimension Table.
Star Schema dtDATA(DATA,MESE,ANNO) dtCLIENTE(CLIENTE,CITTA,REGIONE,STATO) dtMAGAZZINO(MAGAZZINO,CITTA,REGIONE,STATO)
SnowFlake Schema
dtDATA(DATA,MESE:dtMESE) dtMESE(MESE,ANNO)
dtCLIENTE(CLIENTE,CITTA:dtCITTA) dtMAGAZZINO(MAGAZZINO,CITTA:dtCITTA)
dtCITTA(CITTA,REGIONE:dtREGIONE) dtREGIONE(REGIONE,STATO)
10
Alimentazione
La query di alimentazione della fact table costituita come segue: FROM: oltre alla tabella SPEDIZIONE, si deve considerare la tabella REPARTO (che contiene MAGAZZINO) e la tabella ORDINE (che contiene CLIENTE). GROUP BY: banale, contiene le dimensioni del fatto SELECT: oltre alle dimensioni del fat