Vi è mai capitato in fase di ETL di dover fare un calcolo su campi che stanno su tabelle non in join a quella che state caricando?
Per fare un esempio: sulla load della tabella delle fatture devo confrontare il prezzo pagato dal cliente con il prezzo di listino sulla base della categoria del cliente. Fatture e Clienti sono associate per ClienteID ma il calcolo deve essere fatto in fase di carico delle fatture perché, per esempio, è base di calcolo per altri valori statistici. Ma il listino non è collegato alle fatture e non deve essere collegato… (ci sono mille altri esempi più calzanti ma questo è il primo che mi è venuto in mente, nei commenti i vostri esempi) Come fare?
Tre possibilità:
- 😕 Caricare i campi necessari con una join (1 a 1) e caricare il valore con una seconda load sulla tabella dove ci servono, poi eliminare i campi/tabelle temporaneamente caricate.
- 🙂 Usare la LookUp() per cercare il valore che ci serve per il calcolo
- 🤩 Usare la ApplyMap() precaricando solo i valori che ci servono e le chiavi per trovarli
La prima possibilità è ovviamente abbastanza veloce ma ci impone di caricare e ricaricare i dati, perdendo leggibilità e obbligandoci a modificare spesso se cambiano regole o campi. Se parliamo di un calcolo semplice come quello sopra detto, va bene, se la cosa è più complicata rischiamo di perderci i pezzi man mano che si aggiungono necessità, dovendo poi aggiungere strutture in join su altre strutture in join…
Con la LookUp()
La seconda invece è decisamente “elegante” ma si perde un attimo in performance, quindi si deve stare attenti alla numerosità di record sui quali si esegue la ricerca. Come funziona?
Listini:
Load
Categoria,
DataIni,
DataFin,
Sconto
From [lib://dati/Listini.qvd];
Cliente:
Load
IdCliente,
RagSoc,
//Categoria, --> non caricarla se le tabelle non si devono collegare...
LookUp('Sconto', 'Categoria', Categoria) as ScontoCliente
from [lib://dati/Clienti.qvd];
Notare che la categoria del cliente, se non ci serve, possiamo anche non caricarla, evitando associazioni con la tabella Listini che magari deve stare per fatti suoi, collegata ad altro. Possiamo anche non caricare lo ScontoCliente, se ci basta, possiamo ovviamente calcolarlo e moltiplicarlo per il prezzo “al volo” senza doverlo tenere in tabella:
...,
...,
Prezzo * LookUp('Sconto', 'Categoria', Categoria) as PrezzoFinale,
...,
...Se il listino avesse delle date di validità, basterebbe mettere una Where nel carico della tabella listini prendendo solo i valori nel range di date necessario, volendo complicarci la vita, possiamo fare lo stesso con una serie di if() nidificate per trovare lo Sconto che ci serve… perdendo di leggibilità e di performance.
lookup(‘CampoDaRiportare’, ‘CampoSuCuiCercare’, CampoDellaTabellaAttuale [, ‘NomeTabella’])
Il nome della tabella su cui fare la ricerca è opzionale perché di solito si usa la tabella corrente, nel caso in questione il campo “Categoria” si trova sulla tabella in corso di caricamento.
Considerate anche che il campo di ricerca può essere una AutoNumberHash128() che unisce diversi campi in modo rapido.
Con la ApplyMap()
La ApplyMap è decisamente interessante perché permette di caricare temporaneamente le tabelle dalle quali prendere i valori di lookup ma una volta usate vengono cancellate automaticamente. Questa è una cosa doppiamente utile; ovviamente se non vanno eliminate perché per esempio devono servire in interfaccia, basta non caricarle come mappe, ma come tabelle normali e funziona allo stesso modo.
Come funziona?
MapSconto:
Mapping Load Distinct Categoria, Sconto from [Lib://Dati/Clienti.qvd];
Cliente:
Load
IdCliente,
RagSoc,
Categoria,
ApplyMap('MapSconto',Categoria) as Sconto
from [lib://dati/Clienti.qvd];
oppure
...,
ApplyMap('MapSconto',Categoria) * Prezzo as PrezzoFinale
ApplyMap(‘NomeTabellaDiMap’,CampoDaCercare [,Alternativa])
viene restituito il 2′ campo della tabella oppure l’alternativa, nel caso non sia trovato il valore cercato (opzionale, altrimenti restituisce Null()).
Questo è in assoluto il sistema più rapido in termini di performance. e mantiene una certa leggibilità.
La LookUp() è però imbattibile quando dobbiamo calcolare campi su altre tabelle in fase di ETL e non vogliamo perdere di linearità nel codice.