1
UNIVERSITÀ DI PISA
Dipartimento di Informatica
Corso di Laurea Magistrale in Informatica per l’economia e per l’azienda (Business Informatics)
TESI DI LAUREA
UNA SOLUZIONE DI BUSINESS INTELLIGENCE PER L’ANALISI
DI DATI DI VENDITA DELLA GRANDE DISTRIBUZIONE
RELATORE / TUTORE: Prof. Salvatore RINZIVILLO Dott. Graziano CARUSO
CONTRORELATORE: Prof. Roberto BRUNI
CANDIDATO: Cristian CRISCOLO
2
Abstract/Riassunto
L’obiettivo della tesi è la realizzazione, l’interrogazione e l’ottimizzazione di un sistema di data warehousing che consenta di creare e fornire all’azienda committente (che nel corso del documento chiameremo Alfa) diverse tipologie di report che le permettano di monitorare l’andamento delle vendite dei prodotti rientranti nei periodi promozionali da parte dei 14 affiliati (che chiameremo Beta1…Beta14).
La tesi è stata realizzata presso l’azienda Value Lab S.p.a.
Per raggiungere tale obiettivo, vengono svolte ad intervalli regolari diverse operazioni sui dati, che saranno descritte successivamente.
Particolare attenzione sarà rivolta allo sviluppo di adeguate procedure, mediante il linguaggio PL/SQL, che permettano di svolgere la maggior parte delle attività in maniera automatica e schedulata, in modo da ridurre al minimo la gestione manuale all’interno dell’intero processo, limitando al massimo la probabilità di errore nello svolgimento delle sotto-fasi del processo stesso. Alcune attività del processo sono state inizialmente sviluppate in maniera non efficiente, ed altre sono eseguite manualmente da parte dell’utente predisposto all’application maintenance del progetto. Un ulteriore obiettivo, successivamente raggiunto, è quello di rendere efficienti le prime e di automatizzare anche le seconde.
Il lavoro è introdotto da una presentazione generale sull’evoluzione e sulla diffusione dei DWH all’interno delle aziende. Successivamente vengono descritti gli strumenti utilizzati per la progettazione dello stesso e per la sua interrogazione. Inoltre viene descritto come sono stati integrati i differenti applicativi per svolgere al meglio ed in maniera automatica tutte le attività necessarie per la redazione dei report finali e, laddove sia stato necessario, come sia stata ottimizzata una procedura ed i relativi risultati raggiunti.
3
Introduzione e Strutturazione Capitoli
Molti gruppi commerciali e non, hanno il bisogno di salvare enormi quantità di dati, provenienti da un numero di fonti variabile e non uniche, ed allo stesso tempo renderli disponibili per poter effettuare analisi, oppure come strumento di resoconto attività.
Inoltre, c’è il bisogno di organizzarli in un modo utile, efficiente, con l’obiettivo di automatizzare task ripetitivi come le azioni di caricamento dei dati, quelle di lavorazione dei dati, quelle di aggregazione e tutte le altre azioni necessarie per raggiungere l’obiettivo prefissato.
Il data warehousing e la business intelligence rendono più semplice l’esecuzione di tutti i task, oltre a permettere l’immagazzinamento di una quantità di dati enorme. L’utilizzo di data warehouse per lo storage dei dati è solo una parte di tutto ciò che è possibile fare. Infatti vengono utilizzati per il supporto ai processi decisionali, operazionali ed analitici.
L’obiettivo di tesi consiste nell’implementare soluzioni aggiuntive e di apportare modifiche di ottimizzazione in modo da rendere il compito di application maintenance più semplice, efficace, efficiente e con un più ridotto margine di errore, mediante l’automatizzazione di processi svolti manualmente e il miglioramento delle performance delle query che richiedono tempi di esecuzione eccessivamente ampi.
Al fine di compiere ciò di cui sopra, si ringrazia Value Lab S.p.a. che ha messo a disposizione del sottoscritto tutti gli strumenti necessari per disporre di un ambiente completo per la gestione efficiente del sistema di data warehouse. Attraverso il tool Oracle SQL Developer ed alla piattaforma Oracle DB, adoperati in azienda, si è avuta la possibilità di ottimizzare la produzione dei report richiesti dal committente ed incrementare la gestione automatica dell’intero processo. Il titolo di tesi suggerisce che sia stato realizzato un progetto per un caso reale, concreto: l’azienda sede del progetto formativo è stata scelta dall’azienda Alfa come partner di progetto per la realizzazione di un sistema di Data Warehousing e Analytic Report.
Il progetto realizzato per Alfa ricoprirà il ruolo di caso di studio della tesi.
La struttura dei capitoli, così come i contenuti, rispecchiano il processo di crescita del progetto formativo nelle sue fasi salienti.
Come primo capitolo (AZIENDE COINVOLTE E PROGETTO) è stata introdotta una breve descrizione del contesto, ovvero l’azienda sviluppatrice, l’azienda committente con le sue richieste ed infine le soluzioni proposte dal sottoscritto per ottemperare alle richieste del cliente.
Successivamente, al fine di fornire un punto di partenza al lettore e alla commissione di laurea, il secondo capitolo (DATABASE VS DATA WAREHOUSE) tratterà la diffusione dei data warehouse nel corso degli anni, i concetti chiave dei database operazionali e dei data whareouse e delle principali differenze tra i due sistemi.
La sezione successiva (CASO DI STUDIO) ha lo scopo di illustrare l’architettura del data warehouse, con i differenti livelli e processo ETL, le modalità di svolgimento iniziali dell’application maintenance ed infine l’ottimizzazione messa in atto nell’intero processo.
Nello specifico verrà descritto come l’autore della tesi abbia avuto bisogno di implementare soluzioni in PL/SQL per raggiungere l’automatizzazione di alcuni processi e di studiare possibili query alternative per rendere più rapida la risposta del DB.
Il capitolo quarto, intitolato AMBIENTE DI SVILUPPO, mostra semplicemente il tool Oracle SQL Developer utilizzato e le modalità di creazione degli strumenti utili per le il raggiungimento degli obiettivi.
L’ultimo capitolo (INTEGRAZIONE APPLICATIVI) riguarderà per l’appunto l’integrazione tra di versi applicativi e/o linguaggi per permettere l’automatizzazione delle attività.
4 Verranno fornite altresì le considerazioni finali dell’autore sul percorso conseguito e su eventuali sviluppi.
Infine, nella sezione relativa alle CONSIDERAZIONI FINALI, verranno rappresentati gli obbiettivi raggiunti e le attività che potrebbero essere sviluppate in futuro sul progetto.
5
Sommario
Abstract/Riassunto ...2
Introduzione e Strutturazione Capitoli ...3
1 Aziende coinvolte e progetto...7
1.1 Azienda sviluppatrice ... 7
1.2 Il gruppo Alfa: richieste ... 9
1.3 Soluzioni proposte ... 9
2 Database vs Data Warehouse ... 12
2.1 Un po’ di storia ... 13
2.2 Database e Data Warehouse ... 13
2.2.1 Le basi di dati ... 13
2.2.2 I data warehouse ... 14
2.3 Differenze tra un DB relazionale e un Data Warehouse ... 18
3 Caso di studio ... 19
3.1 Analisi dei requisiti e progettazione concettuale ... 19
3.1.1 Sviluppo del progetto ... 19
3.1.2 Fasi del progetto ... 20
3.1.3 Report generati ... 20
3.1.4 Sorgenti dati ... 22
3.2 Data Warehouse Alfa ... 23
3.2.1 Architettura e livelli del DWH ... 23
3.2.2 Processo ETL ... 26
3.2.3 Gestione scarti ... 31
3.3 Application maintenance ... 33
3.3.1 Gestione arrivi e report arrivi ... 33
3.3.2 Gestione solleciti ... 34
3.4 Preparazione alla reportistica ... 36
3.4.1 Aggiornamento calendario ... 36 3.4.2 Gestione FIDFEE ... 37 3.4.3 Gestione PDV ... 38 3.5 Creazione reportistica ... 39 3.5.1 Procedura in e out ... 39 3.5.2 Controlli report ... 40 3.6 Ottimizzazione ... 41 3.6.1 Chiusure infrasettimanali ... 41
6
3.6.3 Reportistica ... 46
3.6.4 Controlli report ... 59
4 Ambiente di sviluppo ... 61
4.1 Oracle SQL Developer: Interfaccia e operazioni eseguibili... 61
4.2 Funzionalità Oracle DB ... 65
4.2.1 Trigger... 65
4.2.2 Procedure e funzioni ... 66
4.2.3 Package ... 66
5 Integrazione applicativi ... 68
5.1 Architettura del processo ... 68
5.2 Integrazione batch – Oracle SQL Developer – WinSCP – UTL_SMTP ... 70
5.3 Integrazione Oracle SQL – Java – Python ... 75
Considerazioni finali ... 76
Sitografia ... 80
7
1 Aziende coinvolte e progetto
1.1 Azienda sviluppatrice
Value Lab S.p.a è una società di consulenza di management e Information Technology che opera a livello nazionale e internazionale, specializzata sui temi di marketing, vendite e retail. Fondata da Marco Santambrogio (CEO) nel 1990 a Milano, è divenuta una società privata non quotata che conta ad oggi più di 200 dipendenti ed una seconda sede a Roma.
Il core business dell’azienda è quello di affiancare le aziende di produzione, distribuzione e servizi per favorire l’aumento dei ricavi, la riduzione dei rischi e la ottimizzazione dei costi migliorando le scelte strategiche e la gestione operativa di mercati, clienti, punti vendita e reti di vendita.
I servizi offerti da Value Lab possono essere sintetizzati in: • Pricing & Profitability Practice
Tematiche di gestione del prezzo e di ottimizzazione della profittabilità a supporto della creazione di valore dei clienti.
• Digital & E-commerce
Gestione del Digital & E-commerce, dove dispone di un osservatorio privilegiato sulle Best Practice al fine di:
o introdurre o razionalizzare il proprio store on-line;
o definire l'approccio al digital marketing e alla gestione delle leve di marketing digitale;
o impostare e utilizzare gli analytics in ambito web. • Geomarketing & Location Analytics
Nasce dall'incrocio di due storiche discipline: il marketing e la geografia. Il geomarketing consiste nell'analizzare i comportamenti dei soggetti economici (consumatori e imprese) tenendo conto delle nozioni di spazio. Il "territorio" diventa, quindi, un elemento fondamentale a supporto delle scelte strategiche e della gestione operativa, utilizzato a fianco delle variabili tradizionali: chi, cosa, quando, perché. Il geomarketing è un approccio di marketing che prevede l'utilizzo della componente geografica per rendere più efficaci ed efficienti le decisioni e le attività di strategia, comunicazione, vendita, distribuzione e servizio ai clienti.
• Location Based Marketing
Tecniche e metodologie, elaborazione dati e informazioni per segmentare e profilare il target in base alla localizzazione. In termini di marketing, si punta ad identificare, studiare e gestire almeno tre tipologie di target:
o le persone residenti in una determinata zona (night-time population);
o le persone gravitanti in una zona per diverse motivazioni come lavoro, studio, tempo libero, gestione familiare (day-time population);
o le persone di passaggio, sistematiche o sporadiche (flusso). • Analytics & Data Mining
Tecniche che fanno emergere le tendenze, le relazioni tra i dati, le ragioni legate al manifestarsi dei fenomeni. Il data mining è da un lato, un processo che mira a scoprire variabili nuove o implicite e le correlazioni di causa e effetto, dall’altro un processo interattivo che consiste nell'esplorazione di correlazioni, modelli e tendenze significative
8 che si manifestano tra i dati (generalmente creati integrando fonti interne ed esterne), utilizzando tecniche statistiche ed algoritmi matematici.
Le numerose tecniche impiegate nel data mining possono essere ricondotte a cinque aree di applicazione:
o Previsione
Utilizzo di valori noti per la previsione di quantità non note (es. stima del fatturato di un punto vendita sulla base delle sue caratteristiche);
o Classificazione
Individuazione delle caratteristiche che indicano a quale gruppo un certo caso appartiene (es. discriminazione tra comportamenti ordinari e fraudolenti);
o Segmentazione
Identificazione di gruppi con elementi omogenei all'interno del gruppo e diversi da gruppo a gruppo (es. individuazione di gruppi di consumatori con comportamenti simili);
o Associazione
Individuazione di elementi che compaiono spesso assieme in un determinato evento (es. prodotti che frequentemente entrano nello stesso carrello della spesa); o Sequenze
Riconoscimento di una cronologia di associazioni (es. percorsi di visita di un sito web).
• Customer Experience Management & CRM
Metodologia di CRM per fidelizzare i clienti con iniziative e comunicazioni mirate, aumentando contestualmente il corredo informativo relativo ai propri clienti in termini di comportamenti, reattività alle azioni di marketing e consumi. Nello specifico:
o differenziazione delle strategie e delle attività di marketing in funzione di profilo, comportamenti e risultati attesi dai singoli segmenti/cluster di clienti;
o piano delle campagne di contatto e di comunicazione; o target e meccaniche delle singole campagne;
o gruppo e parametri di controllo e valutazione dell'efficacia e dell'efficienza della campagna (nel breve e nel medio-lungo termine);
o ottimizzazione dei processi aziendali afferenti alla relazione con il cliente; o segmentazione in base a elementi anagrafici e comportamentali;
o localizzazione e/o provenienza geografica della clientela; o profilo della propria clientela;
o valore economico dei singoli clienti/cluster (life time value); o comportamenti denotanti rischio di abbandono;
o scoring in funzione di differenti obiettivi di marketing (cross-selling, up selling, retention, win back);
o reattività dei diversi clienti/cluster alle campagne di marketing e alle diverse modalità di contatto;
o elasticità al prezzo dei diversi segmenti di clientela; o prodotti acquistati congiuntamente o alternativamente.
9
1.2 Il gruppo Alfa: richieste
L’azienda Alfa è un gruppo commerciale composto da 15 imprese affiliate sparse in tutto il territorio italiano. Con gli oltre 2500 punti vendita e supermercati affiliati, è uno dei più grandi operatori della grande distribuzione in Italia.
Alfa, per il suo schema fedeltà, ha intrapreso un potenziamento del proprio programma promozionale per i clienti titolari di carte fedeltà. Questa attività prende il nome di Azioni Grandi Sconti (AGS).
Per riportare l’andamento delle attività promozionali e per il riconoscimento del rimborso dei contributi alle singole imprese relativi a tali attività, Alfa ha realizzato diversi report, creati e strutturati ad hoc per la suddetta attività.
In questo ambito, Value Lab è stato scelto da Alfa per farsi carico della realizzazione e predisposizione di attività automatizzate che portino alla creazione dei report AGS per gli associati.
1.3 Soluzioni proposte
È stata sviluppata una soluzione di Data Warehouse e Business Intelligence che potesse soddisfare al meglio le richieste presentate dal cliente.
Al momento dell’arrivo del sottoscritto presso l’azienda sviluppatrice, quindi, la struttura di base del Data Warehouse era già definita. In fase di sviluppo si è cercato di arrivare ad una soluzione che svolgesse tutte le attività necessarie, senza curarsi della sua complessiva efficienza. In pratica si è sviluppato il tutto assicurandosi solo dell’effettivo raggiungimento degli obiettivi. Per questo motivo, nell’implementazione delle procedure necessarie alle varie attività, si è pensato di sfruttare molto le viste raggruppanti la totalità dei dati.
Al termine del lavoro suddetto, ho continuato a farmi carico dell’attività di Application Maintenance.
Nella seconda fase il sottoscritto ha proposto e implementato l’ottimizzazione delle procedure di creazione della reportistica e la riduzione del fattore umano per quanto riguarda alcune attività. La prima ottimizzazione consiste nella riduzione dei tempi necessari per la creazione dei report, che risultano essere molto elevati. I dati caricati all’interno del Data Warehouse sono suddivisi in tabelle settimanali, quindi tutti i dati ricevuti sono collocati all’interno della tabella relativa alla settimana a cui fanno riferimento. È stata poi creata una vista unica che raggruppa tutti i dati presenti nel Data Warehouse, indipendentemente dalla settimana. Ogni report, fa riferimento ad un range di 7 settimane di analisi del venduto. Per poter estrarre queste settimane si sfrutta la suddetta vista, dalla quale vengono estratti solo i dati che sono all’interno del range di analisi. La soluzione proposta è una modifica della procedura per il caricamento dei dati al livello più alto del Data Warehouse, ovvero quello relativo ai report, in modo tale che, invece di sfruttare la vista, si vada ad effettuare una selezione delle sole tabelle settimanali oggetto di analisi. In questo modo, si eviterebbe il caricamento totale dei dati e la seguente estrazione dei giorni interessati tramite una “WHERE CONDITION”, che implica un controllo di ogni singolo record, permettendo di ottenere
10 quindi un miglioramento delle performance sia per quanto riguarda il tempo necessario, sia per le risorse macchina utilizzate.
La seconda ottimizzazione consiste nella riduzione del fattore umano all’interno del processo, che può essere fonte di errori o di mancanze. In particolar modo, questa riduzione è stata richiesta per le attività di gestione dei solleciti, in modo da evitare l’invio di solleciti ad alcuni falsi positivi presenti nei report dei punti vendita missing. In questa attività, un problema che si può verificare è la presenza di alcuni punti vendita che risultano essere falsi positivi, in quanto il processo di gestione dei solleciti effettua un controllo sulla presenza di dati di venduto per tutti i punti vendita e restituisce in output alcuni file, uno per ogni associato, contenente uno “specchietto” in cui sono riportati per ogni associato i punti vendita che non hanno inviato dati in una determinata settimana. Però in alcune circostanze, come ad esempio le chiusure infrasettimanali, chiusure eccezionali per ristrutturazione, terremoti o altri eventi straordinari, i punti vendita in questione non hanno dati da inviare, poiché chiusi. Essi, nel processo di gestione dei solleciti non devono essere considerati, nonostante la loro presenza nel suddetto report, per cui occorre effettuare un controllo manuale per procedere alla loro rimozione dalla lista dei punti vendita mancanti, così da non sollecitarli. Essendo questa un’attività manuale, può capitare che all’utente sfugga qualche punto vendita da rimuovere, e ciò causa un invio di sollecito che non andava effettuato. La soluzione proposta è, quindi, quella di creare una tabella che contenga tutti i punti vendita che in queste circostanze non devono essere sollecitati, con il giorno (o i giorni) delle chiusure straordinarie e la motivazione di chiusura. Questa tabella poi va integrata nella procedura di controllo dei punti vendita mancanti, in modo da non inserirli nella lista dei punti vendita da sollecitare. Questa soluzione riduce il fattore umano per quanto riguarda il controllo e la rimozione di tali punti vendita, riducendo quindi il rischio di eliminare per errore un punto vendita che andrebbe sollecitato o, viceversa, la non rimozione di un punto vendita che presenta una chiusura straordinaria e che quindi verrebbe erroneamente sollecitato.
Una ulteriore ottimizzazione consiste nel migliorare la gestione del problema di disallineamento tra i punti vendita presenti in anagrafica e i punti vendita di cui si ricevono i dati di venduto. Come riportato nella sezione apposita del documento, ogni settimana antecedente la redazione dei report, occorre aggiornare le anagrafiche dei punti vendita che partecipano alle azioni. Possono verificarsi alcuni casi in cui un associato invii i dati di venduto di alcuni punti vendita che non sono ancora presenti in anagrafica, a causa di ritardi nello scambio dei documenti tra l’associato ed il referente Alfa. Nella redazione dei report, si considera il venduto dei soli punti vendita di cui sono presenti le anagrafiche, per cui nel caso si verifichino tali disallineamenti, il punto vendita assente non verrà calcolato nell’analisi del venduto. Il controllo dei punti vendita che non sono presenti in anagrafica è svolto manualmente dall’utente, mediante l’esecuzione di alcune query che vanno a ricercare quali punti vendita stanno inviando dati senza essere presenti in anagrafica. Poiché sono presenti 3 diversi canali di vendita e 14 associati, questa query va eseguita per ogni combinazione “associato/canale” possibile. In questo caso, è stata proposta una soluzione per un duplice miglioramento. Il primo miglioramento riguarda l’automatizzazione del processo, ed è ottenuto mediante la creazione di una procedura opportuna che ricorsivamente effettui tale controllo per ogni associato e per ogni canale, inserendo in una tabella apposita la lista dei punti vendita coinvolti nel disallineamento. Il secondo miglioramento riguarda le prestazioni, poiché, dato che le query di analisi del venduto sono effettuate sulla stessa vista descritta precedentemente, ovvero quella che aggrega tutti i dati presenti nel Data Warehouse, i tempi di esecuzione di questa attività di controllo sono piuttosto elevati. Per cui, si è proposto di analizzare solo il venduto degli ultimi 5 mesi, in modo da notificare la richiesta delle anagrafiche dei punti vendita che inviano dati inerenti
11 ai prossimi report da redigere, senza considerare quelli che hanno riguardato azioni ormai concluse.
Infine, un’ultima ottimizzazione riguarda il controllo e la validazione dei report generati. L’azienda ha richiesto di effettuare alcuni controlli manuali sulle quantità di prodotti venduti risultanti dai report, dopo la loro creazione, per assicurare che nulla sia saltato nella procedura e che non ci siano discordanze tra le quantità totali riportate nei diversi report. Ricordando che gli associati sono 14, ciò significa che occorre effettuare 14 controlli puntuali. Questa operazione, seppur non complessa, risulta essere dispendiosa per l’utente. Per cui è stata proposta la soluzione di creare uno script in Python che effettui questi controlli per tutti i file dei report in maniera completamente automatica, riuscendo ad ottenere un resoconto nel giro di qualche secondo, contro il tempo necessario all’utente per controllarli tutti manualmente.
Le soluzioni proposte sono state pensate in modo che potessero portare dei consistenti vantaggi in termini di automatizzazione ed efficienza. La loro implementazione ed i risultati raggiunti saranno descritti successivamente nel capitolo riguardante l’ottimizzazione.
12
2 Database vs Data Warehouse
Il capitolo ha lo scopo di illustrare al lettore le principali caratteristiche di un database relazionale e di un data warehouse, in modo da rendere più semplice la successiva comprensione delle differenze tra le due strutture.
Inoltre vuole mostrare come le strutture informatiche per l’immagazzinamento e l’interrogazione dei dati si siano evolute nel corso del tempo, a causa della crescente necessità di estrarre informazioni rilevanti dai dati stessi, rendendo le basi di dati sempre più “business-oriented”. Prima di procedere, sicuramente molte persone si pongono il seguente quesito:
Ma cosa è un data warehouse? Procediamo per ordine.
Per prima cosa, la definizione formale è che un data warehouse è una collezione di dati in supporto al processo decisionale.
Immaginiamo di essere in casa, e di avere una grande scatola dove buttare dentro tutte le cose vecchie, per far spazio al nuovo. Questa scatola poi viene riposta da qualche parte. Se un giorno aveste l’esigenza di recuperare uno di quei vecchi oggetti per qualsiasi motivo, basterà aprire la scatola e cercarlo al suo interno. Ecco! Il data warehouse è come quello scatolone. Un magazzino di dati storici (e non), dentro il quale andare a cercare qualsiasi informazione che possa riguardare eventi passati, in maniera veloce ed efficiente.
Il DWH, inoltre, è utile a creare conoscenza, trasformando il dato grezzo in informazione. Nel DWH i dati vengono archiviati in modo che possano essere facilmente letti o elaborati dagli utenti, cioè in modo da favorire la produzione di informazioni. L’integrazione è un requisito fondamentale del DWH, in quanto in esso confluiscono dati provenienti da più fonti. L’obiettivo dell’integrazione può essere raggiunto in modi differenti: mediante l’utilizzo di metodi di codifica uniformi, raggiungendo una certa omogeneità delle variabili, mediante l’utilizzo delle stesse unità di misura, ecc..
Bene. Ora verrebbe da chiedersi perché usare un data warehouse anziché un semplice data base? Innanzitutto, i dati operazionali coprono un arco temporale di solito piuttosto limitato, poiché la maggior parte delle transazioni coinvolge i dati più recenti, mentre il DW deve permettere analisi che spaziano sulla prospettiva di alcuni anni. Per questo motivo, il DW è aggiornato a intervalli regolari a partire dai dati operazionali ed è in crescita continua.
Volendo fare un esempio, possiamo supporre che, a intervalli regolari, venga scattata una fotografia istantanea dei dati operazionali. La progressione delle fotografie scattate viene immagazzinata nel DW, dove si genera un film che documenta la situazione aziendale da un istante zero fino al tempo attuale.
Infine, un’altra fondamentale differenza sta nel fatto che, per i DB tradizionali, le interrogazioni eseguono in genere una lettura e una scrittura di un ridotto numero di record, ottenuti da diverse tabelle legate da semplici relazioni: per esempio, si ricercano i dati di un cliente per inserire un suo nuovo ordine.
Al contrario, il tipo di elaborazione per cui nascono i DWH è caratterizzato da un’analisi dinamica e multidimensionale che richiede la scansione di un’enorme quantità di record per calcolare un insieme di dati numerici di sintesi che quantificano le prestazioni dell’azienda.
13 2.1 Un po’ di storia
Il ruolo dei Sistemi Informatici è radicalmente cambiato dai primi anni ’70 a oggi. I sistemi informatici si sono trasformati da semplici strumenti per migliorare l’efficienza dei processi a elementi centrali dell’organizzazione aziendale in grado di rivoluzionare la struttura dei processi aziendali.
Le motivazioni alla base di questo cambiamento possono essere ricondotte a:
- Trasformazione dell’economia: l’economia moderna è basata sulle conoscenze e sull’informazione ed è caratterizzata da una breve vita dei prodotti che richiede decisioni tempestive.
- Trasformazione dell’impresa: per operare con profitto in un sistema economico altamente competitivo le aziende hanno bisogno di dotarsi di una struttura flessibile e snella in grado di reagire rapidamente alle mutate situazioni esterne/interne
- Globalizzazione: con l’allargamento dei mercati a livello mondiale nasce l’esigenza del controllo di mercati a larga scala
L’aumento esponenziale del volume dei dati operazionali ha reso il calcolatore l’unico supporto adatto al processo decisionale. L’utilizzo massiccio di tecniche di analisi dei dati aziendali ha reso il sistema informativo un elemento strategico per la realizzazione del business, per cui il ruolo dell’informatica è passato da passivo strumento per la registrazione delle operazioni a fattore decisivo per la individuazione di elementi critici dell’organizzazione e di potenziali aree di business. Negli anni ’80 nascono i sistemi di supporto alle decisioni (decision support system), ovvero l’insieme delle tecniche e degli strumenti informatici atti a estrapolare informazioni da un insieme di dati memorizzati su supporti elettronici.
Il ruolo dei decision support system ha subito molte evoluzioni nel tempo, partendo dall’avere un’utilità nella descrizione del passato, nella riduzione dei costi e nella descrizione dei problemi, fino ad arrivare ad una utilità nella previsione del futuro, nell’aumento dei profitti e nel suggerire i cambiamenti da apportare.
Negli anni, si sono sentite sempre più spesso delle lamentele, come il possesso di montagne di dati a cui però non era possibile accedere, il non comprendere i motivi per cui persone che svolgono lo stesso ruolo presentino risultati sostanzialmente diversi, oppure richieste di selezione, raggruppamento e manipolazione dei dati in ogni modo possibile, fino ad arrivare a richieste come “mostratemi solo ciò che è importante!”.
Da qui l’evoluzione al data warehouse, oggi in grado di rispondere a tutte queste lamentele.
2.2 Database e Data Warehouse
2.2.1 Le basi di dati
Uno dei principali compiti dei sistemi informatici consiste nelle attività di raccolta,
organizzazione e conservazione dei dati. Questo può riguardare i più svariati ambiti, come ad esempio nelle banche, per i movimenti dei conti correnti, i supermercati, per il venduto, gli aeroporti, per i vari voli, e così via.
Il punto è quello di conservare in maniera permanente sui dispositivi appositi, aggiornarli in caso di variazioni e renderli disponibili per le interrogazioni da parte degli utenti. I sistemi informatici garantiscono tutto ciò.
14 Per cui la base di dati possiamo definirla come una collezione di dati omogenei, ovvero riguardanti uno stesso argomento, o anche argomenti diversi, purché correlati, strutturata affinché i dati possano essere utilizzati dalle varie applicazioni e possano cambiare nel tempo.
Il Data Base Management System (DBMS) è un sistema software progettato per mantenere e utilizzare grandi collezioni di dati.
Le sue caratteristiche principali sono:
- Permettere agli utenti la creazione di nuovi database e di specificare la struttura dei loro schemi utilizzando il data definition language;
- Dare la possibilità di eseguire query sui dati mediante il query language;
- Dare la possibilità di inserire, modificare e cancellare i dati presenti mediante il data-manipulation language;
- Supportare il salvataggio di grandi quantità di dati per un certo periodo di tempo (medio-lungo), mantenendo un buon grado di sicurezza e offrendo un accesso ai dati in modo efficiente;
- Mantenere sotto controllo l’accesso da parte di più utenti, evitando accessi simultanei che possano comprometterne l’integrità.
Nel caso dei database, abbiamo i concetti di dato, informazione, tabella e relazione.
Una base di dati è organizzata in tabelle, che è la struttura fondamentale di un DB relazionale. Mediante le tabelle si rappresentano le entità e le relazioni dello schema concettuale.
Le tabelle sono popolate dai dati, denominati record, righe o tuple, che a loro volta sono composti in colonne, denominate campi o attributi.
- Ogni record rappresenta una istanza (o occorrenza) dell’entità/relazione; - Ogni campo rappresenta un attributo dell’entità/relazione;
Fondamentali sono i vincoli di integrità, ossia delle regole che garantiscono l’integrità dei dati quando siamo in presenza di associazioni tra più tabelle. Queste regole servono per rendere valida l’associazione tra tabelle e per salvaguardare i dati in caso di errori nell’inserimento, modifica o cancellazione dei dati collegati tra loro.
2.2.2 I data warehouse
Un data warehouse è una collezione di dati di supporto per il processo decisionale, che, a differenza dei sistemi operazionali, ha le seguenti caratteristiche:
- è orientata ai soggetti di interesse; - è integrata e consistente;
- è non volatile.
La costruzione di un sistema di data warehousing non avviene tramite l’inserimento di nuove Informazioni, ma attraverso la riorganizzazione di quelle esistenti.
Ciò significa che implica pertanto l’esistenza di un sistema informativo.
A differenza dei dati operazionali, che coprono un arco temporale di solito piuttosto limitato, visto che la maggior parte delle transazioni coinvolge i dati più recenti, il DW permette analisi che spaziano su distanze di alcuni anni.
Per questo motivo, il DW è aggiornato ad intervalli regolari ed è in continua crescita.
Dato che, almeno in linea di principio, i dati di un DW non vengono mai eliminati o modificati, e che l’aggiornamento avvenga quando il DW è offline, potremmo considerare un DW come un database di sola lettura.
15 Il tipo di elaborazione per cui nascono i DW viene detto On-Line Analytical Processing (OLAP), ed è caratterizzato da un’analisi multidimensionale che richiede la scansione di un enorme quantità di record (alcuni milioni) per calcolare un insieme di dati numerici di sintesi.
Proprio per queste analisi “multidimensionali”, i dati sono visti come dei punti in uno spazio, le cui dimensioni corrispondono alle loro caratteristiche, sulle base delle quali effettuare diverse aggregazioni per ottenere dei risultati di analisi.
Nel caso di un data warehouse, abbiamo i concetti di fatto, attributo, dimensione e metrica. Nello specifico:
- Fatti. Un fatto modella un insieme di eventi che si verificano quando un obiettivo viene raggiunto.
- Attributi. Sono dei campi la cui valorizzazione si accompagna alla registrazione di un fatto ad opera di un obiettivo.
- Dimensioni. Una dimensione è una proprietà di un fatto che ne descrive una possibile coordinata di analisi.
- Misure. Una misura è una proprietà numerica di un fatto che ne descrive un aspetto quantitativo
2.2.2.1 Architettura dei data warehouse
Esistono diverse architetture per la progettazione di un sistema data warehousing, tra queste si distingue l’architettura a due livelli; il nome deriva dalla volontà di evidenziare la separazione tra il livello sorgenti e quello del DW, sebbene in realtà si articola su quattro livelli distinti:
1. Livello delle sorgenti. Il DW utilizza fonti di dati eterogenei, essi possono essere estratti dall’ambiente di produzione e quindi originariamente archiviati in database aziendali relazionali, oppure provenire da sistemi informativi esterni all’azienda.
2. Livello dell’alimentazione. I dati memorizzati nelle sorgenti devono essere estratti, ripuliti per eliminare le inconsistenze e completare eventuali parti mancanti. I cosiddetti strumenti ETL (Extraction, Transformation and Loading) permettono di integrare schemi eterogenei, nonché di estrarre, trasformare, pulire, validare, filtrare e caricare i dati dalle sorgenti nel DW.
3. Livello del warehouse. Le informazioni vengono raccolte in un DW. Esso può essere direttamente consultato ma anche usato come sorgente per costruire data mart. Con il termine data mart si intende un sottoinsieme o un’aggregazione dei dati presenti nel DW primario.
4. Livello di analisi. Permette la consultazione efficiente e flessibile dei dati integrati a fini di stesura di report, di analisi, di simulazione.
2.2.2.2 Obiettivi, requisiti e modellazione dimensionale
Gli obiettivi del DW e BI consistono nel fornire una interfaccia per l’accesso ai dati storici in modo veloce e immediato. Si sono spesso sentite lamentele circa il fatto che si collezionino tonnellate di dati, ma che non si sa come accedervi, che c'è il bisogno di dividere e tagliare i dati in ogni modo, che le persone del business necessitano di accedere ai dati facilmente, e così via.
Queste richieste si trasformano in requisiti:
- i sistemi DW/BI devono fornire informazioni facilmente accessibili: il contenuto di tali sistemi deve essere comprensibile. I dati devono essere intuitivi e ovvi per gli utenti del
16 business, non solo per gli sviluppatori. Gli utenti di business vogliono separare e combinare i dati analitici in infinite combinazioni. I tool e le applicazioni di BI che accedono ai dati, dovrebbero essere semplici e facili da usare, oltre a dover riportare i risultati di una query in un tempo di attesa minimo. Praticamente il primo requisito è SEMPLICE e VELOCE. - i sistemi DW/BI devono presentare informazioni in modo coerente: i dati devono essere
credibili. Devono essere assemblati accuratamente da una varietà di sorgenti, puliti, assicurati sulla qualità e rilasciati solo quando sono adatti al consumo degli utenti. La coerenza implica anche comuni definizioni ed etichette tra i vari sistemi DW/BI, ovvero che se due misure di performance hanno lo stesso nome, allora devono significare la stessa cosa.
- i sistemi DW/BI si devono adattare ai cambiamenti: ogni cosa è soggetta a cambiamenti. I sistemi devono essere disegnati per gestire questi inevitabili cambiamenti in una buona maniera, senza invalidare dati e applicazioni esistenti. Questi ultimi non dovrebbero essere cambiati o distrutti quando vengono richieste nuove domande o nuovi dati vengono aggiunti al DW.
- i sistemi DW/BI devono presentare informazioni in modo tempestivo: siccome il sistema DW/BI è usato molto intensivamente per decisioni operazionali, i dati grezzi devono essere convertiti in informazioni azionabili in ore, minuti o secondi.
- i sistemi DW/BI devono essere sicuri per la protezione delle informazioni: le informazioni dell'organizzazione sono salvate nel data warehouse. Per cui i sistemi DW/BI devono efficientemente controllare l'accesso alle informazioni confidenziali dell'organizzazione. - il sistema DW/BI deve servire come fondamento autorevole e affidabile per un miglior
processo decisionale: il DW deve avere i giusti dati per supportare le decisioni. Il più importante output di un sistema DW/BI sono le decisioni che sono prese sulla base dell'evidenza analitica presentata.
La modellazione dimensionale è la tecnica preferita per presentare dati analitici, perchè tratta due requisiti simultanei:
- inviare dati che siano comprensibili agli utenti business; - fornire prestazioni di query rapide.
Il punto focale è la semplicità, ed è un punto critico perchè assicura che gli utenti possano facilmente comprendere i dati, così come permettere ai software di navigare e fornire risultati velocemente ed efficientemente.
Immaginiamo il DWH come un cubo di dati, con ai bordi i prodotti, il mercato e il tempo. Immaginiamo di tagliare e ritagliare attraverso queste dimensioni. I punti all'interno del cubo sono le misure, come volume vendite o profitti, per le varie combinazioni di prodotti, mercati e tempi salvati. L'abilità di vedere qualcosa di astratto come un set di dati in una maniera concreta e tangibile è la chiave del successo.
Un data model che parte semplice ha buone probabilità di finire semplice, viceversa, un data model che parte complesso sicuramente terminerà ancor più complesso, tramutandosi quindi in performance di query lente e quindi rigettate dagli utenti di business.
Prima di iniziare la modellazione dimensionale, è fondamentale capire appieno le necessità aziendali e le realtà sottostanti i dati.
17 Durante la progettazione del modello dimensionale sono richieste 4 decisioni importanti:
1. selezione del processo aziendale: i processi aziendali sono attività eseguite dall’organizzazione, come prendere ordini, processare e assicurare richiami, registrare studenti per una classe. Gli eventi dei processi generano o catturano metriche di performance che si traducono in fatti nella fact table. Molte fact table si focalizzano sul risultato di un singolo processo. La scelta del processo è importante perché definisce l’obiettivo del progetto, e permette di dichiarare la granularità, le dimensioni e i fatti. 2. dichiarazione della granularità: dichiarare la granularità è lo step centrale per un
progetto dimensionale. Infatti, la granularità stabilisce esattamente cosa una singola riga della fact table rappresenta, ovvero il livello di dettaglio dell’informazione. Va stabilita prima della scelta di dimensioni e fatti, poiché ogni dimensione e fatto deve essere coerente con la granularità.
3. identificazione delle dimensioni: le dimensioni stabiliscono il “chi, che cosa, dove, quando, perché e come” dell’evento di processo da analizzare.
Le dimensioni contengono gli attributi descrittivi usati dalle applicazioni di BI per il filtraggio e il raggruppamento dei fatti. Con piena conoscenza della granularità stabilita è possibile identificare tutte le dimensioni.
Quindi le dimensioni sono il punto di partenza, e possiamo definirle in modo informale come delle descrizioni che permettono al sistema DW/BI di essere la leva per le analisi aziendali.
4. identificazione dei fatti: i fatti sono le misurazioni che risultano da un evento del processo e sono quasi sempre numerici. Una singola riga della fact table ha una relazione one-to-one con la misurazione di un evento come descritto nella granularità. La fact table corrisponde ad un evento fisicamente osservabile. Con una fact table, sono permessi solo i fatti che sono coerenti con la granularità stabilita.
2.2.2.3 ETL (Extract, Transform & Load)
Per facilitare le analisi di business si caricano regolarmente i dati nel data warehouse. I dati provenienti da molteplici sistemi operazionali vengono estratti e copiati al suo interno. Il punto chiave è quello di integrare, riorganizzare e consolidare una larga quantità di dati provenienti da molti sistemi, in modo da fornire una nuova base di informazioni unificata per il business intelligence. Il processo prende il nome di ETL (Extract, Transform & Load).
L’estrazione avviene da differenti sorgenti, molto spesso non è possibile identificare lo specifico sottoinsieme di interesse. Inoltre la dimensione dei dati estratti può variare da alcune centinaia di KiloByte fino a GigaByte. Stesso vale per il tempo impiegato. Alcune trasformazioni prendono luogo direttamente durante questo processo.
Una volta estratti, i dati vengono fisicamente spostati su determinati sistemi o su sistemi intermedi per ulteriori processi. Anche in questo caso alcune trasformazioni possono prendere luogo direttamente durante questo processo.
Altre importanti operazioni sono quelle giornaliere, oltre che il supporto a futuri miglioramenti. Il DWH è un sistema IT “di vita”, per cui sorgenti e obiettivi possono cambiare. Questi cambiamenti devono essere mantenuti e tracciati attraverso la durata del sistema, senza sovrascrivere o eliminare le informazioni sul vecchio processo ETL.
18 Al termine delle trasformazioni avviene il caricamento dei dati nel DWH.
2.3 Differenze tra un DB relazionale e un Data Warehouse
Ci si potrebbe chiedere il perché utilizzare un data warehouse invece di un DB tradizionale. Per prima cosa un data warehouse è una aggregazione dei dati di un DBMS. Va sottolineato che per aggregazione non si intende un semplice raggruppamento di dati, ma essa avviene seguendo una determinata logica, stabilita dalle esigenze aziendali.
Infatti, una prima differenza tra un DW e un DB, sta nel fatto che i dati presenti nel DW contengono delle informazioni rilevanti per il business, e sono aggregati in modo tale da garantire l’ottenimento di quelle informazioni.
Quindi possiamo dire che la prima differenza sta nella funzione, con i DB che hanno una funzione di gestione giornaliera, e i DW una funzione di supporto alle decisioni, la quale è strettamente collegata alla progettazione, che per un DB è orientata alle applicazioni, mentre nel caso di un DW è orientata al soggetto (dell’analisi).
Dunque, le principali differenze tra i due sistemi riguardano soprattutto:
- la funzione: per il DB tradizionale è la gestione giornaliera, mentre nel DWH è il supporto alle decisioni;
- la progettazione: orientata alle applicazioni per il DB e orientata al soggetto per il DWH; - la frequenza degli aggiornamenti: giornaliera per i DB e sporadica per i DWH;
- la copertura temporale: dati recenti e dettagliati contro dati storici, riassuntivi e multidimensionali;
- le sorgenti: singola vs multipla; - l’uso: ripetitivo vs ad hoc; - l’accesso: read/write vs read
- la flessibilità di accesso: programmi precompilati vs generatori di query; - i record interrogati: centinaia vs migliaia o milioni
19
3 Caso di studio
Dopo una breve introduzione sul core business dell’azienda sviluppatrice, verranno presentati i paragrafi inerenti l’analisi dei requisiti iniziale e l’architettura del Data Warehouse implementato. Il sottoscritto ha dovuto farsi carico delle attività di Application Maintenance del progetto Alfa, oltre a fornire il supporto nei processi ETL e nell’analisi delle criticità dei processi. Nella parte finale del capitolo verranno illustrate le operazioni di ottimizzazione messe in atto, con le relative statistiche dei risultati ottenuti prima e dopo le migliorie apportate ai processi automatizzati. Oltre a ciò sono presentate le modalità con cui alcune attività, finora svolte manualmente dall’utente, sono state automatizzate.
3.1 Analisi dei requisiti e progettazione concettuale
3.1.1 Sviluppo del progetto
Negli accordi stabiliti tra Value Lab e Alfa, sono state definite le attività a carico dell’una e dell’altra. In particolare, le attività a carico di Value Lab sono le seguenti:
- Progettazione e sviluppo:
o Implementazione DB/DWH
o Implementazione procedure di elaborazione dei dati delle imprese o Verifica della presenza dei dati di tutti i punti vendita
o Elaborazione di un report relativo a dati non inviati (mancanti o missing)
o Implementazione di procedure per l’elaborazione dei dati di input forniti da Alfa o Implementazione di procedure per la creazione e l’invio dei report verso Alfa - Monitoraggio e controllo:
o Verifica dei report relativi ai dati mancanti
o Verifica della creazione effettiva dei report finali verso Alfa ed i suoi affiliati o Verifica della corretta elaborazione dei file settimanali
o Verifica del corretto invio da parte degli associati dei dati di recupero - Attività straordinarie:
o Notifica ad Alfa per la richiesta dei dati di integrazione in caso di problematiche persistenti nell’invio dei dati da parte degli associati
o Monitoraggio dei task di integrazione dei dati mancanti Le attività a carico di Alfa e dei suoi associati sono:
- Invio dei file “FIDFEE”, “PDV” e “Calendario PROMO AGS” contenenti le informazioni di input necessarie per l’elaborazione finale dei report oggetto dell’accordo
- Invio settimanale dei file di dati
- Invio di tutte le informazioni necessarie per l’elaborazione finale dei report (ad es. apertura e/o chiusura punti vendita, chiusure settimanali, aperture e/o chiusure straordinarie, ecc.)
20
3.1.2 Fasi del progetto
Nell’ambito del progetto definito tra Value Lab e Alfa, sono state identificate 3 macro-fasi: 1) Inserimento dei periodi promozionali
a. Ad inizio anno verrà condiviso da Alfa un file con il riassunto di tutti i periodi promozionali per l’anno successivo;
b. Prima dell’elaborazione finale dei report, Alfa metterà a disposizione le informazioni necessarie per l’elaborazione degli stessi, ovvero i file “FIDFEE” e “PDV”.
2) Caricamento continuo dei dati
a. I dati di vendita da parte degli associati verranno caricati in maniera continuativa. I tracciati sono già definiti in precedenza
3) Creazione dei report
a. Al termine di ogni periodo promozionale, verranno predisposte ulteriori 3/4 settimane di tempo per la ricezione di tutti i dati necessari alla generazione dei report;
b. Allo scadere di queste settimane, verranno quindi generati i report ed inviati ad Alfa entro 5 giorni lavorativi.
3.1.3 Report generati
L’azienda Alfa ha commissionato a Value Lab la creazione di 5 tipologie di report, ognuno avente una propria logica di aggregazione dei dati.
Si precisa che vengono escluse dall’analisi di ognuno dei report, le vendite che sono state effettuate da carte fedeltà e codici EAN fittizi, che presentano valori “0000000000000” o “9999999999999”.
I report sono così definiti:
1) Report ControlloEanPromo: contiene, per ogni prodotto, le informazioni riguardanti i punti vendita che lo hanno trattato, i punti vendita che lo hanno venduto e la percentuale di adesione alla promozione.
Il periodo di analisi è di 7 settimane, tra cui abbiamo le 2 settimane di promozione, le 3 settimane antecedenti alla promozione e le 2 settimane successive alla promozione. Le vendite sono conteggiate nelle 7 settimane fisse solo per i prodotti con il codice EAN previsti dalla promozione e solo per i canali trattati dall’associato.
Tale report è utile per evidenziare alle imprese gli EAN che i punti vendita hanno venduto non in promozione, nonostante nel periodo fosse attiva una promozione di sede AGS su essi.
21 2) Report AGS: contiene il numero dei prodotti venduti dai punti vendita per ogni affiliato. È il report utile a definire le quantità vendute in promozione per la liquidazione dei contributi. Il periodo di analisi è di 7 settimane, tra cui le 2 settimane di promozione, le 3 antecedenti e le 2 successive.
Le quantità vendute vengono conteggiate prendendo come base le 5 settimane a partire dalla prima settimana in cui è stato venduto il prodotto in promozione, solo per gli EAN previsti in promozione e indicati nel file FIDFEE, ad eccezione dei casi in cui la prima vendita sia avvenuta nell’ultima settimana di analisi.
Il report è strutturato in modo da ottenere un livello di dettaglio del singolo EAN e del singolo punto vendita, con rappresentazione divisa in diversi sheet, uno per ogni singolo canale.
3) Report Pro: contiene le unità vendute in ogni settimana riguardante l’azione, per ogni punto vendita e per ogni EAN. L’obiettivo è quello di capire quando il punto vendita ha iniziato la promozione e se sono state inviate tutte le settimane.
Il periodo di analisi è di 7 settimane, tra cui le 2 di promozione, le 3 antecedenti e le 2 successive.
Quindi il report Pro offre un dettaglio settimanale dei prodotti venduti nelle 7 settimane, solo per gli EAN previsti in promozione.
4) Report Loc: in un periodo di analisi di 7 settimane, composto dalle 2 settimane di promozione, le 3 antecedenti e le 2 successive, si conteggiano le unità vendute in promozione per gli EAN non previsti in promozione. Il report pro è strutturato per ottenere un livello di dettaglio di singolo EAN e di singolo punto vendita per ognuna delle 7 settimane.
Figura 2: Report AGS
Figura 3: Report Pro
22 5) Report PromoSel: unico report ad essere fornito in formato testuale. Il periodo di analisi è di 7 settimane, tra cui le 2 di promozione, le 3 antecedenti e le 2 successive. Vengono conteggiate le quantità vendute nelle 5 settimane successive alla prima in cui è stato venduto il prodotto in promozione. È riferito ai soli EAN previsti in promo, quindi nel file FIDFEE. Le quantità riportate in questo report devono coincidere, per articolo e per punto vendita, con quelle del report AGS.
Il formato di ogni riga di testo è così definito:
Descrizione campi Numero di caratteri
Canale 2 Blank 3 Codice store 5 Anno 4 Azione 3 Codice EAN 13 Descrizione 60 Codice cedi 5 Quantità rilevate 5 Valore venduto 11 Valore sconto 11 Codice fornitore 5 Descrizione fornitore 30 Contributo unitario 8
Tabella 1: Formato righe PromoSel
3.1.4 Sorgenti dati
Di seguito è riportata una breve descrizione di quelle che sono le fonti e le modalità di accesso da parte del DWH:
- Area SFTP
È stata predisposta un’area SFTP in cui gli associati possono caricare in qualunque momento i file contenenti il venduto. Ogni associato ha accesso alla propria area SFTP, con i propri dati di login, in modo tale che nessuno possa intervenire in un’area che non sia di sua competenza.
Per la corretta elaborazione dei dati è stata definita una naming convention, in modo tale da agire esclusivamente sui file necessari.
All’interno dell’area, quindi, verrà caricato un file .zip, contenente al suo interno i file .dat di ogni punto vendita, composti dalle righe di venduto scritte seguendo un tracciato standard, in modo da automatizzare il processo di elaborazione dei dati, riducendo al minimo la possibilità di errori di codifica o di formattazione.
23 - XLSX file
Per i dati che non hanno una cadenza giornaliera, quali FIDFEE e PDV, è stata stabilita la creazione di un file .xlsx, contenente le informazioni necessarie alla redazione dei report. Questo è dovuto al fatto che l’obiettivo finale è proprio la redazione dei report, i quali sono generati sulla base dei suddetti file. Quindi il caricamento dei FIDFEE e dei PDV è un’operazione molto delicata, che, in caso di automatismi, non permetterebbe il monitoraggio in tempo reale di eventuali errori e/o problemi, come ad esempio un caricamento parziale.
3.2 Data Warehouse Alfa
3.2.1 Architettura e livelli del DWH
L’architettura del DWH può essere suddivisa in più livelli. Ciascuno di essi svolge specifiche funzioni e hanno uno specifico ruolo all’interno dell’intero processo, dall’estrazione ed elaborazione del dato, fino alla creazione della reportistica finale.
Di seguito è possibile osservare la struttura generale dell’intero progetto Alfa:
Figura 6: Architettura DWH e Processi
L’obiettivo è distinguere i dati di staging, volatili e recuperabili in ogni momento, da quelli presenti nei data mart, che, mediante una gestione con logiche e processi specifici per ogni area funzionale, sono più raffinati e ottimizzati per un rapido accesso in fase di redazione dei report.
24 Passando da un livello al successivo, il dato cambia, si evolve, verso una dimensione più significativa per l’obiettivo finale. La scelta di mantenere le 3 aree ben distinte, dipende dal fatto che ogni volta che i dati vengono aggregati e/o trasformati, non si vuole perdere quella che è la base di partenza, in modo tale che il dato precedente alla trasformazione sia sempre disponibile per eventuali futuri sviluppi di progetto o per i casi in cui il cliente (Alfa), o qualcuno dei suoi associati, dovesse richiedere spiegazioni su alcuni risultati ritenuti errati, permettendo delle analisi rapide per ogni livello di informazione necessaria.
Nel dettaglio:
- Livello 0 (Staging):
E’ costituito innanzitutto dal repository fisico ove vengono memorizzati e storicizzati i file sorgente recuperati day-by-day dalle sorgenti remote SFTP, che d’ora in poi chiameremo Staging Area.
E’ inoltre, e soprattutto, costituito da un Database di Staging, dove vengono memorizzati e “parcheggiati” i dati in attesa di essere elaborati e caricati nel livello successivo.
I dati presenti nello Staging DB hanno una retention configurabile (tipicamente impostata a 2-3 giorni) in quanto, una volta elaborati e caricati nel livello successivo non vi è più necessità di mantenerli qui. Attualmente è impostata a 2 anni, così da poter essere osservati e monitorati in fase di Application Maintenance.
L’area di stage per ogni associato è definita come il seguente schema:
o STAGE_<NOME_ASSOCIATO> (ad esempio area di stage per BETA1 sarà
STAGE_BETA1)
All’interno dell’area di staging sono caricati i dati delle transazioni inviate dai vari associati settimanalmente, memorizzati in tabelle settimanali con la seguente nomenclatura:
o TRANSATO_ALOG_AGS_<YYYY>_<WW>
Dove YYYY indica l’anno di riferimento delle transazioni e WW indica la settimana di calendario dell’anno di riferimento.
Inoltre è presenta un’area di stage comune a tutti gli associati, nella quale sono inserite le seguenti tabelle di configurazione per la generazione al Livello 2:
o PDV_AGS
Tabella delle anagrafiche dei vari punti vendita. Attualmente questa tabella è annuale, ed è aggiornata manualmente con le informazioni fornite dai vari associati, quali la chiusura/apertura dei vari punti vendita.
La tabella contiene le date di apertura e chiusura dei vari punti vendita, così da rendere possibile la chiusura logica di un PDV. In caso di chiusura, il punto vendita viene chiuso logicamente e non fisicamente, così da permettere le analisi sullo storico.
Action ETL: le tabelle annuali sono nominate come “PDV_<ANNO>”, e la tabella “PDV_AGS” viene ora creata come una vista che unisce le anagrafiche degli ultimi due anni rolling.
25 o CALENDARIO_ALFA_AGS:
Tabella riepilogativa del calendario delle settimane promozionali di AGS. Attualmente la tabella prima di ogni esecuzione dei report deve essere aggiornata con le informazioni relative all’azione, ricavate da una tabella annuale “CALENDARIO_ALFA_<ANNO>, quest’ultimo fornito ad inizio anno da Alfa.
In questo modo non si presenta il rischio di intaccare le informazioni originali fornite da Alfa, che, una volta caricate, non vengono più toccate, ma solo copiate nella tabella riepilogativa di tutte le azioni svolte.
Action ETL: creare la tabella annuale del calendario ad inizio anno, effettuando un caricamento spot da un file excel inviato da Alfa. Successivamente, effettuare gli aggiornamenti della tabella “CALENDARIO_ALFA_AGS” ogni volta che bisogna produrre una azione.
o FIDFEE_AGS:
Tabella contenente tutti i prodotti soggetti a promozioni da canali FS/AG/PJ. La tabella è annuale e, come per la tabella delle anagrafiche dei PDV, è aggiornata manualmente per ogni azione di Alfa.
Action ETL: le tabelle annuali sono nominate come “FIDFEE_<ANNO>”, e la tabella “FIDFEE_AGS” viene creata come una vista che unisce quelle degli ultimi due anni rolling.
o F_SCONTRINI (vista complessiva testate scontrino) o F_SCONTRINI_R (vista complessiva righe scontrino) - Livello 1 (Operational Data Store):
E’ il repository centrale nel quale i dati vengono memorizzati in una struttura dati relazionale e normalizzata e dove vengono implementate le logiche di pulizia, normalizzazione e trasformazione del dato. D’ora in poi faremo riferimento a questo livello col termine ODS.
Tale area per ogni associato è identificata dallo schema DSS_AGS_<NOME_ASSOCIATO> (ad esempio per BETA1 l’area in oggetto sarà identificata da DSS_AGS_BETA1).
In questa area sono presenti le tabelle dei fatti così distinte:
o F_SCONTRINI_<YYYY>_<WW> (identifica la tabella della testata scontrino) o F_SCONTRINI_R_<YYYY>_<WW> (identifica la tabella delle righe scontrino)
Dove YYYY indica l’anno di riferimento delle transazioni e WW indica la settimana di calendario dell’anno di riferimento. Inoltre in quest’area viene vengono create due viste che permettono di mettere insieme le informazioni di tutte le transazioni di tutte le settimane:
o F_SCONTRINI (identifica la vista delle testate scontrino) o F_SCONTRINI_R (identifica la vista delle righe scontrino) La generazione dei Data Mart del livello successivo sfruttano le viste.
26 - Livello 2 (Data Mart):
In quest’area vengono generati i data mart per ogni report richiesto.
Quest’area viene identificata con la nomenclatura DM_<NOME_ASSOCIATO> (ad esempio per l’associato BETA1 sarà nominata come DM_BETA1).
I data mart sviluppati quindi sono: o DM_AGS_AGS
o DM_AGS_PRO o DM_AGS_LOC
o DM_AGS_CONTROLLOEANPROMO o DM_AGS_PROMOSEL
Inoltre, in questo livello è presente anche un ulteriore data mart, il “DM_ALFA”, che contiene tabelle ausiliarie al progetto, elencate di seguito e la cui spiegazione verrà affrontata nei paragrafi successivi:
o DM_AGS_ARRIVI o DM_AGS_SCARTI
In breve, possiamo affermare che nel primo livello sono caricati i dati così come sono dalla fonte, senza effettuare alcun controllo e alcun processo di pulizia. Nel secondo livello si applicano le verifiche necessarie ad escludere i dati errati, i dati duplicati e tutti gli altri motivi per cui i dati debbano essere esclusi dalle analisi. Quindi a questo livello troveremo sempre i dati originali, ma ripuliti da quelli “inutili”.
Nel terzo livello, verranno caricati i dati una volta applicata la logica necessaria alla creazione dei report, ovvero vengono applicate delle logiche e delle aggregazioni tali da rendere il dato “parlante”, ossia in grado di esprimere al meglio e rapidamente l’informazione finale ricercata dal cliente.
Infine, sono presenti due ulteriori aree, situate al di fuori dei 3 livelli appena descritti, ovvero l’area “AM_MONITOR”, che è l’area adibita al monitoraggio del processo di creazione della reportistica, contenente i log dei vari processi di creazione dei report, e l’area “META”, che non tratteremo in questo documento, predisposta per collezionare le informazioni generali del progetto e i log dei processi ETL.
3.2.2 Processo ETL
Per facilitare le analisi di business si carica regolarmente il data warehouse. I dati vengono quindi estratti da molteplici fonti e caricati nel DWH. Il punto è quello di integrare, riorganizzare e consolidare una larga quantità di dati da molti sistemi, fornendo così una nuova base di informazioni unificata per la business intelligence.
In ambito Data warehousing viene definito "Extract, Transform and Load" (ETL) quel processo sequenziale con cui il dato presente in un uno o più sistemi sorgente, spesso non omogenei, viene estratto per poter essere poi elaborato, raffinato ed infine integrato ad altri dati, al fine di rappresentare un set informativo conforme all’interno del DWH.
27
Figura 7: Livelli ETL
Di seguito viene descritto come è stato implementato il processo ETL, con relativa spiegazione degli step principali che vengono eseguiti tra la fonte dei dati e la loro destinazione.
Per prima cosa è stata implementato un package Oracle, denominato ETL_AGS, in cui sono definite le procedure con cui gestire questa parte di processo.
Un’importanza rilevante ha avuto l’integrazione della piattaforma Oracle con altri applicativi, soprattutto nella fase di estrazione.
Il package è costituito da 7 procedure principali, 21 procedure ausiliare e 1 funzione. Di seguito è possibile visionare la struttura del package:
Figura 8: Package ETL_AGS
Figura 9: Package Body ETL_AGS
Il processo ETL è stato schedulato in modo che possa partire in maniera automatica ogni giorno, durante la notte, così da aver elaborato il tutto per la mattina seguente e quindi avere a disposizione ogni giorno i dati aggiornati sin da subito.
28 La struttura del processo ETL e il suo ordine di esecuzione possono essere analizzati dal seguente schema, riportato nella forma di Petri Net:
Figura 10: Petri Net processo ETL
Dall’immagine è possibile identificare 4 macro fasi:
1) La prima si occupa dello spostamento dei file dall’area SFTP predisposta per il caricamento del venduto da parte degli associati, su un’area locale.
2) La seconda si occupa di elencare tutti i file presenti in locale, popolando una external table con tale lista.
3) La terza si occupa dell’elaborazione dei file, procedendo quindi con l’unzip dei file, il parsing di ogni riga di scontrino e il caricamento nell’area di staging (livello 0 del DWH). 4) La quarta ed ultima fase prevede l’invio di una mail con il log di processo e l’esito all’utente
predisposto all’attività di application maintenance del progetto.
Una spiegazione puntuale delle varie attività verrà riportata nel successivo capitolo di integrazione applicativi, poiché esse sono svolte mediante l’uso di applicazioni diverse.
In generale, si può intuire dal nome che la procedura che dà il via a tutto è “avvio”, la quale orchestra l’ordine di esecuzione delle procedure ausiliarie utili ad effettuare l’estrazione dei dati dalla sorgente, fino al caricamento degli stessi nelle tabelle di destinazione.
La prima chiamata è per “estrai_sftp_files”, che, con l’ausilio di external table e di bat file, sposta fisicamente gli archivi dei dati dall’area SFTP sulla staging area (file system). Successivamente, per mezzo della procedura “elenca_files_scaricati”, si estraggono i .dat dagli zip e si aggiorna una tabella con la lista di tutti i file estratti.Quest’ultima è utile per la terza procedura, la “elabora_files_transato”, la quale, mediante una external table, un ciclo ed un alter table, modifica di volta in volta il file da elaborare (nel seguente codice è evidenziato il file in questione). La external table è così costituita:
CREATE TABLE "STAGE_BETAX"."TRANSATO_AGS" ( "RECORD_SEQ" NUMBER,
29 "RECORD_LINE" VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "BETAX_STAGE_IN_AGS_UNZIPPED" ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8 badfile BETAX_log_ags:'transato_%a_%p.bad' logfile BETAX_log_ags:'transato_%a_%p.log' FIELDS ( RECORD_SEQ RECNUM , RECORD_LINE CHAR(100) ) ) LOCATION ( 'VENCF530.DAT' ) );
Tramite l’alter table modifichiamo la “location” del file, puntando man mano a quello successivo. Questa procedura segna contemporaneamente la fine del processo di estrazione e l’inizio del processo di trasformazione e caricamento dei dati, in quanto, per ognuno dei file trattati, effettua la chiamata alle procedure “carica_transato_alog_temp_ags” e “carica_dwh”.
La prima effettua il caricamento dei dati sulle tabelle temporanee dello staging DB, con l’ausilio di codice Java che esegue il parsing delle righe degli scontrini, assicurandosi di non trovare errori. La seconda invece effettua il caricamento sul DWH, ricercando le settimane coinvolte dai dati (“set_anno_settimane”) e creando e popolando le tabelle settimanali di transato (“crea_tabella_transato_alog” e “carica_transato”). Successivamente sfrutta la funzione “check_existing_table” per controllare se la tabella dei fatti è già esistente o è da creare (“crea_tabella_F_SCONTRINI” e “crea_tabella_F_SCONTRINI_R”). A questo punto procede con il caricamento delle tabelle in questione, delle tabelle relative agli arrivi e agli scarti, ed all’aggiornamento della vista per includere le nuove settimane caricate.
Le tabelle dei fatti sono così strutturate:
DDS_AGS_<BETAX>.F_SCONTRINI
COLUMN NAME DATA TYPE
F_SCONTRINI_SK NUMBER(32,0)
DATA DATE
ID_ASSOCIATO CHAR(2 BYTE)
CODICE_PDV NUMBER(5,0)
CASSA NUMBER(5,0)
NUMERO_SCONTRINO NUMBER(10,0)
30 CANALE_VENDITA NUMBER(1,0) OPERATORE NUMBER(6,0) TIPO_PAGAMENTO NUMBER(3,0) TOT_PUNTI NUMBER(5,0) TOT_NUMERO_PRODOTTI NUMBER(7,0) TOT_IMPORTO_NETTO NUMBER(12,2) TOT_SCONTO NUMBER(10,2)
Tabella 2: Struttura tabelle F_SCONTRINI
DDS_AGS_<BETAX>.F_SCONTRINI_R
COLUMN NAME DATA TYPE
F_SCONTRINI_FK NUMBER(32,0)
DATA DATE
ID_ASSOCIATO CHAR(2 BYTE)
CODICE_PDV NUMBER(5,0)
CODICE_CARTA_FEDELTÀ VARCHAR2(20 BYTE)
CODICE_EAN VARCHAR2(20 BYTE)
CODICE_REPARTO NUMBER(10,0)
FLAG_PROMO NUMBER(2,0)
CODICE_PROMO VARCHAR2(2 BYTE)
SCONTO NUMBER(10,2)
PUNTI NUMBER(5,0)
NUMERO_PRODOTTI NUMBER(7,0)
IMPORTO_NETTO NUMBER(12,2)
NUMERO_RIGA NUMBER(4,0)
Tabella 3: Struttura tabelle F_SCONTRINI_R
In aggiunta sono aggiornate le tabelle DM_AGS_ARRIVI e DM_AGS_SCARTI, che tengono traccia, rispettivamente, una dei punti vendita che hanno e che non hanno inviato i dati e una degli scontrini e delle righe degli scontrini che sono stati scartati in fase di caricamento, con la relativa motivazione.
La procedura che esegue il caricamento dei dati (“carica_dm_arrivi”), all’interno di ogni schema degli associati, aggiorna un’unica tabella globale presente nello schema DM_ALFA, la DM_AGS_ARRIVI.
La tabella degli arrivi presenta il seguente schema:
Campo Descrizione
NUMERO_STORE numero store presenti negli ultimi 100 giorni rolling del transato
CEDI Descrizione Store
LUN Numero store inviati lunedì della settimana riferimento
MAR Numero store inviati martedì della settimana riferimento
MER Numero store inviati mercoledì della settimana riferimento
GIO Numero store inviati giovedì della settimana riferimento
