Data Warehouse - Micro ETL Foundation - La gestione dei NULL

Nella comunità di  Data Warehouse, la presenza o assenza dei valori NULL è sempre stata oggetto di opinioni contrastanti. Per me è sempre stato di grande interesse vedere come argomenti apparentemente insignificanti, possano invece  influenzare il caricamento  e/o i risultati dei dati estratti manualmente o con i tool di Business Intelligence. Argomenti come la gestione dei NULL, hanno la straordinaria capacità di assumere le sembianze di dettagli tecnici, quasi astratti, che pensiamo di poter trascurare di fronte alla presenza di ben altre complessità insite nello sviluppo di un progetto di Data Warehouse.
Purtroppo in un Data Warehouse non c’è nulla, ma proprio nulla che possa essere trascurato, poichè ogni sua componente, ed ogni implementazione di concetti logici è interconnessa l’un l’altra  ed ha sempre delle conseguenze sul risultato finale.
Questo significa avere coscienza dei problemi che possono manifestarsi in futuro per poterli affrontare subito, prima che sia troppo tardi. Non dimentichiamo che in Data Warehouse “tornare indietro” a causa di una scelta sbagliata, o peggio ancora, ignorata, può essere molto doloroso. In termini di tempo, in termini di soldi, in termini di credibilità e di recupero delle aspettative.
La gestione dei NULL, per dirla in linguaggio tecnico, o la gestione dell’assenza dell’informazione, per dirla in un linguaggio logico, è proprio uno di questi argomenti.

Il significato dei NULL

Nei database relazionali, quindi nella maggioranza dei database che sono alla base delle soluzioni di Data Warehouse & Business Intelligence, un valore NULL su un campo di una tabella, significa la mancanza dell’informazione, quindi non è un valore, ma l’assenza del valore.

Questo non significa che sia un errore ,anche se è possibile che sia la conseguenza di un problema presente nel sistema di alimentazione che fornisce i dati. Spesso non è proprio possibile associare un valore ad una certa informazione. Supponete di prendere in considerazione un contratto di finanziamento. Fra le sue varie informazioni, c’è sicuramente la data di chiusura del contratto. E’ ovvio che questo campo rimanga NULL, in quanto è una informazione che sarà possibile valorizzare  solo in futuro, al momento della chiusura. Per il momento sarà NULL.

Anche nel dominio dei valori numerici, la presenza del NULL ha un significato ben preciso, che è diverso dal valore 0 (zero). Pensate ad un elenco di valori di commissioni che un cliente paga ad una banca. Il valore 0 significa che il cliente, magari perchè è collegato a particolari convenzioni, paga un valore 0 su una certa commissione, ma quella commissione fa parte del contratto. Il valore NULL può significare  che quella commissione non è contemplata perché il cliente non ha sottoscitto alcun contratto.

In definitiva, la presenza del NULL in un flusso di alimentazione, può avere numerosi significati.

Il problema dei NULL

Al di là del significato intrinseco dei valori  NULL, quali sono le conseguenze che può arrecare al Data Warehouse ? I problemi si manifestano al momento della estrazione dei dati. Sia utilizzando normali  statement  SQL, sia utilizzando dei Tool di estrazione. Vediamo due esempi.

Esempio 1

Supponete di avere un elenco di contratti con la propria data di scadenza. Per semplicità simuliamo 3 contratti utilizzando una clausola SQL “with” per simulare al volo una tabella di tre righe. La prima riga rappresenta un contratto scaduto 2 giorni fa, la seconda riga rappresenta un contratto che già si sa che scadrà fra 5 giorni, la terza riga rappresenta un contratto non scaduto. La richiesta (o il report) è quella di estrarre tutti i contratti che non scadranno nei prossimi 10 giorni.

La soluzione SQL di per sè è concettualmente molto semplice: basta estrarre tutti i contratti la cui data di scadenza è superiore a oggi +10. Ne dovremmo trovare solo uno. Purtroppo la presenza del NULL mi produrrà un risultato errato, cioè zero righe

SQL> with tab as (

  2  Select 'C1' contr, sysdate-2 data_scad from dual

  3  union all

  4  Select 'C1' contr, sysdate+5 data_scad from dual

  5  union all

  6  Select 'C1' contr, null data_scad from dual)

  7  select * from tab

  8  where data_scad > sysdate+10;

no rows selected

Esempio 2

Supponete di avere una tabella che contiene per un cliente, in riga, gli importi commissionali di tutti i possibili contratti sottoscrivibili. Fra di essi, la terza colonna rappresenta una commissione che per quel cliente non ha senso, quindi ha un valore NULL.

La richiesta è quella di avere l’importo totale delle commissioni pagate dal cliente. Anche in questo caso la soluzione SQL è molto semplice: basta fare la somma di tutti i campi commissionali. Purtroppo, come nell’esempio precedente, la presenza del NULL mi produrrà un risultato errato in quanto la presenza del NULL “nullifica” l’intera somma.

SQL> with tab as (

  2  Select 'C1' cliente

  3  , 10 com1

  4  , 40 com2

  5  ,null com3

  6  ,18 com4

  7  from dual

  8  )

  9  select cliente,com1+com2+com3+com4 tot

 10  from tab;

CL        TOT

-- ----------

C1

Questi due esempi, molto semplici, mostrano le insidie insite nella presenza  di valori NULL all’interno del Data Warehouse. Ovviamente è possibile forzare all’interno degli SQL dei valori di default che gestiscano i NULL, ma questo dovrebbe essere fatto sempre, con il rischio di dimenticanze sempre in agguato. Il consiglio che posso dare è sintetizzato nella regola seguente.

Regola 1

Non permettere all’interno del Data Warehouse l’assenza dell’informazione. Ogni campo deve avere un valore di default che vada a sostituire il valore NULL. Questo deve essere fatto subito, in modo che le tabelle finali di Staging Area, che saranno la base di quelle successive, non abbiano valori NULL al loro interno.

Vedremo, comunque, alcuni casi di eccezione a questa regola.

 

I valori di default

Come conseguenza della regola precedente, dobbiamo decidere quali valori predefiniti dobbiamo utilizzare per sostituire i valori NULL. Per poter prendere questa decisione, è necessario consigliare una nuova regola:

 

Regola 2

Semplificare al massimo le tipologie di dato da utilizzare nel Data Warehouse. Utilizzare, se possibile, solo 2 tipologie: i valori  di testo  (VARCHAR2 per Oracle) e i valori numerici (NUMBER per Oracle). Le date devono essere tutte espresse come la concatenazione di anno,mese e giorno, cioè numeriche nel formato YYYYMMDD.

Ovviamente, se sono presenti  delle informazioni di tipo CLOB o BLOB,  utilizziamo pure questi tipi, a cui potremmo non associare dei valori di default. Anche l’utilizzo del formato DATE, ma solo per campi di tipo tecnico, può essere permesso.

Con questa semplificazione, possiamo quindi decidere dei valori di default solo per poche categorie di tipi dato. Per i dati testuali cercherei di occupare meno spazio possibile, quindi non ‘Undefined’, ma qualcosa di più semplice, come ‘nd’. Personalmente, io utilizzo ‘?’.

Per quanto riguarda i dati numerici, il valore di default consigliato è Zero. Anche se così facendo, perdiamo il significato di assenza dell’informazione, sono sicuro comunque di non produrre dei risultati errati (non dimentichiamo che in matematica 0 è l’elemento neutro  di somme e sottrazioni).  Sui dati numerici, è necessario fare una ulteriore distinzione: se il dato numerico rappresenta una data, allora il default non deve essere zero, ma, consiglio, il 99991231, cioè la massima data. Utilizzando questi due default, i due esempi visti precedentemente produrrebbero un risultato corretto. Per i campi tecnici di tipo DATE, può essere utile impostare la data sistema.

Le Eccezioni

Non esistono regole senza eccezioni. Le eccezioni, sono tutti quei casi, per la verità abbastanza limitati, in cui l’utilizzo del default dovrebbe essere evitato perchè la logica con cui poi viene utilizzato il dato è contraria al valore di default o ne ostacola l’utilizzo. Facciamo un paio di esempi:

Spesso un campo data non viene valorizzato nel sistema alimentante, in quanto significa che è una informazione che esiste dall’inizio dei tempi. In questi casi il valore di default della  data non deve essere la massima, ma la minima possibile, come 11110101.

In tabelle anagrafiche di clientela, spesso l’intestazione è spezzata in più campi a causa della limitata lunghezza dei campi del sistema alimentante. Questo significa che per ottenere l’intestazione completa bisogna concatenare più campi. In questo caso sarebbe sbagliato inserire dei default, perchè la concatenazione produrrebbe una intestazione piena di ‘?’ che renderebbero l’intestazione illeggibile e quindi da correggere. Possiamo quindi enunciare una nuova regola.

 

Regola 3

La scelta dei valori globali e dei valori delle eccezioni (fra cui mantenere il NULL è una delle opzioni) devono essere decisi sulla base dei Business requirement. Sarà quindi una fase di analisi  a determinare questa scelta.

 

L’implementazione

Poiché la Micro ETL Foundation non vuole essere solo una metodologia astratta, ma vuole fornire anche soluzioni concrete che aiutino negli sviluppi dei progetti di Data Warehose, vediamo di “sporcarci le mani” e di implementare quanto detto in precedenza.

Creeremo una tabella di Staging Area in modo tale che abbia , per ogni campo, la valorizzazione  del dato di default, che sarà impostato secondo la regola generale e terrà conto delle eccezioni. Lo statement SQL che effettuerà la sostituzione del NUL con il valore di default, lo farà sulla tabella finale di Staging Area come post-processing. Nella fase che ho definito in un mio post precedente, di arricchimento.

L’implementazione farà uso di tabelle di configurazione che permetteranno la creazione di SQL dinamici che potranno essere applicati a tutte le tabelle di Staging Area. Ciò fornirà la massima scalabilità alla soluzione.

 

Naming convention utilizzata

Ho già scritto più volte sulla importanza della naming convention all’interno di un progetto di Data Warehouse. In questa implementazione si hanno  le seguenti convenzioni:

EDW = Codice progetto

COM_MEF = Area comune (COM), sottoarea Micro Etl Foundation (MEF)

CUST = Codice flusso sorgente

STA_SS1 = Area di Staging (STA), sottoarea Sistema Sorgente 1 (SS1)

 

Configurazione globale dei dati di default

Partiamo con la creazione di una tabella di configurazione di carattere generale, valida per tutto il Data Warehouse. In essa imposteremo i default per i tipi di dati utilizzati. Negli SQL che seguono, creeremo la tabella e la inizializzeremo con i valori di default che abbiamo deciso: un punto interrogativo per i valori testuali, zero per i dati numerici, 99991231 per le date in formato numerico, la data di sistema per i campi tecnici di tipo data.

SQL> CREATE TABLE EDW_COM_MEF_CFT (

  2     DEF_V VARCHAR2(30)

  3     ,DEF_N NUMBER

  4     ,DEF_YMD NUMBER

  5     ,DEF_D VARCHAR2(30)

  6  );

Table created.

SQL> INSERT INTO EDW_COM_MEF_CFT

  2  VALUES (''''||'?'||'''',0,99991231,'SYSDATE');

1 row created.

Se eseguiamo la select del contenuto della tabella, otterremo:

 

Configurazione del flusso

A questo punto creiamo la tabella di configurazione del flusso di input nella quale indicheremo oltre al codice univoco del flusso, il nome della tabella che configura i vari campi del flusso, il nome dell’oggetto da cui prendere i dati da caricare nella tabella di staging e il nome della tabella di Staging. Questa tabella di configurazione è molto importante perché ci permetterà di generalizzare il processo di caricamento utilizzando degli  statement SQL  dinamici.

SQL> CREATE TABLE EDW_COM_MEF_IO_CFT (

  2     IO_COD VARCHAR2(10)

  3     ,CXT_COD VARCHAR2(30)

  4     ,FXV_COD VARCHAR2(30)

  5     ,STT_COD VARCHAR2(30)

  6  );

Table created.

SQL>

SQL> INSERT INTO EDW_COM_MEF_IO_CFT

  2  VALUES ('CUST'

  3  ,'EDW_STA_SS1_CUST_CXT'

  4  ,'EDW_STA_SS1_CUST_FXV'

  5  ,'EDW_STA_SS1_CUST_STT'

  6  );

1 row created.

Se eseguiamo la select del contenuto della tabella, otterremo:

 

Creazione e configurazione della tabella di dettaglio del flusso

Dopo avere configurato il flusso, è necessario configurare la sua composizione, cioè quali sono le colonne di cui è composto (che saranno le stesse della tabella di Staging) , il loro tipo e, quello che più ci serve, il valore di default  nel caso in cui si voglia fare un’eccezione al valore di default di livello globale per quel tipo di dato.

SQL> CREATE TABLE EDW_STA_SS1_CUST_CXT (

  2  COLUMN_COD VARCHAR2(30)

  3  ,DATA_TYPE VARCHAR2(30)

  4  ,DEF_TXT VARCHAR2(30)

  5  );

Table created.

SQL> INSERT INTO EDW_STA_SS1_CUST_CXT VALUES ('KEY_ID','NUMBER',NULL);

1 row created.

SQL> INSERT INTO EDW_STA_SS1_CUST_CXT VALUES ('F1_COD','VARCHAR2',NULL);

1 row created.

SQL> INSERT INTO EDW_STA_SS1_CUST_CXT VALUES ('F2_NUM','NUMBER',NULL);

1 row created.

SQL> INSERT INTO EDW_STA_SS1_CUST_CXT VALUES ('F3_YMD','NUMBER',11110101);

1 row created.

SQL> INSERT INTO EDW_STA_SS1_CUST_CXT VALUES ('F4_DAT','DATE',NULL);

1 row created.

SQL> INSERT INTO EDW_STA_SS1_CUST_CXT VALUES ('F5_COD','VARCHAR2','NULL');

1 row created.

Se eseguiamo la select del contenuto della tabella, otterremo:

Con questa configurazione, vogliamo lasciare i valori di default globali per i campi KEY_ID, F1_COD, F2_NUM e F4_DAT, mentre vogliamo forzare un default specifico per i campi F3_YMD e F5_COD.

 

Simulazione della sorgente dati

Simuliamo una sorgente dati con due righe, una con tutti NULL e una con valori effettivi. La sorgente dati, in un caso reale, potrebbe essere una una tabella effettiva, una external table al flusso fisico o altro. Faremo invece uso della clausola WITH per creare una vista che simula le due righe. Questo è fatto esclusivamente per comodità di esposizione.

SQL> CREATE OR REPLACE VIEW EDW_STA_SS1_CUST_FXV AS

  2  SELECT

  3     CAST(1 AS NUMBER) KEY_ID

  4     ,CAST(NULL AS VARCHAR2(30)) F1_COD

  5     ,CAST(NULL AS NUMBER) F2_NUM

  6     ,CAST(NULL AS NUMBER) F3_YMD

  7     ,CAST(NULL AS DATE) F4_DAT

  8     ,CAST(NULL AS VARCHAR2(30)) F5_COD

  9  FROM DUAL

 10  UNION ALL

 11  SELECT 2 KEY_ID

 12  ,'CODE1' F1_COD

 13  ,250 F2_NUM

 14  ,20140207 F3_YMD

 15  ,sysdate-10 F4_DAT

 16  ,'CODE2'

 17  FROM DUAL;

View created

 

Se eseguiamo la select del contenuto della sorgente dati, otterremo:

 

Creazione della tabella di Staging Area

Creiamo la tabella di Staging Area che sarà caricata dalla sorgente dati mostrata in precedenza.

SQL> CREATE TABLE EDW_STA_SS1_CUST_STT (

  2     KEY_ID NUMBER

  3     ,F1_COD VARCHAR2(30)

  4     ,F2_NUM NUMBER

  5     ,F3_YMD NUMBER

  6     ,F4_DAT DATE

  7     ,F5_COD VARCHAR2(30)

  8  );

Table created.

 

Impostazione dei default alla tabella di Staging Area

Utilizzando le impostazioni precedenti, possiamo creare una procedura dinamica, che ricevendo in input il codice flusso è in grado di associare i dati di default alla tabella di Staging Area associando l’impostazione generale quando non c’è la forzatura presente nella tabella di configurazione. I nomi delle colonne interessate li prendiamo direttamente dal dizionario dati di Oracle (COLS, cioè la USER_TAB_COLUMNS)

create or replace procedure p_default(p_io varchar2) as

   v_sql varchar2(4000);

   v_io edw_com_mef_io_cft%rowtype;

   v_cft edw_com_mef_cft%rowtype;

   v_def varchar2(60);

   type t_rc is ref cursor;

   v_cur t_rc;

   v_column_name varchar2(30);

   v_data_type varchar2(30);

   v_def_txt varchar2(30);

begin

   select * into v_cft

   from edw_com_mef_cft;

   select * into v_io

   from edw_com_mef_io_cft

       where io_cod = p_io;

       v_sql := 'select a.column_name,a.data_type,b.def_txt'||

            ' from cols a'||

            ' left outer join '||v_io.cxt_cod||' b'||

            ' on (a.column_name = b.column_cod)'||

            ' where a.table_name = '||''''||v_io.stt_cod||'''';

   open v_cur for v_sql;

   loop

      fetch v_cur into v_column_name,v_data_type,v_def_txt;

      exit when v_cur%notfound;

      if (v_data_type = 'NUMBER') then

         if (v_column_name like '%_YMD') then

            v_def := nvl(v_def_txt,v_cft.def_ymd);

         else

            v_def := nvl(v_def_txt,v_cft.def_n);

         end if;

      elsif (v_data_type = 'DATE') then

         v_def := nvl(v_def_txt,v_cft.def_d);

      else  -- varchar

         v_def := nvl(v_def_txt,v_cft.def_v);

      end if;

      v_sql := 'ALTER TABLE '||v_io.stt_cod||

               ' MODIFY('||v_column_name||'  DEFAULT '||v_def||')';

      execute immediate v_sql;

   end loop;

   close v_cur;

 end;

/

sho errors

Dopo avere creato la procedura, possiamo lanciarla .

SQL> exec p_default('CUST');

Possiamo verificare l’esito della procedura estraendo la struttura della tabella presente nel dizionario dati. Essa mostrerà I valori di default impostati nel campo USER_TAB_COLUMNS.DATA_DEFAULT

 

Caricamento della tabella di Staging Area

Per poter caricare la Staging Area dalla vista che simula i dati di input, possiamo utilizzare la procedura seguente, dinamica, quindi riutilizzabile per qualunque tabella di Staging.

create or replace procedure p_ins_stt(p_io varchar2) as

   v_io edw_com_mef_io_cft%rowtype;

   v_sql varchar2(32000);

   v_list varchar2(4000);

begin

   select * into v_io

       from edw_com_mef_io_cft

       where io_cod = p_io;

       v_sql :=

   'select listagg(f.column_name,'||''''||','||''''||') '||

   'within group (order by f.column_id) '||

   'from cols f  '||

   'inner join cols t on ( f.column_name = t.column_name  '||

   'and t.table_name = upper('||''''||v_io.stt_cod||''''||')) '||

   'where f.table_name = upper('||''''||v_io.fxv_cod||''''||')';

   execute immediate v_sql into v_list;

       v_sql := 'insert into '||v_io.stt_cod||'('||v_list||')'||

       ' select distinct '||v_list||' from '||v_io.fxv_cod;

       execute immediate v_sql;

   commit;

end;

/

sho errors

Dopo avere creato la procedura, possiamo lanciarla .

SQL> exec p_ins_stt('CUST');

Possiamo verificare l’esito della procedura estraendo le righe dalla tabella di Staging. Come possiamo vedere, l’inserzione ha mantenuto intatto il contenuto dei dati.

Desidero sottolineare il fatto che il caricamento non deve modificare i dati sorgente. Ovviamente la forzatura dei dati di default potrebbe essere eseguita nel momento di caricamento della tabella di Staging. Il motivo per cui è conveniente farlo come post è legato alla presenza di possibili controlli di congruenza (vedi post) che vogliamo implementare sui dati di input.  Per poter eseguire tali controlli, il dato non deve essere modificato o trasformato, deve essere identico. Solo dopo l’esito positivo dei  controlli potremo arricchire i dati con i valori di default.

 

Creazione della funzione per estrarre il data default

La creazione di questa funzione è utile per ottenere in formato leggibile il valore di default dal dizionario dati di Oracle poiché è di tipo LONG. Questa funzione la utilizzeremo nella procedura successiva.

create or replace function f_dd(

   p_tab varchar2

   , p_col varchar2

) return varchar2 as

v_out varchar2(4000);

begin

   select data_default into v_out

   from cols where table_name = p_tab

   and column_name = p_col;

   return nvl(v_out,'null');

end;

/

sho errors

 

Modifica della tabella di Staging Area

Con l’aiuto della funzione precedente, possiamo ora creare la procedura che modificherà tutti i valori NULL secondo il valore di default.

create or replace procedure p_upd_stt(p_io varchar2) as

   v_sql clob;

   v_io edw_com_mef_io_cft%rowtype;

begin

   select * into v_io

   from edw_com_mef_io_cft

   where io_cod = p_io;

   for r in (select ','||column_name||' = '||'nvl('||column_name||

      ','||f_dd(table_name,column_name)||')' stm

      from cols where table_name = v_io.stt_cod) loop

      v_sql := v_sql ||r.stm;

   end loop;

   v_sql := 'UPDATE '||v_io.stt_cod||' SET '||substr(v_sql,2);

   execute immediate v_sql;

   commit;

end;

/

sho errors

Possiamo lanciarla con:

exec p_upd_stt('CUST');

Verifichiamo ora il risultato ottenuto.

 

Conclusione

Con due tabelle di configurazione e qualche semplice procedura con SQL dinamico, siamo riusciti ad ottenere un risultato importante. Siamo in grado, con estrema facilità di ottenere una completa gestione dei NULL che ci eviterà problemi nelle fasi successive del processing e dell’analisi dei dati.