Power Query Wechselkursabfrage mit TABLE.Manager nach SQL Server laden

Publiziert von

Eine der Stärken unseres Tools TABLE.Manager liegt darin, daß die Funktionen auch modular in eigenen Excel Anwendungen eingesetzt werden können. So kann beispielsweise die Rückschreibefunktion dazu verwendet werden, um eine Abfrage aus Power Query nach der Aktualisierung auf Knopfdruck gleich auch nach SQL Server zurückzuschreiben. Das dafür notwendige Customizing der Excel-Datei wird in diesem Blogbeitrag erläutert.

Zur TABLE.Manager Testversion!

Wir verwenden dazu die Datei aus dem Blogbeitrag Wechselkurse aus dem Web mit Power Query nach Excel einbinden aus dem Jahr 2016. Damals haben wir als fertiges Resultat eine täglich aktualiserbare Wechselkurstabelle USD-EUR aus dem Datenfeed der EZB erzeugt (Hinweis: in Excel 2016 ist Power Query im Bereich „Get & Transform“ vollständig in Excel integriert):

Die Zieltabelle T_FXrates in SQL Server besteht ebenfalls aus 4 Feldern (die jedoch nicht den gleichen Namen haben wie die Tabelle in Excel), die Tabelle ist noch leer:

Als erstes wird in der Excel Datei oberhalb der Überschrift Platz geschaffen, um die für die TABLE.Manager Rückschreibefunktion notwendigen Definitionsbereiche einsetzen zu können. Diese Systemzeilen können über die Excel Gruppierungsfunktion aus- und eingeblendet werden.

Hier wird der erste tabellenspezifische Definitionsbereich mit dem Namen FLEXtables_Writeback_ForexTable eingesetzt:

  • Der Bereich muß gleich viele Spalten haben wie die Tabelle, die übertragen werden soll (also 4)
  • Der Bereich besteht aus zwei Zeilen: die erste Zeile enthält das Schlüsselwort „Write“, das bedeutet die Spalte wird nach SQL Server übertragen. Alternativ könnte das Schlüsselwort „Ignore“ eingesetzt werden, dann wird die Spalte bei der Übertragung ignoriert. Die zweite Zeile enthält die Spaltennamen aus der SQL Zieltabelle.
  • Eine Definition der Formate ist nicht notwendig, jedoch müssen die Inhalte der zu übertragenden Datenspalte mit dem Feldformat im SQL Server kompatibel sein.
  • Die dunkelblaue Überschriftszeile (Zeile 3) dient lediglich zur schnelleren Orientierung, ist aber nicht notwendig für die Übertragung.
  • WICHTIG: der Namensbereich – wie auch alle weiteren zu definierenden Namensbereiche – muß auf das Worksheet, in unserem Fall also „FX-Rates“, bezogen werden:

    Der Grund liegt darin, daß für TABLE.Manager das Sheet die Ausführungsebene darstellt, also jedes Sheet seine eigenen Definitionen (mit aber tw den gleichen Namensbereichen) haben können muß.

Auf diese Weise könnten auch mehrere tabellenspezifische Definitionsbereiche pro Worksheet angelegt werden, es können also beliebig viele Tabellen innerhalb eines Worksheets zur Übertragung definiert werden.

Nun folgt der allgemeine Definitionsbereich, dieser besteht aus 3 Namensbereichen:

  1. FLEXactions (3 Spalten breit) = die hier definierte Liste wird beim Betätigen der Buttons „READ Process“ und „WRITE Process“ abgearbeitet.
  2. FLEXconnections (10 Spalten breit) = hier werden die SQL Verbindungseigenschaften für sämtliche READ- und WRITE-Prozesse definiert. In diesem Anwendungsfall reicht eine „Connection1“, die in der Excel Datei auch gar nicht angelegt zu sein braucht sondern lediglich die SQL Infos für den Writeback aufnimmt. Es wird also pro SQL Server Ziel-Datenbank eine Definitionszeile angelegt.
  3. FLEXtables_Writeback (10 Spalten breit) = hier werden die rückschreibe-spezifischen Parameter definiert, etwa der Name der Excel-Quelltabelle und der SQL-Zieltabelle. Aber auch der Verweis auf den zuvor vergebenen tabellenspezifischen Namensbereich. Es wird also pro Rückschreibe-Tabelle eine Definitionszeile angelegt.

WICHTIG: diese 3 Namensbereiche müssen ebenfalls sheet-bezogen definiert werden. Auch sind die dunkelblauen Überschriftszeilen nicht Bestandteil des Definitionsbereichs und dienen lediglich zur Orientierung:

Zur Kontrolle, die 4 definierten Namensbereiche im Namensmanager sowie die bereits vorhandene Excel Tabelle:

Sobald diese 4 Definitionsbereiche angelegt sind, kann der Button WRITE Process aus dem Linearis Excel Add-In gedrückt werden und es startet der Rückschreibeprozess, der schon aus dem TABLE.Manager bekannt ist, mit der Sicherheitsabfrage

… und weiter mit der eigentlichen Datenübertragung. Dabei werden technisch gesehen die bestehenden Datensätze der Tabelle gelöscht und die neuen übertragen, der Übertragungsvorgang kann also beliebig oft wiederholt werden, ohne daß es zu einer Vervielfachung der Datensätze kommen könnte:

Die 4.666 Datensätze aus der Power Query Abfrage sind jetzt in der SQL Server Tabelle angekommen:

Fazit

Der WRITE-Funktion aus der TABLE.Manager Anwendung kann mit wenig Aufwand in eigenen Excel Anwendungen genutzt werden und so ein hoher Automatisierungsgrad erreicht werden. Auf ähnliche Weise kann auch die READ-Funktion in eigenen Anwendungen dafür verwendet werden, um etwa eine SQL Verbindung dynamisch zu parametrisieren und damit eine höhere Berichtsflexibilität zu erreichen … mehr dazu gibt es womöglich in einem  weiteren Blogbeitrag. :)

Downloads

Die TABLE.Manager Testversion kann hier kostenlos heruntergeladen werden:
Zur TABLE.Manager Testversion!

Robert Lochner

Robert Lochner ist seit 2001 als Unternehmer tätig und Gründer der Linearis. Er hält Trainings und Seminare und unterstützt Fachabteilungen bei der Umsetzung ihrer Business Intelligence Anforderungen.

Weitere Beiträge

Kategorien: Analyse, Datawarehousing, Excel als BI Frontend, FLEX-Reporting!, SQL Server als DWH nutzen