Als Profiling wird eine vorausgehende Datenanalyse bei der Integration neuer Daten bezeichnet. Wenn Daten in ein bestehendes System übernommen werden sollen, muss als erstes überprüft werden, ob die neuen Daten den eigenen Qualitätsstandards entsprechen. Besonders wenn mit Exporten aus anderen Quellen gearbeitet wird, sollte dem Thema mit erhöhter Vorsicht begegnet werden.
Folgende Probleme können unter anderem bei einem Profiling erkannt werden:
- Verwendung unterschiedlicher Feldlängen
- Daten werden in mehreren Felder gespeichert
- Verwendung unterschiedlicher Datentypen
- Schlüssel innerhalb von Referenzen haben unterschiedliche Bedeutungen
- unterschiedliche semantische Datenbeschreibung
Diese Liste kann natürlich noch erweitert werden und nicht alle der aufgeführten Punkte sind für jede Strategie gleich wichtig.
Im folgenden zwei kleine Beispiele, die die Problematiken hinter einigen der aufgeführten Punkte stärker verdeutlichen sollen.
Derzeit werden in Deutschland Rufnummern nur mit 12 Ziffern, in einigen Fällen mit 13 Ziffern vergeben, nach der Spezifikation E.164 ist die Rufnummer im internationalen Verkehr auf 15 Ziffern einschließlich Ländercode begrenzt. Wenn man nun noch an die unterschiedlichen Schreibweisen von Telefonnummern denkt die einem im Alltag begegnen, kann man schnell auf 20 oder mehr Zeichen für eine Telefonnummer kommen. Hier können Probleme mit einer unterschiedlichen semantischen Datenbeschreibungen auftreten. Was ist wenn im Zielsystem mit viel Aufwand eine spezielle Rufnummernschreibweise eingeführt wurde? Weitere Probleme bei Telefonnummer können dadurch entstehen, dass z.B. die Landesvorwahl im Quellsystem über eine Referenztabelle “Länder” bezogen wird, oder die Vorwahl und Rufnummer in zwei verschiedenen Feldern gespeichert werden.
Häufig befinden sich in den Datenextrakten aus Quellsystemen Angaben wie 12760219 als Datumswert. Dieser Wert muss, z.B. mit dem Task “Abgeleitete Spalte”, nach 19.02.1276 konvertiert werden, was sagt dann der SQL Server dazu? Und was sagt die Datenbeschreibung dazu? Innerhalb welchem Zeitraumes muss ein Geburtsdatum liegen, damit es für die Anwendung gültig ist? Werden Kunden übernommen, oder auch nur dessen Geburtsdatum, ohne eine zusätzliche Prüfung wenn der Kunde am 12.06.1901 geboren ist? Immerhin wäre der Kunde derzeit 107 Jahr alt. Ok, das kann sein, und wenn er am 12.06.1900, 12.06.1899, 12.06.1898 usw. geboren wurde?
Wie können nun beim importieren von Daten Analysen vorgenommen werden, die helfen spezielle Pattern, Fehler, Datenbereiche usw. zu erkennen?
SSIS 2008 und der Datenprofilerstellungs-Task
Seit der Version 2008 verfügen die Integration Services über einen Datenprofilerstellungs-Task. Der Task lädt Daten aus einer bestehenden SQL Server Datenbank und analysiert diese nach speziellen Parametern. Die einzelnen Funktionen/Regeln zum Analysieren der Daten nennen sich im Task Profilanforderungen. Je nach Typ lassen sich verschiedene Einstellungen vornehmen, bei denen es sich in den meisten Fällen aber nur um Feineinstellungen handelt. Der Task verfügt insgesamt über 8 verschiedene Profilanforderungen die nicht erweitert werden können.
Über ein sogenanntes Schnellprofil wird die Verbindung zur überprüfenden Tabelle und 6 der zur Verfügung stehenden Anforderungsprofile mit den Standardparametern eingerichtet.
Die Daten aus der Analyse werden in einer XML Datei gespeichert, die später mit dem Programm Data Profil Viewer ausgewertet werden kann. Der Data Profile Viewer wird mit den SSIS installiert und ist über Programme > Microsoft SQL Server 2008 > Integration Services zu finden. Für eine 1 GB große Tabelle benötigt der Datenprofilierungstask knapp 23 Minuten.
Innerhalb des Viewers kann man aus den Ergebnissen der einzelnen Profilanforderungen heraus einen Drilldown auf die original Daten durchführen. Hierfür speichert der Profilerstellungs-Task innerhalb der XML Datei entsprechende Referenzen ab, mit denen auf die Originaldaten im Server verweisen werden. Je nach Regel und Profilanforderung können an dieser Stelle aber auch recht umfangreiche Originaldaten in der XML Datei gespeichert werden.
Folgende Regeln stellt der Datenprofilerstellungs-Task zur Verfügung:
- Profil für NULL Verhältnisse
Anzahl von NULL Values pro Spalte
- Spaltenstatistikprofil
Minimum, Maximum, Mittelwert, Standardabweichung numerischer Spalten
- Verteilungsprofil für Spaltenlänge
Anzahl Werte pro Länge mit Drilldown auf die originalen Datensätze
- Verteilungsprofil für Spaltenwert
Anzahl unterschiedlicher Werte pro Spalte mit Drilldown auf die originalen Datensätze
- Kandidatenschlüsselprofil
Analysiert welche Spalte als Schlüssel für die analysierte Tabelle in Frage kommt; ebenfalls mit Drilldown auf die originalen Datensätze.
- funktionales Abhängigkeitsprofil
Analysiert das Vorkommen von Schlüsselwerten und daraus resultierenden abhängigen Werten in Referenztabellen.
- Wertinklusionsprofil
Überprüft ob eine Spalte oder eine Gruppe von Spalten zwischen ausgewählten Spalten als Fremdschlüssel fungieren kann.
- Spaltenmusterprofile
Erstellt reguläre Ausdrücke für die vorhandenen Werte und analysiert dazu die Häufigkeit.
Nicht alle Regeln/Anforderungsprofile werden auf die zur Verfügung stehenden Eingabespalten angewendet. Die Anforderungsprofile sind nur für spezielle Datentypen zulässig. Eine genaue Auflistung der Profile mit den zu verwendenden Datentypen ist in der MSDN zu finden.
Wenn man die XML Datei mit einem normalen Editor öffnet, kann man schnell den Eindruck gewinnen, das alle Daten zur Analyse und für den Drilldown in der XML gespeichert werden. Der Datenprofilierungstask speichert jedoch nur die Werte, die für die ersten beiden Ebenen der Analyse benötigt werden in der XML Datei. Die Daten für das Drilldown werden während der Analyse vom SQL Server gezogen; aus diesem Grund wird hier auch eine Verbindung zum SQL Server benötigt.
Der größte Nachteil des Datenprofilierungs-Task ist, das Daten zuerst in den richtigen Datentypen auf dem SQL Server vorliegen müssen um sie zu analysieren. Meiner Ansicht nach ein bisschen spät um einen Datenprofiler einzusetzen. Schöner wäre es wenn der Datenprofilierungs-Task im Datenfluss agieren würde und z.B. ein “Datentypprofil” mitbringt würde. Als weiteren Nachteil sehe ich das Fehlen von Möglichkeiten zum Erstellen eigenständiger Erweiterungen. Alles in allem hat Microsoft angefangen eine bestehende Lücke zu schließen und, obwohl der Weg zu einem vernünftigen Programm noch weit scheint, dabei viele gute Ideen umgesetzt.
Mehr Infos gibt es noch von Microsoft und zwar hat Steffen Krause einen Webcast in seiner SQL Server 2008 kurz & knapp Reihe zum Thema veröffentlicht:
SQL Server 2008 kurz & knapp (Teil 9) – Datenprofiling in Integration Services
