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 identica 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 speciche.

Le Origini installate durante l'installazione di Integration Services si distin- guono in :

• ADO.NET : Utilizza una gestione connessione ADO.NET in cui è spe- cicato il provider .NET per connettersi a un'origine dati. Per congu- rare l'origine ADO.NET, è necessario specicare l'istruzione SQL che denisce 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 congurare connessioni, leggere metadati di colonne dall'origine dati esterna e congura- 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 denirla 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 è ragurato 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 beneci 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, denire 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 identicano 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"

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 modicati 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 decisionale 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 specicato. Questa trasformazione prevede inoltre un output predenito, 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, specicata nell'espressione, che restituisce  True. Nella dire- zione  Invalid niranno invece, tutti i record per cui la condizione 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

tutti i record non aventi una chiave naturale che li identichi.

Se manca anche solo un attributo che compone la chiave naturale di quel record, quest'ultimo sarà scartato. I record scartati non verranno persi ma verranno salvati in un le, che verrà rimandato indietro al cliente sino a quando le informazioni non saranno complete. Dall'incompletezza dei le può dipendere la povertà delle informazioni visualizzate nelle Dashboard che non saranno in grado di soddisfare le esigenze conoscitive del cliente stesso. Quindi è nell'interesse del cliente stesso fornire le completi.

I record aventi la chiave naturale verranno processati dal componente di Conversione Dati, chiamato  DCNV -Check Data Type, il quale converte i dati della colonna in input in un tipo di dati diverso e quindi li copia in una nuova colonna di output. Nella gura 4.31 vengono selezionate solo le colonne delle quali si vuole convertire il tipo e/o stabilire la lunghezza nel caso di una stringa, la precisione e la scala nel caso di un tipo numerico. Nella gura 4.31 è mostrato l'editor del componente di Trasformazione  Data Conversion

Figura 5.31: Editor Data Conversion Transformation

Una volta convertito il tipo dei dati, verrà salvata nella variabile @var- RowCountInsert, il numero delle righe che andranno a popolare la tabella di Staging. Il popolamento nella tabella di STG avviene tramite il componente

 OLE DB Destination tramite la gestione connessione OLE DB. Il Compo- nente include i mapping tra le colonne di input e quelle nell'origine dei dati della destinazione (colonne della tabella di STG).

Il usso è mostratto in gura 4.32

Figura 5.32: Data Flow. "OLE DB Destination"

Ai record che invece non hanno soddisfatto la condizione denita nel com- ponente di Suddivisione condizionale, viene aggiunta tramite il componente di Trasformazione Colonna derivata, DCOL- Add Rejection Reason , la co- lonna  Rejection Reason valorizzata con la stringa denita nell'espressione:  NULL values in mandatory eld. Ovvero i record sono stati scartati per- chè hanno dei valori NULL nei campi deniti come obbligatori ( mandatory) perchè componenti la chiave naturale.

La gura 4.33 mostra l'editor del DCOL- Add Rejection Reason

Questi record saranno poi uniti con quelli scartati dal Componente di Tra- sformazione,  Conversione Dati. L'unione avviene tramite il componente  Union All, denominato  UA- Merge Errors, mostrato nella gura 4.34. I record verranno poi salvati in un le tramite il componente  Flat File De- stination, FFD -Invalid Records dopo averne salvato il numero tramite il task  Row Count, denominato RC -Error count.

Figura 5.34: Data Flow. "FED - Invalid Records"

Il numero dei record presente nel le di input, il numero di quelli inseriti nella tabella di Staging, e il numero di quelli scartati vengono inseriti nella tabella in cui viene loggato lo stato di esecuzione del pacchetto. L'inserimento avviene tramite l'istruzione lanciata dall' Execute SQL Task, denominato SQL -Insert Log Success, che viene eseguito solo se il Data Flow Task viene processato con successo.

Figura 5.35: Control Flow. "SQL - Insert Log Success

Il le verrà poi archiviato nella cartella Archive che conterrà tutti i le processati con successo. Il task File System che sposta il le dalla cartella Process alla cartella Archive è l' FST -Archive Processed File mostrato sempre nella gura 4.35

Capitolo 6

Reporting

Dall'integrazione dei dati provenienti da sorgenti eterogenee è possibile vi- sualizzare le informazioni mostrate nelle dashboard raccolte nel modulo dei Volumi:

• Volume Dashboard • Monthly Review • Territory Dashboard • Market Report SOM

La prima Dashboard visualizzata nella gura 5.1, mostra l'andamento dei volumi. Il livello massimo di dettaglio è il Mese per la Dimensione Time, City per la Dimensione Geography ( gerarchia: Market, Area, Region, Ci- ty), e SubFamilyDescription per la Dimensione Product (Gerarchia: Brand Family, SubBrandFamily, SubBrandFamilyDescription).

Figura 6.1: Volume Dashboards

I dati dell'intera dashboard saranno ltrati in base all'anno selezionato nel ltro, in alto a sinistra, e eventualmente dal mese selezionato nella prima Bar Chart. Le informazioni sono raggrupate nelle quattro sezioni:

• Nel graco in alto a sinistra (Bar chart waterfall) vengono comparati i volumi dell'anno selezionato, con quelli dell'anno precedente. I volumi sono splittati per mese.

• In alto a destra (bar chart stacked) i volumi sono splittati per tipo di Contract, Price e Flavor Segment (attributi della Dimensione Pro- dotto). Le sigarette sono classicate in base al prezzo: Low, cheap, Medium, Premium, Above Premium, in base alla Flavor (F = Filter, FF = FullFlavor, LT=Light, UL=Ultra Light, LEN = 1mg), e in base al Contract (Gold, Bronze, Silver).

• Nei graci sottostanti i volumi sono esposti secondo il dettaglio sele- zionato delle gerarchie di Prodotto e Geography: su entrambi i livelli è possibile eseguire il Drill-Down. Nella gura 5.2 è mostrata la Dash- board Monthly Review, in cui i dati sono ltrati per il mese selezionato, e sono mostrati i primi cinque Brand e prodotti (SKU, codice prodotto) più venduti. Anche qua, i volumi sono splittati per Prezzo, Flavor, Time e Geography Hierarchy e vengono utilizzati i graci a torta per indicare l'owner dei volumi.

Nella gura 5.3 viene mostarta la Territory Dashboard. Cliccando , l'icona che individua il Customer nella Mappa verrà visualizzata la scheda del Customer con tutti i suoi dati e quelli dei volumi del mese selezionato,

Figura 6.2: Dashboard Monthly Review Dashboard

confrontati con quelli del mese precedente, (Varianza in %). L'icona che rappresenta il Customer cambia forma a seconda della tipologia di contratto del Customer. :

Figura 6.3: Terrytory Dashboard

La gura 5.4 mostra il report standard in cui è visualizzata la share of mar- ket dei prodotti: le percentuali di distribuzione in base al Price and Flavor Segment, Pack Type, Brand, Family etc. , e la percentuale di distribuazio- ne nell'intero mercato (come sono venduti rispetto a quelli dei concorrenti), negli ultimi 13 mesi da quello selezionato nel ltro:

Report.png

Figura 6.4: ShareOfMarket Report

Ogni mercato avrà le sue dashboard ospitate nel proprio portale. L'uten- te potrà monitorare i processi back end, di Integrazione e Data Warehousing schedulati in un job in SQL Server 2012, con frequenza giornaliera. Il job esegue in maniera sequenziale i singoli pacchetti .dtsxappartenenti all'unica soluzione creata per quel Mercato, sviluppata in SQL Server Integration Ser- vices (SSIS) e deployata nel Catalog di SQL Server. Il mancato o avvenuto successo dei singolipackage .dtsx è indicato dal bollino (Indicator) verde o rosso, visualizzato nel report Monitor ETL (Figura 5.5). Ogni package avrà il suo bollino. Se il package "Padre" avrà il bollino verde, signica che tutti i suoi package gli sono stati eseguiti con successo, se rosso almeno un dtsx ha fallito l'esecuzione, per cui è necessario indagare la causa e provvedere a correggere l'errore.

Figura 6.5: Monitor ETL Report

Inne il portale mette a dispozione una form che permette all'utente di completare le informazioni che arrivano dai Data Source, eettuando un mapping. (Master Data Management).

Capitolo 7

Conclusioni e sviluppi futuri

Ad oggi, la soluzione ETL descritta in questo lavoro di tesi, gestisce l'estra- zione e integrazione dei dati nella piattaforma di BI dell'applicativo software venduto ai tre mercati: Egitto, Senegal e Marocco. Per la sua progettazione e sviluppo è stato necessario approfondire le conoscenze dei Servizi Microsoft, in particolare del SQL Server Integration Services, e del linguaggio SQL, ap- presi a livello accademico durante il percorso universitario.

La Versione del tool Visual Studio, utilizzato per lo sviluppo della soluzio- ne, non è quella più aggiornata. Molte migliorie possono essere fatte con versioni più attuali o con un applicativo, sviluppato in contemporanea dallo stesso Team della Talent Community di Business Analytics , che permette agli utenti stessi di scegliere il formato dei le in cui memorizzare i loro dati, rendendo la soluzione ETL riutilizzabile con facilità.

Invece, importanti passi avanti possono essere fatti con:

• Una migrazione di tutti i ussi nel Cloud, in unico Data Lake Store, integrando i le importati sino ad oggi, con le multimediali, non strut- turati. (In questo caso l'estrazione, la validazione e caricamento dei le avviene tramite Pipeline e Custom Activity; quest'ultime sviluppate in codice U-SQL, Python e C#)

• L'utilizzo di servizi oerti da Microsoft Azure, come Machine Learning Studio, che consente di creare delle soluzioni di analisi predittive. I vantaggi sono riscontrabili in Dashboard più ricche di informazioni, capaci di fornire non solo una panaromica attuale e passata, ma anche predditiva e futura.

Bibliograa

[1] Douglas Laudenschlager, Saisang Cai

https://docs.microsoft.com/it-it/sql/integration-services/sql-server- integration-services;, 2017

[2] Carl Rabeler, Aaron Kunz, Jason Roth

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-