ETL-Prozess

In der Datenverarbeitung ist Extrahieren, Transformieren, Laden (ETL) ein dreistufiger Prozess, bei dem Daten extrahiert, transformiert (bereinigt, bereinigt, bereinigt) und in einen Ausgabedatencontainer geladen werden. Die Daten können aus einer oder mehreren Quellen zusammengetragen und an ein oder mehrere Ziele ausgegeben werden. Die ETL-Verarbeitung wird in der Regel mit Hilfe von Softwareanwendungen durchgeführt, kann aber auch manuell von Systembetreibern vorgenommen werden. ETL-Software automatisiert in der Regel den gesamten Prozess und kann manuell oder nach wiederkehrenden Zeitplänen entweder als Einzelauftrag oder in einem Stapel von Aufträgen ausgeführt werden. ⓘ

Konventionelles ETL-Diagramm ⓘ

Ein ordnungsgemäß konzipiertes ETL-System extrahiert Daten aus Quellsystemen, setzt Datentyp- und Datengültigkeitsstandards durch und stellt sicher, dass die Daten strukturell mit den Anforderungen der Ausgabe übereinstimmen. Einige ETL-Systeme können auch Daten in einem präsentationsfähigen Format bereitstellen, damit Anwendungsentwickler Anwendungen erstellen und Endbenutzer Entscheidungen treffen können. ⓘ

Der ETL-Prozess wurde in den 1970er Jahren zu einem beliebten Konzept und wird häufig im Data Warehousing eingesetzt. ETL-Systeme integrieren in der Regel Daten aus mehreren Anwendungen (Systemen), die in der Regel von verschiedenen Anbietern entwickelt und unterstützt oder auf separater Computerhardware gehostet werden. Die separaten Systeme, die die ursprünglichen Daten enthalten, werden häufig von verschiedenen Beteiligten verwaltet und betrieben. Ein Kostenrechnungssystem kann beispielsweise Daten aus der Lohnbuchhaltung, dem Vertrieb und dem Einkauf kombinieren. ⓘ

Extract, Transform, Load (ETL) ist ein Prozess, bei dem Daten aus mehreren, gegebenenfalls unterschiedlich strukturierten Datenquellen in einer Zieldatenbank vereinigt werden. ⓘ

ETL: Datenfluss und unterstützende Programme ⓘ

Bekannt ist der Prozess vor allem durch die Verwendung beim Betrieb eines Data-Warehouses. Hier müssen große Datenmengen aus mehreren operationalen Datenbanken konsolidiert werden, um dann im Data-Warehouse gespeichert zu werden. ⓘ

Extrahieren

Bei der Datenextraktion werden Daten aus homogenen oder heterogenen Quellen extrahiert; bei der Datentransformation werden die Daten bereinigt und in ein geeignetes Speicherformat/eine geeignete Speicherstruktur für Abfrage- und Analysezwecke umgewandelt; beim Datenladen schließlich werden die Daten in die endgültige Zieldatenbank eingefügt, z. B. in einen operativen Datenspeicher, einen Data Mart, einen Data Lake oder ein Data Warehouse. ⓘ

Die ETL-Verarbeitung umfasst das Extrahieren der Daten aus dem/den Quellsystem(en). In vielen Fällen ist dies der wichtigste Aspekt von ETL, da die korrekte Extraktion der Daten die Grundlage für den Erfolg der nachfolgenden Prozesse darstellt. Die meisten Data-Warehousing-Projekte kombinieren Daten aus verschiedenen Quellsystemen. Jedes einzelne System kann auch eine andere Datenorganisation und/oder ein anderes Datenformat verwenden. Zu den gebräuchlichen Datenquellenformaten gehören relationale Datenbanken, XML, JSON und flache Dateien, aber auch nicht-relationale Datenbankstrukturen wie Information Management System (IMS) oder andere Datenstrukturen wie Virtual Storage Access Method (VSAM) oder Indexed Sequential Access Method (ISAM) oder sogar Formate, die durch Mittel wie Web Spidering oder Screen Scraping aus externen Quellen geholt werden. Das Streaming der extrahierten Datenquelle und das fliegende Laden in die Zieldatenbank ist eine weitere Möglichkeit, ETL durchzuführen, wenn keine Zwischenspeicherung von Daten erforderlich ist. ⓘ

Ein wesentlicher Bestandteil der Extraktion ist die Datenvalidierung, um zu bestätigen, dass die aus den Quellen gezogenen Daten die richtigen/erwarteten Werte in einem bestimmten Bereich (z. B. ein Muster/Standard oder eine Liste von Werten) aufweisen. Wenn die Daten die Validierungsregeln nicht erfüllen, werden sie ganz oder teilweise abgelehnt. Die abgelehnten Daten werden idealerweise an das Quellsystem zurückgemeldet, damit sie weiter analysiert werden können, um die fehlerhaften Datensätze zu identifizieren und zu korrigieren. ⓘ

Umwandlung

In der Datentransformationsphase wird eine Reihe von Regeln oder Funktionen auf die extrahierten Daten angewendet, um sie für das Laden in das Endziel vorzubereiten. ⓘ

Eine wichtige Funktion der Transformation ist die Datenbereinigung, die darauf abzielt, nur "richtige" Daten an das Ziel zu übergeben. Die Herausforderung bei der Interaktion verschiedener Systeme liegt in der Schnittstelle und der Kommunikation zwischen den jeweiligen Systemen. Zeichensätze, die in einem System verfügbar sind, sind es in anderen möglicherweise nicht. ⓘ

In anderen Fällen können eine oder mehrere der folgenden Transformationsarten erforderlich sein, um die geschäftlichen und technischen Anforderungen des Servers oder Data Warehouse zu erfüllen:

• Auswahl von nur bestimmten zu ladenden Spalten: (oder Auswahl von Nullspalten, die nicht geladen werden sollen). Wenn die Quelldaten beispielsweise drei Spalten (auch "Attribute" genannt) haben, nämlich roll_no, age und salary, dann kann die Auswahl nur roll_no und salary berücksichtigen. Oder der Auswahlmechanismus kann alle Datensätze ignorieren, in denen das Gehalt nicht vorhanden ist (Gehalt = null).
• Übersetzung kodierter Werte: (z. B. wenn das Quellsystem männlich als "1" und weiblich als "2" kodiert, das Lagerhaus aber männlich als "M" und weiblich als "F" kodiert)
• Kodierung von Freiformwerten: (z. B. Abbildung von "Männlich" auf "M")
• Ableitung eines neuen berechneten Wertes: (z. B. Verkaufsbetrag = Menge * Stückpreis)
• Sortieren oder Ordnen der Daten auf der Grundlage einer Liste von Spalten zur Verbesserung der Suchleistung
• Zusammenführen von Daten aus mehreren Quellen (z. B. Lookup, Merge) und Deduplizieren der Daten
• Aggregieren (z. B. Rollup - Zusammenfassung mehrerer Datenzeilen - Gesamtverkäufe für jede Filiale und für jede Region usw.)
• Erzeugen von Ersatzschlüsselwerten (Surrogate Key)
• Transponieren oder Pivotieren (Umwandlung mehrerer Spalten in mehrere Zeilen oder umgekehrt)
• Aufteilung einer Spalte in mehrere Spalten (z. B. Umwandlung einer kommagetrennten Liste, die in einer Spalte als String angegeben ist, in einzelne Werte in verschiedenen Spalten)
• Disaggregation von sich wiederholenden Spalten
• Nachschlagen und Validieren der relevanten Daten aus Tabellen oder referentiellen Dateien
• Anwendung jeglicher Form der Datenvalidierung; eine fehlgeschlagene Validierung kann zu einer vollständigen, teilweisen oder gar keiner Ablehnung der Daten führen, so dass je nach Regeldesign und Ausnahmebehandlung keine, einige oder alle Daten an den nächsten Schritt weitergegeben werden; viele der oben genannten Transformationen können zu Ausnahmen führen, z. B. wenn eine Codeübersetzung einen unbekannten Code in den extrahierten Daten parst ⓘ

Laden

In der Ladephase werden die Daten in das Endziel geladen, bei dem es sich um einen beliebigen Datenspeicher handeln kann, z. B. eine einfache, abgegrenzte flache Datei oder ein Data Warehouse. Je nach den Anforderungen des Unternehmens variiert dieser Prozess stark. Einige Data Warehouses überschreiben vorhandene Informationen mit kumulativen Informationen; die Aktualisierung der extrahierten Daten erfolgt häufig auf täglicher, wöchentlicher oder monatlicher Basis. Andere Data Warehouses (oder sogar andere Teile desselben Data Warehouses) fügen in regelmäßigen Abständen - z. B. stündlich - neue Daten in historischer Form hinzu. Um dies zu verstehen, betrachten Sie ein Data Warehouse, das die Verkaufsdaten des letzten Jahres aufbewahren soll. In diesem Data Warehouse werden alle Daten, die älter als ein Jahr sind, mit neueren Daten überschrieben. Die Eingabe von Daten für ein beliebiges Jahresfenster erfolgt jedoch auf historische Weise. Der Zeitpunkt und der Umfang des Ersetzens oder Anhängens sind strategische Designentscheidungen, die von der verfügbaren Zeit und den Geschäftsanforderungen abhängen. Komplexere Systeme können eine Historie und einen Prüfpfad für alle Änderungen an den in das Data Warehouse geladenen Daten führen. Da die Ladephase mit einer Datenbank interagiert, gelten die im Datenbankschema definierten Einschränkungen (z. B. Eindeutigkeit, referenzielle Integrität, Pflichtfelder), die ebenfalls zur Gesamtleistung des ETL-Prozesses beitragen. ⓘ

• Ein Finanzinstitut könnte beispielsweise Informationen über einen Kunden in mehreren Abteilungen haben, und jede Abteilung könnte die Informationen über diesen Kunden auf eine andere Weise auflisten. Die Mitgliederabteilung könnte den Kunden namentlich aufführen, während die Buchhaltungsabteilung den Kunden nach seiner Nummer auflistet. ETL kann all diese Datenelemente bündeln und in einer einheitlichen Darstellung konsolidieren, z. B. zur Speicherung in einer Datenbank oder einem Data Warehouse.
• Eine weitere Möglichkeit für Unternehmen, ETL zu nutzen, besteht darin, Informationen dauerhaft in eine andere Anwendung zu übertragen. Die neue Anwendung könnte zum Beispiel einen anderen Datenbankanbieter und höchstwahrscheinlich ein ganz anderes Datenbankschema verwenden. ETL kann dazu verwendet werden, die Daten in ein Format umzuwandeln, das für die neue Anwendung geeignet ist.
• Ein Beispiel hierfür wäre ein Kostenerfassungssystem (Expense and Cost Recovery System, ECRS), wie es von Wirtschaftsprüfungs-, Beratungs- und Anwaltskanzleien verwendet wird. Die Daten landen in der Regel im Zeit- und Abrechnungssystem, obwohl einige Unternehmen die Rohdaten auch für Berichte über die Mitarbeiterproduktivität an die Personalabteilung oder für Berichte über die Gerätenutzung an das Facility Management verwenden können. ⓘ

ETL-Zyklus in der Praxis

Der typische ETL-Zyklus in der Praxis besteht aus den folgenden Ausführungsschritten:

1. Einleitung des Zyklus
2. Aufbau von Referenzdaten
3. Extrahieren (aus Quellen)
4. Validierung
5. Transformieren (bereinigen, Geschäftsregeln anwenden, Datenintegrität prüfen, Aggregate oder Disaggregate erstellen)
6. Stage (Laden in Staging-Tabellen, falls verwendet)
7. Audit-Berichte (zum Beispiel über die Einhaltung von Geschäftsregeln. Hilft auch im Falle eines Fehlers bei der Diagnose/Reparatur)
8. Veröffentlichen (in Zieltabellen)
9. Archivieren ⓘ

Herausforderungen

ETL-Prozesse können sehr komplex sein, und bei unsachgemäß konzipierten ETL-Systemen können erhebliche betriebliche Probleme auftreten. ⓘ

Die Bandbreite der Datenwerte oder die Datenqualität in einem operativen System kann die Erwartungen der Entwickler zum Zeitpunkt der Festlegung der Validierungs- und Transformationsregeln übersteigen. Die Erstellung eines Datenprofils einer Quelle während der Datenanalyse kann die Datenbedingungen identifizieren, die durch die Spezifikation von Transformationsregeln gehandhabt werden müssen, was zu einer Änderung der explizit und implizit im ETL-Prozess implementierten Validierungsregeln führt. ⓘ

Data Warehouses werden in der Regel aus einer Vielzahl von Datenquellen mit unterschiedlichen Formaten und Zwecken zusammengestellt. ETL ist daher ein Schlüsselprozess, um alle Daten in einer standardisierten, homogenen Umgebung zusammenzuführen. ⓘ

Bei der Entwurfsanalyse sollte die Skalierbarkeit eines ETL-Systems über die gesamte Nutzungsdauer ermittelt werden, einschließlich der Datenmengen, die im Rahmen von Service Level Agreements verarbeitet werden müssen. Die für die Extraktion aus den Quellsystemen verfügbare Zeit kann sich ändern, was bedeuten kann, dass dieselbe Datenmenge in kürzerer Zeit verarbeitet werden muss. Einige ETL-Systeme müssen so skaliert werden, dass sie Terabytes an Daten verarbeiten können, um Data Warehouses mit Dutzenden von Terabytes an Daten zu aktualisieren. Wachsende Datenmengen können Entwürfe erfordern, die von täglichem Batch über mehrtägiges Micro-Batch bis hin zur Integration mit Message Queues oder Echtzeit-Änderungsdatenerfassung für kontinuierliche Transformation und Aktualisierung skalierbar sind. ⓘ

Leistung

ETL-Anbieter messen ihre Datensatzsysteme mit mehreren TB (Terabyte) pro Stunde (oder ~1 GB pro Sekunde) und verwenden dazu leistungsstarke Server mit mehreren CPUs, mehreren Festplatten, mehreren Gigabit-Netzwerkverbindungen und viel Speicher. ⓘ

Im wirklichen Leben findet der langsamste Teil eines ETL-Prozesses in der Regel in der Ladephase der Datenbank statt. Datenbanken können langsam arbeiten, weil sie sich um Gleichzeitigkeit, Integritätspflege und Indizes kümmern müssen. Um die Leistung zu verbessern, kann es daher sinnvoll sein, diese Methode anzuwenden:

• Direkte Pfadextraktionsmethode oder Massenentladung, wann immer dies möglich ist (anstatt die Datenbank abzufragen), um die Belastung des Quellsystems zu verringern und gleichzeitig eine schnelle Extraktion zu erhalten.
• Der größte Teil der Transformationsverarbeitung außerhalb der Datenbank
• Bulk-Load-Operationen, wann immer möglich ⓘ

Doch selbst bei der Verwendung von Bulk-Operationen ist der Datenbankzugriff in der Regel der Engpass im ETL-Prozess. Einige gängige Methoden zur Leistungssteigerung sind:

• Partitionierung von Tabellen (und Indizes): Versuchen Sie, die Partitionen ähnlich groß zu halten (achten Sie auf Nullwerte, die die Partitionierung verfälschen können)
• Führen Sie alle Validierungen in der ETL-Schicht vor dem Laden durch: Deaktivieren Sie die Integritätsprüfung (deaktivieren Sie Constraints ...) in den Zieldatenbanktabellen während des Ladens
• Deaktivieren Sie Trigger (disable trigger ...) in den Zieldatenbanktabellen während des Ladevorgangs: Simulieren Sie deren Wirkung in einem separaten Schritt
• Generieren Sie IDs in der ETL-Schicht (nicht in der Datenbank)
• Löschen Sie die Indizes (auf einer Tabelle oder Partition) vor dem Laden - und erstellen Sie sie nach dem Laden neu (SQL: drop index ...; create index ...)
• Parallele Bulk-Loads verwenden, wenn möglich - funktioniert gut, wenn die Tabelle partitioniert ist oder keine Indizes vorhanden sind (Hinweis: Der Versuch, parallele Loads in dieselbe Tabelle (Partition) durchzuführen, führt normalerweise zu Sperren - wenn nicht auf den Datenzeilen, dann auf den Indizes)
• Wenn die Anforderung besteht, Einfügungen, Aktualisierungen oder Löschungen vorzunehmen, sollten Sie herausfinden, welche Zeilen auf welche Weise in der ETL-Schicht verarbeitet werden sollen, und dann diese drei Operationen in der Datenbank getrennt verarbeiten. Für Einfügungen können Sie oft Bulk-Load durchführen, aber Aktualisierungen und Löschungen erfolgen in der Regel über eine API (mit SQL). ⓘ

Die Entscheidung, ob bestimmte Operationen in der Datenbank oder außerhalb durchgeführt werden, kann einen Kompromiss darstellen. Beispielsweise kann das Entfernen von Duplikaten mit distinct in der Datenbank langsam sein; daher ist es sinnvoll, dies außerhalb zu tun. Andererseits ist es sinnvoll, Duplikate so früh wie möglich in der Datenbank zu entfernen, bevor die Daten entladen werden, wenn die Verwendung von distinct die Anzahl der zu extrahierenden Zeilen erheblich (x100) verringert. ⓘ

Eine häufige Quelle von Problemen bei ETL ist eine große Anzahl von Abhängigkeiten zwischen ETL-Jobs. So kann beispielsweise Job "B" nicht starten, solange Job "A" noch nicht beendet ist. Eine bessere Leistung lässt sich in der Regel dadurch erzielen, dass man alle Prozesse in einem Diagramm darstellt und versucht, das Diagramm unter maximaler Ausnutzung der Parallelität zu verkleinern und die "Ketten" aufeinander folgender Verarbeitungen so kurz wie möglich zu halten. Auch hier kann die Partitionierung von großen Tabellen und deren Indizes sehr hilfreich sein. ⓘ

Ein weiteres häufiges Problem tritt auf, wenn die Daten auf mehrere Datenbanken verteilt sind und die Verarbeitung in diesen Datenbanken sequentiell erfolgt. Manchmal wird die Datenbankreplikation als Methode zum Kopieren von Daten zwischen den Datenbanken eingesetzt - dies kann den gesamten Prozess erheblich verlangsamen. Die gängige Lösung besteht darin, den Verarbeitungsgraphen auf nur drei Ebenen zu reduzieren:

• Quellen
• Zentrale ETL-Schicht
• Ziele ⓘ

Mit diesem Ansatz kann die Verarbeitung die Parallelität maximal ausnutzen. Wenn Sie beispielsweise Daten in zwei Datenbanken laden müssen, können Sie die Ladevorgänge parallel durchführen (anstatt in die erste zu laden und dann in die zweite zu replizieren). ⓘ

Manchmal muss die Verarbeitung sequentiell erfolgen. So werden z. B. Dimensionsdaten (Referenzdaten) benötigt, bevor man die Zeilen für die Haupt-"Fakten"-Tabellen abrufen und validieren kann. ⓘ

Parallele Verarbeitung

Eine neuere Entwicklung in der ETL-Software ist die Implementierung der Parallelverarbeitung. Sie hat eine Reihe von Methoden zur Verbesserung der Gesamtleistung von ETL bei der Verarbeitung großer Datenmengen ermöglicht. ⓘ

ETL-Anwendungen implementieren drei Haupttypen von Parallelität:

• Daten: Durch Aufteilung einer einzelnen sequentiellen Datei in kleinere Datendateien, um einen parallelen Zugriff zu ermöglichen
• Pipeline: Ermöglicht die gleichzeitige Ausführung mehrerer Komponenten auf demselben Datenstrom, z. B. die Suche nach einem Wert in Datensatz 1 zur gleichen Zeit wie das Hinzufügen zweier Felder in Datensatz 2
• Komponente: Gleichzeitige Ausführung mehrerer Prozesse auf verschiedenen Datenströmen im selben Auftrag, z. B. Sortieren einer Eingabedatei und Entfernen von Duplikaten in einer anderen Datei ⓘ

Alle drei Arten der Parallelität werden normalerweise in einem einzigen Auftrag oder einer einzigen Aufgabe kombiniert. ⓘ

Eine zusätzliche Schwierigkeit besteht darin, sicherzustellen, dass die hochgeladenen Daten relativ konsistent sind. Da mehrere Quelldatenbanken unterschiedliche Aktualisierungszyklen haben können (einige können alle paar Minuten aktualisiert werden, während andere Tage oder Wochen brauchen), muss ein ETL-System möglicherweise bestimmte Daten zurückhalten, bis alle Quellen synchronisiert sind. Wenn ein Warehouse mit dem Inhalt eines Quellsystems oder mit dem Hauptbuch abgeglichen werden muss, ist die Einrichtung von Synchronisations- und Abgleichspunkten erforderlich. ⓘ

Wiederverwendbarkeit, Wiederherstellbarkeit

Bei Data-Warehousing-Verfahren wird ein großer ETL-Prozess in der Regel in kleinere Teile unterteilt, die sequentiell oder parallel ablaufen. Um den Datenfluss zu verfolgen, ist es sinnvoll, jede Datenzeile mit einer "row_id" und jeden Teil des Prozesses mit einer "run_id" zu kennzeichnen. Im Falle eines Fehlers helfen diese IDs dabei, den fehlgeschlagenen Teil zurückzusetzen und neu zu starten. ⓘ

Zu den bewährten Verfahren gehören auch Kontrollpunkte, d. h. Zustände, in denen bestimmte Phasen des Prozesses abgeschlossen sind. Sobald ein Checkpoint erreicht ist, ist es eine gute Idee, alles auf die Festplatte zu schreiben, einige temporäre Dateien zu löschen, den Status zu protokollieren, usw. ⓘ

Virtuelles ETL

Im Jahr 2010 begann die Datenvirtualisierung, die ETL-Verarbeitung voranzutreiben. Die Anwendung der Datenvirtualisierung auf ETL ermöglichte die Lösung der häufigsten ETL-Aufgaben wie Datenmigration und Anwendungsintegration für mehrere verstreute Datenquellen. Virtuelles ETL arbeitet mit einer abstrahierten Darstellung der Objekte oder Entitäten, die aus einer Vielzahl von relationalen, halbstrukturierten und unstrukturierten Datenquellen gesammelt werden. ETL-Tools können die objektorientierte Modellierung nutzen und mit den Darstellungen von Entitäten arbeiten, die in einer zentral gelegenen Hub-and-Spoke-Architektur persistent gespeichert sind. Eine solche Sammlung, die Darstellungen der Entitäten oder Objekte enthält, die aus den Datenquellen für die ETL-Verarbeitung gesammelt wurden, wird als Metadaten-Repository bezeichnet und kann im Speicher liegen oder persistent gemacht werden. Durch die Verwendung eines persistenten Metadaten-Repositorys können ETL-Tools von einmaligen Projekten zu persistenter Middleware werden, die Datenharmonisierung und Datenprofilierung konsistent und nahezu in Echtzeit durchführen. ⓘ

Der Umgang mit Schlüsseln

Eindeutige Schlüssel spielen in allen relationalen Datenbanken eine wichtige Rolle, da sie alles miteinander verbinden. Ein eindeutiger Schlüssel ist eine Spalte, die eine bestimmte Entität identifiziert, während ein Fremdschlüssel eine Spalte in einer anderen Tabelle ist, die auf einen Primärschlüssel verweist. Schlüssel können aus mehreren Spalten bestehen; in diesem Fall handelt es sich um zusammengesetzte Schlüssel. In vielen Fällen handelt es sich bei dem Primärschlüssel um eine automatisch generierte Ganzzahl, die keine Bedeutung für die dargestellte Wirtschaftseinheit hat, sondern nur für die Zwecke der relationalen Datenbank existiert - gemeinhin als Surrogatschlüssel bezeichnet. ⓘ

Da in der Regel mehr als eine Datenquelle in das Warehouse geladen wird, sind die Schlüssel ein wichtiger Aspekt, der berücksichtigt werden muss. Ein Beispiel: Kunden können in mehreren Datenquellen vertreten sein, mit ihrer Sozialversicherungsnummer als Primärschlüssel in einer Quelle, ihrer Telefonnummer in einer anderen und einem Surrogat in der dritten. Ein Data Warehouse kann jedoch die Konsolidierung aller Kundeninformationen in einer Dimension erfordern. ⓘ

Es wird empfohlen, einen Lagerhaus-Surrogatschlüssel hinzuzufügen, der als Fremdschlüssel von der Faktentabelle verwendet wird. ⓘ

Normalerweise werden die Quelldaten einer Dimension aktualisiert, was natürlich im Data Warehouse berücksichtigt werden muss. ⓘ

Wenn der Primärschlüssel der Quelldaten für die Berichterstattung erforderlich ist, enthält die Dimension diese Information bereits für jede Zeile. Wenn die Quelldaten einen Ersatzschlüssel verwenden, muss das Data Warehouse diesen nachverfolgen, auch wenn er nie in Abfragen oder Berichten verwendet wird; dazu wird eine Nachschlagetabelle erstellt, die den Surrogatschlüssel des Warehouses und den Ursprungsschlüssel enthält. Auf diese Weise wird die Dimension nicht mit Surrogaten aus verschiedenen Quellsystemen verunreinigt, während die Fähigkeit zur Aktualisierung erhalten bleibt. ⓘ

Die Nachschlagetabelle wird je nach Art der Quelldaten auf unterschiedliche Weise verwendet. Es gibt 5 Typen, von denen hier drei vorgestellt werden:

Typ 1

Die Dimensionszeile wird einfach aktualisiert, um dem aktuellen Stand des Quellsystems zu entsprechen; das Warehouse erfasst keine Historie; die Nachschlagetabelle wird verwendet, um die zu aktualisierende oder zu überschreibende Dimensionszeile zu identifizieren

Typ 2

Eine neue Dimensionszeile wird mit dem neuen Zustand des Quellsystems hinzugefügt; ein neuer Surrogatschlüssel wird zugewiesen; der Quellschlüssel ist in der Lookup-Tabelle nicht mehr eindeutig

Vollständig protokolliert

Es wird eine neue Dimensionszeile mit dem neuen Zustand des Quellsystems hinzugefügt, während die vorherige Dimensionszeile aktualisiert wird, um anzugeben, dass sie nicht mehr aktiv ist und wann sie deaktiviert wurde. ⓘ

Werkzeuge

Ein etablierter ETL-Rahmen kann die Konnektivität und Skalierbarkeit verbessern. Ein gutes ETL-Tool muss in der Lage sein, mit den vielen verschiedenen relationalen Datenbanken zu kommunizieren und die verschiedenen Dateiformate zu lesen, die in einem Unternehmen verwendet werden. ETL-Tools haben sich zu Enterprise Application Integration- oder sogar Enterprise Service Bus-Systemen entwickelt, die inzwischen weit mehr als nur die Extraktion, Transformation und das Laden von Daten umfassen. Viele ETL-Anbieter bieten jetzt auch Funktionen für Datenprofilierung, Datenqualität und Metadaten. Ein häufiger Anwendungsfall für ETL-Tools ist die Konvertierung von CSV-Dateien in Formate, die von relationalen Datenbanken gelesen werden können. Eine typische Übersetzung von Millionen von Datensätzen wird durch ETL-Tools erleichtert, die es den Benutzern ermöglichen, csv-ähnliche Datenfeeds/Dateien einzugeben und sie mit so wenig Code wie möglich in eine Datenbank zu importieren. ⓘ

ETL-Tools werden in der Regel von einem breiten Spektrum von Fachleuten verwendet - von Informatikstudenten, die schnell große Datensätze importieren wollen, bis hin zu Datenbankarchitekten, die für die Verwaltung von Unternehmenskonten zuständig sind, sind ETL-Tools zu einem praktischen Werkzeug geworden, auf das man sich verlassen kann, um maximale Leistung zu erzielen. ETL-Tools enthalten in den meisten Fällen eine grafische Benutzeroberfläche, mit der die Benutzer Daten mithilfe eines visuellen Datenmappers bequem umwandeln können, anstatt große Programme zum Parsen von Dateien und Ändern von Datentypen zu schreiben. ⓘ

Während ETL-Tools traditionell Entwicklern und IT-Mitarbeitern vorbehalten waren, schreibt das Marktforschungsunternehmen Gartner, dass der neue Trend darin besteht, diese Funktionen den Geschäftsanwendern zur Verfügung zu stellen, damit diese bei Bedarf selbst Verbindungen und Datenintegrationen erstellen können, anstatt sich an die IT-Abteilung zu wenden. Gartner bezeichnet diese nicht-technischen Benutzer als Citizen Integrators. ⓘ

ETL vs. ELT

Extrahieren, Laden, Transformieren (ELT) ist eine Variante von ETL, bei der die extrahierten Daten zunächst in das Zielsystem geladen werden. Die Architektur der Analyse-Pipeline muss auch berücksichtigen, wo die Daten bereinigt und angereichert werden und wie die Dimensionen angepasst werden. ⓘ

Das Buch The Data Warehouse ETL Toolkit von Ralph Kimball und Joe Caserta (Wiley, 2004), das als Lehrbuch für Kurse zur Vermittlung von ETL-Prozessen im Data Warehousing verwendet wird, behandelt dieses Thema. ⓘ

Cloud-basierte Data Warehouses wie Amazon Redshift, Google BigQuery und Snowflake Computing sind in der Lage, hoch skalierbare Rechenleistung bereitzustellen. Dadurch können Unternehmen auf Preload-Transformationen verzichten und Rohdaten in ihre Data Warehouses replizieren, wo sie sie nach Bedarf mit SQL transformieren können. ⓘ

Nach der Verwendung von ELT können die Daten weiterverarbeitet und in einem Data Mart gespeichert werden. ⓘ

Jeder Ansatz hat seine Vor- und Nachteile. Die meisten Datenintegrationstools tendieren zu ETL, während ELT in Datenbank- und Data-Warehouse-Anwendungen beliebt ist. Ebenso ist es möglich, TEL (Transformieren, Extrahieren, Laden) durchzuführen, bei dem die Daten zunächst auf einer Blockchain transformiert werden (als Möglichkeit zur Aufzeichnung von Datenänderungen, z. B. beim Brennen von Token), bevor sie extrahiert und in einen anderen Datenspeicher geladen werden. ⓘ

Funktionsweise

Das Verfahren lässt sich als allgemeiner Prozess der Informationsintegration auch auf andere Datenbanken übertragen. Dabei gilt es, heterogen strukturierte Daten aus unterschiedlichen Quellen zusammenzuführen. Der Prozess muss sowohl effizient ablaufen, um Sperrzeiten bei den Quellen zu minimieren, als auch die Qualität der Daten sichern, damit sie trotz möglicher Änderungen der Quellen vollständig und konsistent im Data-Warehouse gehalten werden können. ⓘ

Neuere Einsatzgebiete von Data-Warehouses erfordern das beschleunigte Hinzufügen von Daten. Der Fokus von ETL richtet sich daher zunehmend auf die Minimierung der Latenzzeit, bis die Daten aus den Quellsystemen zur Verfügung stehen. Hierzu ist eine häufigere Durchführung des Prozesses notwendig. ⓘ

Im Allgemeinen wird bei allen Schritten ein Repositorium eingebunden, das insbesondere die notwendigen Datenbereinigungs- und Transformationsregeln sowie die Schemadaten als Metadaten aufnimmt und langfristig hält. ⓘ

Die meisten ETL-Programmsysteme haben Routinen zum Data-Profiling. Bei Migrationen aus Altsystemen ist oft die Datenqualität der Quellsysteme nicht absehbar. Diese wird im Data Profiling gemessen. Die Mappingregeln in der Transformation müssen darauf abgestimmt sein, um ein Funktionieren des Zielsystems nach dem Load zu gewährleisten. ⓘ

Tools/Hersteller

Auch wenn man ETL-Prozesse mit eigenen Programmen umsetzen kann, sprechen folgende Gründe für den Einsatz von Standardwerkzeugen:

• Jedes Standardwerkzeug unterstützt den Zugriff auf die gängigen Datenbanksysteme sowie ERP- und Dateisysteme.
• Die Entwicklung wird durch geeignete Transformationen, Methoden und Verfahren (wie Visualisierung des Datenflusses, Fehlerbehandlung, Scheduling) unterstützt.
• Meist sind auch für High-Performance-Loading die entsprechenden Voraussetzungen bereits im Standardwerkzeug implementiert. Eine genaue Kenntnis der Mechanismen der Zielsysteme entfällt dadurch meistens.
• Entwicklung und Wartung der ETL-Prozesse sind in der Regel durch visualisierende Standardwerkzeuge einfacher und kostengünstiger durchzuführen als bei Systemen auf Basis entwickelter Programme unter Verwendung von Programmiersprachen. ⓘ

Führende Hersteller von Programmen zur Datenintegration: SAS Institute, IBM (Produkt: Information Server), Informatica (PowerCenter), SAP-Business Objects (BusinessObjects Data Integrator), SAP Data Services, Altova (MapForce), Oracle (Oracle Warehouse Builder, Oracle Data Integrator) und Microsoft (SQL Server Integration Services). Ein weiterer Anbieter ist Comit mit der Data Management Suite (DMS). ⓘ

Die bekanntesten Tools im Open-Source-Umfeld sind Kettle Pentaho Data Integration, Scriptella ETL, CloverETL, Talend Open Studio und das Perl-Framework Catmandu, das aus dem Bibliotheksumfeld kommt. ⓘ