In diesem Blogbeitrag möchte ich zeigen, wie mit einer Azure Logic App und der data1.io REST-API eine vollautomatisierte Schnittstelle für den Upload der Dimensionstabelle aus Excel in die data1.io Planungsapplikation umgesetzt werden kann. Die Azure Logic App überwacht dabei die in OneDrive gespeicherte Excel Quelldatei im Minutentakt und übernimmt die Daten, sobald diese geändert wurden.

Anstelle der Technologie Azure Logic App könnte auch die sehr ähnliche Technologie Power Automate verwendet werden.

Stufe 1: Ausgangssituation und Anforderungen

Die data1.io Planungsapplikation besteht im Frontend im Wesentlichen aus einem Excel Workbook (links) und dem Dimension Tree (rechts).

Der Dimension Tree ist im Backend über einen Dimension Table definiert, welcher - vorerst noch ohne Schnittstelle - durch einen manuellen Upload der geänderten Excel Quelldatei gewartet werden muß.

Die Definitionen für den Dimension Table werden in einer Excel Datei auf OneDrive gepflegt ...

... welche aktuell den gleichen Inhalt wie auch der Dimension Table in der Planungsapplikation selbst:

Wichtig für den Aufbau der Logic App ist die Definition eines Tabellenobjekts in Excel, da nur auf diesen Objekttyp referenziert werden kann.

Die Anforderung lautet nun, eine Schnittstelle aufzubauen, die vollautomatisch im Minutentakt die Datei auf Änderungen untersucht und die Datenübernahme in die data1.io Zieltabelle (siehe oben) im sogenannten "Update Modus" übernimmt. Dazu müssen bereits bestehende Datensätze mit dem gleichen Key vor der Übernahme gelöscht werden, neue Datensätze werden hinzufügt und Datensätze, die es in der Zieltabelle gibt aber nicht (mehr) in der Excel Datei, sollen erhalten bleiben.

Hier das Zielbild für die Prozesschritte der fertigen Logic App:

Stufe 2: Erstellen der Logic App im Azure Portal

Neue Logic Apps können im Azure Portal bspw. im Marketplace erstellt werden, hier am besten nach "Logic App" suchen:

Im ersten Schritt wird der Hosting Plan gewählt, wir verwenden den "Consumption" plan (= Abrechnung je Ausführung):

Im zweiten Schritt wird die Subscription, die Ressourcengruppe und - ganz wichtig - der Logic App Name definiert:

Stufe 3: Entwickeln der Anwendung im "Logic app designer"

Nach der Erstellung navigieren wir zur erstellten Logic App zum "Logic app designer" in der Rubrik "Development Tools":

A) Parameter für REST-API Verbindung erstellen

Mit dem Button Parameter erstellen wir zuerst 3 Parameter für die REST-API Verbindung:

• application
• odatauser
• odatapassword

Die Default values für die 3 Parameter erhalten wir aus den "API & Datenfeeds" Einstellungen unserer data1.io Planungsapplikation:

B) Trigger für OneDrive Folder erstellen

Jetzt beginnen wir mit dem Aufbau des eigentlichen Datenflusses und erstellen zuerst einen Trigger. Dazu im Suchfeld "when a file is modified" eintragen und den passenden Trigger für OneDrive for Business aus der Liste auswählen:

Hier wird der OneDrive Pfad eingetragen, dessen Files auf Veränderungen getrackt werden sollen sowie der zeitliche Intervall in dem der Ordner auf Änderungen der darin enthaltenen Files gecheckt wird:

C) Condition für konkretes File erstellen

Jetzt wird eine "Action" angelegt, die ausgeführt werden soll, sofern eines der Files im zuvor angegebenen Ordner verändert worden ist:

Als Action suchen wir nach dem Typ "condition" und wählen die entsprechende Action "Condition" aus der Gruppe "Control" aus:

In dieser Condition überprüfen wir, ob es sich um das für diesen Anwendungsfall relevante File "Upload Weekly Projects DIM001.xlsx" handelt. Dazu wird eine Prüfung auf den Dateinamen und die Funktion base64('Upload Weekly Projects DIM001.xlsx') eingetragen:

D) Action für List Rows erstellen

Für den Fall "True" wird jetzt eine weitere Action vom Typ "list rows" angelegt, um damit den Inhalt der Excel Datei auszulesen:

Dazu sind folgende Definitionen anzugeben:

E) Erstellen einer "For Each" Schleife zum Iterieren durch die Excel Tabelle

Jetzt legen wir eine "For Each" Schleife an ...

... um die Excel Tabelle zeilenweise abzuarbeiten:

Beachte bitte, daß dieser "For Each" Container die folgenden beiden "HTTP + Swagger" Objekte beinhalten wird und damit den Kontext für die Ausführung dieser beiden folgenden Steps bilden wird.

F) "HTTP + Swagger" DELETE Befehl zum Löschen der bereits bestehenden Datensätze

Jetzt wird innerhalb des "For Each" Containers eine "HTTP + Swagger" Action eingefügt ...

Zuerst wird der sog. "Swagger endpoint" eingefügt. Das ist eine URL der data1.io API, die sämtliche Befehle zum schreibenden Zugriff auf den data1.io Cloud Service (bzw. genau genommen auf die ODATA API) beinhaltet. In das dafür vorgesehene Feld wird diese URL eingefügt (und hier können die Befehle dieser API eingesehen werden):

https://data1-service.azurewebsites.net/swagger/docs/v1

Jetzt kann die "Swagger Operation" ausgewählt, also in diesem Fall der Delete Befehl auf eine konkrete Tabelle im Datenmodell unserer data1.io Planungsapplikation:

Abschließend für diese Action sind folgende Parameter zu konfigurieren - die Authentication muß über das Dropdown "Advanced parameters" hinzuzugefügt werden und mit den eingangs definierten Parameter belegt werden:

Mit dieser Konfiguration wird der "Update Modus" realisiert. Die Einträge der Spalte "Dimension Tree KEY" in der Excel Quelltabelle bestimmen, welche bereits bestehenden Keys in der Spalte "DIM001_KEY" in der Zieltabelle gelöscht werden. Falls es den Key in der Zieltabelle gibt, liefert der Step den Status "Is Successfull", falls nicht ist der Status "Has Failed" ... diese beiden Stati werden dann weiter unten noch behandelt.

G) "HTTP + Swagger" POST Befehl zum Anfügen der Datensätze aus der Excel Tabelle

Jetzt wird innerhalb des "For Each" Containers eine weitere "HTTP + Swagger" Action eingefügt ...

Es wird wieder die URL für den "Swagger Endpoint" konfiguriert (siehe oben) und die "Swagger Operation" zum Anfügen der Datensätze aus der Excel Tabelle ausgewählt:

Zentral ist jetzt die Auswahl der relevanten Felder aus der "Advanced Parameter" Liste ...

... um das Mapping der Excel Spaltennamen auf die Spalten der Zieltabelle vornehmen zu können:

Wir benötigen aber auch einige Formeln, um die folgenden Felder belegen zu können:

• DIM001_Created_UTC
  = utcNow()
  
• DIM001_Visible
  = if(equals(items('For_each')?['Is Visible'],''), null, bool(items('For_each')?['Is Visible']))
  
• DIM001_DIMTYPE_UID
  = if(equals(items('For_each')?['Element Type'],''), null, decimal(items('For_each')?['Element Type']))

Das Feld "DIM001_IsFromUpload" kann mit "Yes" hard-codiert werden und den Abschluß der Definitionen bildet wieder die Authentication (mittels der eingangs definierten Parameter):

Hier der Formeleditor anhand des Feldes "DIM001_IsVisible":

Übrigens, Datumsattribute müssten mit folgender Formel übertragen werden:

• DIM001_D001
  = if(empty(items('For_each')?['Dim01 D001 Projektstart']), null, addDays('1899-12-30',int(items('For_each')?['Dim01 D001 Projektstart']),'yyyy-MM-dd'))
  Hinweis: Excel speichert Datumsangaben intern als Zahlen, daher muss die Zahl in ein echtes Datum umgewandelt werden. Wenn das Feld leer ist, wird der Standardwert "null" gesetzt.

H) Behandlung des Status "Failed" aus dem DELETE Befehl

Damit der Ladeprozess nicht abbricht, falls es einen Key aus der Excel Tabelle in der Zieltabelle nicht gibt, sind die "Run after" Settings der zweiten "HTTP + Swagger" Action folgendermaßen zu konfigurieren:

I) Action für False Condition

Zum Abschluß brauchen wir noch eine "Terminate" Action für den Fall, daß unsere eingangs definierte "Condition" einen anderen Dateinamen liefert, als die Datei die wir importieren möchten und damit das Ergebnis "False" liefert:

Stufe 4: Ausführen und Monitoring der Anwendung

Die Logic App ist automatisch "Enabled" - zu erkennen am "Disabled" Button. :) Die Logic App prüft im Minutentakt den angegebenen OneDrive Folder auf Änderungen, diese Checks sind in der Trigger History protokolliert:

Werden jetzt in der Excel Quelldatei Änderungen vorgenommen ...

... lösen diese Änderungen in spätestens 1 Minute eine Datenübernahme aus, die in der Runs History protokolliert werden:

Die neuen Datensätze aus der Excel Datei sind auch tatsächlich in der Zieltabelle der data1.io Planungsapplikation angekommen:

Fazit und Zusammenfassung

Die Logic App überwacht die Excel-Quelldatei in OneDrive und integriert die Daten mit dem data1.io OData-Endpunkt der definierten Planungsapplikation im Minutentakt. Azure Logic Apps bieten damit eine leistungsstarke und flexible Möglichkeit, eine Automatisierung der Daten-Workflows zu erreichen.

Der hier abgebildete Fall ist einer von mehreren Anwendungsmöglichkeiten. Beim Upload von Faktendatensätzen ist zusätzlich der Inhalt der "SYSTEM_Delta" zu verarbeiten, um einen "Replace Modus" von ganzen Datenpaketen zu realisieren.

Kontaktiere uns gerne, sofern Du Fragen hast oder Unterstützung brauchst.

Quellen

https://azure.microsoft.com/de-de/products/logic-apps

https://data1.io/