Download - Il controllo temporale dei data file in staging area

Transcript
Page 1: Il controllo temporale dei data file in staging area

Come avere il controllo temporaledei data file in Staging Area

Tecniche di Data Warehouse and Business Intelligence

Sei quello giusto ? Hai quello che mi aspetto ?

Sei completo?

DATAFILE

Page 2: Il controllo temporale dei data file in staging area

• In questo articolo concentriamo la nostra attenzione sulla gestione del giorno di caricamento del data file, del giorno di riferimento dei dati associato al data file, e sul numero di righe atteso. Questi temi sono già stati trattati in modo sintetico in alcuni miei articoli precedenti pubblicati su slideshare e sul mio blog. Ora ne vediamo l'applicazione pratica.

• Come caso reale, useremo come esempio il data file dei mercati MTF (MultilateralTrading Facilities). Al data file è stato associato un "row" file che contiene al suo interno il numero di righe attese nel data file stesso.

• Il file di controllo, creato a mano a tal fine, è composto da tre righe:

#MTF CONTROL FILE OF 20160314ROWS = 160#END OF MTF CONTROL FILE OF 20160314

• Supponiamo che il data file debba arrivare tutti i giorni lavorativi, e il giorno di riferimento dei dati è il giorno lavorativo precedente.

• Il giorno di riferimento è specificato nel nome del file, ma dobbiamo fare attenzione, perché il sistema alimentante imposta, come riferimento, il giorno di produzione del flusso e non il giorno lavorativo precedente.

Il caso di esempio

Page 3: Il controllo temporale dei data file in staging area

• Sulla base delle informazioni indicate in precedenza, per avere il pieno controllo del caricamento del data file, il sistema ETL deve fornirmi tutte le informazioni necessarie per esaudire i seguenti requisiti.

• Dobbiamo avere una visione chiara di quali sono le caratteristiche del data file, sia di tipo generale, sia di tipo strettamente tecnico. In particolare, quelle legate al suo nome, alla struttura del file, al modo con cui è definito il giorno di riferimento dei dati, alla struttura del suo file di controllo (se presente)

• Ovviamente dobbiamo sapere quali sono le caratteristiche temporali del data file utilizzando un codice che ne rappresenta la gestione.

I requisiti di controllo

Page 4: Il controllo temporale dei data file in staging area

• Per comodità riassumo i modi con cui il sistema alimentante può indicarmi il giorno di riferimento dei dati.

I requisiti di controllo

A column of

data file

Inside the

data file

Where is the

reference

day of data ?

In the heading

of data file

In the tail of

data file

In the name

of data file

Missing, assume

the system date

Outside the

data file

Page 5: Il controllo temporale dei data file in staging area

• Dobbiamo avere una visione chiara di quale è la struttura interna del data file, cioè quali sono le colonne che lo costituiscono. E per ogni colonna devono essere presenti quanti più metadati possibili.

• Sia statici, come il tipo o la lunghezza, che dinamici, come la presenza di un dominio di valori o se la colonna fa parte della chiave univoca.

I requisiti di controllo

Page 6: Il controllo temporale dei data file in staging area

I requisiti di controllo

• Dobbiamo avere una tabella di calendario che, per ogni giorno solare, mi dica, duplicandone semplicemente il giorno, se mi aspetto l’arrivo del data file e quale è il giorno di riferimento atteso dei dati contenuti nel data file di quel giorno.

• Se il data file contiene più giorni, devo sapere quale è il range di giorni che mi aspetto.

Page 7: Il controllo temporale dei data file in staging area

I requisiti di controllo

• Dobbiamo sapere l’esito conclusivo della elaborazione. Lo stato finale e il tempo impiegato. Se il caricamento ha avuto problemi, devo sapere l’errore prodotto e quale è il modulo che lo ha generato.

• Se l’esito è negativo, dobbiamo sapere esattamente perché si è generato l’errore. Per esempio, se è fallito il controllo di congruenza, devo sapere in quale punto si è verificato.

Page 8: Il controllo temporale dei data file in staging area

I requisiti di controllo

• Dobbiamo sapere l’esito finale del controllo dei giorni di riferimento del flusso e dei dati.

• Per ottenere l’esito finale dei controlli indicato in figura, dobbiamo pensare a implementare una logica di controllo simile a quella indicata nella figura successiva.

• In verde scuro le situazioni sicuramente corrette. In rosso le situazioni di allerta. In verde chiaro quelle presumibilmente corrette ma che richiedono attenzione.

Page 9: Il controllo temporale dei data file in staging area

I requisiti di controllo

1 – OK(arrived and right day)

Expected day = referenceday ?

It had to arrive ?

Data file is arrived ?

2 - NOT OK( arrived but wrong day)

3 - OK(unespected file)

4 - NOT OK (unespected file and

wrong day)

5 - OK(maybe file)

6 - NOT OK(maybe file and wrong

day)

7 - NOT OK(missing file)

8 – OK(no file to load)

9 - OK(maybe file)

Expected day = referenceday ?

Expected day = referenceday ?

It had to arrive ?

yes

no

maybe

yes

no

maybe

yes

yes

yes

yes

no

no

no

no

Page 10: Il controllo temporale dei data file in staging area

I requisiti di controllo

• Dobbiamo avere via email l’esito della elaborazione.

• Utilizzando la Micro ETL Foundation possiamo gestire questa situazione e il suo controllo in pochi passi.

MEF:Aprire il link:https://drive.google.com/open?id=0B2dQ0EtjqAOTQzZSaUlyUmxpT1kAccedere alla cartella mef_v2 e seguire le istruzioni del readme.

Il data file è presente nella cartella ..\dat e si chiama mtf_export_20160314.csv. Il file di controllo con il numero di righe atteso si chiama mtf_export_20160314.row. Anche esso è presente nella cartella ..\datIl file che configura la struttura dei campi del data file si trova nella cartella ..\cft e si chiama mtf.csv

Page 11: Il controllo temporale dei data file in staging area

Configurazione del data file e del file di controllo

• Il primo step è quello di inserire in una tabella di configurazione, che chiameremo per brevità IO_CFT, tutte le informazioni che conosciamo sulla caratteristiche del data file che dobbiamo caricare. Inoltre, per questo caso, bisogna inserire in IO_CFT anche le informazioni relative al file di controllo.

• Il secondo step è quello di inserire nella tabella IO_CFT, l'informazione relativa al giorno atteso di arrivo del data file. Dobbiamo definire un codice, chiamiamolo FR_COD (File Reference Code) dietro il quale ci sarà la logica di caricamento di una seconda tabella di configurazione che chiameremo IODAY_CFT. Il codice FR_COD rappresenta la periodicità di arrivo del flusso. Per il momento sono stati definiti alcuni valori di uso comune:• AD = Tutti i giorni. Significa che il data file deve arrivare tutti i giorni. Quindi nella

IODAY_CFT saranno valorizzati tutti i giorni.• AWD = Tutti i giorni lavorativi. Significa che il data file deve arrivare solo nei

giorni lavorativi. Quindi tutti i giorni festivi più i sabati e le domeniche saranno null.

• ? = Non so quando arriva, è variabile. Tipico di alcuni flussi mensili di cui non si sa di precisione quando saranno disponibili.

• Sulla base del codice FR_COD sarà caricata la tabella IODAY_CFT impostando la presenza del giorno atteso nel campo FR_YMD.

Page 12: Il controllo temporale dei data file in staging area

Configurazione dei giorni di riferimento dei dati

• Il terzo step è quello di inserire nella tabella IO_CFT, l'informazione relativa al• giorno di riferimento atteso dei dati.• Il codice DR_COD deve indicare quale deve essere il giorno di riferimento dei dati nel

data file. Ricordo che il giorno di riferimento dei dati deve sempre essere presente o deducibile. La stessa logica che è stata applicata al campo FR_COD si applica anche al campo DR_COD. Servirà per valorizzare la IODAY_CFT. Per il momento sono stati definiti solo alcuni valori di uso comune:• 0 = il giorno di riferimento coincide con il giorno corrente. • 1 = il giorno di riferimento coincide con il giorno precedente, cioè il corrente -1• 1W = indica il primo giorno lavorativo precedente.

• L'attività di configurazione della tabella IODAY_CFT avviene solo una volta in fase di configurazione del flusso. Dopo non sarà più necessario intervenire.

• Notate che l'utilizzo dei codici è un modo per agevolare velocemente l'attività di configurazione della tabella IODAY_CFT. Nessuno vi impedisce di modificare a mano la tabella o con SQL ad-hoc.

Page 13: Il controllo temporale dei data file in staging area

Configurazione del fattore di correzione

• Il codice OFF_COD presente nella IO_CFT indica il fattore di correzione da applicare al giorno di riferimento dei dati indicato dal sistema alimentante. Il campo OFF_COD non agisce nel controllo, ma agirà come correttore di giorno a run-time. Per il momento sono stati definiti alcuni codici di uso comune:• 0 = il giorno di riferimento coincide con il giorno indicato dal sistema

alimentante. • 1 = il giorno di riferimento coincide con il giorno precedente, cioè il corrente -1• 1W = il giorno di riferimento coincide con il giorno lavorativo precedente.

• I campi FROM_DR_YMD e TO_DR_YMD hanno lo stesso significato del campo FR_COD, ma permettono di identificare un range di date di riferimento possibili. Per il momento è stato definito un solo codice• PM = mese precedente del giorno solare corrente.

MEF:

Il data file è presente nella cartella ..\dat e si chiama mtf_export_20160314.csv. Il file di controllo con il numero di righe atteso si chiama mtf_export_20160314.row. Anche esso è presente nella cartella ..\datIl file che configura la struttura dei campi del data file si trova nella cartella ..\cft e si chiama mtf.csvIl file di configurazione del data file si chiama io_mtf.txt e si trova sotto la cartella ..\cft. Ha le seguenti impostazioni:

Page 14: Il controllo temporale dei data file in staging area

Configurazione del fattore di correzione

IO_COD:MTF (data file code)IO_DEB:Multilateral Trading Facilities (descrizione del data file)TYPE_COD:FIN (tipo data file – file di input)SEC_COD:ESM (sistema alimentante: ESMA)FRQ_COD:D (requenza giornaliera)FILE_LIKE_TXT:mtf_export%.csv (nome generico del data file senza data)FILE_EXT_TXT:mtf_export_20160314.csv (nome del data file di esempio)HOST_NC:., (priorità sul segno decimale)HEAD_CNT:1 (numero di righe di testata)FOO_CNT:0 (numero righe di coda)SEP_TXT:, (simbolo separatore se csv)START_NUM:12 (carattere di partenza del giorno di riferimento nel nome)SIZE_NUM:8 (dimensione del giorno)RROW_NUM:2 (riga del file di controllo in cui si trova il numero righe del data file)RSTART_NUM:8 (dove inizia il numero delle righe)RSIZE_NUM:6 (dimensione del numero)MASK_TXT:YYYYMMDD (formato del giorno)FR_COD:AWD (codice dei giorni di riferimento del data file)DR_COD:1W (codice dei giorni di riferimento dei dati)OFF_COD:1W (offset sul gorno di riferimento)RCF_LIKE_TXT:mtf_export%.row (nome generico del file di controllo senza data)RCF_EXT_TXT:mtf_export_20160314.row (nome del file di controllo di esempio)FTB_TXT:NEWLINE (indicatore fine riga per external table)TRUNC_COD:1 (indica se la tabella di staging deve essere troncata prima del caricamento)NOTE_IO_COD:MTF (presenza di un file di note)

Page 15: Il controllo temporale dei data file in staging area

Configurazione dei giorni di riferimento

• L'attività di configurazione della tabella IODAY_CFT avviene solo una volta in fase di configurazione del flusso. Dopo non sarà più necessario intervenire.

MEF:Il codice DR_COD è gestito della funzione mef_sta_build.p_dr_codIl codice FR_COD è gestito della funzione mef_sta_build.p_fr_codIl codice OFF_COD è gestito dalla funzione mef_sta.f_off_cod . Vedi ulteriore dettaglio in Recipe 12 su SlideshareLe funzioni che gestiscono il range di giorni sono la mef_sta_build.p_from_dr_cod e la mef_sta_build.p_to_dr_cod.In questo modo modificando le funzioni possiamo definire altri codici. La procedura mef_sta_build.p_objday_cft caricherà la tabella IODAY_CFT

La configurazione completa del data file avviene lanciando la procedura SQL> @sta_conf_io MTF

Page 16: Il controllo temporale dei data file in staging area

Caricamento del data file

• Il processo di caricamento del data file, deve inserire in una tabella di log le informazione relative alla data di elaborazione del flusso e al giorno di riferimento dei dati ricevuto dal sistema alimentante.

MEF:SQL> exec mef_job.p_run('sta_esm_mtf');

• Confrontando, a fine caricamento, quanto configurato con quanto è stato caricato, possiamo dedurre un esito finale di correttezza. Questo confronto può essere visualizzato per mezzo di una vista che chiameremo IODAY_CFV.

• La logica con cui lavora la vista è stata sintetizzata in una figura precedente. Sulla base di questo esito, dovrà essere concordata una strategia di intervento.

• Nel nostro caricamento di esempio, lanciato in un giorno lavorativo, vediamo che c'è un problema nel giorno di riferimento.

• Inoltre c'è un altro problema su cui indagare: il numero di righe dichiarate nel file di controllo è diverso dal numero di righe caricate..

Page 17: Il controllo temporale dei data file in staging area

Conclusione

• Qualunque sia il modo con cui implementeremo una soluzione, il punto importante da sottolineare è che dobbiamo conoscere prima le precise caratteristiche temporali del data file che dobbiamo caricare.

• Per ogni giorno solare, dobbiamo avere chiaro quali data file mi aspetto di ricevere in quel giorno e, per ogni data file, quale è il giorno di riferimento dei dati che mi aspetto di trovare al suo interno.

• Non possono esserci dubbi o ambiguità: sono informazioni che dobbiamo conoscere a priori e che dobbiamo configurare. Terminato il processo di caricamento della Staging Area, solo il confronto tra quello che prevedevamo di ricevere con quello che abbiamo effettivamente ricevuto, ci permetterà di valutare la correttezza dei dati caricati.

• E' giusto ricordare che questa verifica di correttezza è prioritaria ed è riferita solo alle due componenti temporali dei dati. Solo se questi controlli sono positivi, avrà senso proseguire con gli altri controlli di qualità.

Page 18: Il controllo temporale dei data file in staging area

Riferimenti

Su Slideshare la serie Recipes of Data Warehouse and Business Intelligence.

Blog:http://microetlfoundation.blogspot.ithttp://massimocenci.blogspot.it/

Micro ETL Foundation free source at: https://drive.google.com/open?id=0B2dQ0EtjqAOTQzZSaUlyUmxpT1kLast version v2.

Email: [email protected]