Verwandeln Sie Chaos in Profit: Den ETL-Prozess verstehen

Veröffentlicht: 2022-03-11

Einer der schwerwiegendsten Fehler, der Unternehmen daran hindert, Geschäftsanwendern wichtige Informationen zur Entscheidungsfindung bereitzustellen, ist das Fehlen zuverlässiger Daten aus einer oder mehreren Datenquellen, die an einem einzigen Ort gesammelt, organisiert und für die Verwendung vorbereitet werden.

Stellen Sie sich dieses Szenario vor: Sie sind der IT-Manager eines Unternehmens für Handyhüllen mit Einzelhandelsstandorten in den gesamten Vereinigten Staaten. Ihr Vorstand hat Mühe, Entscheidungen auf der Grundlage von Verkäufen zu treffen, da die Informationen zwar verfügbar sind, sich aber an unterschiedlichen Orten und in unterschiedlichen Formaten befinden. Einer der Direktoren bittet Sie, ein Dashboard mit Verkaufsinformationen von allen Verkaufsstellen zu erstellen, um es in der nächsten Vorstandssitzung vorzustellen.

Sie wissen bereits, dass es aufgrund unterschiedlicher Formate und Strukturen kaum möglich ist, die Informationen zusammenzuführen. Einige Einzelhandelskioske verwenden immer noch ein proprietäres System in einer Oracle-Datenbank. Größere Geschäfte verwenden ein neues Salesforce-System. Die neuesten Kioske, die während des Systemübergangs in Betrieb genommen wurden, verfügen über gemeinsame Tabellenkalkulationen, die zur Berechnung der Verkäufe verwendet werden.

Wie werden Sie alle Daten von verschiedenen Standorten, Formaten und Strukturen in einer einzigartigen Datenbank zusammenführen, auf die für die Dashboard-Generierung zugegriffen werden kann?

ETL-Grundlagen

ETL steht für Extrahieren, Transformieren und Laden . ETL ist eine Gruppe von Prozessen, die entwickelt wurden, um diesen komplexen Datenspeicher in einen organisierten, zuverlässigen und replizierbaren Prozess umzuwandeln, der Ihrem Unternehmen hilft, mit den bereits vorhandenen Daten mehr Umsatz zu erzielen.

In unserem Fall erhalten wir Daten von einer Oracle-Datenbank (die meisten Kioske), von Salesforce (Geschäfte) und von Tabellenkalkulationen (neuere Kioske), extrahieren die Daten, wandeln sie bei Bedarf um und laden sie in eine einzige Data-Warehouse-Datenbank Zugriff durch Reporting-Tools und zum Generieren von Dashboards und Scorecards.

Lassen Sie uns in die drei Schritte von ETL eintauchen, um den Prozess näher zu erläutern.

Extraktion

Extraktion ist der Vorgang des Empfangens von Daten aus einer oder mehreren Quellen. Quellen können unterschiedliche Formate und Strukturen haben, z. B. Dokumente, Tabellenkalkulationen, CSV-Dateien, Flatfiles, relationale Datenbanken wie Oracle, MySQL, SQL Server, nicht relationale Datenbanken usw.

Es gibt zwei Haupttypen der Extraktion: vollständig und teilweise .

  • Die vollständige Extraktion wird für anfängliche Extraktionen verwendet oder wenn die Datenmenge und folglich die Zeit für die Extraktion akzeptabel sind.
  • Eine teilweise Extraktion wird empfohlen, wenn nicht alle Daten erneut extrahiert werden müssen oder die Datenmenge groß genug ist, um eine vollständige Extraktion unmöglich zu machen. Bei einer teilweisen Extraktion werden nur aktualisierte oder neue Daten extrahiert.

Neben diesen Aspekten sind bei der Wahl zwischen vollständiger oder teilweiser Extraktion einige weitere Überlegungen notwendig, von denen ich eine beschreiben möchte: Datenverfügbarkeit und -integrität .

Dies bedeutet, dass nur abgeschlossene Transaktionen für die Extraktion berücksichtigt werden, wodurch Daten eliminiert werden, die einen Integritätsfehler verursachen könnten. Zum Beispiel ein Online-Test, um das Wissen eines Ingenieurs mit 10 Fragen zu ermitteln. Wenn sich ein Ingenieur mitten im Test befindet und einige Fragen beantwortet, aber noch nicht abgeschlossen hat, kann der Extraktionsprozess die Fragen aus nicht abgeschlossenen Tests nicht lesen. Dies könnte einen Integritätsfehler verursachen.

Transformation

Nach dem Extrahieren der Daten können wir mit dem Transformationsprozess beginnen: Daten gemäß Geschäftsregeln und technischen Kriterien bereinigen, manipulieren und transformieren, um ein akzeptables Maß an Datenqualität aufrechtzuerhalten.

Abhängig von einer Reihe von Faktoren kann die Verwendung einer Bereitstellungsfläche erforderlich sein. Ein Staging-Bereich ist ein Zwischenspeicherplatz, der verwendet wird, um Daten, die aus zu transformierenden Datenquellen extrahiert wurden, vorübergehend zu speichern.

In einigen Projekten, normalerweise solchen mit einer kleinen Datenmenge, besteht keine Notwendigkeit, Staging-Bereiche zu verwenden, aber die meisten Projekte verwenden sie.

In der Transformationsphase fallen verschiedene Aufgaben an:

  • Auswahl: Kriterien zur Auswahl extrahierter Daten. Die Auswahl kann während der Extraktionsphase, während der Transformationsphase oder in beiden Phasen erfolgen.
  • Integration: die Kombination von Daten aus der Extraktionsphase in den Staging-Bereich. Diese Kombination bedeutet das Hinzufügen, Löschen und Aktualisieren von Daten im Bereitstellungsbereich basierend auf den extrahierten Daten.
  • Joins: Wird verwendet, um extrahierte Daten zu verbinden, ähnlich wie SQL-Joins (Inner Join, Left Join, Right Join, Full Join usw.)
  • Bereinigung oder Scrubbing: entfernt inkonsistente oder ungültige Daten oder Daten mit Fehlern, um die Datenqualität zu verbessern. Die Arbeit mit mehreren Datenquellen erhöht die Wahrscheinlichkeit, dass Datenprobleme auftreten, die behoben werden müssen, wie z. B.:
    • Referentielle Integrität (Kunde mit nicht vorhandener Kategorie)
    • Fehlende Werte (Kunde ohne ID)
    • Eindeutigkeit (mehr als eine Person mit derselben Sozialversicherungsnummer)
    • Rechtschreibfehler (Sun Diego, Kanada, L. Angeles)
    • Widersprüchliche Werte (Alex geb. 27.04.1974, Alex geb. 14.04.2000)
    • und viele andere
  • Zusammenfassungen: Datensätze für die spätere Verwendung zusammenfassen
  • Aggregationen: Daten, die in Gruppen zusammengestellt und zusammengefasst werden
  • Konsolidierungen: Daten aus mehreren Quellen oder Strukturen, die in einem einzigen Datensatz konsolidiert werden

Hier sind einige gängige Transformationstypen:

  • Löschen Sie doppelte Daten
  • Teilen und Zusammenführen
  • Konvertierungen (Datum, Uhrzeit, Zahlenmasken, Maße)
  • Kodierung (Stecker auf M)
  • Berechnungen (Artikelwert = Einheitspreis * Menge)
  • Schlüsselgenerierung

Wird geladen

Zu guter Letzt besteht der letzte Prozess in ETL darin, die Daten in das Ziel zu laden. Laden ist das Einfügen transformierter Daten (aus einem Staging-Bereich oder nicht) in das Repository, normalerweise eine Data-Warehouse-Datenbank.

Es gibt drei Haupttypen zum Laden von Daten: vollständig oder initial, inkrementell und aktualisieren.

  • Vollständig oder anfänglich bedeutet ein vollständiges Laden extrahierter und transformierter Daten. Alle Daten im Staging-Bereich werden in das endgültige Ziel geladen, um für Geschäftsbenutzer bereit gemacht zu werden.
  • Beim inkrementellen Laden werden transformierte Daten mit den Daten am endgültigen Ziel verglichen und nur neue Daten geladen. Das inkrementelle Laden kann in Verbindung mit dem unten erläuterten Auffrischladen verwendet werden.
  • Das Aktualisieren des Ladens ist der Vorgang des Aktualisierens der Daten am endgültigen Ziel, um die Änderungen widerzuspiegeln, die in der ursprünglichen Quelle vorgenommen wurden. Eine Aktualisierung kann vollständig oder inkrementell sein.

Zusammenfassend kann jedes Unternehmen, unabhängig von seiner Größe, ETL-Prozesse nutzen, um bereits vorhandene Informationen zu integrieren und eine noch größere Informationsfülle für die Entscheidungsfindung zu generieren, indem bisher nicht nutzbare Daten zu einer neuen Einnahmequelle werden.

Testen

Das Testen ist eine der wichtigsten Phasen von ETL, aber auch eine der am meisten übersehenen.

Daten aus unterschiedlichen Quellen und Strukturen zu transformieren und in ein Data Warehouse zu laden ist sehr komplex und kann Fehler erzeugen. Die häufigsten Fehler wurden in der Transformationsphase oben beschrieben.

Datengenauigkeit ist der Schlüssel zum Erfolg, während Ungenauigkeit ein Rezept für eine Katastrophe ist. Daher haben ETL-Experten die Aufgabe, die Datenintegrität während des gesamten Prozesses zu gewährleisten. Nach jeder Phase muss ein Test durchgeführt werden. Unabhängig davon, ob Daten aus einer einzelnen Quelle oder aus mehreren Quellen extrahiert werden, müssen die Daten überprüft werden, um festzustellen, dass keine Fehler vorliegen.

Dasselbe muss nach jeder Transformation erfolgen. Beispielsweise müssen beim Zusammenfassen von Daten während der Transformationsphase die Daten überprüft werden, um sicherzustellen, dass keine Daten verloren gegangen sind und die Summen korrekt sind.

Nach dem Laden der transformierten Daten in das Data Warehouse muss der Testprozess erneut angewendet werden. Die geladenen Daten müssen mit den transformierten Daten und anschließend mit den extrahierten Daten verglichen werden.

In unserem Firmenbeispiel für Handyhüllen arbeiten wir mit drei verschiedenen Quellen (proprietäre Oracle-Datenbank, Salesforce und Tabellenkalkulationen) und unterschiedlichen Formaten. In der Testphase können Beispieldaten aus den Originalquellen verwendet und mit den Daten im Staging-Bereich verglichen werden, um sicherzustellen, dass die Extraktion fehlerfrei erfolgt ist.

Die Beispieldaten – die in diesem Fall Verkaufsinformationen von drei verschiedenen Standorten (Geschäfte, alte Kioske, neue Kioske) sein können – sollen mit der ursprünglichen Quelle verglichen werden. Die Unterschiede, falls vorhanden, müssen analysiert werden, um festzustellen, ob sie akzeptabel sind oder ob es sich um Fehler handelt.

Wenn Fehler gefunden werden, müssen sie behoben werden, und es sind einige Entscheidungen zu treffen, wenn Sie sie beheben müssen: Sollen die Originaldaten geändert werden? Ist das möglich? Wenn die Fehler in der Originalquelle nicht behoben werden können, können sie durch eine Transformation behoben werden?

In einigen Fällen müssen die Daten mit Fehlern eliminiert und eine Warnung ausgelöst werden, um die Verantwortlichen zu informieren.

Einige Testbeispiele:

  • Daten müssen validiert werden
  • Datenqualität
  • Leistung
  • Datenregeln
  • Datenmodellierung

Protokollierung

Die Protokollierung von ETL-Prozessen ist die wichtigste Garantie dafür, dass Sie über wartbare und einfach zu reparierende Systeme verfügen.

Ein ETL mit dem richtigen Protokollierungsprozess ist wichtig, um den gesamten ETL-Betrieb in einem Zustand ständiger Verbesserung zu halten und dem Team zu helfen, Fehler und Probleme mit Datenquellen, Datenformaten, Transformationen, Zielen usw. zu verwalten.

Ein robuster Protokollprozess hilft Teams, Zeit zu sparen, sodass sie Probleme schneller und einfacher identifizieren können, und leitende Ingenieure benötigen weniger Zeit, um das Problem direkt zu lokalisieren. Manchmal treten Fehler mitten beim Extrahieren von Tonnen von Daten auf, und ohne ein Protokoll ist es schwierig, das Problem zu identifizieren – manchmal fast unmöglich. Ohne Protokolle muss der gesamte Prozess erneut ausgeführt werden. Mithilfe von Protokollen kann das Team schnell die Datei und Zeile identifizieren, die das Problem verursacht haben, und nur diese Daten beheben.

Der einzige Fall, den ich mir vorstellen kann, wo Protokolle nicht so wichtig sind, sind sehr kleine nicht automatisierte Systeme, wo der Prozess manuell läuft und es eine kleine Menge an Daten gibt, die von Hand überwacht werden können.

Protokolle verbessern die Automatisierung. ETL-Prozesse mit großen Datenmengen, die automatisch ablaufen, benötigen Protokollsysteme. Wenn sie gut geplant und ausgeführt werden, zahlt sich der Aufwand für den Aufbau eines Protokollierungssystems in Form einer schnelleren Fehlererkennung, zuverlässigerer Daten und der in Protokolldateien gefundenen Verbesserungspunkte aus.

Es gibt drei Hauptschritte beim Erstellen eines Protokollsystems: Generieren, Archivieren und Analysieren .

  • Generieren ist der Prozess des Dokumentierens, was während der Ausführung von ETL-Pipelines vor sich geht: wann der Prozess gestartet wurde, welche Datei oder Tabelle extrahiert wird, die Daten, die im Staging-Bereich gespeichert werden, die Fehlermeldungen und mehr. Alle wichtigen Informationen, die Ingenieuren helfen können, müssen protokolliert werden. Warnung : Achten Sie darauf, nicht so viele Informationen zu generieren, die nur Zeit und Platz verbrauchen und nicht hilfreich sind.
  • Protokolldaten zu archivieren bedeutet, eine Aufzeichnung vergangener Ausführungen zu führen, um vergangene Szenarien zu durchsuchen, um Fehler zu identifizieren oder mit dem aktuellen Szenario nach Verbesserungen zu vergleichen. Es ist wichtig, die Relevanz eines bestimmten Zeitpunkts in der Geschichte zu prüfen, um ihn zu speichern – Daten aus einer langen Zeit, in der sich die Struktur viele Male geändert hat, sind es nicht wert, aufbewahrt zu werden.
  • Analysieren . Die Protokollanalyse ist von entscheidender Bedeutung. Tonnen von Daten zu speichern, die nicht analysiert werden, macht keinen Sinn. Es kostet nur Zeit und Geld, die Daten zu generieren und zu speichern. Die Protokollanalyse ist nicht nur wichtig, um bei der Suche nach Fehlern zu helfen, sondern auch um Verbesserungspunkte zu identifizieren und die allgemeine Datenqualität zu verbessern.

Leistung

ETL-Prozesse können mit Tonnen von Daten arbeiten und können viel kosten – sowohl in Bezug auf den Zeitaufwand für die Einrichtung als auch auf die Rechenressourcen, die zur Verarbeitung der Daten erforderlich sind. Bei der Planung einer Integration müssen Ingenieure die Notwendigkeit der Verwendung aller Daten berücksichtigen. Das Beste, was Sie tun können, ist, mit der minimalen Datenmenge zu arbeiten, um die Ziele zu erreichen, anstatt Zeit und Geld dafür aufzuwenden, nur nutzlose Daten zu migrieren. Denken Sie auch daran, dass die Datenmengen im Laufe der Zeit tendenziell zunehmen, also versuchen Sie, Ihre zukünftigen Anforderungen zu berücksichtigen.

Wie viel Aufwand sollte in die Entwicklung eines Logging-Systems gesteckt werden?

Sie hängt von verschiedenen Faktoren wie der Menge und Häufigkeit der zu verarbeitenden Daten ab. Kleine Systeme mit wenigen Daten können manuell verwaltet werden, ohne dass in fortschrittliche Protokollierungssysteme investiert werden muss.

Unternehmen mit großen Datenmengen, vielen verschiedenen Prozessen, unterschiedlichen Datenquellen und -formaten und komplexen Pipelines müssen in die Erstellung von Protokollierungssystemen investieren. Wie bereits erwähnt, kann ein gutes Protokollsystem viel Zeit und Geld sparen.

Für einen offensichtlicheren Ansatz muss ein ETL-Prozess unabhängig von Unternehmensgröße, Datenmenge und Integrationshäufigkeit rentabel sein . Eine Investition in Zeit, Geld und technische Ressourcen muss sich auszahlen – wie der Ökonom Milton Friedman sagte: „There is no free lunch.“

Zusammenfassend kann ein ETL-Prozess Unternehmen helfen, den Gewinn mit Daten zu steigern, die sie bereits haben, aber nicht richtig nutzen. ETL-Pipelines können verschiedene Systeme, diese Tabellenkalkulationen mit wichtigen Informationen und andere Teile von Daten integrieren, die über verschiedene Abteilungen und Niederlassungen verteilt sind, sodass Unternehmen ihre Daten optimal nutzen können.