Sachsen HÜIS Kettle-Projekte
Projekt: Excel-Upload zur Anpassung von Daten in festgelegten Spalten
Im Folgenden wird erläutert wie Excel-Dateien genutzt werden um Datenbanktabellen zu manipulieren. Hierfür wird zunächst das Anwendungsbeispiel Stellenbewirtschaftungsbericht (STBB), welcher für das Land Sachsen entwickelt wurde, vorgestellt. Im Weiteren werden an diesem Beispiel, mit Blick auf die verwandte Software, diverse technische Details erläutert.
Anwendungsbeispiel
Die Werte der Spalte 4 (Soll lt. HHPL) sollen bei Bedarf durch Upload einer Excel-Datei (Excel-Template) angepasst werden können.
Die Datenbasis für die Spalte Soll lt. HHPL liefert die Tabelle kenn_stelle_hhpl, deren Inhalt über den Bericht "Stellen lt. HH-Plan" abgefragt wird.
Die Maske bietet die Möglichkeit das sogenannte "Template für die manuelle Schnittstelle des STBB" als XSLX-Datei herunterzuladen.
Das Template enthält alle Zeilen des Stellenbewirtschaftungsberichtes (STBB), sowohl diejenigen, welche mit Daten gefüllt sind, als auch jene, welche keine Daten enthalten.
Im Template wird die Anzahl der Prorektoren auf 5 und die Anzahl der Leitenden Direktoren auf 11 erhöht. Das Template wird anschließend gespeichert.
Um das Template hochzuladen wird der Bericht "Grunddaten und Kennzahlen hinzufügen (Datei)" genutzt. Es wird der Job "Stellen lt. HH-Plan manuell einlesen" ausgewählt, das Template angehängt und der Bericht abgeschickt.
Anschließend erscheinen die Änderungen im Stellenbewirtschaftungsbericht.
Den Ladejob mit Kettle erstellen
Der Ladejob wird mit Pentaho Data Integration (Kettle) erstellt. Im ersten Schritt "tmp_kenn_stelle_hhpl" wird per SQL eine temporäre Tabelle erstellt. Es folgen zwei Transformationsschritte (excel_into_tmp_kenn_stelle_hhpl und get_kapitel.ktr). Diese führen beide jeweils eine Transformation aus. Abschließend wird ein SQL-Skript ausgeführt (Update kenn_stelle_hhpl). Folgend wird der Transformationsschritt excel_into_tmp_kenn_stelle_hhpl näher betrachtet, welcher den Inhalt des Templates in die zuvor angelegte temporäre Tabelle schreibt.
Die Transformation excel_into_tmp_kenn_stelle_hhpl besteht aus zwei Schritten. Dem "Microsoft Excel Input" und dem "Table output". Zunächst wird der "Microsoft Excel Input"-Schritt betrachtet.
Im Reiter "Files" wird der Pfad zur Exceldatei angegeben. In diesem Falle handelt es sich um Die Variable ${PATH_TO_UPLOADFILE}, welche beim Abschicken der Maske "Grunddaten und Kennzahlen hinzufügen (Datei)" gefüllt wird.
Um den Ladejob zum Test lokal ausführen zu können, wird die Variable mit einem Default gefüllt. Das geschieht unter "Edit-->Settings..." im Reiter "Parameters".
Im Reiter Sheets des "Microsoft Excel Input"-Schrittes wird der Name des Tabellenblattes der Excel-Datei angegeben, aus welchem gelesen wird. Dazu gehört eine Angabe der Zeile und Spalten, ab welcher Daten gelesen werden. Es wird bei 0 begonnen, das bedeutet in diesem Beispiel wird ab Zeile 6 und Spalte B ausgelesen.
Da im Beispieltemplate in Zeile 6 die Kopfzeile der Tabelle beginnt wird Header angeklickt, um zu signalisieren, dass die eingelesene Tabelle eine Kopfzeile enthält.
Im Reiter Fields werden die einzulesenden Spalten definiert. Wichtig ist, das bei führende 0-en (Nullen) auch bei Strings entfernt werden. Dies sollte bei Feldern, welche beispielsweise Schlüssel enthalten, unbedingt vermieden werden. Dazu wird unter Format für das jeweilige Feld eine Raute # eingetragen.
Im zweiten Schritt der Transformation wird der zuvor eingelesene Inhalt in eine Datenbanktabelle geschrieben.
Wichtig ist die Connection eduetl zu nennen, damit der Ladejob auf den BI-Systemen läuft. Die Zieltabelle ist tmp_kenn_stelle_hhpl. Zudem kann beispielsweise ein Haken bei "Truncate Table" gesetzt werden, falls bei jedem Laden des Templates, die Zieltabell zunächst gelöscht werden soll. Um die Zielfelder der Zieltabelle näher zu definieren wird "Specify database fields" aktiviert und in den Reiter "Database fields" gewechselt.
Dort werden die Tabellenfelder ausgewählt und die dazugehörigen "Stream fields", aus welchen die Daten in die Tabellenfelder der Zieltabelle geschrieben werden. Bei den "Stream fields" handelt es sich um jene Felder, welche im "Microsoft Excel Input"-Schritt im Reiter fields benannt wurden.
Den Ladejob in das BI-System integrieren
Bevor der Job im System läuft, wird die Connection gelöscht. Hierzu ist der gesamte Tag sowohl im Job, als auch in den Transformationen zu löschen.
Die Connection-Information in den einzelnen Schritten bleibt jedoch enthalten.
Um den Job im System bekannt zu machen wird ein Eintrag in der Tabelle sx_jobs angelegt. Sie erreichen die Bearbeitung über das Menü Administration -> Tabelle suchen.
Im Listenformular können Sie neue Jobs anlegen, oder vorhandene ändern.
Hier unser Beispiel:
Die Felder haben folgende Bedeutung:
Feld Beschreibung tid tid uniquename Unique Name caption Bezeichnung filepath Pfad zur Datei sachgebiete_id Sachgebiet kenn_profil Optional: Kenn-Profil hs_nr Optional: Hochschulnummer modus_supported Modus unterstützt? params optionale Parameter check_sql optionaler Prüf-SQL Für das Beispiel sieht der Datensatz wie folgt aus:
tid uniquename caption filepath ... check_sql 1450 nhs_stbb_hhpl_manuell Stellen lt. HH-Plan manuell einlesen kenn/etl/nhs_stbb_manuell/stbb_hhpl_manual_upload.kjb ... select count(*) from kenn_stelle_hhpl Das Ergebnis des Prüfprotokolls erscheint nach Ausführen des Jobs im Ladeprotokoll. Hier wird die Anzahl Datensätze in der Zieltabelle gezählt.
Die Masken, welche Ladejobs ausführen benötigen zwingend das Feld dokettlejob. Anhand dieses Feldes wird dem System mitgeteilt, dass ein Kettle-Job auszuführen ist. Das Feld darf versteckt werden.
Um die auswählbaren Kettle-Jobs zu definieren wird auf die Tabelle sx_jobs zugegriffen.
Projekt: Execute-Mask und Mailversand
Die folgende Beschreibung bedient sich am Beispiel des Hochschulübergreifenden Systems (HÜ-IS) in Sachsen. Im HÜ-IS wird über ein Formular der Mailversand verwaltet. Im Hintergrund arbeitet ein Kettle-Job, welcher den Mailversand steuert.
Mailprojekte verwalten
Über ein Formular im HÜ-IS werden diverse Mailprojekte verwaltet. Für jedes Mailprojekt wird eine eigene Mail versandt. Jedes Mailprojekt beinhaltet quasi einen Bericht. Es geht darum Exporte der Berichte an ausgewählte Mailadressen zu senden. Über das Formular wird bspw. der Bezugszeitraum, sowie die Mailadressen angegeben und ob das Mailprojekt aktiv oder inaktiv ist, das heißt, ob die Mails versandt oder nicht versandt werden.
Über das Formular wird die Tabelle 'kennx_bestand_nachricht' befüllt.
Der Kettle-Job
Der Kettle-Job wertet die über das Formular getätigten Eingaben aus, führt die entsprechenden Berichte aus und erstellt Excelexporte. Zudem werden über das HÜ-IS abrufbare Downloads versandt.
Das Skript mailversand_berichte_kettle_fuellen.sql stellt die für den Mailversand benötigten Informationen zusammen. Dazu gehört beispielsweise die Information, ob eine Maske ausgeführt wird oder es sich um einen Download handelt. Falls eine Maske ausgeführt wird, werden die Parameter zusammengestellt.
Initial wird ein temporärer Ordner erstellt. Zuvor wird dieser Ordner wieder gelöscht.
Innerhalb dieses Ordners wird für jedes aktive Mailprojekt ein weiterer temporärer Ordner erstellt. In den Ordnern der jeweiligen Mailprojekte werden die Excelexporte und Downloads gesammelt. Es werden alle dort abgelegten Dateien per Mail versandt. Die Erstellung der temporären Ordner pro Mailprojekt, sowie die Ausführung der Masken und die Kopie der Downloads in die temporären Ordner erfolgt über zwei Shell-Skripte.
Vor Ausührung des Shell Skriptes kennx_execute_mask.x wird in der Transformation get_hochschulen mit SQL eine Liste der Hochschulen erstellt, welche Daten in das HÜ-IS geliefert haben. Auf Basis dieser Liste werden die Masken ausgeführt. Vor dem kennx_copy_downloads.x Skript wird im Schritt 'downloads_rows > 0?' gecheckt, ob zusätzlich Downloads zu verschicken sind und gegebenenfalls per SQL eine Liste der Hochschulen erstellt. Falls keine Downloads zu verschicken sind, wirft der Schritt false aus und springt direkt zur Transformation get_mails_to_send.ktr.
Die Transformation get_mails_to_send.ktr erstellt die Betreffzeilen und Mailtexte und verschickt anschließend die Mails.
