Hier bei Bissantz & Company legen wir sehr großen Wert auf Konsistenz beim Datenladeprozess. Die Summe des Inputs muss der Summe des Outputs entsprechen. Alles andere führt zum protokollierten Abbruch mit Fehlern. Wir versuchen nicht Prozesssicherheit vorzutäuschen, indem wir mit Tricks das Laden von inkonsistenten Daten zulassen. Leider klopfen in der Praxis dennoch oft inkonsistente Daten aus Vorsystemen an die große Tür der OLAP-Welt. Hier sehen wir unsere Rolle des strengen Türstehers, der lieber zu genau hinschaut als einfach nur durchwinkt. Doch der Aufwand lohnt sich. Denn was bringt uns OLAP, wenn am Ende gar nicht mehr die gleiche Anzahl an Daten analysiert werden kann wie ursprünglich vorhanden. Wenn man sich am Anfang das Leben leicht machen will, indem man hundertprozentige Prozesssicherheit vortäuscht, zahlt man den Preis für eine OLAP-Scheinwelt mit falschen Daten. Der nachfolgende Blog stellt eine Anleitung für die strukturierte Fehlersuche beim Datenladeprozess dar. Als Beispiel für typische Fehler beim Datenimport dient ein simples DeltaMaster-Modeler-Vertriebsdatenmodell eines fiktiven Lautsprecherherstellers mit einfachen Excel-Tabellen als Datenquelle.
Üblicherweise werden unsere Datenladeprozesse in SQL Server Management Studio als SQL-Server-Agent-Jobs definiert und zeitgesteuert zur Ausführung gebracht. Daher beginnen Sie mit der Fehlersuche genau an dieser Stelle, denn grundsätzlich sollte die strukturierte Fehlersuche stets auf grober Ebene beginnen und dann immer weiter verfeinert werden.
Beispiel 1
Der SQL-Server-Agent bringt bei der Ausführung des Datenimports per SSIS-Paket folgenden Fehler:
Abb. 1: Fehler bei Lauf des SQL-Server-Agents
Schritt 1
Rufen Sie das Protokoll für den Auftragsverlauf des SQL-Server-Agent per Rechtsklick auf den betreffenden Auftrag auf, Verlauf anzeigen. Dieser beinhaltet den ersten Hinweis auf einen Fehler.
Abb. 2: Verlaufsprotokoll SQL-Server-Agent
Schritt 2
Mehr Informationen enthält gegebenenfalls das SSIS-Protokoll, sofern dieses aktiviert wurde. So aktivieren Sie die Protokollierung für SSIS-Pakete: Betreffendes Paket im Projektmappen-Explorer per Doppelklick auswählen, Menü SSIS, Protokollierung…
Im sich öffnenden Dialogfenster fügen Sie ein neues Protokoll hinzu. Hier empfiehlt es sich als Anbietertyp den SSIS-Protokollanbieter für Textdateien oder den SSIS-Protokollanbieter für SQL Server zu wählen. Entscheidet man sich für die erste Variante, wird das Protokoll in Form einer Textdatei an einem frei wählbaren Ort gespeichert. Die Dateiendung kann dabei ebenfalls frei vergeben werden. Bei der Option SSIS-Protokollanbieter für SQL Server wird das Protokoll in einer SQL-Datenbank unter Systemtabellen, dbo.sysssis.log gespeichert.
Um den Umfang der Protokollierung zu bestimmen, müssen unter Container und beim SSIS-Protokollanbieter entsprechende Häkchen gesetzt werden. Weiterhin müssen unter dem Reiter Details die zu protokollierenden Ereignisse angehakt werden. Es empfiehlt sich alle Ereignisse zu wählen, mindestens jedoch OnError und OnInformation. Wichtig: Wenn hier keine Haken gesetzt sind, erfolgt auch keine Protokollierung.
Abb. 3: Protokollierung für SSIS-Pakete einstellen
Abb. 4: Ereignisse für die Protokollierung bestimmen
Mit der vorliegenden SSIS-Paketprotokollierung verdichtet sich der Verdacht aus dem SQL-Server-Agent-Verlauf immer mehr:
Abb. 5: SSIS-Paketprotokollierung
Schritt 3
Die feinste Granularitätsstufe liefert die DeltaMaster-Modeler-Analysesitzung bei der strukturierten Fehlersuche. Doch auch hier gilt es schrittweise vorzugehen, beginnend mit dem Bericht Transform Log im Ordner Monitoring.
Alternativ zur DeltaMaster-Modeler-Analysesitzung kann die vom Modeler erstellte Prozedur P_SYSLOG_LOG für die Protokollierung ausgeführt werden.
Abb. 6: Transform Log in der DeltaMaster-Modeler-Analysesitzung
Der Bericht Transform Log zeigt zwei Fehler (Spalte Status und Error): SQL-Fehler 4 („2627 – Verletzung der PRIMARY KEY-Einschränkung ‚PK_T_DIM_02_02_Product‘. Ein doppelter Schlüssel kann in das dbo-T_DIM_02_02_Product-Objekt nicht eingefügt werden.“) und SQL-Fehler 6 („547 – Die INSERT-Anweisung steht im Konflikt mir der FOREIGN KEY-Einschränkung ‚FK_T_FACT_01_Retailssales_Product‘…“). Fehler 4 deutet auf doppelte Schlüssel (IDs) mit inkonsistenten Bezeichnungen und/oder inkonsistenten Attributen hin. Fehler 6 ist ein Folgefehler auf nächsthöherer Ebene – eine Schlüsselverletzung (Constraintverletzung) – hierzu weiter unten mehr.
Abb. 7: SQL-Fehler 6 – Schlüsselverletzung
Im nächsten Schritt der Fehlersuche mithilfe der DeltaMaster-Modeler-Analysesitzung bringt der Bericht Data error (wo. subseq. Error) mehr Informationen ans Licht. Folgefehler werden hier nicht mehr mit angezeigt.
Abb. 8: Bericht Data error (wo. subseq. errors)
Nun wissen wir, in welcher Dimension, Hierarchie, Tabelle und in welchem Level sich ein doppelter Schlüssel befindet, sogar welches Element betroffen ist. Nämlich das Element mit der ID „P4“.
Der Bericht Data error detail (wo. subseq. errors) gibt uns nun völligen Aufschluss darüber, wo die Inkonsistenzen vorliegen.
Abb. 9: Bericht Data error detail (wo. subseq. errors)
Die gleiche ID mit unterschiedlicher Bezeichnung und unterschiedlichen Attributen kommt doppelt in der Produktdimension vor. Ein Blick auf die dazugehörige SQL-View bestätigt dies.
Abb. 10: SQL View mit doppeltem Schlüssel
Nun gilt es den Fehler in der Datenquelle zu beseitigen, d. h. in diesem Fall in der Excel-Tabelle.
Abb. 11: Auf Konsistenz korrigierte Excel Quelle
Weitere Beispiele für typische Transformationsfehler durch Inkonsistenzen der zu ladenden Daten sind im Folgenden aufgeführt.
Beispiel 2 – Schlüsselverletzung in der MeasureGroup
Abb. 12: Unbekannter Schlüssel in der Faktentabelle
Die Faktentabelle enthält einen Schlüssel, der nicht in der Dimensionstabelle enthalten ist. Dies führt bei der Transformation zu folgender Constraintverletzung (SQL-Fehler 6) in den Monitor-Berichten der DeltaMaster-Modeler-Analysesitzung.
„547 – Die INSERT-Anweisung steht im Konflikt mit der FOREIGN KEY-Einschränkung ‚FK_T_FACT_01_Retailssales_Product‘. Der Konflikt trat in der Audiophile-Datenbank, Tabelle ‚dbo.T_DIM_02_02_Product‘, column ‚‘ProductID‘ auf.“
Abb. 14: SQL-Fehler 6
Abb. 15: Error details
Beispiel 3 – doppelte Schlüssel mit gleichen Merkmalen aber unterschiedlichen Vätern
Liegt ein doppelter Schlüssel mit gleichem Namen und gleichen Attributen vor, aber mit unterschiedlichen höher gelegenen Ebenen, kommt es ebenfalls zu Fehlern bei der Transformation. Die protokollierten Fehler entsprechen dabei denen aus Beispiel 1 (SQL-Fehler 4 und SQL-Folgefehler 6).
Abb. 16: Doppelter Schlüssel mit unterschiedlichen Vätern
Beispiel 4 – Bindungsfehler
Ändert sich beispielsweise die der Abfrage zugrundeliegende Tabelle oder existiert diese nicht mehr, kommt es zu Bindungsfehlern, die sich folgendermaßen äußern:
Abb. 17: Protokollierte Fehler im Transform Log
In diesem Fall existiert die Importtabelle mit den Daten für die Produktdimension nicht mehr.
Sonstige typische Fehler beim Datenladeprozess:
- Auf die zu importierende Excel-Tabelle kann nicht zugegriffen werden (Excel-Datei ist nicht geschlossen, SSIS verlangt exklusiven Zugriff)
- Excel-Datei hat Passwortschutz
- Das Format hat sich geändert (.xlsx statt .xls)
Dieses Dokument stellt den Anfang einer umfangreichen Dokumentation zur strukturierten Fehlersuche beim Datenladeprozess dar. Weitere typische Fehler werden gesammelt und ergänzt.
