La fase di estrazione consiste nell’acquisizione dei dati da numerose fonti tra loro eterogenee che possono basarsi su tecnologie diverse presentandosi sia come fonti relazionali, sia non relazionali. La fase di estrazione, inoltre, deve tener conto
CAPITOLO 3. CASO DI STUDIO - ETL
delle caratteristiche della base di dati sorgente e dei singoli campi da estrarre: non è opportuno riportare campi che non verranno utilizzati soprattutto quando si tratta di una grande mole di dati.
Le fonti di dati per questo progetto di tesi sono le tabelle del DB transazionale dell’azienda committente presenti su MS SQL server.
Ogni operatore dell’azienda, come già introdotto, è dotato di un device dove annota i dettagli dell’operazione che sta eseguendo: le informazioni sul contatore, i timestamp di inizio e fine di ogni operazione, esito dell’operazione e descrizione facoltativa della stessa.
Tutti i dati registrati in real time vengono inviati al DB transazionale di MS SQL server e catalogati in diverse tabelle arricchiti con i dati già presenti su DB (tabelle anagrafiche). Per l’analisi richiesta dalla committenza non si ha necessità di utilizzare tutte le tabelle presenti, quelle ritenute utili sono18:
• LETTURA, indica tutte le informazioni dettagliate relative ad una singola lettura: quale operatore l’ha eseguita, a quale contatore è riferita, il tipo di operazione svolta, il timestamp della lettura attuale e quella precedente ecc. • TEMPOLETTURA, contiene le informazioni aggiuntive sui tempi della lettura
effettuata: CodLetturista, IDLettura, Data, TipoChiusura e TempoLettura. • TIPOLETTURA identifica la tipologia di lettura ad esempio se è su appunta-
mento o massiva.
• CONTATORE, include tutte le informazioni relative al contatore. Tra cui l’indirizzo in cui si trova, l’intestatario, l’anno di installazione, modello e marca. • CHIUSURA, contiene le informazioni relative all’esito dell’operazione con
annessa descrizione breve. L’esito viene categorizzato in tre risultati: ’A’ cliente Assente, ’P’ operazione conclusa positivamente e ’N’ operazione conclusa negativamente (es. impianto inaccessibile, cliente sconosciuto, indirizzo errato); • APPALTO, include tutte le informazioni in riferimento all’azienda appaltatrice; • CONTRATTO mette in relazione un contratto con il rispettivo appalto;
18Per semplicità i nomi delle tabelle e dei campi sono state sintetizzate e/o modificate rispetto
CAPITOLO 3. CASO DI STUDIO - ETL
• OPERAZIONE contiene l’IDoperazione e la sua rispettiva Descrizione; • LOTTO e VALIDAZIONE LOTTO contengono un dettaglio relativo alla
ripartizione delle letture rilevate;
• COMUNE, contiene le informazioni generali su tutti i comuni in cui l’azienda opera.
3.3
Processo di ETL
Per il caso di studio in esame è stato realizzato un processo di ETL (job) svolto su SDS. È bene sottolineare che, per attenersi agli obiettivi prefissati, è necessaria una conoscenza strutturale sia dello schema dei dati già presenti nel Data Warehouse e sia dello schema del database operazionale per garantire la coerenza dei dati che verranno utilizzati per alimentare il data mart. Avendo un’unica fonte dati non vi è stata la necessità di integrare gli stessi con un processo di Data Integration. Un Job è l’unico oggetto in Data Services che puo’ essere eseguito e testato in modalità manuale o pianificata, esso è formato da un workflow che definisce il processo decisionale per l’esecuzione dei suoi dataflow. In generale, durante l’esecuzione di un Job, il ruolo di un workflow è di organizzare l’esecuzione temporale dei dataflow, gestire la configurazione dell’ambiente di esecuzione prima e dopo che i dataflow siano terminati e prendere percorsi alternativi in presenza di determinate condizioni. Il job in analisi è formato da sette workflow collegati tra loro, come mostrato nella Figura 3.3, che verranno di seguito descritti.
CAPITOLO 3. CASO DI STUDIO - ETL
3.3.1
WF: Import tabelle
All’interno del primo workflow è contenuto un dataflow che permette di importare i dati presenti sul server del DB transazionale sul server del DB che costituirà la base di dati del DW. Prendendo in esame ciascuna tabella, si è stabilito quali campi riportare nel DW e quali chiavi assegnare ad ogni tabella. Dopo aver effettuato una serie di test, si è preferito effettuare un filtro iniziale del campi di interesse per evitare un inutile dispendio di risorse, di tempi per il caricamento dei dati e migliorare la leggibilità ai fini della validazione dei dati. Il filtro è stato settato mantenendo una granularità del dettaglio massima. Come mostrato in Figura 3.4, i nodi coinvolti all’interno del dataflow sono quelli delle tabelle di input e output collegate ad un nodo di query che ci permette di filtrate e mappare le colonne.
CAPITOLO 3. CASO DI STUDIO - ETL
3.3.2
WF: Min IDLettura
Nel secondo workflow abbiamo due dataflow sequenziali. Il fine ultimo è quello di conoscere (per una data commessa) la lettura minima registrata dato un tipo specifico di operazione, stato di lettura e esito uguale a ’A’ (Assente) e ’P’ (Positivo). Il primo dataflow è formato da tre tabelle (LETTURA, CONTATORE e CHIUSU- RA) di input, messe in relazione tra di loro per avere non solo il dettaglio della lettura ma anche l’arricchimento dello stesso con le informazioni relative ai contatori, come da richiesta. L’esito di queste operazioni è stato salvato in un’unica tabella di output(LETTURECONTATORE) avente le seguenti informazioni (Figura 3.5): ID- Lettura, IDContatore, Stato, TipoOperazione, Esito, CodAppalto e ChiaveContatore. Il secondo dataflow prende in input la tabella precedentemente creata e ne esegue una query (Figura 3.6) che raggruppa l’IDContatore, CodAppalto e ChiaveContatore estraendo il valore minimo di IDLettura. In questo modo, la tabella di output, avrà per ogni contatore di quell’appalto una sola lettura in modo univoco.
Figura 3.5: Contenuto primo DF
3.3.3
WF: Tabelle Pivot
Nel workflow seguente abbiamo un’importazione ma, diversamente dal workflow descritto precedentemente, le tabelle che vengono importate sono tabelle pivotizzate. Una tabella pivot è uno strumento analitico e di reporting necessario alla creazione
CAPITOLO 3. CASO DI STUDIO - ETL
Figura 3.6: Esempio Query di raggruppamento
di tabelle riassuntive. Uno dei fini principali di queste tabelle è l’organizzazione di dati complessi tramite una scelta opportuna dei campi e degli elementi che devono comporla. Inoltre è possibile scambiare le righe e le colonne per poter visualizzare riepiloghi diversi dei dati di origine, filtrare i dati o aggregarli per poter ottenere dei subtotali.
Per la creazione delle tabelle pivot sono state effettuate due viste direttamente sulle tabelle del DB transazionale e importato su SDS i risultati delle stesse. Il codice eseguito (due query distinte per tabella) è quello della Figura 3.7 e Figura 3.8 che ci ha permesso di trasporre i dati di riga in colonna.
La prima tabella pivot contiene per ogni lettura e operazione la sintesi di diversi dettagli di chiusura. La seconda invece indica per ogni IDLettura l’elenco dei codici degli accessori. Una volta importate sul SDS vengono mappate nella tabella che sarà successivamente inviata al DW.
CAPITOLO 3. CASO DI STUDIO - ETL
Figura 3.7: Query tabella Pivot LETTURADETTAGLIACCESSORI
Figura 3.8: Query tabella Pivot LETTURAACCESSORI
3.3.4
WF: Contatore
Obiettivo del presente flusso è quello di arricchire la tabella del contatore con alcuni dati presenti in tabelle che non saranno trasportate sul DB di destinazione. Nei due workflow seguenti si utilizzano le tabelle CONTATORE e APPALTO e vengono effettuate operazioni di :
• Select and Where per filtrare righe e colonne per favorire le operazioni di anticipazione delle query;
• Join tra le tabelle di interesse;
• Data transfer per facilitare le operazioni di trasporto di una grande quantità di dati dalla sorgente alla destinazione (Figura 3.9). Dopo aver effettuato tali operazioni i tempi computazionali risultano notevolmente diminuiti.
Alla fine di questo processo si avrà la tabella CONTATORE2 con la chiave univoca dell’IDContatore. Il workflow successivo effettua il join tra la tabella APPALTOCO- MUNE e VALIDAZIONELOTTO per poter ottenere la tabella finale VALIDAZIO- NELOTTO con tutte le informazioni necessarie e arricchite.
Anche in questo caso sono state apportate migliorie sulle query per poter ottimizzare le prestazioni.
CAPITOLO 3. CASO DI STUDIO - ETL
Figura 3.9: DF - Utilizzo nodo data transfer
3.3.5
WF: Tabella Anomalie
Durante la fase di Data Understanding19 sono state riscontrate alcune operazioni
che avevano dei dati mancanti o dei tempi registrati molto diversi dalle operazioni con le stessa tipologia. Si è avuta la necessità, quindi, di individuarle per poterle analizzare successivamente caso per caso.
Il workflow seguente si suddivide in tre dataflow per trovare tutte le "anomalie" presenti nei dati, dove per anomalie si intendono tutte le letture classificabile come outlier20 rispetto alle letture registrate e validate nel DB.
Nel primo si mettono in join tutte le letture (tabella LETTURE), filtrate prima a livello di riga con il valore ID maggiore 230 milioni (> 230’000’000), con i corrispettivi tempi di lettura (tabella TEMPILETTURA) il cui IDLettura è nullo. Inoltre a quest’ultimo campo si modifica il valore NULL con il valore zero. La sostituzione di tale valore è stata necessaria in quanto nei diversi strumenti degli step successivi il valore NULL non era ritenuto valido o veniva sostituito con un valore vuoto. Si ottengono infine tutti gli ID delle letture da noi considerate atipiche.
Una volta ottenuti gli ID delle letture anomale, queste vanno arricchite di tutte le sue corrispondenti informazioni (contatore, appalto, operazione ecc.) mettendole in
19E’ la fase in cui si analizzano i dati a disposizione per capirne il significato
20Outlier è un termine utilizzato in statistica per definire, in un insieme di osservazioni, un valore
CAPITOLO 3. CASO DI STUDIO - ETL
join con le appropriate tabelle (Figura 3.10) e ottenendo LETTUREANOMALE. Infine si crea una tabella contenente le informazioni necessarie all’analisi chiamata TEMPIOPERAZIONI e venutasi a creare matchando le letture anomale, i tempi operazioni e le chiusure (Figura 3.11).
Figura 3.10: DF - Join con tutte le tabelle di informazioni
CAPITOLO 3. CASO DI STUDIO - ETL