ETL sta per Extract, Transform e Load. È un concetto importante nei sistemi di Data Warehousing.Extraction sta per estrarre dati da diverse fonti di dati come sistemi o applicazioni transazionali. Transformationsta per applicare le regole di conversione sui dati in modo che diventino idonei per il reporting analitico. Illoading processo implica lo spostamento dei dati nel sistema di destinazione, normalmente un data warehouse.

I tre livelli coinvolti in un ciclo ETL sono:

  • Staging Layer - Il livello di staging viene utilizzato per memorizzare i dati estratti da diversi sistemi di dati di origine.

  • Data Integration Layer - Il livello di integrazione trasforma i dati dal livello di staging e sposta i dati in un database, dove i dati sono organizzati in gruppi gerarchici, spesso chiamati dimensions, e in fatti e fatti aggregati. La combinazione di tabelle di fatti e dimensioni in un sistema DW è chiamata aschema.

  • Access Layer - Il livello di accesso viene utilizzato dagli utenti finali per recuperare i dati per il reporting analitico.

Uno strumento ETL viene utilizzato per estrarre i dati da diverse origini dati, trasformare i dati e caricarli in un sistema DW. Al contrario, uno strumento BI viene utilizzato per generare report interattivi e ad hoc per gli utenti finali, dashboard per il senior management, visualizzazioni dei dati per riunioni del consiglio mensili, trimestrali e annuali.

Gli strumenti ETL più comuni includono - SAP BO Data Services (BODS), Informatica, Microsoft - SSIS, Oracle Data Integrator ODI, Talend Open Studio, Clover ETL Open source, ecc.

Gli strumenti BI più comuni includono: SAP Business Objects, SAP Lumira, IBM Cognos, JasperSoft, Microsoft BI Platform, Tableau, Oracle Business Intelligence Enterprise Edition, ecc.

I popolari strumenti ETL disponibili sul mercato sono:

  • Informatica - Power Center
  • IBM - Websphere DataStage (precedentemente noto come Ascential DataStage)
  • SAP - BOD di Business Objects Data Services
  • IBM - Cognos Data Manager (precedentemente noto come Cognos Decision Stream)
  • Microsoft - SQL Server Integration Services SSIS
  • Oracle - Data Integrator ODI (precedentemente noto come Sunopsis Data Conductor)
  • SAS - Data Integration Studio
  • Oracle - Warehouse Builder
  • ABInitio
  • Open source Clover ETL

L'area di staging è un'area intermedia che si trova tra le origini dati e i sistemi di data warehouse / data mart. Le aree di staging possono essere progettate per fornire molti vantaggi, ma le motivazioni principali per il loro utilizzo sono aumentare l'efficienza dei processi ETL, garantire l'integrità dei dati e supportare le operazioni di qualità dei dati.

Il data warehousing è un concetto più ampio rispetto al data mining. Il data mining implica l'estrazione di informazioni nascoste dai dati e l'interpretazione per previsioni future. Al contrario, il data warehousing include operazioni come report analitici per generare report dettagliati e report ad-hoc, elaborazione delle informazioni per generare dashboard e grafici interattivi.

OLTP è l'acronimo di Online Transactional Processing system che è comunemente un database relazionale e viene utilizzato per gestire le transazioni quotidiane.

OLAP è l'acronimo di Online Analytical Processing system che è comunemente un sistema multidimensionale ed è anche chiamato data warehouse.

Supponiamo che un'azienda venda i suoi prodotti ai clienti. Ogni vendita è un fatto che avviene all'interno dell'azienda e la tabella dei fatti viene utilizzata per registrare questi fatti. Ogni tabella dei fatti memorizza le chiavi primarie per unire la tabella dei fatti alle tabelle delle dimensioni e alle misure / fatti.

Example - Fact_Units

Cust_ID Prod_Id Time_Id Numero di unità vendute
101 24 1 25
102 25 2 15
103 26 3 30

Una tabella delle dimensioni memorizza attributi o dimensioni che descrivono gli oggetti in una tabella dei fatti. È un insieme di tabelle associate a una tabella dei fatti.

Example - Dim_Customer

Cust_id Cust_Name Genere
101 Jason M
102 Anna F

Un data mart è una forma semplice di data warehouse e si concentra su un'unica area funzionale. Di solito riceve i dati solo da poche fonti.

Example - In un'organizzazione, possono esistere data mart per Finanza, Marketing, Risorse umane e altri singoli reparti che memorizzano i dati relativi alle loro funzioni specifiche.

Le funzioni aggregate vengono utilizzate per raggruppare più righe di una singola colonna per formare una misurazione più significativa. Vengono anche utilizzati per l'ottimizzazione delle prestazioni quando salviamo tabelle aggregate nel data warehouse.

Le funzioni aggregate comuni sono:

MIN restituisce il valore più piccolo in una data colonna
MAX restituisce il valore più grande in una data colonna
SOMMA restituisce la somma dei valori numerici in una data colonna
AVG restituisce il valore medio di una data colonna
CONTARE restituisce il numero totale di valori in una data colonna
CONTARE(*) restituisce il numero di righe in una tabella

Example

SELECT AVG(salary) 
FROM employee 
WHERE title = 'developer';

Le istruzioni DDL (Data Definition Language) vengono utilizzate per definire la struttura o lo schema del database.

Examples -

  • CREATE - creare oggetti in un database

  • ALTER - altera la struttura di un database

Le istruzioni DML (Data Manipulation Language) vengono utilizzate per manipolare i dati all'interno del database.

Examples -

  • SELECT - recupera i dati da un database

  • INSERT - inserisce i dati in una tabella

  • UPDATE - aggiorna i dati esistenti all'interno di una tabella

  • DELETE - Elimina tutti i record da una tabella, lo spazio per i record rimane

Le istruzioni DCL (Data Control Language) vengono utilizzate per controllare l'accesso agli oggetti del database.

Examples -

  • GRANT - concede all'utente i privilegi di accesso al database

  • REVOKE - ritira i privilegi di accesso forniti con il comando GRANT

Gli operatori vengono utilizzati per specificare le condizioni in un'istruzione SQL e per servire come congiunzioni per più condizioni in un'istruzione. I tipi di operatore comuni sono:

  • Operatori aritmetici
  • Operatori di confronto / relazionali
  • Operatori logici
  • Imposta operatori
  • Operatori utilizzati per negare le condizioni

Gli operatori di insiemi comuni in SQL sono:

  • UNION
  • UNION ALL
  • INTERSECT
  • MINUS

L'operazione di intersezione viene utilizzata per combinare due istruzioni SELECT, ma restituisce solo i record comuni da entrambe le istruzioni SELECT. In caso di Intersect, il numero di colonne e il tipo di dati devono essere gli stessi. MySQL non supporta l'operatore INTERSECT. Una query di intersezione ha il seguente aspetto:

select * from First 
INTERSECT 
select * from second

L'operazione meno combina il risultato di due istruzioni Select e restituisce solo il risultato che appartiene al primo gruppo di risultati. Una query meno ha il seguente aspetto:

select * from First 
MINUS 
select * from second

Se esegui l'origine meno la destinazione e la destinazione meno l'origine e se la query meno restituisce un valore, è necessario considerarla come un caso di righe non corrispondenti.

Se la query meno restituisce un valore e l'intersezione del conteggio è inferiore al conteggio di origine o alla tabella di destinazione, le tabelle di origine e di destinazione contengono righe duplicate.

Group-by la clausola è usata con select dichiarazione per raccogliere tipi di dati simili. HAVING è molto simile a WHERE tranne che le affermazioni al suo interno sono di natura aggregata.

Syntax -

SELECT dept_no, count ( 1 ) FROM employee GROUP BY dept_no;  
SELECT dept_no, count ( 1 ) FROM employee GROUP BY dept_no HAVING COUNT( 1 ) > 1;

Example - Tavolo dei dipendenti

Country Salary
India 3000
NOI 2500
India 500
NOI 1500

Group by Country

Country Salary
India 3000
India 500
NOI 2500
NOI 1500

Il test ETL viene eseguito prima che i dati vengano spostati in un sistema Data Warehouse di produzione. A volte è anche chiamato come bilanciamento della tabella o riconciliazione della produzione.

L'obiettivo principale del test ETL è identificare e mitigare i difetti dei dati e gli errori generali che si verificano prima dell'elaborazione dei dati per il reporting analitico.

La tabella seguente cattura le caratteristiche chiave dei test di database ed ETL e il loro confronto:

Funzione Test di database Test ETL
Obbiettivo primario Validazione e integrazione dei dati Estrazione, trasformazione e caricamento dei dati per il reporting BI
Sistema applicabile Sistema transazionale in cui si verifica il flusso aziendale Sistema contenente dati storici e non in ambiente di flusso aziendale
Strumenti comuni nel mercato QTP, selenio, ecc. QuerySurge, Informatica, ecc.
Esigenza aziendale Viene utilizzato per integrare i dati di più applicazioni, impatto grave. Viene utilizzato per rapporti analitici, informazioni e previsioni.
Modellazione Metodo ER Multidimensionale
Tipo di database Viene normalmente utilizzato nei sistemi OLTP Si applica ai sistemi OLAP
Tipo di dati Dati normalizzati con più join Dati denormalizzati con meno join, più indici e aggregazioni.

I test ETL possono essere suddivisi nelle seguenti categorie in base alla loro funzione:

  • Source to Target Count Testing - Implica la corrispondenza del conteggio dei record nel sistema di origine e di destinazione.

  • Source to Target Data Testing- Implica la convalida dei dati tra il sistema di origine e quello di destinazione. Comprende anche l'integrazione dei dati e il controllo del valore di soglia e il controllo dei dati duplicati nel sistema di destinazione.

  • Data Mapping or Transformation Testing- Conferma la mappatura degli oggetti nel sistema di origine e di destinazione. Comprende anche il controllo della funzionalità dei dati nel sistema di destinazione.

  • End-User Testing- Implica la generazione di report per gli utenti finali per verificare se i dati nei report sono conformi alle aspettative. Implica la ricerca di deviazioni nei report e il controllo incrociato dei dati nel sistema di destinazione per la convalida del report.

  • Retesting - Implica la correzione di bug e difetti nei dati nel sistema di destinazione e di eseguire nuovamente i report per la convalida dei dati.

  • System Integration Testing - Si tratta di testare tutti i singoli sistemi e successivamente combinare il risultato per trovare se c'è una deviazione.

  • Perdita di dati durante il processo ETL.

  • Dati errati, incompleti o duplicati.

  • Il sistema DW contiene dati storici, quindi il volume dei dati è troppo grande e molto complesso per eseguire test ETL nel sistema di destinazione.

  • Ai tester ETL normalmente non viene fornito l'accesso per visualizzare le pianificazioni dei lavori nello strumento ETL. Difficilmente hanno accesso agli strumenti di BI Reporting per vedere il layout finale dei report e dei dati all'interno dei report.

  • Difficile da generare e costruire casi di test poiché il volume di dati è troppo alto e complesso.

  • I tester ETL normalmente non hanno un'idea dei requisiti dei report degli utenti finali e del flusso aziendale delle informazioni.

  • Il test ETL coinvolge vari concetti SQL complessi per la convalida dei dati nel sistema di destinazione.

  • A volte ai tester non vengono fornite informazioni sulla mappatura da origine a destinazione.

  • I risultati di un ambiente di test instabile ritardano lo sviluppo e il test del processo.

Le responsabilità chiave di un tester ETL includono:

  • Verifica delle tabelle nel sistema di origine: controllo del conteggio, controllo del tipo di dati, chiavi non mancanti, dati duplicati.

  • Applicazione della logica di trasformazione prima di caricare i dati: convalida della soglia dei dati, controllo ky surrogato, ecc.

  • Caricamento dati dall'area di gestione temporanea al sistema di destinazione: valori aggregati e misure calcolate, campi chiave non mancanti, controllo del conteggio nella tabella di destinazione, convalida del report BI, ecc.

  • Test dello strumento ETL e dei suoi componenti, casi di test: creare, progettare ed eseguire piani di test, casi di test, strumento ETL di test e la sua funzione, sistema DW di test, ecc.

Una trasformazione è un insieme di regole che genera, modifica o trasmette dati. La trasformazione può essere di due tipi: attiva e passiva.

In una trasformazione attiva, il numero di righe create come output può essere modificato una volta che si è verificata una trasformazione. Questo non accade durante una trasformazione passiva. L'informazione passa attraverso lo stesso numero assegnatogli come input.

Il partizionamento è quando dividi l'area dell'archivio dati in parti. Normalmente viene fatto per migliorare le prestazioni delle transazioni.

Se il tuo sistema DW è di grandi dimensioni, ci vorrà del tempo per individuare i dati. Il partizionamento dello spazio di archiviazione consente di trovare e analizzare i dati in modo più semplice e veloce.

Il parting può essere di due tipi: partizionamento round robin e partizionamento hash.

Nel partizionamento round robin, i dati vengono distribuiti uniformemente tra tutte le partizioni, quindi il numero di righe in ciascuna partizione è relativamente lo stesso. Il partizionamento hash si verifica quando il server utilizza una funzione hash per creare chiavi di partizione per raggruppare i dati.

  • Un Mapplet definisce le regole di trasformazione.

  • Le sessioni vengono definite per istruire i dati quando vengono spostati dal sistema di origine a quello di destinazione.

  • Un flusso di lavoro è un insieme di istruzioni che istruisce il server sull'esecuzione dell'attività.

  • La mappatura è il movimento dei dati dalla sorgente alla destinazione.

La trasformazione della ricerca consente di accedere ai dati da tabelle relazionali che non sono definite nei documenti di mappatura. Consente di aggiornare le tabelle delle dimensioni che cambiano lentamente per determinare se i record esistono già nella destinazione o meno.

Una chiave surrogata è qualcosa che ha numeri generati in sequenza senza significato e serve solo a identificare la riga in modo univoco. Non è visibile agli utenti o all'applicazione. Viene anche chiamata chiave candidata.

Una chiave surrogata ha numeri generati in sequenza senza significato. Ha lo scopo di identificare le righe in modo univoco.

Una chiave primaria viene utilizzata per identificare le righe in modo univoco. È visibile agli utenti e può essere modificato in base alle esigenze.

In questi casi, è possibile applicare il metodo checksum. È possibile iniziare controllando il numero di record nei sistemi di origine e di destinazione. Seleziona le somme e confronta le informazioni.

In questo test, un tester convalida la gamma di dati. Tutti i valori di soglia nel sistema di destinazione devono essere controllati per assicurarsi che siano conformi al risultato atteso.

Example - L'attributo Age non deve avere un valore maggiore di 100. Nella colonna Data DD / MM / YY, il campo del mese non deve avere un valore maggiore di 12.

Select Cust_Id, Cust_NAME, Quantity, COUNT (*)
FROM Customer GROUP BY Cust_Id, Cust_NAME, Quantity HAVING COUNT (*) >1;

Quando non viene definita alcuna chiave primaria, potrebbero essere visualizzati valori duplicati.

La duplicazione dei dati può anche verificarsi a causa di una mappatura errata ed errori manuali durante il trasferimento dei dati dal sistema di origine a quello di destinazione.

Il test di regressione si verifica quando apportiamo modifiche alla trasformazione dei dati e alle regole di aggregazione per aggiungere una nuova funzionalità e aiutare il tester a trovare nuovi errori. I bug che compaiono nei dati forniti nei test di regressione sono chiamati Regression.

I tre approcci sono: top-down, bottom-up e ibrido.

Gli scenari di test ETL più comuni sono:

  • Validazione della struttura
  • Convalida del documento di mappatura
  • Convalida vincoli
  • Controllo della coerenza dei dati
  • Convalida della completezza dei dati
  • Convalida della correttezza dei dati
  • Convalida della trasformazione dei dati
  • Convalida della qualità dei dati
  • Validazione nulla
  • Convalida duplicata
  • Controllo di convalida della data
  • Convalida completa dei dati utilizzando la query meno
  • Altri scenari di test
  • Pulizia dei dati

L'eliminazione dei dati è un processo di eliminazione dei dati da un data warehouse. Rimuove i dati spazzatura come righe con valori nulli o spazi extra.

Il bug cosmetico è correlato alla GUI di un'applicazione. Può essere correlato a stile del carattere, dimensione del carattere, colori, allineamento, errori di ortografia, navigazione, ecc.

Si chiama bug relativo all'analisi del valore limite.

Puoi farlo creando una variabile di mappatura e una trasformazione filtrata. Potrebbe essere necessario generare una sequenza per avere il record ordinato in modo specifico richiesto.

Value comparison- Implica il confronto dei dati nei sistemi di origine e di destinazione con una trasformazione minima o nulla. Può essere eseguito utilizzando vari strumenti di test ETL come Source Qualifier Transformation in Informatica.

Le colonne di dati critici possono essere controllate confrontando valori distinti nei sistemi di origine e di destinazione.

È possibile utilizzare le istruzioni Minus e Intersect per eseguire la convalida della completezza dei dati. Quando si esegue l'origine meno la destinazione e la destinazione meno l'origine e la query meno restituisce un valore, allora è un segno di righe non corrispondenti.

Se la query meno restituisce un valore e l'intersezione del conteggio è inferiore al conteggio di origine o alla tabella di destinazione, esistono righe duplicate.

Shortcut Transformationè un riferimento a un oggetto disponibile in una cartella condivisa. Questi riferimenti sono comunemente usati per varie fonti e obiettivi che devono essere condivisi tra diversi progetti o ambienti.

In Repository Manager, viene creato un collegamento assegnando lo stato "Condiviso". Successivamente, gli oggetti possono essere trascinati da questa cartella a un'altra cartella. Questo processo consente un unico punto di controllo per l'oggetto e più progetti non hanno tutte le origini e le destinazioni di importazione nelle loro cartelle locali.

Reusable Transformation è locale in una cartella. Example- Generatore di sequenze riutilizzabile per l'assegnazione degli ID dei clienti del magazzino. È utile caricare i dettagli del cliente da più sistemi di origine e assegnare ID univoci a ciascuna nuova chiave di origine.

Quando unisci un singolo tavolo a se stesso, viene chiamato Self-Join.

La normalizzazione del database è il processo di organizzazione degli attributi e delle tabelle di un database relazionale per ridurre al minimo la ridondanza dei dati.

La normalizzazione comporta la scomposizione di una tabella in tabelle meno ridondanti (e più piccole) ma senza perdere informazioni.

Una tabella dei fatti senza fatti è una tabella dei fatti che non ha misure. È essenzialmente un'intersezione di dimensioni. Esistono due tipi di tabelle prive di fatti: una serve per catturare un evento e l'altra per descrivere le condizioni.

Le dimensioni che cambiano lentamente si riferiscono alla variazione del valore di un attributo nel tempo. Gli SCD sono di tre tipi: Tipo 1, Tipo 2 e Tipo 3.