UNIVERSITA DI PISA Dipartimento di Informatica
Corso di Laurea Magistrale in Informatica per l'economia e per l'azienda (Business Informatics)
TESI DI LAUREA
SVILUPPO DI UN PROCESSO DI INTEGRAZIONE DATI PER UN TOOL DI REPORTISTICA COMMERCIALE
RELATORE
Prof. Roberto Bruni
CANDIDATO Claudia Lancioni
Sommario
La tesi riguarda le scelte implementative dei ussi ETL all'interno di un siste-ma di Business Intelligence adottato nello sviluppo di un tool di reportistica commerciale, realizzato per un cliente, leader mondiale nel mercato del ta-bacco. L'applicativo ha l'obiettivo di fornire all'utente nale uno strumento che permetta di monitorare l'andamento delle vendite e capire di conseguenza quali strategie intraprendere per accrescere il suo pro tto. Ad oggi, il Soft-ware è stato venduto nei Paesi dell' Asia (Corea, Honk Hong, Giappone), Medio Oriente ed Europa (Belgio, Francia e UK) ed è stato customizzato secondo i requisiti del cliente.
L'ETL dovrà pertanto essere riutilizzabile nelle future implementazioni del tool. La s da è stata quella di creare un ETL essibile e dinamico, capace di garantire la qualità del dato, riduzione dei tempi e dei costi.
Il presente lavoro descrive l'attività svolta al ne di sviluppare un sistema di data warehousing allo scopo di aumentare l'e cienza e l'e cacia del processo decisionale. Il data warehouse, avente un ruolo strategico nel garantire un facile accesso alle informazioni chiave, viene utilizzato come piattaforma di reporting.
È stata implementata, mediante tecnologia Microsoft, una soluzione di inte-grazione dati, in grado di dare coerenza ai dati provenienti da fonti di dati interne ed esterne per renderli accessibili e omogenei in un unico ambiente. Il lavoro inizia con una panoramica del contesto nel quale si sviluppa il pro-getto, a seguire le scelte tecniche e gli strumenti tecnologici utilizzati e in ne il caso di studio e gli obiettivi raggiunti.
Indice
1 Introduzione 7
2 Servizi Cloud, SQL Server Microsoft 9
2.1 Cloud Computing . . . 9
2.2 Microsoft Azure . . . 12
2.3 Architettura infrastruttura tool (IaaS) . . . 17
2.4 Architettura BI e Servizi Microsoft . . . 20
3 Analisi requisiti, funzionalitátool 23 3.1 Obiettivi e analisi dei requisiti . . . 23
3.2 Moduli funzionali applicativo . . . 24
4 Progettazione - Strutture Dati 29 4.1 Data Source . . . 29
4.2 Staging . . . 30
4.3 Data Warehouse . . . 33
5 Il caso di studio: ETL dinamico e parametrico 35 5.1 Control Flow . . . 35
5.2 Data Flow . . . 54
6 Reporting 67
7 Conclusioni e sviluppi futuri 73
Bibliogra a 75
Capitolo 1
Introduzione
Le aziende che vogliono rimanere competitive sul mercato si a dano alla BI (Business Intelligence) per trarre valore dalle informazioni e dati a loro disponibili. I sistemi di BI determinano i maggiori bene ci allorchè vengano utilizzati per ottimizzare e rendere meno onerosi i processi chiave mentre si sviluppa il business.
L'obiettivo della tesi è dimostrare come seguire un approccio Template-Driven nello sviluppo del processo di ETL comporta una diminuzione degli sforzi generalmente richiesti in un progetto di DataWarehousing (+ del 50%). Usare l'approccio standardizzato che fa uso di templates garantisce velocità di sviluppo nelle future implementazioni e assicura la qualità dei dati, scala-bilità e performance.
Le principali barriere e criticità che ostacolano l'adozione di sistemi di BI riguardano la scarsa qualità dei dati e la di coltà di integrazione.
Un dato non accurato, incompleto o inacessibile comporta una bassa pro-duttivita e decisioni ine caci. Per questo motivo oltre all'ETL sono stati sviluppati sistemi di Master Data Management per mappare le relazioni tra i dati e garantire che queste siano mantenute.
Il risultato sono Dashboard più signi cative, basate su più dati, ora a dabili, accurati e aggiornati.
Per raggiungere l'obiettivo è stata implementata una soluzione di integra-zione di dati, inclusi l'estraintegra-zione, trasformaintegra-zione e caricamento di pacchetti per il data warehousing.
Nella prima fase, i dati, provenenti da sorgenti dati diverse, sono sottoposti alla fase di Data Quality e vengono ltrati, `integrati' nelle tabelle di staging tramite SSIS (SQL Server Integration Services).
Nella seconda fase, sono invece, organizzati e storicizzati nelle tabelle del DWH (DataWarehouse) mediante l'esecuzione di SP (Stored Procedures), ar-chiviate all'interno del Database.
I dati sono in ne, modellati nel Cubo mediante SSAS (SQL Server Analysis Services) e mostrati a video nei report realizzati in Tableau e SSRS (SQL Server Reporting Services), su un portale dedicato, in SharePoint.
La tesi è così strutturata:
• Capitolo 1 : Fornisce una panoramica della tecnologia utilizzata, dalla piattaforma Cloud all' architettura BI;
• Capitolo 2 : Descrive il contesto applicativo, dalle funzionalità del software all'analisi dei requisiti;
• Capitolo 3 : Spiega le strutture dati utilizzate, dalle tabelle di Staging a quelle del Data Warehouse;
• Capitolo 4 : Aronta l'intero caso studio sulle scelte implementative dei ussi ETL;
• Capitolo 5 : Mostra la reportistica del tool, dai report in SSRS a quelli sviluppati in Tableau;
• Conclusioni 6 : Saranno analizzati gli obiettivi raggiunti e i possibili sviluppi futuri applicabili al progetto.
Capitolo 2
Servizi Cloud, Microsoft Azure
2.1 Cloud Computing
La soluzione implementata per lo sviluppo dell'applicativo sfrutta il modello IaaS (Infrastructure as a Service) del servizio di Cloud Hosting di Azure, e i servizi di Microsoft SQL Server.
I servizi oerti dal cloud computing sono di base tre:
• SaaS (Software as a Service) : Consiste nell'utilizzo di programmi in remoto, ad esempio tramite un web server. Si richiede all'utente l'accesso tramite browser, senza alcuna applicazione da dover installare .
• PaaS (Platform as a Service) : Si tratta essenzialmente di una piat-taforma software remota che può essere costituita da diversi servizi, programmi, librerie.
• IaaS (Infrastructure as a Service) : In questo caso, avviene l'utilizzo di risorse hardware in remoto. In questo tipo di cloud le risorse vengono utilizzate su richiesta al momento in cui una piattaforma ne ha bisogno, e non vengono assegnate a prescindere dal loro utilizzo eettivo. I motivi più comuni per cui le aziende ricorrono al cloud computing sono:
Costo : Il cloud computing elimina le spese di capitale associate al-l'acquisto di hardware e software e alla con gurazione e alla gestione di data center locali, che richiedono rack di server, elettricità 24 ore su 24 per alimentazione e rareddamento ed esperti IT per la gestione dell'infrastruttura.
Velocità : La maggior parte dei servizi di cloud computing viene for-nita in modalità self-service e on demand, quindi è possibile eettuare il provisioning anche di grandi quantità di risorse di calcolo in pochi minuti, in genere con pochi clic del mouse, e questo garantisce alle aziende eccezionale essibilità senza la pressione legata alla necessità di piani care la capacità.
Scalabilità graduale : I vantaggi dei servizi di cloud computing in-cludono la possibilità di usufruire di scalabilità elastica. In materia di cloud questo signi ca fornire la giusta quantità di risorse IT, ad esem-pio una quantità maggiore o minore di potenza di calcolo, risorse di archiviazione e larghezza di banda, proprio quando è necessario e dalla posizione geogra ca appropriata.
Produttività : I data center locali richiedono in genere molto spazio per rack e ampliamento dei server, nonché con gurazione di hardware, applicazione di patch software e altre attività di gestione IT dispendiose in termini di tempo. Il cloud computing elimina la necessità di molte di queste attività, consentendo ai team IT di dedicare il loro tempo al raggiungimento di obiettivi aziendali più importanti.
Prestazioni : I più grandi servizi di cloud computing vengono eseguiti su una rete mondiale di data center sicuri, aggiornati regolarmente all'ultima generazione di hardware, veloce ed e ciente. Questo ore diversi vantaggi rispetto a un singolo data center aziendale, tra cui latenza di rete ridotta per le applicazioni e maggiori economie di scala. A dabilità : Il cloud computing aumenta la semplicità e riduce i costi di backup dei dati, ripristino di emergenza e continuità aziendale, grazie alla possibilità di eseguire il mirroring dei dati in più siti ridondanti nella rete del provider di servizi cloud.
Nella scelta dell'architettura cloud computing per lo sviluppo dell'applica-tivo, si è preferito utilizzare il modello IaaS invece di quello PaaS, perchè maggiormente personalizzabile in quanto si riceve semplicemente l'infrastrut-tura hardware virtualizzata su cui è possibile installare l'ambiente software preferito prima di realizzare la propria applicazione web. Invece, con il ser-vizio PaaS, il cliente riceve anche l'ambiente software, ad esempio una serie di soluzioni (sistema operativo, supporto per database, software per server web e supporto alla programmazione) da utilizzare per passare direttamente all'installazione e allo sviluppo della propria applicazione web.
In generale, le tipologie Saas, Paas ed Iaas, si dierenziano per il livello di controllo che viene concesso all'utente sull'infrastruttura del provider. IaaS rappresenta il livello di astrazione più basso e corrisponde all'utilizzo di una macchina virtuale che l'utente può con gurare con un livello di granula-rità molto ne adattandola alle proprie esigenze nel modo più opportuno. Il NIST, ovvero il National Institute of Standards and Technology, l'agenzia del governo degli Stati Uniti d'America che si occupa della gestione delle tecnologie, de nisce il cloud Iaas come quel servizio che fornisce all'utente la capacità di elaborazione, storage, le reti ed altre risorse di calcolo fondamen-tali; l'utente è in grado di distribuire ed eseguire software arbitrario, che può includere sistemi operativi e applicazioni.
L'utente non gestisce, ne controlla l'infrastruttura cloud sottostante ma con-trolla tutto il resto.
Il vantaggio di questa soluzione è appunto la capacità di controllo consentita all'utente. Scelto l'hardware più opportuno con il contratto di fornitura del servizio è poi possibile sfruttare da remoto e da computer poco potenti o da cellulari, la potenza presente su altri computer, ed eseguire elaborazioni complesse e che possono chiedere molto tempo è un vantaggio di questa vir-tualizzazione.
L'utente del servizio potendo infatti scegliere il sistema operativo che pre-ferisce, ha un grado di libertà totale e può personalizzare la con gurazione del sistema senza limiti. Allo stesso tempo bisogna occuparsi degli aspetti legati a carico della CPU, numero di applicazioni in esecuzione, occupazione su disco, gli stessi che si dovrebbero gestire su un pc reale, però così facendo l'utente non è vincolato poi nella scelta delle applicazioni da installare, per esempio può scegliere il web server con cui ha più con denza o installare il data base più adatto alle proprie esigenze.
Per quanto riguarda i costi, si paga l'infrastruttura: una istanza IaaS è un costo per il fornitore solo quando lavora e non quando è ferma, ciò vuol dire che per l'utente è conveniente una fatturazione a consumo. Solitamente in Italia, si tende a fornire un servizio con fatturazione at, perché l'utente non arriva mai a far lavorare l'infrastruttura a pieno regime, e quindi contando sui tempi morti il costo di fornitura è più basso, mentre il guadagno del provider aumenta.
I maggiori vantaggi nell'utilizzo di un modello Iaas sono :
Scalabilità : le infrastrutture IaaS consentono di aumentare e diminuire le risorse dei singoli cloud server, in tempo reale.
Pagamento pay per use : le istanze virtuali sono tariate per singola ora, o per singolo GB nel caso di servizi di storage.
Ridondanza geogra ca : le infrastrutture di cloud computing di tipo IaaS sono solitamente distribuite su più data center.
Accesso ad infrastruttura di computing, storage e networking : i provider di servizi infrastructure as a service forniscono una piat-taforma di cloud computing completa all'interno della quale il cliente può gestire anche servizi di networking, come switch e router virtuali, o servizi di storage virtualizzati.
Ad oggi, secondo uno studio studio condotto dagli analisti dell'americana Synergy Research Group, i più grandi provider del cloud mondiale sono:
Amazon con Amazon web services, Microsoft con Azure,
Google con Alphabet.
Il modello Iaas per lo sviluppo del tool, è stato fornito dalla piattaforma Microsoft Azure, conosciuta anche come Windows Azure.
2.2 Microsoft Azure
Microsoft Azure essendo una piattaforma di cloud computing, provvede alla distribuzione di servizi di calcolo, come server, risorse di archiviazione, data-base, rete, software eanalisi tramite il cloud.
Come detto in precedenza, la maggior parte dei servizi di cloud computing rientra in tre ampie categorie (IaaS, PaaS e SaaS)mostrate nella gura 1.1. Talvolta si parla di stack di cloud computing, in quanto queste categorie sono basate una sull'altra.
Paas
Si riferisce a servizi di cloud computing che forniscono un ambiente on de-mand per lo sviluppo, il test, la distribuzione e la gestione di applicazioni software. Una soluzione PaaS è progettata per consentire agli sviluppatori di creare in modo più semplice e rapido App Web o per dispositivi mobili, senza doversi preoccupare della con gurazione o della gestione dell'infrastruttura di server sottostante, della rete di archiviazione e dei database necessari per lo sviluppo.
La gura 1.2 mostra la con gurazione dell'infrastruttura per lo sviluppo e i test di un sistema PaaS standard. Questa soluzione è basata sui servizi gestiti da Azure: Visual Studio Team Services, Database SQL, Cache Redis e Application Insights.
Figura 2.2: Paas (Platform as a Service)
Nell'immagine sopra, sono mostrati tre ambienti: Dev (Development Resource Group)
QA (Quality Resource Group) Prod (Production Resource Group)
Visual Studio Team Services gestisce l'intero processo di sviluppo, mentre gli agenti di compilazione e rilascio di Microsoft Release Management distribui-scono il modello di Azure Resource Manager e il codice associato nei diversi ambienti di Dev, Qa e Prod.
Ogni ambiente ha:
• un Database SQL di Azure in cui vengono gestiti i dati per il sito Web. • un servizio di Cache Redis per migliorare le prestazioni del sito. • un'app web che esegue il sito Web.
• un'Application Insights che monitora l'applicazione Web durante lo svi-luppo e le esecuzioni dei test e di conseguenza il sistema di produzione completo quando viene rilasciato.
Saas
È un metodo per la distribuzione di applicazioni software tramite Internet, on demand e in genere in base a una sottoscrizione. Con una soluzione
Figura 2.3: Saas (Software as a Service)
SaaS, i provider di servizi cloud ospitano e gestiscono l'applicazione software e l'infrastruttura sottostante e si occupano delle attività di manutenzione, come gli aggiornamenti software e l'applicazione di patch di protezione. Gli utenti si connettono all'applicazione tramite Internet, in genere con un Web browser nel telefono, tablet o PC.
IaaS standard. Questa soluzione è basata sui servizi gestiti di Azure:Visual Studio Team Services, Azure DevTest Labs, Macchine virtuali e Application Insights.
A dierenza dell'architettura Paas, negli ambienti di test e di sviluppo del modello SaaS, si ha l'Azure DevTest Labs, che consente una migliore gestione delle macchine virtuali di cui è costituito.
Iaas
È la categoria più di base dei servizi di cloud computing. Come detto prima, con una soluzione IaaS, a tti l'infrastruttura IT, ovvero server e macchine virtuali (VM), risorse di archiviazione, reti e sistemi operativi, da un provider di servizi cloud con pagamento in base al consumo.
L'immagine sotto ra gura un esempio di architettura IAAS, in cui in tutte e tre le macchine virtuali di Azure (Dev, QA, Prod) è installato SQL Server.
Figura 2.4: Iaas (Infrastructure as a Service) utilizzato dal tool
Azure ore due opzioni per l'hosting dei carichi di lavoro di SQL Server in Microsoft Azure:
• Database SQL Azure,
• SQL Server in macchine virtuali di Azure (utilizzata per lo svi-luppo del tool) .
Figura 2.5: SQL Server in MV (IaaS) VS SQL DB Azure(Paas)
Database SQL Azure
È un database relazionale distribuito come servizio (DBaaS) ospitato nel cloud di Azure, che rientra nella categorie di settore SaaS (Software-as-a-Service) e PaaS (Platform-as-a-(Software-as-a-Service). Database SQL Azure si basa su hardware e software standardizzati appartenenti, ospitati e gestiti da Micro-soft. È possibile eseguire operazioni di sviluppo direttamente nel servizio con le funzioni e le funzionalità prede nite. Il pagamento sarà in base al consu-mo con la possibilità di aumentare o diminuire il numero di istanze per una maggiore e cienza e senza interruzioni.
SQL Server in macchine virtuali di Azure
Rientra nella categoria di settore IaaS (Infrastructure-as-a-Service) e consen-te di eseguire SQL Server in una macchina virtuale nel cloud. Analogamenconsen-te al database SQL di Azure, si basa su hardware standardizzato appartenente, ospitato e gestito da Microsoft. È possibile aumentare o ridurre le prestazioni e sospendere o riavviare la macchina virtuale in base alle esigenze.
In generale, queste due opzioni SQL sono ottimizzate per scopi diversi: • Il database SQL di Azure è ottimizzato per ridurre al minimo i costi
complessivi per il provisioning e la gestione di numerosi database. Ri-duce i costi amministrativi correnti perché non è necessario gestire le macchine virtuali, il sistema operativo o il software del database, E non
è neanche necessario gestire gli aggiornamenti, la disponibilità elevata o i backup. In generale, il database SQL di Azure può aumentare si-gni cativamente il numero di database gestiti da una singola risorsa IT o di sviluppo.
• SQL Server in macchine virtuali di Azure è ottimizzato per eseguire la migrazione di applicazioni esistenti in Azure o per estendere nel cloud le applicazioni locali esistenti in distribuzioni ibride. SQL Server in ese-cuzione in una macchina virtuale può essere usato anche per sviluppare e testare applicazioni SQL Server tradizionali. SQL Server nelle VM di Azure fornisce diritti amministrativi completi per un'istanza di SQL Server dedicata e una macchina virtuale basata sul cloud. È la scel-ta ideale quando un'organizzazione ha già delle risorse IT disponibili per mantenere le macchine virtuali. Queste funzionalità permettono di creare un sistema altamente personalizzato per soddisfare i requisiti speci ci dell'applicazione in termini di prestazioni e disponibilità.
2.3 Architettura infrastruttura tool (IaaS)
L'architettura utilizzata per la con gurazione dell'infrastruttura del tool è mostrata in gura:
Figura 2.6: Architettura Iaas del Tool
In ogni VM sono state installate le istanze di Analysis Services (AS), Repor-ting Services(RS) e il motore di Database SQL Server (SQL DB Engine). La soluzione BI comprende una soluzione di integrazione dati (SSIS), una so-luzione di modellazzione dati (SSAS), e una soso-luzione di reportistica (SSRS),
tutte quante sviluppate con Visual Studio 2012. Tutte le soluzioni sono sta-te sviluppasta-te nella macchina in locale, e deployasta-te nei tre server, ciascuno ospitato dalla macchina a cui fa riferimento:
• L'ambiente di Sviluppo è la MV (Macchina virtuale) che ospita il server di Dev.
• L'ambiente di Test ospita il Server QA (Quality Assurance) .
• L'ambiente di produzione ospita il Server di Produzione, in cui risiedono i dati eettivi del Cliente.
In ciascuno ambiente è stato installato oltre a SQL Server, il tool SQL Ser-ver Management Studio, l'applicazione che permette di gestire SQL SerSer-ver, i database e gli oggetti di database tramite interfaccia gra ca.
L'utilizzo del SQL Server nelle macchine virtuali di Azure ha facilitato la migrazione rapida al cloud con poche modi che.
Inizialmente è stata sviluppata in locale, la soluzione di un unico mercato. Il progetto è stato :
• distribuito nel catalogo SSISDB (SQL Server Integration Services Da-ta Base) in un'isDa-tanza di SQL Server Integration nella macchina di sviluppo (Dev) nel cloud,
• testato nell'ambiente di Test,
• deployato in quello di Produzione, sempre nel cloud. Tutte le macchine di sviluppo, test e produzione sono ospitate nel cloud.
La soluzione del secondo mercato non viene creata da zero, ma viene eredi-tata quella sviluppata per il primo mercato, adattando i singoli "pacchetti .dtsx" alle nuove esigenze.
I "pacchetti .dtsx" sono le di database in cui sono contenute le imposta-zioni e con guraimposta-zioni per la migrazione dei dati memorizzati da e verso più database. Sono utilizzati dal SSIS.
Gli step seguiti sono i seguenti:
• Viene deployato il progetto del 1' mercato, presente nel Catalogo SSI-SDB nel server Integration dell'ambiente di Produzione, nel Visual Studio in locale.
• Il progetto subirà delle modi che a seconda delle esigenze richieste ora dal nuovo mercato, facendo attenzione che queste nuove modi che non vadano ad impattare quanto è stato fatto per il primo mercato.
• La nuova versione verrà distribuita nella nuova istanza dedicata al nuo-vo mercato sempre nello stesso Catalog SSISDB del server Integration che ospitava l'istanza del primo mercato, nella macchina di Sviluppo. Verrà poi deployata in QA per essere testata, e deployata alla ne in Produzione.
• Si ripetono le operazioni sopra, per il terzo mercato.
Si fa una migrazione del progetto di un mercato già esistente, dal Ca-talog in SSISDB del Server Integration al Visual Studio in locale e viceversa per il nuovo mercato. La solution verrà così modi cata me-diante il tool Integration Services di Visual Studio in locale e distribuita nuovamente nel Catalog in una nuova istanza (sempre nel cloud). L'esecuzione viene schedulata mediante SQL Server Agent. Il risultato è un'unica soluzione di integrazione dati essibile e valida per tutti e tre i mercati per cui è stata sviluppata (Egitto, Senegal e Marocco) Queste operazioni si ripetono ogni volta che viene creata una nuova solution per un nuovo Mercato.
L'immagine sotto illustra i passaggi dal locale (on- promises) al Cloud :
Oltre alla solution, anche i Database sono replicati e si tengono le tabelle, viste, Store Procedures, funzioni, etc., ntantochè possano essere
riutilizzate.
2.4 Architettura BI e Servizi Microsoft
I servizi Microsoft utilizzati nell'architettura Bi sono : • SQL Server Integration Services (SSIS)
• SQL Server Analysis Services (SSAS) • SQL Server Reporting Services (SSRS) • SQL Server Management Studio (SSMS)
Le Dashboard sono realizzate in Tableau, e il portale in SharePoint.
Il Modello di Architettura di BI utilizzato è rappresentato nella gura se-guente:
Figura 2.8: Modello Architettura BI
Le basi di dati e i le che forniscono i dati sui quali sviluppare le informazioni, costituiscono il livello Data Source.
I le contententi le informazioni interne all'azienda sono forniti dall' Infor-mation Tecnology Team del Cliente e da un applicativo SFA (Sales Force Automation) che supporta la gestione delle relazioni con i clienti.
una società esterna, nota per essere la più grande società al mondo di servizi informativi di marketing che ore a supporto del processo decisionale delle aziende utenti, un usso continuo di informazioni alimentato mediante rile-vazioni continuative condotte in diversi settori di mercato.
Nella prima fase, sui dati agiscono gli strumenti del livello di Data Quality e Data Integration:
• I dati sono ltrati nelle tabelle di staging tramite SSIS, secondo delle regole stabilite in precedenza per garantire la completezza e la qualità del dato e allo stesso tempo la essibilità e la riduzione di carico dello strumento di estrazione.
• Dall'Area di Staging i dati subiranno un processo di Integrazione e Trasformazione, per essere accessibili e omogenei in un unico ambiente, il DWH. Nel DWH i dati sono storicizzati e consolidati e rappresentano la fonte da cui attingere per le analisi.
Nella fase successiva, i dati che popolano le tabelle del DWH saranno orga-nizzati se e quando necessario in delle viste materializzate e non , per poi essere modellati nel Cubo (SSAS) e mostrati a video nei report realizzati in Tableau e SSRS, sul portale in SharePoint.
Capitolo 3
Analisi requisiti, funzionalitá tool
3.1 Obiettivi e analisi dei requisiti
Il motivo per cui le aziende, oggi impegnate in un processo di evoluzione digi-tale, si a dano alla business intelligence è aumentare la propria reddittività attraverso il valore che riescono a trarre dalla moltitudine di informazioni e dati che si trovano a gestire. Per un'azienda riuscire ad essere competitivi, al giorno d'oggi, signi ca avere un quadro chiaro delle dinamiche che rego-lano le sue attività. Un business che miri ad essere vincente deve poter fare a damento su una reportistica esauriente e su un'attenta analisi in grado di anticipare scenari futuri. I dati devono essere disponibili con immediatezza e in questo modo si potranno assumere decisioni con rapidità ottimizzando i risultati.
Il tool di reportistica commerciale per il quale sono stati sviluppati i sistemi ETL e MDM è basato su un sistema di Business Intelligence, realizzata per aiutare il Cliente a migliorare la gestione del proprio business grazie ai dati disponibili.
Il Cliente è una società di beni di consumo e principale produttore al mondo di tabacco. Ha il proprio headquarter negli Stati Uniti, a New York, ed il centro operativo in Svizzera.
La solution realizzata ha l'obiettivo di aiutare il Cliente a sfruttare il valore di tutti i suoi dati, renderlo autonomo nelle sue analisi, e a raggiungere piena consapevolezza dell'andamento aziendale.
Il problema da arontare è quello di dare coerenza ai dati provenienti da di-verse applicazioni progettate per scopi diversi, renderli accessibili e omogenei in un unico ambiente.
Da qui la necessità di implementare un sistema di Datawarehousing che 23
permetesse di:
• Analizzare le vendite del passato e presente per capire i fattori di successo e fallimento;
• Individuare i trend per prodotto, zona e POS (Point of Sales); • Analizzare i risultati delle promozioni;
• Comprendere le preferenze degli utenti per inviare promozioni mirate; • Individuare i clienti che non acquistano più e capire come riconquistarli; • Monitorare l'andamento degli ordini e del fatturato;
• Monitorare le spedizioni e consegne: puntualità, distribuzione nel tem-po e nello spazio.
Il Datawarehouse integra i dati insterni ed esterni dell'azienda degli ultimi cinque anni: I dati vengono forniti in parte dal'IT del Cliente, in parte da una società esterna.
I dati non vengono modi cati dall'utente ma storicizzati, e quando incomple-ti, tramite i sistemi MDM (Master Data Management), si richiede al cliente stesso di completare le informazioni mancanti, inserendo i dati mancanti e mappandoli a quelli esistenti.
I requisiti sopra citati dovranno essere soddisfatti a nchè i pilars, moduli funzionali del tool soddis no le esigenze conoscitve dell'utente nale.
3.2 Moduli funzionali applicativo
Il tool comprende i quattro pilars mostrati nella gura 2.1: • Volumi
• Merchandising • Trade Engagement • Consumer Engagement
Tutti e quattro hanno l'obiettivo di fornire agli utenti le Actionable Insight, informazioni che forniscono una panoramica chiara non solo della situazione attuale e passata ma in grado di delineare uno scenario futuro a nchè i di-rigenti possano mettere in atto un piano strategico.
Figura 3.1: Moduli funzionali del tool
L'utente potrà:
• Analizzare l'andamento delle vendite sulla base dei volumi e KPI mo-strati nelle dashboard e standard reports
• Essere aggiornato sulle attività di merchandising, sulle performance dei suoi punti vendita (POS), dei suoi Customers
• Conoscere meglio i suoi Consumers.
Il modulo dei volumi consente all'utente di monitorare l'andamento dei vo-lumi e della Share Of Market dei prodotti, e di conseguenza lo faciliterà nel:
• Ridurre gli sforzi per monitorare i singoli Distributori e Merchandiser; • Ottimizzare le performance delle vendite e la Trade E ciency ( KPI che identi ca se la merce è stata venduta a un buon prezzo; al 100% identi ca una e cienza ottima);
• Ridurre l'indice di volatilità dei volumi e delizzare i clienti; • Ottimizzare le spese per le campagne di marketing;
• Soermarsi sugli eetti dei fenomeni esogeni contro quelli controllabili interni;
• Avere evidenza del variare delle vendite in seguito alle iniziative di Consumer Engagement e Trade Engagement.
Il Modulo Merchandise informa l'utente sul reale potenziale del punto ven-dita che può dipendere non solo dalla locazione dello store ma anche dalla gestione sica dei prodotti esposti.
L'utente potrà analizzare la forza di vendita dei distributori, o POS attraver-so il monitoraggio dei KPI raccolti in seguito alle attività dei Merchandisers, i quali visitando i punti vendita, si assicurano che i prodotti siano posti in vendita, con la giusta visibilità, secondo le condizioni pattuite. Vengono così monitorate le campagne pubblicitarie a nche siano correttamente piazzate, la programmazione e la periodicità delle visite al punto vendita, la disponi-bilità e visidisponi-bilità del prodotto.
Il modulo Trade Engagement consente di monitorare le performance dei for-nitori sulla base di KPI che rivelano il numero dei contratti, la tipologia di contratto, gli ordini emessi, la valutazione dei Mystery Shoppers ( persone in grado di valutare la vendita di un servizio o di un prodotto ngendo di essere un vero cliente; partecipano ad un processo di acquisto reale o simulato ed hanno il compito di valutare i comportamenti, la gestione e la capacità del personale coinvolto nell'erogazione del servizio), e l'e cienza dei programmi di Retail Advocacy, miranti al coinvolgimento diretto dei dipendenti nel so-stenere e promuovere la società, attraverso la condivisione di contenuti sui propri pro li social, email, forum.. . Il Trade Engadgement vede nel Retail una gura in grado di raorzare la brand awareness, delizzare i clienti, e aumentare la ducia dei consumatori, con l'obiettivo di incrementare la red-ditività.
Attraverso il modulo Consumer Engagement, invece, l'utente può analizzare l'impatto che il brand ha sul consumatore, monitorando i suoi acquisti, le sue visite nello store e online, monitorando tutti i canali utilizzati dal Consumer per informarsi sul prodotto. Per analizzare il comportamento dei consuma-tori è necessario andare a monte del processo di acquisto.
Bisogna capire cosa guardano i clienti, dove vanno, come chiedono, in che modo confrontano e cosa pensano nel valutare un prodotto o un servizio. L'utente potrà adottare delle misure per orire ai suoi clienti un'esperienza veramente personalizzata dinamica utilizzando delle analitiche evolute che consentono di registrare, monitorare e tracciare i percorsi, le scelte e i com-portamenti d'acquisto.
dalle Dashboard e Standard Report, raccolte nel portale realizzato in Share-point v.2013, mostrato nella gura 2.2.
Figura 3.2: Portale Web
I moduli sono rappresentati visamente dalle icone ra gurate nella gura 2.3 e compaiono a sinistra, in cascata nel menu del portale.
Figura 3.3: Icone dei moduli della Web App
Cliccando, ad esempio, sull'icona dei volumi appariranno nella barra a destra le form contenenti laReport Library (Standard Report e Report ad hoc) e le Dashbord. Quest'ultime sono realizzate in Tableau e sono una collezione di Report e KPI. Possono essere personalizzate eettuando operazioni di Drill Down nelle bar Chart e "zoomando" le chart di interesse:
Figura 3.4: Drill-Down chart
Gli standard Report realizzati in SSRS, hanno un layout prede nito. Le informazioni sono strutturate e consolidate nel tempo. È possibile esportare i report con il formato PDF o Excel.
Figura 3.5: Esempio layout Standard Report
Report e Dashboard verranno mostrate nel dettaglio, nel capitolo dedicato alla Reportistica.
Capitolo 4
Progettazione - Strutture Dati
4.1 Data Source
La fonte dati da cui la Soluzione ETL prende i dati è costituita da più le che fanno riferimento alle informazioni consolidate nel DWH, relative a:
• Customer (POS, Punti vendita) • Geogra a • Produzione • Contratti • Volumi • Employee (Dipendenti) • Merchandise
Le informazioni relativi ai prodotti del cliente riesiedono in parte nel Databa-se aziendale e in parte sono invece forniti, tramite le, da una società esterna, in cui sono presenti anche le informazioni dei prodotti dei concorrenti. La dimensione tempo è invece, generata automaticamente, tramite una Store Procedure.
Inizialmente i le che devono essere processati vengono caricati nel server in una cartella denominata PROCESS. Una volta processati, vengono archi-viati nella cartella ARCHIVE. Se i le sono non sono validi, ad esempio hanno un formato che non ci si aspetta non vengono processati ma spostati nella cartella ERROR.
Ogni le ha dei requisiti che deve rispettare. Ad esempio, Il le dei Custo-mers deve avere il formato CSV (comma separated-value).
Il nome del le sarà formato da:
• Sigla del mercato a cui si riferisce.
• Descrizione del le (ad es. Customer, Employee). • Data di quando è stato generato il le.
• Un contatore per distinguere due le dello stesso tipo che arrivano lo stesso giorno, quindi con la stessa data.
Nella tabella 3.1 sono elencate alcuni dei requisiti che i le devono rispettare come il formato (Format), il separatore di testo (Separator), l'intestazione (Heading), il nome (Name), le informazioni relative alla sorgente dati (DS), e campi obbligatori (Mandatory)
File Format Separator Heading Name DS Mandatory
Customer csv , No MA_Customer_2018-01-15-01.csv IT/SFA Mercato, CodiceCustomer
Volumi xls N/A Si MA_Volumi_2018-01-15-02.csv IT/SFA Data, Mercato,Codice Prodotto
Produzione dat | Si MA_Produzione_2018-01-15-05.csv Nielsen Data, Mercato,Codice Prodotto
Employee dat | Si MA_Employee_2018-01-15-06.csv SFA Mercato,CodiceEmployee
Contratti dat | Si MA_Contratti_2018-01-15-07.csv SFA Mercato, CodiceContratto
Tabella 4.1: Elenco dei requisiti di dettaglio dei le in input
4.2 Staging
Le modalità di importazione dei dati dai le alle tabelle di staging è oggetto di studio del lavoro presentato e verrà descritto nel capitolo 4.
Nel database ci sono tante tabelle di staging quanti sono i le importati. Nelle tabelle 3.2, 3.3 e 3.4, sono riportati degli esempi di strutture di tabelle di Staging in cui vengono importati i dati relativi ,rispettivamente, ai Prodotti, Customer e Geography. I dati di queste tabelle andranno a popolare, nello step successivo, le tabelle dimensionali comuni alle tabelle dei fatti nel Cubo OLAP. La tabella 3.5 contiene gli attributi che descrivono le transazioni dei Volumi nello Staging, mentre la 3.6 è la dimensione relativa al Tempo, (DimTime).
Nome Attributi Descrizione ISNULL IS Key
CodiceProdotto Codice identi cativo del prodotto No No
DescrizioneProdotto Descrizione del prodotto Si No
CodiceSottoFamigliaProdotto Codice della sotto-famiglia del prodotto Si No
DescrizioneSottoFamigliaProdotto Descrizione della sotto-famiglia del prodotto Si No
CodiceFamigliaProdotto Codice-famiglia del prodotto Si No
DescrizioneFamigliaProdotto Descrizione della famiglia del prodotto Si No
CodiceProduttoreProdotto Codice del produttore del prodotto Si No
DescrizioneProduttoreProdotto Descrizione del produttore del prodotto Si No
DescrizioneTipoProdoctto Descrizione del tipo del prodotto Si No
DescrizioneLineaProdotto Descrizione della linea del prodotto Si No
PrezzoLocale Prezzo locale di vendita del prodotto Si No
ValutaPrezzoLocale Valuta relativa al prezzo di vendita al pubblico Si No
ClassePrezzo Classi cazione Prezzo Si No
SigarettePerPacco Numero di sigarette per pacco Si No
SigarettePerCartone Numero di sigarette per cartone Si No
Tabella 4.2: Staging Prodotti
Nome Attributi Descrizione ISNULL IS Key
CodiceCustomer Codice univoco del Customer No Si
NomeDistributore Nome del distributore si No
Regione Regione del Customer (POS, Point Of Sales) Si No
Area Area del Customer (POS, Point of sales) Si No
Città Città del Customer (POS, Point of sales) si No
Mercato Mercato No No
Latitudine Latitudin si No
Longitudine Longitudin si No
Nome Attributi Descrizione ISNULL IS Key
Mercato Mercato Si Si
Regione Regione del Customer(POS, Point of sales) Si No
Area Area del Customer (POS, Point of sales) Si No
Città Città del Customer (POS, Point of sales) Si No
Tabella 4.4: Staging Geography
Nome Attributi Descrizione ISNULL IS Key
Data Data di registrazione dei volumi venduti No Si
Mercato Mercato No No
CodiceCustomer Codice del Customer che ha venduto il prodotto No No
CodiceProdotto Codice del prodotto cui si riferisce il volume No No
UnitàDiMisura Unità di misura Si No
Volume Volume venduto Si No
Tabella 4.5: Staging Volumi
Nome Attributi Descrizione ISNULL IS Key
CalendarioDataKey Chiave che identi ca univocamente una data No Si
GiornoDellaSettimana Numero di giorni della Settimana No No
GiornidelMese Numero di giorni del Mese Si No
GiornidellAnno Numero giorni dell'anno Si No
SettimanaDelMese Numero Settimane del Mese Si No
SettimanaDellAnno Numero Settimane dell'anno Si No
MeseID Anno + NumeroMese es. '20150-01' Si No
QuadrimestreID Anno + Numero dell'ultimo mese e giorno del quadrimestre Si No
MeseDellAnno Mese dell'anno Si No
SettimanaID Settimana dell'anno Si No
4.3 Data Warehouse
Nello step successivo all'importazione in staging, i dati verranno ulterior-mente elaborati e inseriti nelle tabelle delle dimensioni e dei fatti del DWH, tramite Stored Procedures che vengono chiamate dalla stessa Solution che si occupa dell' estrazione dei dati dai le e del caricamento nelle tabelle di Staging
I dati nelle tabelle di Staging sono identi cati da una chiave naturale, che può essere formata da uno o più attributi. Nelle tabelle dimensionali i dati sono raggrupati secondo la chiave naturale e per ognuna di esse è stata creata una chiave surrogata (Primary Key). Nelle tabelle dimensionali non ci saranno duplicati, ma ogni record sarà individuato dalla Primary Key.
Nella gura 3.1 sono rappresentati degli esempi di connessioni tra le due ta-belle dei fatti relative ai Volumi e Merchandise e le tata-belle dimensionali. La tabella dei fatti dei Volumi ha la chiave primaria VolumeKey, e le Foreign Key relative ai Customer, Product, Geography e Time.
La misura è il volume di un determinato prodotto venduto in una certa data a da un Customer speci co di un determinato mercato.
Il volume è una misura numerica e addittiva. La tabella dei fatti di Merchan-dise misura le risposte relativi ai questionari posti dal MerchanMerchan-diser/Employee agli utenti.
Capitolo 5
Il caso di studio: ETL dinamico e
parametrico
5.1 Control Flow
Oggetto di studio della tesi è lo sviluppo di una soluzione ETL (Estrazio-ne, Trasformazione e Caricamento dei pacchetti per il Data Warehousing) in grado di processare dinamicamente i le strutturati secondo le speci che accordate durante l'analisi dei requisiti.
La condizione necessaria per un'esecuzione dinamica delle attività che prove-dono alla lettura, trasformazione e caricamento dei dati dai le alle tabelle di staging, è la parametrizzazione delle variabili utilizzate in fase di esecuzione. Inizialmente ogni le è letto da un singolo "pacchetto .dtsx". Ciascun pac-chetto può leggere sequenzialmente più le contenenti lo stesso tipo di in-formazioni, ma non le di diverso contenuto. Ad esempio, un pacchetto è in grado di processare più le dei Customers, ma non può processare i le dei Customers e quello degli Employee, perchè è stato con gurato secondo le speci che del primo. Di cilmente un pacchetto può essere con gurato per processare le di diverso contenuto a meno che essi non abbiano la stessa struttura.
Esisteranno pertanto, tanti pacchetti quante sono le tipologie dei le, in-tendendo per esse il contenuto informativo.
Eseguire dinamicamente i pacchetti signi ca che si può scegliere se fare un'e-strazione e caricamento di tutti i le, o un'eun'e-strazione e caricamento di dei
soli le che si desidera.
In un processo dinamico non si ha priorità di esecuzione ma i task vengono processati in maniera parallela.
La parallelizzazione è resa possibile mediante la parametrizzazione delle va-riabili in cui vengono memorizzate le informazioni relative a:
- Nome del le.
- Cartella in cui è archiviato il le. - Numero delle colonne del le. - Estensione del le.
- Phase: classe di appartenenza del formato nome del le. - Nome della tabella di staging in cui sono destinati i dati del le. La soluzione SSIS ha come root, nodo iniziale, il pacchetto chiamato Or-chestrator, proprio perchè ha la funzione di orchestrare tutte le attività dall'estrazione dei dati dal le al processo del cubo. In esso vengono eseguiti all'interno di un Sequence Container (Contenitore di Sequenza) i "pacchetti .dtsx" che eseguono il caricamento in STG (Staging), popolamento del DWH, e processo del Cubo.
Nella gura 4.1, sono rappresentati , da sinistra a destra, gli "Execute Package Task" che consentono di eseguire nel processo del pachetto padre (Orchestra-tor), i pacchetti " gli" contenuti nello stesso progetto in cui è contenuto il pachetto padre ("Orchestrator"). I tre task sono connessi da "precedence
Figura 5.1: "Execute Package tasks" :1 Caricamento dello STG, 2 Popolamento del DWH, 3 Processo del Cubo.
constraint", vincoli di precedenza, che stabiliscono l'ordine di esecuzione. Verranno perciò eseguiti in ordine di priorità i seguenti task:
• EPT- Staging Loading (Execute Package Task Staging Loading). • EPT- Dimension&Fact Loading (Execute Package Task
Dimen-sion&Fact Loading).
• EPT- Cube Processing (Execute Package Task Cube Processing). I pacchetti che caricano i dati di ciascun le nello Staging sono organizzati in quettro blocchi. Ciascun blocco processa un quarto dei pacchetti totali. Ciò signi ca che se il numero totale dei pacchetti è 40, allora ciascun blocco ne processerà 10. L'appartenenza di un pacchetto a un blocco è indicata nella colonna Phase della tabella di con gurazione salvata nel database.
La gura 4.2 ra gura i quattro blocchi contenuti nel task EPT- Staging Loading:
• E_MSL_Block1 (Management StagingLoading_Block1) • E_MSL_Block2 (Management StagingLoading_Block2) • E_MSL_Block3 (Management StagingLoading_Block3) • E_MSL_Block4 (Management StagingLoading_Block4)
Figura 5.2: 1) E_MSL_Block1; 2) E_MSL_Block2, 3) E_MSL_Block3, 4) E_MSL_Block4
Ciascun blocco è rappresentato da un task indipendente, senza vincoli di precedenza, all'interno del contenitore sequenza denominato Parallel Exe-cution.
L'esecuzione dei task avviene parrallelamente e ognuno di essi esegue il rispet-tivo pacchetto glio, mostrato nella gura 4.3: Il primo task "Block1" è un
Figura 5.3: E_MSL_Block1.dtsx
Execute SQL Task, che esegue l'istruzione scritta in codice SQL all'interno del SQL Statement.
Figura 5.4: Execute Task SQL Editor (Block1)
Cliccando nella riga del SQL Statement è possibile visualizzare il codice inserito.
Figura 5.5: Script SQL Statement
Lo script seleziona dalla tabella di con gurazione : • Nome del Pacchetto,
• Nome della cartella in cui è memorizzato il le, • Numero delle colonne del le,
• Estensione del le,
• Phase: classe di appartenenza del formato nome del le. • Nome della tabella di staging in cui sono destinati i dati del le. I valori restituiti dalla query vengono mappati nelle variabile locali del Fo-reach Loop Container indicato con il valore numerico "1 ", che indica il numero del blocco di appartenenza, e passate al Execute Package task al-l'interno del contenitore, denominato STG Table Block1 .
L'immagine 4.6 mostra il mapping delle variabile nel Container.
Figura 5.6: Variable Mapping
La gura 4.7 mostra il mapping delle variabili locali ai parametri del pachetto glio.
Figura 5.7: Parameter Binding
Il nome del pacchetto glio è invece mostrato nella gura 4.8 nell'editor Package
Figura 5.8: Child Package Setting
Il pachetto glio è responsabile della:
• Validazione della correttezza del formato del le.
• Controllo della qualità sui dati che vengono caricati nel sistema di destinazione (tabelle di Staging).
Entrambe le operazioni vengono eettuate in momenti consecutivi: la prima nel usso di controllo, la seconda nel usso di dati di ciascun pacchetto di Integration Services che processa il singolo le.
L'intero Flusso di controllo è riportato nella gura 4.9.
Figura 5.9: Control Flow
Nel Flusso di Controllo vengono eettuati, in ordine di esecuzione, tre check:
• Veri ca della correttezza del nome del le. Il Nome del le deve rispettare lo schema:
Ad es. 'Volumi_20170115001.xls' = 'Volumi' + '20170115' + '001'. Se la data non è valida o il nome non corrisponde a quello previsto, il le non verrà processato.
Il controllo viene eettuato da una Store Procedure che viene eseguita nel primo Execute SQL task contenuto all'interno del Foreach loop container denominato FELC Nielsen, il nome FELC è l'abbreviati-vo di Foreach loop container, Nielsen è il tipo di le processato dal pacchetto.
Il valore restituito dall SP sarà uguale a zero se il le ha un nome cor-retto, maggiore di zero, viceversa.
L'istruzione SQL è dichiarata nel Sql Statement nel Execute SQL task mostrato nella gura 4.10. Lo script SQL è il seguente:
Figura 5.10: Sql Statement
EXEC ETL.CheckFileName ?, ?, ? EXEC è l'istruzione SQL,
ETL.CheckFileName è il nome della Store Procedure (SP): - ETL = schema del Database in cui è memorizzata.
- CheckFileName = nome SP.
- " ?, ?, ?" indicano i parametri che vengono passati alla SP secon-do l'ordine dichiarato nella colonna "Parameter Name" nel "Parameter Mapping" del task "SQL-Check File Name", mostrato nella gura 4.11. Il primo prarametro si riferisce al nome del usso dei dati, il secondo al nome della cartella in cui risiede il le, il terzo alla "Phase" . Questi
Figura 5.11: Parameter Mapping del task SQL-Check File Name
tre parametri sono dichiarati localmente con i nomi @varFolderNa-me, @varFileName e @varPhase. La prima indica il nome della cartella in cui è memorizzato il le, il terzo la Phase. Entrambi vengo-no ereditati dal pacchetto padre. La seconda viene, invece, valorizzata all'interno del contenitore FELC Nielsen : viene de nita nella Di-rectory il percorso della cartella in cui è presente il le, indicato dalla variabile @varFolderPath.
Il processo trova il le e il nome vien mappato nella variabile @varFi-leName.
La gura 4.12 mostra la valorizzazione della Directory nel Foreach Loop Container.
Figura 5.12: Foreach Loop Editor-Directory Setting
La gura 4.13 mostra il mapping della variabile @varFileName.
Il valore restituito dalla Stored Pocedures verrà mappato nella viariabile User::varIsIncorrectFile,dichiarata localmente.
Figura 5.14: Mapping del valore restituito dalla Stored Procedure
I valori delle variabili ereditate dal pacchetto padre sono contenute all'interno della tabella di con gurazione, de nita nel Database in SQL Server, in cui sono riportate le informazioni relative :
Nome del pacchetto che processa il le, PackageName, Nome della cartella in cui è inserito il le, FolderName, Numero delle colonne, ColumnNumber,
Estensione, FileEstension
Classi o labels: Enable e Tread.
La prima può assumere i valori zero o uno: zero se non si vuole che il pacchetto venga processato, uno viceversa.
La label Tread indica il blocco a cui appartiene il pacchetto: i pacchetti sono poco più 40 e sono distribuiti in 4 blocchi eseguiti in parallelo.
La tabella di Con gurazione è mostrata di seguito:
ID Package Folder Extension ColumnNumber Enable Tread Table
1 StgCustomer.dtsx Customer csv 20 1 1 [Stg].[StgCustomer]
2 StgVolume.dtsx Volume xls 5 1 1 [Stg].[StgVolumes]
3 StgContract.dtsx Contract csv 13 1 1 [Stg].[StgContract]
4 StgEmployee.dtsx Employee xls 15 0 3 [Stg].[StgEmployee]
5 StgProduction.dtsx Production csv 25 0 3 [Stg].[StgProduction]
6 StgNielsen.dtsx Nielsen xls 30 0 4 [Stg].[StgNielsen]
Tabella 5.1: Tabella di con gurazione delle variabili
Se il nome del le è corretto, il processo continua e verrà eseguito il secondo task, Check Column and File Extension, che veri ca la cor-rettezza del numero delle colonne e dell'estensione del le.
Se invece, non è corretto, verra eseguito il task SQL-InsertLogInvalidName, che aggiorna la tabella ETL.LogFileProcessing in cui vengono loggati gli esiti dei processi di tutti i pacchetti che compongono la soluzione ETL.
Verrà peratanto, inserito nella Colonna Status della tabella, il valore Failed e nel LogDescription, il tipo di errore: The le name is not in expected format.
Il le verrà poi spostato nella cartella InvalidFile tramite il task File System Task Editor.
L'esecuzione dei task all'interno del contenitore è stabilito mediante i vincoli di precedenza, utilizzando la proprietà ExpressionAndCon-straint, che controlla che il vincolo sia veri cato e che l'espressione restituisca il valore True a nchè si possa procedere con l'esecuzione del task connesso in uscita.
La gura 4.15 mostra la condizione necessaria a nchè il task Check Column and File Extension possa essere eseguito. La condizione è de -nita nell'Expression con la sintassi : @[User::varIsIncorrectFile]==0 . Nella variabile @varIsIncorrectFile è stato mappato il valore restituito dalla Stored Procedures eseguita dal task Padre, SQL -CheckFileName.
Figura 5.15: Precedence Constraint Editor
La gura 4.16 mostra invece, le logiche di processo: se la variabile @varIsIncorrectFile è uguale a zero verrà processato il task a destra, altrimenti verrà eseguito quello immediatamente sotto.
Figura 5.16: Logiche di processo
• Veri ca del numero delle colonne e dell'estensione del le. Il check sul numero di colonne e estensione viene eettuato mediante lo script Task, Check Column and File Extension. L'imagine sotto (Figura 4.17) mostra la con gurazione del Task: nella riga ReadOnly-Variablessono inserite le variabili di input User::varColumnNumber, User::varFileName , User::varFilePath e User::varFileExtension.
Queste variabili verranno utilizzate in lettura. Il valore restituito dallo script sarà invece memorizzato nella variabile User::varFileValidityFlag nella riga ReadWriteVariables. Il valore sarà un'intero: 0 se la validazione è corretta, 1 viceversa.
Figura 5.17: Script Task Editor
Lo script è strutturato nel seguente modo:
Inizialmente vengono dichiarate, in locale, le variabili a cui vengo-no assegnati i valori delle variabili passate in input: FolderPath, ColumnCountExpected, FileName e FileExt. I valori di queste va-ribili sono i valori che ci si aspetta, ovvero i valori de niti nella tabella di Con gurazione per quel determinato le.
Successivamente vengono ricavati il numero delle colonne e l'esten-sion del le mediante la funzione strmFile.ReadLine(); tramite la quale si memorizza in una variabile di tipo Stringa, strFir-stLine, l'intestazione del le che contiene sia il numero delle co-lonne che l'estensione, separati da una virgola. Tramite la funzio-ne strFirstLifunzio-ne.Split(',').Length si memorizza in una variabile di tipo Int, chiamata colCount, il numero delle virgole che coin-cide con il numero delle colonne. Lo script che esegue l'istruzione sopra, è scritto con il linguaggio c# ed è il seguente:
STRING strFirstLine = strmFile.ReadLine(); INT colCount = strFirstLine.Split(',').Length;
Il controllo sul numero delle colonne e sull'estensione del le viene fatto confrontando la variabili relative ai valori che ci si aspetta con quelli eettivi, sia delle colonne che dell'estensione. Per cui, se i valori coincidono il valore restituito sarà 0 altrimenti 1: Il controllo viene eseguito con questa porzione di codice, sempre in c#:
IF (
(colCount == ColumnCountExpected) & (FileExt == CheckExt)
)
Dts.Variables["User::varFileValidityFlag"].Value = 0; ELSE
Dts.Variables["User::varFileValidityFlag"].Value = 1;
L'intero script è il seguente:
try { STRING FolderPath = Dts.Variables["User::varFilePath"].Value.ToSTRING(); INT32 ColumnCountExpected = (INT)Dts.Variables["User::varColumnNumber"].Value; STRING FileName = Dts.Variables["User::varFileName"].Value.ToSTRING(); STRING FileExt = Path.GetExtension(FileName); STRING CheckExt = Dts.Variables["User::varFileExtension"].Value.ToSTRING(); using (System.IO.StreamReader strmFile =
new System.IO.StreamReader(FolderPath)) {
STRING strFirstLine = strmFile.ReadLine(); INT colCount = strFirstLine.Split(',').Length; INT32 ColumnFlag = 0;
IF ((colCount == ColumnCountExpected) & (FileExt != CheckExt)) { Dts.Variables["User::varFileValidityFlag"].Value = 0; } ELSE { Dts.Variables["User::varFileValidityFlag"].Value = 1;
} } Dts.TaskResult = (INT)ScriptResults.Success; } CATCH (Exception e) { Dts.TaskResult = (INT)ScriptResults.Failure; } }
Se il valore restituito dallo script è uguale a uno, il le non verrà pro-cessato perchè signi ca che una delle due condizioni o entrambe non sono soddisfatte.
Verrà così, eseguito il task connesso al vincolo di precedenza, Insert Log Column or Extension in cui viene eseguita un'istruzione SQL , direttamente nel DB, tramite SQL Statement, che aggiorna la tabella ETL.LogFileProcessing.
Nella colonna Status, che indica lo stato attuale di esecuzione del task, verrà inserito il valore Failed mentre nel logDescription il log che descrive lo stato di processo: "The le has wrong column count or extension".
Al termine dell'esecuzione del task, si avvierà il task immediatamen-te sucessivo, connesso con il vincolo di precedenza, che provederà allo spostamento del le nella cartella degli InvalidFile. Se invece il valore restituito dallo script sarà zero, il le non verrà scartato, ma continuerà ad essere processato.
Nella gura sotto è riportata la logica di esecuzione dello Script Task, Check Column and File Extensione.
Figura 5.18: Logica di processo - Script Task
• Controllo se il le è stato già processato
Tramite l' Execute SQL Task , SQL - Check ProcessingStatus vie-ne interrogata la tabella ETL.LogFileProcessing, contevie-nente tutti i le processati sino a quel momento. Se il nome del le attuale è presente nella colonna FileName della tabella, signi ca che il le è stato già processato, per cui verrà restituito il valore 1 e il le verrà scartato. Lo script restituirà un valore pari a zero se invece il nome non esiste nella tabella. Lo script SQL è inserito nel SQL Statement, ed è il seguente:
IF EXISTS ( SELECT [FileName]
FROM ETL.LogFileProcessing
WHERE [FileName] = ? AND ProcessingStatus <> 'Failed' )
SELECT 1 AS ProcessedStatus ELSE
SELECT 0 AS ProcessedStatus
Il valore restituito dallo script, con il nome ProcessedStatus viene mappato nella variabile User::varIsProcessed dichiarata localmente nel pacchetto, come mostrato nella gura sotto:
Figura 5.19: Mapping User::varIsProcessed
Se la variabile assume il valore 1 verrà eseguito il task cSQL Insert Log Processed, se assumerà il valore 0 il task SQL Truncate Staging Table, nel quale viene troncata la tabella di STG (Staging) per poi essere ricaricata con i dati del le.
Figura 5.20: Logica di processo
Il log inserito nella tabella ETL.FileProcessingStatus avrà nello status il valore Failed, nel logDescription il valore The le was processed before. Il task successivo al SQL Truncate Staging Table è quello del Data Flow nel quale avviene la traformazione e il caricamento dei dati nelle tabelle di Staging.
5.2 Data Flow
Il usso di dati utilizza il template qui sotto:
Figura 5.21: "Data Flow"
Le operazioni svolte nel Flusso Dati di tutti i pacchetti di integrazione in Staging, sono quasi sempre le stesse:
• Conversione delle date e dei numeri nel formato desiderato.
• Creazione di una nuova colonna che identi ca il mercato a cui appar-tiene il le.
• Controllo della presenza dell'intera chiave naturale. (Se manca anche solo un attributo che compone la chiave naturale, l'intera riga viene scartata).
I le in input sono di tipo .csv, .xls o .dat. Per ciascuno di essi, Integra-tion Services fornisce diverse sorgente dati, quali FlatFile Source ed Excel Source. In entrambi i casi è possibile ignorare in `Input' l'intestazione del le, e dare il nome che si desidera alle colonne della tabella di Staging di de-stinazione. In questo modo è possibile processare le con diverse intestazioni
utilizzando lo stesso task.
Si è cercato di realizzare una soluzione che fosse il più essibile possibile pensando a un task che fosse in grado di processare più le aventi stessa tipologia di informazione e appartenenti a mercati diversi. Ad esempio si possono avere tre le contenenti i dati dei volumi del mercato Egitto e sei le dei volumi del mercato Marocco. Un pacchetto essibile è un pacchetto che riesce a processare tutti i nove le, seppur con un' intestazione dierente o un'ordine diverso degli attributi.
Le Componenti di Origine estraggono i dati da archivi dati, quali tabelle e viste di database relazionali, le e database di Analysis Services. Sono disponibili tre tipi di di Componenti di Origine:
• Origini installate durante l'installazione di Integration Services. • Origini disponibili per il download dal sito Web Micorosoft.
• Origini personalizzate create dall'utente se le origini esistenti non sod-disfano esigenze speci che.
Le Origini installate durante l'installazione di Integration Services si distin-guono in :
• ADO.NET : Utilizza una gestione connessione ADO.NET in cui è spe-ci cato il provider .NET per connettersi a un'origine dati. Per con gu-rare l'origine ADO.NET, è necessario speci care l'istruzione SQL che de nisce il set di risultati.
• Excel: Estrae i dati da un le Excel. • Flat File: Estrae i dati da un Flat File.
• OLE DB: Consente di utilizzare dati da un provider OLE DB.
• ODBC : Estrae dati da un database supportato da ODBC mediante una tabella di database, una vista o un'istruzione SQL.
• Componente Script: Estrae, trasform o caricare dati. • Origine XML. Estrae Dati da un le XML.
Le Origini disponibili per il download dal sito Web Microsoft, comprendono le Origini: Oracle, SAP BI, e Teradata. L'origine Oracle è il componente di origine del connettore Microsoft per Oracle di Attunity. L'origine SAP BI è il componente di origine del connettore Microsoftper SAP BI. L'origine
Teradata è il componente di origine del connettore Microsoft per Teradatadi Attunity. Il connettore Microsoft per Teradata di Attunity, così come quello per SAP BI e Oracle, include anche una gestione connessione e una destina-zione.
Per quanto riguarda, invece le Origini personalizzate create dall'utente, SQL Server Integration Services ore agli sviluppatori la possibilità di scrivere componenti di origine in grado di connettersi a origini dati personalizzate e di fornire dati da tali origini ad altri componenti in un'attività Flusso di dati. La possibilità per creare origini personalizzate si rivela utile quando è necessario connettersi a origini dati non accessibili tramite una delle Origini di Integration Services esistenti.
I componenti di origine includono uno o più output e nessun input. In fase di progettazione tali componenti vengono utilizzati per creare e con gurare connessioni, leggere metadati di colonne dall'origine dati esterna e con gura-re le colonne di output dell'origine in base all'origine dati esterna. Durante l'esecuzione, si connettono all'origine dati esterna e aggiungono righe a un buer di output. L'attività Flusso di dati fornisce quindi questo buer di righe di dati ai componenti a valle.
Le Origini utilizzate nella Solution rientrano tra quelle installate durante l'installazione di Integration Services: Origine Flat File, Excel e OLEDB. La prima è quella più utilizzata ed è quella su cui si sono implementate delle logiche di essibilità per evitare che una diversa intestazione di le aventi lo stesso di tipo di informazioni fosse la causa di un proliferare di nuovi pacchetti che dieriscono tra loro solo per la componente Origine.
Ad esempio, se i tre mercati fornisco i le contenenti i dati del Customer con un'intestazione diversa, o con l'ordine diverso degli attributi, è possibile ignorare l'intera intestazione ereditata dal le, e de nirla in output mappan-dola a quella di default creata dal sistema quando viene ignorata l'intera intestazione, ossia, quando non viene inserita la spunta nella casella Co-lumn Name in the First Row, nel task Origine Flat File.
Nella gura 4.22 è ra gurato l'editor del task, senza la spunta nella casella.
Figura 5.22: Editor Flat File Source
Un Flat File contiene record con un'ampiezza variabile, separato da un segno che li delimita. Di solito tale segno consiste in una virgola, punto e virgola, due punti o una barra verticale. Quando non viene inserita la spunta nella cella Column names in the rst row, il componente Origne SSIS assegna ad ogni nome della prima riga, separato dalla virgola, un nome di default che contiene la posizione in cui si trova rispetto ai nomi che lo seguorno. Ad es. il primo nome, qualsiasi esso sia, verrà sempre rimpiazzato con Co-lumn0 , il secondo con Column1 , il terzo con Column2 , sino a ColumnN-1 . Cliccando sulla sezione Columns del task, è possibile selezionare in alto le colonne Esterne disponibili. I nomi delle colonne sono stati già rimpiaz-zati con quelli di default. Nella parte bassa della sezione è possibile fare un mapping delle colonne, dando la possibilità all'utente di scegliere il nome che ritiene più appropriato, in modo tale da standardizzare il processo di lettura
di le aventi stesso tipo di contenuto, ma intestazioni dierenti.
La gura 4.23 mostra il mapping tra le colonne di default e le colonne di-chiarate dall'utente. Iin questo modo la Componente Origine del Flusso è
Figura 5.23: Mapping Colonne
riutilizzabile per quei iile aventi stesso tipo di contenuto ma intestazione e ordine colonne diverse. I bene ci sono riscontrabili nella memoria e tempo di gestione. Le Componenti di Trasformazione, utilizzate nella soluzione, sono sempre le stesse per i pacchetti che utilizzano le componenti di Origine Flat File ed Excel. In ordine di esecuzione si hanno:
• Trasformazione Conteggio Righe (Trasformazione che conta le righe al suo interno e archivia il totale in una variabile).
• Trasformazione Colonna Derivata (Trasformazione che popola colonne con risultati di espressioni).
• Trasformazione Suddivisione Condizionale (Trasformazione che indi-rizza righe di dati verso output diversi).
• Trasformazione Conversione Dati (Trasformazione che converte il tipo di dati di una colonna in un tipo di dati diverso).
• Trasformazione Unione (Trasformazione che unisce più set di dati). La prima componente di trasformazione, RC Source Count, conta il nu-mero delle righe restituite dalla Componente Origine e salva il totale nella variabile User::varRowCountSource dichiarata localmente nel .dtsx.
Figura 5.24: Variable Setting-Row Count Source
Questa componente verrà ripetuta altre due volte, a ne processo, per tener traccia stavolta del numero dei record scartati e di quelli che, invece, verrano inseriti nella tabella di Staging.
La componente che conta i record destinati alla tabella di Staging, si chiama RC Insert Count e memorizzerà il numero di questi ultimi nella variabile User::varRowCountInsert:
Figura 5.25: Variable Setting-Row Count Source
La componente che conta i record scartati, o perchè non è stata possibile la conversione o perchè la chiave naturale di quel record è incompleta, si chiama RC Error Count e memorizzerà il numero di righe scartare nella varianile User::varCountError:
Figura 5.26: Variable Setting-Row Count Error
Una volta memorizzato il numero di record processati dal primo task di input, viene eseguita la componente di trasformazione Colonna Deriva-ta. che consente di creare nuovi valori di colonna tramite l'applicazione di
espressioni alle colonne di input della trasformazione. Un'espressione può contenere qualsiasi combinazione di variabili, funzioni, operatori e colonne dell'input della trasformazione. Il risultato può essere aggiunto come nuova colonna o inserito in una colonna esistente come valore di sostituzione. È possibile, ancora, de nire più colonne derivate e qualsiasi variabile o colonna di input può comparire in più espressioni. Le operazioni più frequenti so-no la creazione delle colonne CreatedBy e CreatedDate, che identi caso-no lo User, l'utente che sta sviluppando la procedura, e la Data di creazio-ne. Integration Services mette a disposizione delle variabili di Sistema in cui memorizza queste informazioni. La variabile contenente l'informazione dello User, @[System::UserName], verrà concatenata con la funzione di tipo Cast, (DT_WSTR,100 ). Il risultato sarà una Stringa di tipo Unicode, di lungezza 100. Invece nel caso della Data di creazione della colonna, la va-riabile @[System::StartTime] viene utilizzata da sola. Il risultato sarà una Data di tipo TimeStamp contentente le ore, minuti, secondi, mese, giorno e anno.
Nella gura 4.27 viene mostrata una prima manipolazione dei dati in input tramite l'editor del task Derived Column Trasformation.
Figura 5.27: Editor del componente di "Trasformazione colonna derivata"
trasfor-mazione, è la creazione della colonna Market, quando essa non è presente tra le colonne in input (non è presente nel le). Il nome del mercato viene recuperato dal nome del le. Tutti i nomi dei le contengono la sigla del mercato a cui appartengono. Il nome del le è memorizzato nella variabile di Sistema, @[User::varFileName]. Utilizzando l'espressione mostrata nella gura 4.28, nella riga Market vengono estratti i primi due caratteri del nome, che compongono la Sigla del Mercato.
Figura 5.28: Market Expression Setting
L'espressione utilizzata è la seguente:
(DT_WSTR,100)LEFT(@[User::varFileName], FINDSTRING(@[User::varFileName],"_",1) - 1).
Altre operazioni ricorrenti riguardano la standardizzazione dei valori delle colonne quando assumono contemporanemante sia i valori null che strin-ghe vuote. A seconda delle esigenze si sceglie di assegnare il valore zero se la colonna è di tipo numerico, null se di tipo Stringa.
Una volta che i valori delle colonne sono stati modi cati come da speci -ca, viene eseguito il successivo componente di Trasformazione, Suddivisione Condizionale, mostrato nella gura 4.29 ,che consente di dirigere righe di dati verso output diversi a seconda del contenuto dei dati.
Figura 5.29: Data Flow. "CS -Remove Invalid Row" Task
L'implementazione della trasformazione Suddivisione condizionale è simi-le a una struttura decisionasimi-le CASE in un linguaggio di programmazione. La trasformazione valuta una o più espressioni e, in base ai risultati, dirige la riga di dati verso l'output speci cato. Questa trasformazione prevede inoltre un output prede nito, verso il quale vengono dirette le righe che non corri-spondono ad alcuna espressione.
I dati sono splittati su due direzioni diverse dal componente CS - Remove Invalid Rows. Nella Direzione Valid passeranno tutti i record che soddisfano la condizione, speci cata nell'espressione, che restituisce True. Nella dire-zione Invalid niranno invece, tutti i record per cui la condidire-zione restituirà False.
L'editor del task è mostrato in gura 4.30
Figura 5.30: Editor-Conditional Split
Nella gura 4.30, i record che soddisfano la condizione sono quelli aventi i campi Market e CustomerUniqueID sempre valorizzati, ovvero diversi da NULL". Il componente condizionale viene utilizzato per scartare dal le
