In diesem Blogbeitrag wird aufgezeigt, wie ein Import von Währungskursdaten der EZB per SSIS Paket darstellbar ist. Zudem wird aufgezeigt für welche Zwecke dies sinnvoll sein kann.

Einleitung

Die Europäische Zentralbank (EZB) ist als Organ der Europäischen Union allen voran für die Preisstabilität in der Eurozone zuständig. Dabei gilt für dieses Ziel die Inflationsrate als Hauptindikator. Die Inflationsrate soll dabei bei nahe, aber unter zwei Prozent pro Jahr gehalten werden. Damit soll die Kaufkraft der gemeinsamen Euro-Währung erhalten bleiben.

Neben der Teuerungsrate in der Eurozone und der Euro-Entwicklung ganz generell, ist für die EZB sicherlich der Blick auf den Rest der Welt nicht unerheblich. Wodurch es daher nicht weiter verwundert, dass die EZB, neben diversen makroökonomischen Daten (wie z.B. Inflationsrate, BIP, Population) auch die tagesaktuellen Währungskurse zu 41 Fremdwährungen seit 1999 in ihrem Statistical Data Warehouse vorhält.

Dabei sind die Währungskurse nicht nur aus makroökonomischer Sicht interessant, sondern vor allem auch im Unternehmenskontext eine wichtige Komponente. Hier finden Sie zum einen Anwendung in der Rechnungslegung, um z.B. den Forderungs- oder Vorratsbestand in Fremdwährungen zu bewerten und in der Gesellschaftswährung auszuweisen. Die Bewertung kann je Anwendungsfall nach Stichtagskursen oder Durchschnittskursen erfolgen. Vor allem sind die Währungskurse aber eine wichtige Größe, um monetäre Daten jeglicher Art mit der Umrechnung auf eine Währungseinheit erst tatsächlich vergleichbar zu machen und im Reporting zu verwenden.

Dieser Blog zeigt eine Möglichkeit auf, sich die Währungskurse von der EZB per SSIS-Paket zu extrahieren, um sie dann für jegliche Umrechnungen im Datawarehouse nutzen zu können.

Datenquelle

Die EZB-Webseite (https://www.ecb.europa.eu/) verfügt über einen großen Statistics-Bereich, auf dem sie unterschiedlichste Daten publiziert.

Abb. 1: Statistics-Bereich der EZB-Webseite

Neben der Veröffentlichung der wichtigsten KPIs für die Preisstabilität der Eurozone, findet man im linken Menü unter „ECB/Eurosystem policy and exchange rates“ den Punkt „Euro foreign exchange rates“. Hierunter findet man die Währungskurse der 41 Fremdwährungen in Relation zum Euro. Im unteren Teil der Seite ist der Download-Bereich positioniert, in dem die Daten in den Formaten PDF, CSV (.zip), XML, und RSS feeds angeboten werden.

Abb. 2: Download-Optionen auf der Seite der Währungskurse

Für unsere Extraktion werden wir die CSV (.zip) Variante nutzen. Diese beinhaltet sämtliche Währungskurse seit 1999. Per Rechtsklick auf den CSV (.zip)-Link und „Verknüpfung kopieren“ erhalten wir unser statische Quell-URL, von der wir uns im nächsten Schritt die Daten extrahieren werden. (https://www.ecb.europa.eu/stats/eurofxref/eurofxref.zip?d39d8f79242ae4ec36359adf3e87f411)

Aufbau des SSIS-Pakets

Beim Aufbau des SSIS-Pakets werden wir mit zwei Komponenten konfrontiert, die uns im Beraterleben eher selten über den Weg laufen:

• Das Extrahieren von Daten von einer Webseite
• Das Entpacken von komprimierten Daten (Entzippen)

Um diese beiden Komponenten umsetzen zu können, benötigen wir, neben den SSIS-Toolbox Bordmittel, lediglich ein Zip-Programm. Hier bietet sich 7Zip (http://www.7-zip.de/) an, da es zum einen eine OpenSource-Lösung darstellt und wir über bestimmte Parameter Argumente an die 7Zip.exe übergeben können, die in diesem Fall wichtig sind.

Nun zum generellen Aufbau des SSIS-Pakets. Im ersten Schritt werden wir uns die ZIP-Datei von der EZB-Webseite über ein simples MS Visual C# -Skript in einen von uns gewählten Ordner downloaden. Anschließend werden wir die heruntergeladene Datei entzippen und die daraus hervorgehende CSV-Datei in unseren SQL Server laden.

Abb. 3: Übersicht über das SSIS-Paket

Starten wir mit der Komponente „Download Webdata“. Zuerst einmal legen wir uns drei Variablen an, um das SSIS-Paket am Ende auch möglichst dynamisch zu gestalten. Die erste Variable wäre der „BasePath“. Dieser gibt uns den generellen Netzwerkpfad an, unter dem wir die ZIP-Datei und die entzippte CSV-Datei dann ablegen wollen. Mit der zweiten Variable „BaseZIPDataPath“ geben wir den Ablageort, sowie den Namen der ZIP-Datei an. Dieser setzt sich aus der BasePath-Variablen und dem gewünschten ZIP-Dateinamen samt .zip-Endung zusammen. In die dritte Variable „BaseWebPath“ geben wir die Download-URL ein, die wir uns bereits im vorherigen Schritt von der EZB-Webseite kopiert haben.

Abb. 4: Übersicht über die im ersten Schritt benötigten Variablen

Nun zurück zur eigentlichen Komponente. Hierzu benötigen wir die Script-Task-Komponente aus der SSIS-Toolbox. Im Editor müssen wir unsere beiden Variablen BaseWebPath und BaseZIPDataPath als „ReadOnlyVariables“ aufnehmen, damit wir im Skript darauf referenzieren können. Ansonsten sind keine weiteren Einstellungen nötig.

Abb. 5: Sicht auf den Skripttask-Editor

Über „Skript bearbeiten“ gelangen wir dann zum eigentlichen Skript, welches sich in einem neuen Fenster öffnet. Dieses beinhaltet bereits Standardobjekte auf die das SSIS-Paket Bezug nehmen kann. Die Klasse „Main()“ steht dann wiederum zur Verfügung um das eigene Skript einzubauen. Das Skript selbst ist relativ simpel gehalten und sieht folgendermaßen aus:

Variables varCollection = null;

Dts.VariableDispenser.LockForRead("User::BaseWebPath");
Dts.VariableDispenser.LockForRead("User::BaseZIPDataPath");
Dts.VariableDispenser.GetVariables(ref varCollection);

System.Net.WebClient myWebClient = new System.Net.WebClient();
 string webResource = varCollection["User::BaseWebPath"].Value.ToString();
 string fileName = varCollection["User::BaseZIPDataPath"].Value.ToString() ;
 ;

myWebClient.DownloadFile(webResource, fileName);

Das Skript selbst bezieht sich bereits auf die vorher festgelegten Variablen-Namen und müsste bei anderen Namensgebungen noch angepasst werden. Schlussendlich muss dieses Skript dann unter dem Kommentar „TODO: Add your code here“ eingefügt und abgespeichert werden.

Abb. 6: Skript der „Download Webdata“ Komponente

Damit ist der Download der ZIP-Datei und das Ablegen am definierten Pfad vollzogen. Der nächste Schritt im SSIS-Paket ist eine Notwendigkeit, die entsteht, weil beim Extrahieren der ZIP-Datei ein Fehler entsteht, wenn am Zielpfad sich bereits Daten vom letztmaligen Entpacken befinden. Deshalb wird in der Komponente „Output Verzeichnisinhalt löschen“ der Zielpfad bereits vor dem entpacken der ZIP-Datei geleert. Dies führen wir mit der Komponente „Task Dateisystem“ durch. Doch zuvor müssen wir noch im Verbindungsmanager eine Verbindung zum Ordner anlegen, in dem wir die extrahierte Datei ablegen wollen. Hierzu legen wir eine Verbindung vom Typ „Dateiverbindung“ an und wählen hier den Ablage-Ordner sowie den Verwendungstyp “Vorhandener Ordner“ aus.

Abb. 7: Festlegung der „Output“-Verbindung

Die nun angelegte Verbindung können wir dann ebenfalls noch dynamisieren, indem wir noch eine Variable namens „UnzipFolderPath“ anlegen, die als Ausdruck folgendermaßen aussieht:

@[User::BasePath] + \\Output

Diese Variable kann dann über die Expression den ConnectionString vorgeben. Nun können wir mit der Komponente „Output Verzeichnisinhalt löschen“ fortfahren und im Editor als SourceConnection die gerade angelegte Verbindung verwenden. Zudem müssen wir als Operation noch „Verzeichnisinhalt löschen“ auswählen.

Abb. 8: Sicht auf den Task Dateisystem-Editor

Im nächsten Schritt wird die abgelegte ZIP-Datei nun extrahiert. Hierfür ist, wie einleitend schon beschrieben, das ZIP-Programm 7Zip notwendig. Im SSIS-Paket nutzen wir für das Extrahieren die Komponente „Task Prozess ausführen“. Im Editor ist für uns vor allem die Seite „Verarbeiten“ wichtig. Der Prozess, den wir bei „Executable“ ausführen wollen, ist die 7Zip.exe Datei. Hier müssen wir dementsprechend den Pfad zu dieser Datei hinterlegen. Bei „Arguments“ müssen wir der exe-Datei nun Parameter übergeben, um ihr weitere Anweisungen zu geben. Die Syntax für diese Argumente finden wir unter https://sevenzip.osdn.jp/chm/cmdline/switches/ . Den Pfad zur ZIP-Datei geben wir über den Parameter „x“ vor. Dem Output-Pfad wird wiederum ein „-o“ davorgestellt. Demzufolge sieht die Syntax dann wie folgt aus:

x „ZIP-Datei-Pfad“ -o „Output-Pfad“

Die weiteren Einstellungen müssen nicht verändert werden, wodurch wir mit diesem Schritt fertig wären. Für die Dynamisierung dieses Schrittes können wir in den Expression wieder unsere Variablen folgendermaßen hinterlegen:

Arguments: ” x “+  @[User::BaseZIPDataPath]   +” -o”+ @[User::UnzipFolderPath]

Abb. 9: Sicht auf den Task Prozess ausführen-Editor

Nun können wir uns das Ergebnis unseres Downloads, sowie des Extrahierens betrachten, indem wir das SSIS-Paket nun ausführen und uns in dem vorher definierten Ordner die extrahierte Datei anschauen. Diese liegt uns korrekterweise im CSV-Format vor und heißt „euroxref-hist.csv“. Beim Blick in die Datei erkennen wir relativ schnell den Spaltenaufbau sowie das genutzte Spaltentrennzeichen Komma.

Abb. 10: Extrahierte Währungskurs-Datei im CSV-Format

Diese Erkenntnisse können wir nun nutzen, um für den Import der Datei in den SQL-Server die nötigen Verbindungen anzulegen. Zuerst einmal benötigen wir eine Flatfileverbindung, um die CSV-Datei anzubinden. Hierzu wählen wir im Editor die Datei aus und prüfen auf der Seite „Spalten“ ab, ob das richtige Spaltentrennzeichen erkannt und die CSV-Datei richtig interpretiert wurde. Wenn dies der Fall ist, können wir anschließend diese Verbindung ebenfalls dynamisieren. Hierzu legen wir die Variable „UnzippedImportPath“ an, die vom Ausdruck wie folgt lautet:

@[User::BasePath] + “\\Output\\eurofxref-hist.csv”

Die erstellte Variable müssen wir dann noch in der Verbindung in den Expressions als ConnectionString hinterlegen.

Abb. 11: Sicht auf den Verbindungs-Manager-Editor für die Verbindung der CSV-Datei

In unserem Fall wollen wir die extrahierten Daten nun in eine SQL-Datenbank schreiben. Hierfür legen wir als letztes noch eine OLE DB Verbindung zu unserem gewünschten Datenbankserver an.

Über die „Task SQL ausführen“-Komponente leeren wir die noch nicht vorhandene Tabelle dann jeweils vor dem nochmaligen Befüllen. Im Editor wählen wir die gerade angelegte SQL-Server Verbindung bei „Connection“ aus und geben bei „SQL Statement“ die Syntax „TRUNCATE TABLE [Zieltabellenname]“ an.

Abb. 12: Sicht auf den Editor des „Truncate Table“-SQL Task

In der letzten Komponente („Datenflusstask“) legen wir eine Flatfilequelle und ein OLE-DB Ziel an und verbinden diese. Als Verbindung in der Flatfilequelle geben wir die vorher angelegte Verbindung zur CSV-Datei an und prüfen die ausgelesenen Spalten auf der Seite „Spalten“ des Quell-Editors auf ihre Richtigkeit.

Abb. 13: Spalten der CSV-Quelle im Quell-Editor

Das OLE DB Ziel ändern wir namentlich direkt in „T_IMPORT_FACT_EZB_Waehrungskurse“ um, damit bei der Erstellung der Tabelle über die Schaltfläche „Neu“ diese gleich korrekt benannt wird. Anschließend prüfen wir über die Seite „Zuordnungen“ ob die Eingabe- und Zielspalten automatisch schon korrekt zugeordnet wurden. Sollte das der Fall sein, können wir den Ziel-Editor per „OK“ schließen und haben damit unser SSIS-Paket fertiggestellt.

Abb. 14: Zuordnung der Eingabe- und Zielspalten im Ziel-Editor

Importierte Tabelle

Nachdem wir die Tabelle über das SSIS-Paket in unsere Datenbank importiert haben, können wir uns die Tabelle im SQL Management Studio betrachten. Spätestens hierbei fällt uns  auf, dass die Tabellenstruktur nicht idealtypisch aufgebaut ist, um sie als Faktentabelle anbinden zu können.

Abb. 15: Währungskurs-Tabelle im SQL Management Studio

Für das Transponieren nutzen wir die UNPIVOT-Syntax und machen analog zur Date-Spalte aus den vielen Währungsspalten eine Spalte mit der Merkmalsausprägung, sowie eine weitere mit dem Kurswert. Die Syntax der View sieht dann folgendermaßen aus:

CREATE VIEW [dbo].[V_IMPORT_FACT_EZB_Waehrungskurse] AS
SELECT
       [Date],
       [Currency],
       [Exchangerate]
FROM
       [dbo].[T_IMPORT_FACT_EZB_Waehrungskurse] IMP

  UNPIVOT (Exchangerate
  FOR Currency IN ([USD], [JPY], [BGN], [CYP], [CZK], [DKK], [EEK], [GBP], [HUF], [LTL], [LVL], [MTL], [PLN], [ROL], [RON], [SEK], [SIT], [SKK], [CHF], [ISK], [NOK], [HRK], [RUB], [TRL], [TRY], [AUD], [BRL], [CAD], [CNY], [HKD], [IDR], [INR], [KRW], [MXN], [MYR], [NZD], [PHP], [SGD], [THB], [ZAR], [ILS])
  ) AS UNP

Abb. 16: View für das Transponieren der Daten

Damit steht uns eine View zur Verfügung, die in jedes vorhandene Modell eingebunden werden kann oder woraus ein eigenes Modell entstehen kann.

Schlussfolgerung

Mit dem SSIS-Paket ist es nun einfach möglich, valide Währungskursdaten von der EZB in sein Modell zu integrieren und für seine Zwecke zu nutzen. Aber nicht nur die Integration in ein bestehendes Modell kann interessant sein. Die Währungskurse allein bieten schon eine aussagekräftige Datengrundlage, um daraus analytische Rückschlüsse auf die makroökonomische Entwicklung/Lage zu ziehen. Aber die EZB bietet nicht nur diesen Datenschatz in ihrem Statistical DataWarehouse an, sondern verfügt über eine umfangreiche Datensammlung, die sich lohnt, erkundet zu werden.