Processo ETL (estrazione, trasformazione e caricamento) nel data warehouse

Che cos'è ETL?

ETL è un processo che estrae i dati da diversi sistemi di origine, quindi trasforma i dati (come applicando calcoli, concatenazioni, ecc.) e infine carica i dati nel sistema Data Warehouse. La forma completa di ETL è Estrai, Trasforma e Carica.

È allettante pensare che la creazione di un Data warehouse consista semplicemente nell'estrarre dati da più fonti e caricarli nel database di un Data warehouse. Ciò è ben lontano dalla verità e richiede un processo ETL complesso. Il processo ETL richiede input attivi da parte di vari stakeholder, tra cui sviluppatori, analisti, tester, dirigenti di alto livello, ed è tecnicamente impegnativo.

Per mantenere il suo valore come strumento per i decisori, il sistema di data warehouse deve cambiare con i cambiamenti aziendali. L'ETL è un'attività ricorrente (giornaliera, settimanale, mensile) di un sistema di data warehouse e deve essere agile, automatizzata e ben documentata.

Perché hai bisogno di ETL?

Ci sono molte ragioni per adottare l’ETL nell’organizzazione:

Aiuta le aziende ad analizzare i propri dati aziendali per prendere decisioni aziendali critiche.
I database transazionali non sono in grado di rispondere a complesse domande aziendali a cui è possibile rispondere, ad esempio, tramite ETL.
Un Data Warehouse fornisce un repository di dati comune
ETL fornisce un metodo per spostare i dati da varie origini in un data warehouse.
Man mano che le origini dati cambiano, il Data Warehouse si aggiornerà automaticamente.
Un sistema ETL ben progettato e documentato è quasi essenziale per il successo di un progetto di data warehouse.
Consentono la verifica delle regole di trasformazione, aggregazione e calcolo dei dati.
Il processo ETL consente il confronto dei dati campione tra il sistema di origine e quello di destinazione.
Il processo ETL può eseguire trasformazioni complesse e richiede spazio aggiuntivo per memorizzare i dati.
ETL aiuta a migrare i dati in un data warehouse. Converti nei vari formati e tipi per aderire a un sistema coerente.
ETL è un processo predefinito per accedere e manipolare i dati di origine nel database di destinazione.
L'ETL nel data warehouse offre un contesto storico approfondito per l'azienda.
Aiuta a migliorare la produttività perché codifica e riutilizza senza bisogno di competenze tecniche.

Processo ETL nei data warehouse

L'ETL è un processo in 3 fasi

Passaggio 1) Estrazione

In questa fase dell'architettura ETL, i dati vengono estratti dal sistema sorgente nell'area di staging. Le trasformazioni, se presenti, vengono eseguite nell'area di staging in modo che le prestazioni del sistema sorgente non vengano degradate. Inoltre, se i dati corrotti vengono copiati direttamente dalla sorgente nel database del Data warehouse, il rollback sarà una sfida. L'area di staging offre l'opportunità di convalidare i dati estratti prima che vengano spostati nel Data warehouse.

Il data warehouse deve integrare sistemi diversi

DBMS, hardware, Operating Sistemi e Protocolli di Comunicazione. Le fonti potrebbero includere applicazioni legacy come mainframe, applicazioni personalizzate, dispositivi punto di contatto come ATM, commutatori di chiamata, file di testo, fogli di calcolo, ERP, dati di fornitori, partner tra gli altri.

Pertanto è necessaria una mappa logica dei dati prima che i dati vengano estratti e caricati fisicamente. Questa mappa dati descrive la relazione tra origini e dati di destinazione.

Tre metodi di estrazione dei dati:

Estrazione completa
Estrazione parziale - senza notifica di aggiornamento.
Estrazione Parziale - con notifica di aggiornamento

Indipendentemente dal metodo utilizzato, l'estrazione non dovrebbe influire sulle prestazioni e sui tempi di risposta dei sistemi di origine. Questi sistemi di origine sono database di produzione live. Qualsiasi rallentamento o blocco potrebbe incidere sui profitti dell'azienda.

Alcune convalide vengono eseguite durante l'estrazione:

Riconciliare i record con i dati di origine
Assicurati che non siano caricati dati spam/indesiderati
Controllo del tipo di dati
Rimuovi tutti i tipi di dati duplicati/frammentati
Controlla se tutte le chiavi sono a posto oppure no

Passaggio 2) Trasformazione

I dati estratti dal server di origine sono grezzi e non utilizzabili nella loro forma originale. Pertanto ha bisogno di essere purificato, mappato e trasformato. In effetti, questo è il passaggio chiave in cui il processo ETL aggiunge valore e modifica i dati in modo tale da poter generare report BI approfonditi.

È uno dei concetti ETL importanti in cui si applica una serie di funzioni sui dati estratti. I dati che non richiedono alcuna trasformazione vengono chiamati as mossa diretta or passare attraverso i dati.

Nella fase di trasformazione è possibile eseguire operazioni personalizzate sui dati. Ad esempio, se l'utente desidera la somma delle entrate delle vendite che non è presente nel database. Oppure se il nome e il cognome in una tabella si trovano in colonne diverse. È possibile concatenarli prima del caricamento.

Di seguito sono riportati i dati Integrity Problemi di:

Ortografia diversa della stessa persona come Jon, John, ecc.
Esistono diversi modi per indicare il nome di un'azienda come Google, Google Inc.
Uso di nomi diversi come Cleaveland, Cleveland.
Potrebbe verificarsi il caso in cui diversi numeri di conto vengano generati da diverse applicazioni per lo stesso cliente.
In alcuni dati i file richiesti rimangono vuoti
Il prodotto non valido ritirato al POS poiché l'inserimento manuale può portare a errori.

Le convalide vengono effettuate durante questa fase

Filtraggio: seleziona solo determinate colonne da caricare
Utilizzo di regole e tabelle di ricerca per la standardizzazione dei dati
Conversione del set di caratteri e gestione della codifica
Conversione di unità di misura come conversione di data e ora, conversioni di valuta, conversioni numeriche, ecc.
Controllo di convalida della soglia dati. Ad esempio, l'età non può contenere più di due cifre.
Validazione del flusso dati dall'area di staging alle tabelle intermedie.
I campi obbligatori non devono essere lasciati vuoti.
Pulizia (ad esempio, mappatura NULL su 0 o Sesso maschile su "M" e Femmina su "F" ecc.)
Dividere una colonna in multipli e unire più colonne in un'unica colonna.
Trasposizione di righe e colonne,
Utilizza le ricerche per unire i dati
Utilizzando qualsiasi convalida di dati complessa (ad esempio, se le prime due colonne di una riga sono vuote, la riga viene automaticamente rifiutata dall'elaborazione)

Passaggio 3) Caricamento

Il caricamento dei dati nel database del datawarehouse di destinazione è l'ultimo passaggio del processo ETL. In un tipico data warehouse, è necessario caricare un volume enorme di dati in un periodo relativamente breve (notti). Pertanto, il processo di caricamento dovrebbe essere ottimizzato per le prestazioni.

In caso di errore di caricamento, i meccanismi di ripristino dovrebbero essere configurati per riavviare dal punto di errore senza perdita di integrità dei dati. Gli amministratori del data warehouse devono monitorare, riprendere, annullare i carichi in base alle prestazioni prevalenti del server.

Tipi di caricamento:

Carico iniziale — popolando tutte le tabelle del Data Warehouse
Carico incrementale — applicare le modifiche in corso quando necessario periodicamente.
Aggiornamento completo —cancellare il contenuto di una o più tabelle e ricaricarlo con nuovi dati.

Verifica del carico

Assicurarsi che i dati del campo chiave non siano né mancanti né nulli.
Testare le viste di modellazione in base alle tabelle di destinazione.
Verificare che i valori combinati e le misure calcolate.
Verifiche dei dati nella tabella delle dimensioni e nella tabella della cronologia.
Controllare i report BI sulla tabella dei fatti e delle dimensioni caricata.

Strumenti ETL

Ci sono molti Strumenti ETL sono disponibili sul mercato. Eccone alcuni tra i più importanti:

1. SegnaLogica:

MarkLogic è una soluzione di data warehousing che rende l'integrazione dei dati più semplice e veloce utilizzando una serie di funzionalità aziendali. Può eseguire query su diversi tipi di dati come documenti, relazioni e metadati.

https://www.marklogic.com/product/getting-started/

2. Oracle:

Oracle è il database leader del settore. Offre un'ampia gamma di scelta di soluzioni di Data Warehouse sia on-premise che nel cloud. Aiuta a ottimizzare l'esperienza del cliente aumentando l'efficienza operativa.

https://www.oracle.com/index.html

3. Amazon RossoShift:

Amazon Redshift è uno strumento di datawarehouse. È uno strumento semplice ed economico per analizzare tutti i tipi di dati utilizzando lo standard SQL e strumenti di BI esistenti. Consente inoltre di eseguire query complesse su petabyte di dati strutturati.

https://aws.amazon.com/redshift/?nc2=h_m1

Ecco un elenco completo di utili Strumenti di data warehouse.

migliori pratiche processo ETL

Di seguito sono riportate le best practice per le fasi del processo ETL:

Non tentare mai di pulire tutti i dati:

Ogni organizzazione vorrebbe che tutti i dati fossero puliti, ma la maggior parte di loro non è disposta a pagare per aspettare o non è pronta ad aspettare. Pulirlo tutto richiederebbe semplicemente troppo tempo, quindi è meglio non provare a pulire tutti i dati.

Non pulire mai nulla:

Pianifica sempre di pulire qualcosa perché il motivo principale per costruire il Data Warehouse è offrire dati più puliti e affidabili.

Determinare il costo della pulizia dei dati:

Prima di pulire tutti i dati sporchi, è importante determinare il costo di pulizia per ogni elemento di dati sporchi.

Per velocizzare l'elaborazione delle query, disponi di visualizzazioni e indici ausiliari:

Per ridurre i costi di archiviazione, archiviare i dati riepilogati su nastri su disco. Inoltre, è necessario trovare un compromesso tra il volume dei dati da archiviare e il loro utilizzo dettagliato. Compromesso a livello di granularità dei dati per ridurre i costi di archiviazione.

Sommario

ETL sta per Estrai, Trasforma e Carica.
ETL fornisce un metodo per spostare i dati da varie origini in un file data warehouse.
Nella prima fase di estrazione, i dati vengono estratti dal sistema di origine nell'area di staging.
Nella fase di trasformazione, i dati estratti dalla fonte vengono puliti e trasformati.
Il caricamento dei dati nel datawarehouse di destinazione è l'ultimo passaggio del processo ETL.

Potrebbe piacerti: