Rufnummern mit Referenzdaten sauber trennen

In meinem letzten Vortrag zum Thema Datenqualität mit den SQL Server Integration Services bin ich unter anderem auf die Verwendung von Referenzdaten eingegangen. Als Beispiel habe ich dazu das Auftrennen von Telefonnummer, also Splitten in Landesvorwahl, Vorwahl und Rufnummer, verwendet. Dies gehört häufig mit zu den Wiederkehrenden Aufgaben bei der Übernahme von Daten aus unterschiedlichen Quellen.

Über Versuche, sich diese Aufgabe durch die konsequente Verwendung vordefinierter Trennzeichen in der Quell-Applikation zu erleichtern, kann ich nur sagen “Das klappt nicht!”. Auch die Verwendung von Regulären Ausdrücken ist nicht wirklich einfach und produziert Fehler, die manuell nachbearbeitet werden müssen.

Dabei ist die Lösung des Problems – zumindest für deutsche Rufnummern – eigentlich relativ einfach. Eine Telefonnummer ist durch die ITU fest definiert und darf im internationalen Verkehr max. 15 Zeichen lang sein. In Deutschland werden aktuell Telefonnummern inkl. Vorwahl von 12, in einigen Fällen auch 13 Zeichen, ausgegeben. Dabei ist eine Vorwahl zwischen 3 und 6 Zeichen lang und eine Rufnummer mindestens 3 Zeichen. Der große “Vorteil” bei Telefonnummern – bezogen auf das Auftrennen – ist, dass diese von Links nach Rechts gelesen werden und sich nach hinten hin “blocken”. Würde z.B. jemand in Berlin wohnen und ihm wäre die Rufnummer 123 zugeteilt, so könnten keine Rufnummern länger 6 Stellen existieren, die mit 030123 beginnen. Für eine Stadt in der Größenordnung von Berlin, würden somit eine Menge benötigter Telefonnummern verloren gehen. Dies ist einer der Gründe, warum kurze Telefonnummern heute fast nur noch in ländlichen Regionen anzutreffen sind und nicht mehr neu vergeben werden. Angeblich zahlt die Telekom Kunden mit 3-stelligen Telefonnummer sogar Geld, wenn sich diese eine neue Rufnummer zuteilen lassen.

Die Bundesnetzagentur, die für die Nummernverwaltung in Deutschland zuständig ist, stellt sowohl für die klassischen Ortsnetzbereiche wie auch für Mobilfunkrufnummer alle Vorwahlen zur Verfügung. Die ONB kommen in einer Textdatei daher, die Mobilfunknummern und Sondervorwahlen müssen leider manuell von der Seite übernommen werden. Mit Hilfe dieser Daten lassen sich die Telefonnummern sehr schnell und einfach von ihrer Vorwahl abtrennen.

Aufbereiten der ONB Daten

Die ONB Daten werden als erstes mit den Sondervorwahlen zusammengespielt und danach in verschiedene Dateien aufgeteilt. Für jede mögliche Länge der Vorwahlen wird eine eigene Datei erstellt, zusätzlich werden die beiden Dateien Ortsnetzname und Gemeindename erstellt. Als Datenziel verwende ich die Cache Transform Komponente.

Die beiden Cache-Dateien Ortsnetzname und Gemeindename enthalten identische Daten. Aufgrund des unterschiedlich zu setzenden Index auf den später über einen Lookup Task zugegriffen wird, werden hier jedoch 2 Dateien generiert.

Auftrennen der Telefonnummern

Die eigentlichen Rufnummern werden als erstes mit einem Script-Task bereinigt und für die weitere Verarbeitung aufbereitet.

   1: string strNummer = string.Empty;

   2: string strValue = Row.Telefon.ToString();

   3: strValue = strValue.Replace("+49", "");

   4: strValue = strValue.Replace("0049", "");

   5: strValue = strValue.Replace(" ", "");

   6: if (strValue.Length != 0)

   7: {

   8:     foreach (char numChar in strValue.ToCharArray())

   9:     {

  10:         if (Char.IsNumber(numChar)) strNummer += numChar.ToString();

  11:     }

  12: }

  13: else

  14: {

  15:     strValue = null;

  16: }

  17: Row.Telefon = strNummer;

Die häufig verwendete +49 sowie die 0049 werden hier im Script entfernt, ebenso alle Leerzeichen. Danach wird aus der Telefonnummer ein neuer String erstellt, der nur noch Zahlen enthält. Die nun aufbereitete Telefonnummer wird zurück an den Datenfluss übergeben.

Danach werden alle Telefonnummern <= 6 und >= 16 Zeichen in die gesonderte Ausgabe “Data Steward Level 3” umgeleitet. Zusätzlich wird noch eine Spalte Länderkennzeichen mit dem Wert “+49” hinzugefügt und Rufnummern die keine führende 0 haben um eine entsprechende ergänzt.

Bevor die einzelnen Rufnummern nun mit den Daten der Bundesnetzagentur verglichen werden, müssen die möglichen Vorwahlen separiert werden. Hierfür werden weitere abgeleitete Spalten erstellt, die jeweils die ersten 3, 4, 5 und 6 Zeichen der Telefonnummernspalte beinhalten.

Über einen Lookup werden nun die jeweiligen Telefonnummer in den einzelnen Caches gesucht und mit Zusatzinformationen aus den ONB Daten angereichert. Datensätze bei denen keine Vorwahl gefunden wurde, werden zu einer Suche mit der nächsten Länge weitergeleitet. Bei allen Rufnummern, bei denen eine Vorwahl gefunden wurde, wird nun zuerst die gefundene Vorwahl und die führende 0 (Verkehrsausscheidungsziffer) aus der original Rufnummer entfernt. Eine Spalte mit dem CountryCode (+49)  existiert ja bereits in der Datenbank.

Bei Telefonnummern, bei denen am Ende keine Vorwahlen gefunden wurde, findet noch eine zusätzliche Suche statt, bei denen die Ortsnamen in den zuvor aufgetrennten Gemeindenamen und Ortsnetznamen gesucht werden. Die gefundenen Daten werden dann auch jeweils in eine eigene Tabelle “Data Steward Level 2” und “Data Steward Level 1” geschrieben.

Der Data Steward

An einigen Stellen werden die Daten in einer extra Tabelle gespeichert, die ich hier in der Demo einfach nur Data Steward 1-3 nenne. Zwar haben Tests beim Kunden gezeigt, dass die Auftrennung zusammen mit den Referenzdaten der Bundesnetzagentur mit über 95% Treffer sehr gute Ergebnisse liefern, 5% fehlen aber weiterhin. 
Daten die nicht automatisiert bereinigt werden können, sollten gesondert gespeichert werden, so dass ein Data Steward diese manuell bearbeiten kann. Bei einem manuellen Eingriff kann schnell erkannt werden, ob entsprechenden Daten wirklich fehlerhaft sind oder ob sie durch den Automatismus nur nicht erkannt wurden. Durch die zusätzliche Anreicherung der Daten mit Gemeindenamen und Ortsnetznamen können dem Data Steward weitere Informationen für die Bereinigung an die Hand gegeben werden.

ONB Daten

Die ONB Daten der Bundesnetzagentur garantieren gegenüber vielen anderen im Netz frei verfügbaren Daten eine hohe Qualität, schließlich ist die Bundesnetzagentur hier auch das standardisierende Organ. Die Daten ermöglichen aber auch die Anreicherung der eigenen Daten mit Zusatzinformationen, sowie die Visualisierung der Daten auf ONB Ebene.

Die Bundesnetzagentur stellt auf Ihrer Seite ONB-Grenzen auch die entsprechenden Shape-Dateien zur Verfügung um die Daten visualisieren zu können.

Prüfung mit klickTel

Wem die Standardisierung der Telefonnummern mit Hilfe der ONB Daten nicht ausreicht, der kann Telefonnummern auch mit der Komponente datenfabrik.phone gegen einen klickTel Server prüfen und so Telefonnummern nicht nur normieren und syntaktische validieren, sondern anhand eines offiziellen Telefonbuchs überprüfen.

Wer Internationale Telefonnummern standardisieren muss, dem Helfen wir in der datenfabrik auch gerne mit unserem Know-how weiter.