ETL-proces (extraheren, transformeren en laden) in datawarehouse

Wat is ETL?

ETL is een proces dat de gegevens uit verschillende bronsystemen haalt, vervolgens de gegevens transformeert (zoals het toepassen van berekeningen, aaneenschakelingen, enz.) en de gegevens uiteindelijk in het Data Warehouse-systeem laadt. De volledige vorm van ETL is Extract, Transform en Load.

Het is verleidelijk om te denken dat het creëren van een datawarehouse niets anders is dan het extraheren van gegevens uit meerdere bronnen en het laden ervan in de database van een datawarehouse. Dit is verre van de waarheid en vereist een complex ETL-proces. Het ETL-proces vereist actieve input van verschillende belanghebbenden, waaronder ontwikkelaars, analisten, testers en topmanagers, en is technisch uitdagend.

Om zijn waarde als hulpmiddel voor besluitvormers te behouden, moet het datawarehouse-systeem mee veranderen met zakelijke veranderingen. ETL is een terugkerende activiteit (dagelijks, wekelijks, maandelijks) van een datawarehouse-systeem en moet flexibel, geautomatiseerd en goed gedocumenteerd zijn.

Waarom heb je ETL nodig?

Er zijn veel redenen om ETL in de organisatie te adopteren:

  • Het helpt bedrijven hun bedrijfsgegevens te analyseren voor het nemen van cruciale zakelijke beslissingen.
  • Transactionele databases kunnen complex zakelijke vragen die beantwoord kunnen worden aan de hand van bijvoorbeeld ETL.
  • Een datawarehouse biedt een gemeenschappelijke gegevensopslagplaats
  • ETL biedt een methode om de gegevens uit verschillende bronnen naar een datawarehouse te verplaatsen.
  • Naarmate gegevensbronnen veranderen, wordt het datawarehouse automatisch bijgewerkt.
  • Een goed ontworpen en gedocumenteerd ETL-systeem is bijna essentieel voor het succes van een Data Warehouse-project.
  • Maak verificatie van gegevenstransformatie-, aggregatie- en berekeningsregels mogelijk.
  • Het ETL-proces maakt vergelijking van voorbeeldgegevens tussen het bron- en het doelsysteem mogelijk.
  • ETL-proces kan com uitvoerenplex transformaties en vereist extra ruimte om de gegevens op te slaan.
  • ETL helpt bij het migreren van gegevens naar een datawarehouse. Converteer naar de verschillende formaten en typen om aan één consistent systeem te voldoen.
  • ETL is een vooraf gedefinieerd proces voor toegang tot en manipulatie van brongegevens in de doeldatabase.
  • ETL in datawarehouse biedt een diepe historische context voor het bedrijf.
  • Het helpt de productiviteit te verbeteren omdat het codificeert en hergebruikt zonder dat technische vaardigheden nodig zijn.

ETL-proces in datawarehouses

ETL bestaat uit drie stappen

ETL-proces
ETL-proces

Stap 1) Extractie

In deze stap van de ETL-architectuur worden gegevens uit het bronsysteem geëxtraheerd naar het verzamelgebied. Eventuele transformaties worden uitgevoerd in het staginggebied, zodat de prestaties van het bronsysteem niet verslechteren. Als beschadigde gegevens rechtstreeks van de bron naar de datawarehouse-database worden gekopieerd, zal het terugdraaien ook een uitdaging zijn. Staging area biedt de mogelijkheid om geëxtraheerde gegevens te valideren voordat deze naar het datawarehouse worden verplaatst.

Datawarehouse moet systemen integreren die verschillende mogelijkheden hebben

DBMS, hardware, besturingssystemen en communicatieprotocollen. Bronnen kunnen bestaan ​​uit oudere applicaties zoals mainframes, aangepaste applicaties, point-of-contact-apparaten zoals geldautomaten, oproepschakelaars, tekstbestanden, spreadsheets, ERP, gegevens van leveranciers en partners.

Daarom heeft men een logische datamap nodig voordat gegevens fysiek worden geëxtraheerd en geladen. Deze datakaart beschrijft de relatie tussen bronnen en doelgegevens.

Drie methoden voor gegevensextractie:

  1. Volledige extractie
  2. Gedeeltelijke extractie - zonder updatemelding.
  3. Gedeeltelijke extractie - met updatemelding

Ongeacht de gebruikte methode mag extractie de prestaties en responstijd van de bronsystemen niet beïnvloeden. Deze bronsystemen zijn live productiedatabases. Elke vertraging of blokkering kan de bedrijfsresultaten beïnvloeden.

Tijdens de extractie worden enkele validaties uitgevoerd:

  • Records afstemmen met de brongegevens
  • Zorg ervoor dat er geen spam/ongewenste gegevens worden geladen
  • Controle van gegevenstype
  • Verwijder alle soorten dubbele/gefragmenteerde gegevens
  • Controleer of alle sleutels op hun plaats zitten of niet

Stap 2) Transformatie

Gegevens die van de bronserver worden gehaald, zijn onbewerkt en niet bruikbaar in de oorspronkelijke vorm. Daarom moet het worden gereinigd, in kaart gebracht en getransformeerd. In feite is dit de belangrijkste stap waarbij het ETL-proces waarde toevoegt en gegevens zodanig verandert dat inzichtelijke BI-rapporten kunnen worden gegenereerd.

Het is een van de belangrijke ETL-concepten waarbij u een reeks functies op geëxtraheerde gegevens toepast. Gegevens die geen transformatie vereisen, worden as genoemd directe beweging or gegevens doorgeven.

In de transformatiestap kunt u aangepaste bewerkingen op gegevens uitvoeren. Bijvoorbeeld als de gebruiker de som van de omzet wil die niet in de database staat. Of als de voornaam en de achternaam in een tabel in verschillende kolommen staan. Het is mogelijk om ze samen te voegen voordat ze worden geladen.

Problemen met gegevensintegratie
Problemen met gegevensintegratie

Following zijn gegevensintegriteitsproblemen:

  1. Verschillende spelling van dezelfde persoon zoals Jon, John, etc.
  2. Er zijn meerdere manieren om bedrijfsnamen aan te duiden, zoals Google, Google Inc.
  3. Gebruik van verschillende namen zoals Cleaveland, Cleveland.
  4. Het kan voorkomen dat er door verschillende applicaties voor dezelfde klant verschillende rekeningnummers worden gegenereerd.
  5. Bij sommige gegevens blijven de vereiste bestanden leeg
  6. Ongeldig product opgehaald bij POS, omdat handmatige invoer tot fouten kan leiden.

Tijdens deze fase worden validaties uitgevoerd

  • Filteren – Selecteer alleen bepaalde kolommen om te laden
  • Regels en opzoektabellen gebruiken voor gegevensstandaardisatie
  • Tekensetconversie en coderingsafhandeling
  • Conversie van meeteenheden zoals datum-tijdconversie, valutaconversies, numerieke conversies, enz.
  • Validatiecontrole van gegevensdrempel. De leeftijd mag bijvoorbeeld niet meer dan twee cijfers bedragen.
  • Validatie van de gegevensstroom van het verzamelgebied naar de tussenliggende tabellen.
  • Verplichte velden mogen niet leeg worden gelaten.
  • Opschonen (bijvoorbeeld NULL toewijzen aan 0 of Geslacht Man aan “M” en Vrouw aan “F” etc.)
  • Splits een kolom in veelvouden en voeg meerdere kolommen samen tot één kolom.
  • Rijen en kolommen omzetten,
  • Gebruik lookups om gegevens samen te voegen
  • Met behulp van een complex gegevensvalidatie (als de eerste twee kolommen op een rij bijvoorbeeld leeg zijn, wordt de rij automatisch afgewezen voor verwerking)

Stap 3) Laden

Het laden van gegevens in de doeldatawarehouse-database is de laatste stap van het ETL-proces. In een typisch datawarehouse moet in relatief korte tijd (nachten) een enorme hoeveelheid gegevens worden geladen. Daarom moet het laadproces worden geoptimaliseerd voor prestaties.

In het geval van een laadfout moeten herstelmechanismen worden geconfigureerd om opnieuw op te starten vanaf het punt van de fout zonder verlies van gegevensintegriteit. Data Warehouse-beheerders moeten ladingen controleren, hervatten en annuleren op basis van de heersende serverprestaties.

Soorten laden:

  • Eerste lading — het vullen van alle Data Warehouse-tabellen
  • Incrementele belasting — het toepassen van voortdurende wijzigingen wanneer dat periodiek nodig is.
  • Volledig vernieuwen —de inhoud van een of meer tabellen wissen en opnieuw laden met nieuwe gegevens.

Verificatie laden

  • Zorg ervoor dat de sleutelveldgegevens niet ontbreken of nul zijn.
  • Test modelleringsweergaven op basis van de doeltabellen.
  • Controleer de gecombineerde waarden en berekende metingen.
  • Gegevenscontroles in dimensietabel en geschiedenistabel.
  • Controleer de BI-rapporten op de geladen feiten- en dimensietabel.

ETL-hulpmiddelen

Er zijn veel ETL-tools zijn op de markt verkrijgbaar. Hier zijn enkele van de meest prominente:

1. Mark Logic:

MarkLogic is een datawarehousing-oplossing die data-integratie eenvoudiger en sneller maakt met behulp van een scala aan bedrijfsfuncties. Het kan verschillende soorten gegevens opvragen, zoals documenten, relaties en metadata.

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


2. Orakel:

Oracle is de toonaangevende database in de branche. Het biedt een ruime keuze aan Data Warehouse-oplossingen voor zowel on-premise als in de cloud. Het helpt de klantervaringen te optimaliseren door de operationele efficiëntie te vergroten.

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


3. Amazon Roodverschuiving:

Amazon Redshift is een Datawarehouse-tool. Het is een eenvoudig en kosteneffectief hulpmiddel om alle soorten gegevens met behulp van standaard te analyseren SQL en bestaande BI-tools. Het maakt het ook mogelijk om complex zoekopdrachten op petabytes aan gestructureerde gegevens.

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

Hier is een volledige lijst met nuttige Datawarehouse-hulpmiddelen.

Best practices ETL-proces

Following zijn de best practices voor ETL-processtappen:

Probeer nooit alle gegevens op te schonen:

Elke organisatie wil graag alle gegevens schoon hebben, maar de meeste zijn niet bereid te betalen om te wachten of zijn niet bereid om te wachten. Het zou simpelweg te lang duren om alles op te schonen, dus het is beter om niet te proberen alle gegevens op te schonen.

Maak nooit iets schoon:

Plan altijd iets op te schonen, want de grootste reden voor het bouwen van het Data Warehouse is het bieden van schonere en betrouwbaardere gegevens.

Bepaal de kosten voor het opschonen van de gegevens:

Voordat u alle vervuilde gegevens opschoont, is het belangrijk dat u de opschoonkosten voor elk vervuild gegevenselement bepaalt.

Om de verwerking van zoekopdrachten te versnellen, beschikt u over aanvullende weergaven en indexen:

Om de opslagkosten te verlagen, kunt u samengevatte gegevens opslaan op schijftapes. Ook is er een afweging nodig tussen de hoeveelheid gegevens die moet worden opgeslagen en het gedetailleerde gebruik ervan. Afweging op het niveau van granulariteit van gegevens om de opslagkosten te verlagen.

Samengevat

  • ETLstaat voor Extract, Transform en Load.
  • ETL biedt een methode om de gegevens van verschillende bronnen naar een datawarehouse.
  • Bij de eerste stap-extractie worden gegevens uit het bronsysteem geëxtraheerd naar het verzamelgebied.
  • In de transformatiestap worden de uit de bron geëxtraheerde gegevens opgeschoond en getransformeerd.
  • Het laden van gegevens in het doeldatawarehouse is de laatste stap van het ETL-proces.