Biglietti e Ritardi: schema E/R

1. Biglietti e Ritardi: schema E/R

2. Ritardi: Progettazione dello schema di Fatto • Definire uno schema di fatto per analizzare i ritardi; in particolare l’analisi deve considerare l’aeroporto di partenza, mentre per quello d’arrivo basta considerare solo la citta e lo stato • Si costruisce l’albero degli attributi basato sull’entità VOLOGIORN (tale entità ha come chiave {DATA,CODVOLO}) • Si modifica l’albero aggiungendo la dipendenza CITTA  STATO • Si modifica l’albero eliminando A-SIGLA

3. Ritardi: Progettazione dello Schema di Fatto • Progettazione Schema di Fatto • Si modifica l’albero eliminando CODVOLO per CITTA_ARRIVO, ovvero riportando CITTA_ARRIVO come figlio diretto della radice; questa modifica deriva dalla specifica di analizzare i ritardi direttamente rispetto alla città di arrivo e quindi di far diventare CITTA_ARRIVO una dimensione. Si noti che nell’albero non ho più la relazione che CODVOLO è padre di CITTA_ARRIVO, ovvero nelle analisi non potrò piu’ fare dei ronon potrò

4. Ritardi: Progettazione dello Schema di Fatto • Progettazione Schema di Fatto • Si definisce lo Schema di Fatto selezionando come dimensioni {DATA, CODVOLO, CITTA_ARRIVO} quindi tra le dimensioni ho tutti gli attributi chiave ovvero questo è uno schema transazionale • Si definisce la misura RITARDO e si suppone che essa sia aggregata rispetto a tutte le dimensioni tramite media: RITARDO (AVG) • Si considera CITTA, STATO come gerarchia condivisa : si noti che il ruolo della CITTA come figlio di AEROPORTO_PARTENZA è evidente, mentre per la dimensione occorre esplicitare il nome del ruolo CITTA_ARRIVO

5. Ritardi: Progettazione dello Schema di Fatto • Progettazione Schema di Fatto • Come ultimo passo devo definire il “glossario delle misure” ovvero devo stabilire come calcolare il valore delle misure per gli eventi primari; in questo caso lo schema è transazionale, quindi il valore della misura RITARDO corrisponde direttamente al valore dell’attributo RITARDO del DB operazionale e non occorre raggruppare rispetto alle dimensioni • Si definisce lo Schema di Fatto selezionando come dimensioni {DATA, CODVOLO, CITTA_ARRIVO} quindi tra le dimensioni ho tutti gli attributi chiave ovvero questo è uno schema transazionale • ERRATA/CORRIGE • Nelle dispense distribuite la misura RITARDO e’ definita raggruppando sulle dimensioni : {DATA, CODVOLO, CITTA_ARRIVO} • Questo è sbagliato, anche se il risultato non cambia: siccome DATA,CODVOLO è una chiave, in un gruppo del GROUP BY ci può essere al massimo una tupla e quindi il raggruppamento non ha effetto e la funzione aggregata (AVG,MIN, MAX) restituisce il singolo valore della misura RITARDO

6. Ritardi: Progettazione dello schema di Fatto a partire dallo schema relazionale del DB operazionale • Oltre allo schema E/R normalmente è disponibile anche lo schema logico (relazionale) del DB operazionale • Si suppone che i due schemi siano equivalenti (ovvero che questo sia lo schema relazionale ottenuto da un corretto progetto logico …).

7. Ritardi: Progettazione dello schema di Fatto a partire dallo schema relazionale del DB operazionale • Ovviamente lo schema logico relazionale è indispensabile nella fase di progettazione dell’alimentazione, durante la quale si deve conoscere l’effettiva struttura del DB dal quale verranno prelevati i dati … • Conviene effettuare la progettazione concettuale del Datawarehouse (gli schemi di fatto) a partire dallo schema relazionale? • Con uno schema E/R è più semplice la progettazione, essendo in esso evidenziate le associazioni e le relative cardinalità • A volte lo schema E/R non è disponibile ed occorre ricavarlo dallo schema logico secondo un procedimento di reverse engineering • In presenza dello schema relazionale si può utilizzare lo strumento Wand

8. Ritardi: Progettazione dello Schema di Fatto • Si considera il seguente schema logico per il DM

9. Ritardi: misure derivate • Supponiamo di voler analizzare anche il numero dei voli giornalieri che hanno subito un ritardo • Nel precedente schema si fatto si aggiunge una misura (INRITARDO) a valore booleano calcolata come if RITARDO <> 0 then INRITARDO = 1else INRITARDO = 0INRITARDOè una misura derivata che verrà aggregata tramite somma. • A che punto del progetto si introduce la misura INRITARDO? • Sicuramente conviene indicarla durante la progettazione concettuale, e quindi inserirla nello schema di fatto. Quindi si decide in che punto implementarla • Se l’espressione che definisce la misura è(facilmente) implementabile in Analysis Services, si può introdurla nella realizzazione dei cubi … • … altrimenti conviene introdurla già nella progettazione logica in modo da poterla calcolare (in SQL) e quindi memorizzare nella fact table

10. Ritardi: misure derivate • Modifichiamo la fact table Ritardi aggiungendo INRITARDO. • Come calcolare la misura INRITARDO in SQL? • Si aggiunge alla tabella VOLOGIOR e si calcola tramite update (vedi dispense su Esempio Biglietti pagina 14); questa soluzione mi costringe a modificare lo schema del DB operazionale e ovviamente non sempre è possibile • Si definisce tramite una vista nel DB Operazionale, o meglio, si introduce nella vista che definisce l’alimentazione del DM • Si definisce durante l’alimentazione del DataMart utilizzando il trasferimento dati basato su una opportuna query. • Verrà utilizzato il secondo metodo, perchè facilmente implementabile grazie all’istruzione CASE di SQL

11. Ritardi: Alimentazione del Data Mart - Fact Table • Alimentazione della fact table: raggruppo su DATA,CODVOLO e CITTA_ARRIVO; definiamo una view (nel DB dei Biglietti) che contiene questi attributi (si noti che per CITTA_ARRIVO devo fare un join con AEROPORTO). Considero dei join interni. • Salvo ed edito la view aggiungendogli il raggruppamento e calcolando RITARDO: CREATE VIEW dbo.VistaRitardi AS SELECT dbo.VOLOGIOR.DATA, dbo.VOLOGIOR.CODVOLO, dbo.AEROPORTO.CITTA AS CITTA_ARRIVO, AVG(RITARDO) AS RITARDO FROM dbo.VOLOGIOR INNER JOIN dbo.VOLO ON dbo.VOLOGIOR.CODVOLO = dbo.VOLO.CODVOLO INNER JOIN dbo.AEROPORTO ON dbo.VOLO.A = dbo.AEROPORTO.SIGLA GROUP BY dbo.VOLOGIOR.DATA, dbo.VOLOGIOR.CODVOLO, dbo.AEROPORTO.CITTA

12. Alimentazione del Data Mart: creazione di pacchetti DTS • Si svuota il contenuto del DM: è utile per testare le procedure di estrazione statica • Si copiano le dimensional table: l’unico vincolo da rispettare è quello dell’integrità referenziale: quando si copia la tabella A, devono essere già state copiate tutte le tabelle alle quali A si riferisce tramite una FK • Si definisce un pacchetto DTS per ogni passo. Siccome in ogni passo si devono semplicemente copiare delle tabelle è conveniente (è più semplice) creare tale pacchetto tramite “Importa Dati” • In uno star schema si possono copiare tutte le dimension table in un solo passo • Si copia il contenuto della vista nella fact table • Dopo aver creato e provato i pacchetti (package) per i singoli passi, si può creare un unico package che li include tutti, eseguendoli nell’ordine stabilito

13. Ritardi: Alimentazione del Data Mart - Fact Table • Alimentazione della fact table: raggruppo su DATA,CODVOLO e CITTA_ARRIVO; definiamo una view (nel DB dei Biglietti) che contiene questi attributi (si noti che per CITTA_ARRIVO devo fare un join con AEROPORTO). Considero dei join interni. • Salvo ed edito la view aggiungendogli il raggruppamento e calcolando RITARDO: CREATE VIEW dbo.VistaRitardi AS SELECT dbo.VOLOGIOR.DATA, dbo.VOLOGIOR.CODVOLO, dbo.AEROPORTO.CITTA AS CITTA_ARRIVO, AVG(RITARDO) AS RITARDO FROM dbo.VOLOGIOR INNER JOIN dbo.VOLO ON dbo.VOLOGIOR.CODVOLO = dbo.VOLO.CODVOLO INNER JOIN dbo.AEROPORTO ON dbo.VOLO.A = dbo.AEROPORTO.SIGLA GROUP BY dbo.VOLOGIOR.DATA, dbo.VOLOGIOR.CODVOLO, dbo.AEROPORTO.CITTA

14. Ritardi: Alimentazione del Data Mart - svuoto il DM • Si crea un pacchetto DTS tramite editor • Per prima cosa si inserisce la connessione al DM …

15. Ritardi: Alimentazione del Data Mart - svuoto il DM • Si crea un pacchetto DTS tramite editor • … e quindi si scrive l’istruzione SQL (si noti che occorre cancellare rispettando l’ordine delle FK)

16. Ritardi: Alimentazione del Data Mart - Dimension Table CITTA • Nel DB operazionale la città e lo stato sono specificati in AEROPORTO • Prendo i dati dal DB Biglietti e precisamente dalla tabella AEROPORTO. • Si effettua un “importa dati” basato sulla query select distinct CITTA,STATO from AEROPORTO

17. Ritardi: Alimentazione del Data Mart - Dimension Table CITTA

18. Ritardi: Alimentazione del Data Mart - Dimension Table CITTA

19. Ritardi: Alimentazione del Data Mart - Dimension Table CITTA

20. Ritardi: Alimentazione del Data Mart • Si salva il pacchetto per alimentare Citta • Si crea un pacchetto per alimentare AEROPORTO nel DM • Si effettua un “importa dati” basato sulla query select SIGLA CITTA from AEROPORTO • Non serve il distinct perchè SIGLA è chiave • Si può fare anche senza la query, importando direttamente la tabella • Nello stesso modo si crea un pacchetto per alimentare VOLO nel DM • Si crea un pacchetto per alimentare RITARDI nel DM prendendolo dalla vista creata a pagina 5

21. Ritardi: Alimentazione del Data Mart - Pacchetto complessivo • Si crea un pacchetto DTS complessivo di tutti I pacchetti creati finora, in cui viene imposto l’ordine di esecuzione • Ogni pacchetto viene inserito tramite “Attività Esegui Pacchetto” che viene collegata al pacchetto creato in precedenza

22. Ritardi: Alimentazione del Data Mart - Pacchetto complessivo • … si inserisce il pacchetto per copiare i dati da Città …

23. Ritardi: Alimentazione del Data Mart - Pacchetto complessivo • E quindi si crea il flusso di lavoro tra I due pacchetti:

24. Ritardi: Alimentazione del Data Mart - Pacchetto complessivo • Alle varie “Attività Esegui Pacchetto” si può dare un nome (usando le proprietà)