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 tabelle 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 Merchandiser/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'estrazione 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 re- stituito 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 strFirstLine.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, contenente 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.