Esercizi Svolti Datawarehouse

Click here to load reader

  • date post

    14-Apr-2016
  • Category

    Documents

  • view

    60
  • download

    7

Embed Size (px)

description

esercizi svolti datawarehouse

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