Kettle

Kettle-Grundlagen

Zielsetzung

Das Anwendungsspektrum vom OpenSource-Werkzeug zum Datenmanagement Kettle / Pentaho Data Integration reicht vom Kopieren einzelner Daten zwischen zwei Quellen bis zum professionellen "Data Warehousing". Für diesen Zweck bietet das Werkzeug eine mächtige Funktionalität und graphische Oberfläche. Die Nutzung des Werkzeugs wird unten anhand von praktischen Beispielen aus der Hochschulverwaltung vorgestellt.

Geschichte von Kettle

Zeitpunkt	Was passierte...
Sommer 2001	Der Belgier Matt Casters gründet eigenes Unternehmen als BI-Consultant
Ab 2003	Start der Arbeiten an Kettle. Ursprünglich war geplant, Kettle als KDE-Anwendung zu implementieren, daher der Name KDE Extraction, Transportation, Transformation and Loading Environment.
2004	Kettle Version 1.2 mit SWT statt AWT. Das belgische Verkehrszentrum setzt Kettle ein
2005	Kettle 2.0 wird veröffentlicht, mit Plugin-Architektur und SAP-Plugin. Matt Casters stellt Kettle unter OpenSource-Lizenz. In der ersten Woche hat Kettle 35.000 Downloads
2006	Pentaho wechselt von Octopus zu Kettle, Matt Casters wird angestellt. Kettle wird zu PDI (Pentaho Data Integration)
2010	Kettle / PDI 4.0 Release
2015	Pentaho wurde von Hitachi Data Systems aufgekauft.
2022	Der Kettle Fork "hop" wird ein Top-Level Produkt der Apache Foundation.

Wir sehen dass die Software eine "bewegte" Geschichte hat. Durch die ganzen Namensänderungen bleiben wir erstmal beim Namen "kettle". Weitere Details siehe Wikipedia-Eintrag.

Alternativen

Eine Übersicht finden Sie auf Wikipedia:

Kommerzielle Produkte im DWH-Bereich:

• IBM InfoSphere
• Informatica

OSS-Tools

• Talend
• PDI / Kettle
• Octopus

Installation und Einrichtung

Voraussetzungen

• Kettle läuft unter Windows und Linux bzw. anderen Systemen mit Java Runtime
• Derzeit wird Java 1.8 vorausgesetzt, und zwar nur genau diese Version. Ältere Java Versionen und Java 11 oder höher werden derzeit nicht unterstützt.

Entgegen der Dokumentation funktioniert auch ein Open Source Java, z.B. openjdk, Sie müssen nicht Oracle Java nehmen.

Download und Installation

• Es gibt einen Community Bereich bei Hitachi, wo Sie die Software nach Registrierung herunterladen können: https://community.hitachivantara.com/home
• Einen Download ohne Registrierung erhalten Sie auch
  • auf Sourceforge: https://sourceforge.net/projects/pentaho/
  • auf github: https://github.com/pentaho/pentaho-kettle (nur Quellcode)

Start nach der Installation

Kettle beinhaltet drei "Anwendungen"

• Spoon
• Kitchen
• Pan

Am besten beginnen Sie mit der graphischen Entwicklungsumgebung "Spoon".

Allgemeines zu Spoon

Das Kommandozeilen-Script "spoon.sh" bzw. "spoon.bat" liegt im obersten Verzeichnis der Auslieferung. Wenn Java 1.8 installiert ist, reicht es die Datei mit Doppelklick zu starten. Aus der Kommandozeile starten Sie mit

spoon.bat /norep 

oder

spoon.sh /norep

"/norep" setzt den Parameter, dass Kettle nicht nach einen Repository Server sucht.

Spoon Besonderheiten für Linux

Unter Linux sucht Spoon zunächst nach einer passenden Java-Runtime: entweder Sie setzen die Variable JAVA_HOME / JRE_HOME, oder Sie installieren oberhalb von spoon.sh im Verzeichnis java eine passende runtime. Wenn Sie nur eine spezielle Runtime verwenden wollen, setzen Sie z.B.

PENTAHO_JAVA_HOME=/usr/lib/java
export PENTAHO_JAVA_HOME

Hinweis: Spoon stürzt unter manchen Linux-Versionen ab sobald man auf "Preview" klickt oder eine Transformation starten will.

Ursache

vermutlich ist die SWT Bibliothek in Spoon nicht kompatibel mit dem Betriebssystem.

Lösung

Besorgen Sie sich eine swt-Datei z.B. aus dem aktuellen Eclipse-Paket. Es liegt im Ordner "plugins", ermittelbar dort mit

ls org.eclipse.swt*jar

Diese Dateien kopiert man ins Spoon Verzeichnis nach

libswt/linux

(jeweils x86 und x86_64), die alten Dateien benennt man um nach swt.old. Danach startet man Spoon neu.

Spoon unter Windows

Unter Windows installieren Sie zunächst Java, und setzen dann in der Systemsteuerung die Umgebungsvariable JAVA_HOME auf den Pfad, wo Java installiert ist, z.B.

C:\Program Files\Java\jre1.8.0_45

Probieren Sie den Start mit Doppelklick auf die Datei spoon.bat. Bei Startproblemen unter Windows öffnen Sie eine DOS-Box ("Eingabeaufforderung") und gehen mit cd in das Verzeichnis, wo Kettle installiert ist (z.B. C:\Users\superx\pdi-ce-7.0.0.0-25\data-integration ). Führen Sie in der DOS Box das Kommando aus:

set JAVA_HOME=C:\Program Files\Java\jre1.8.0_45

Editieren Sie dann die Datei spoon.bat und ersetzen Sie den Aufruf "javaw" durch "java". Achtung: das geht nicht mit Windows Notepad, weil es die Unix Zeilenumbrüche nicht versteht, Sie müssen einen alternativen Editor. z. B. Windows WordPad nehmen und starten Sie das Script

spoondebug.bat

Beantworten Sie alle Fragen mit "Y", am Ende wird eine Datei

SpoonDebug.txt

geschrieben, der Sie diagnostische Meldungen entnehmen können. Wenn z.B. das RAM nicht ausreicht, müssen Sie die Datei spoon.bat editieren, und den Passus "-Xmx2048m" ersetzen z.B. durch "-Xmx1024m". Weitere Hilfen bei Startproblemen siehe den FAQ.

Spoon Einstellungen

Als erstes sollten Sie die Sprachumgebung von Spoon einstellen: Im Menü Tools -> Options wählen Sie im Reiter "UI Settings" die deutsche Umgebung:

Achten Sie darauf, daß Sie bei "Alternative Language" English angeben, damit Menüs, die noch nicht übersetzt sind, in Englisch angezeigt werden.

Dateiformate

Kettle unterscheidet Jobs und Transformationen. Beide werden lokal im XML Format gespeichert, die Endung ist jeweils "*.kjb" fürs Jobs und ".ktr" für Transformationen.

Datenbankverbindungen in Kettle-Dateien

Nach der Installation von Spoon ist folgendes zu beachten: Beim Bearbeiten von Kettle Dateien mit Spoon wird die jeweilige dbconnection der lokalen "eduetl"-Datenbank in den KTR/KJB Dateien gespeichert. Vor dem Commit ins GIT muss diese entfern werden. Dafür gibt es ein Groovy-Script im Kernmodul in superx/scripts/groovy/copy_kettlejob.groovy. Dieses wiederum benötigte groovy im PATH, daher muss man

1. groovy installieren, z.B. in /home/superx/tools/groovy/groovy-2.4.10
2. groovy in den PATH mit aufnehmen:

PATH=$PATH:/home/superx/tools/groovy/groovy-2.4.10/bin
export PATH

Dann legt man sich einen lokalen "Arbeitsorder" an, der nicht im git liegt, und erzeugt ein Unterverzeichnis für die temp. Dateien. Hier das Beispiel eines Jobs im SVA Modul mit dem Unterverzeichnis sap_hr_sva

Das Script wird dann so aufgerufen :

groovy ~/git/superx/scripts/groovy/copy_kettlejob.groovy --strip-connections $SVA_PFAD/etl/sap_hr/sap_hr_sva.kjb sap_hr_sva

Die kjb-/ktr Dateien werden aus dem git kopiert ins Unterverzeichnis sap_hr_sva, und dort werden die Connections entfernt. Wenn man die Entwicklung beendet hat, kann man diese Dateien ins richtige git Verzeichnis kopieren und committen:

cp -a sap_hr_sva/* $SVA_PFAD/etl/sap_hr

Grundsätzliches zum Datenflow

Nach einer SQL-Aktion mit dem Löschen von Daten muss man einen Warten Step einbauen.Ab einem bestimmten Step kann man in mehrere folgende Steps verzweigen, denen allen der gesamt Flow zur Verfügung steht. 

(Methode "Copy" auswählen, nicht "Distribute").

Komplexes Beispiel 

siehe unter Aus Excel in Datenbank schreiben / Werte nachtragen (Beispiel erst Kosten- dann Beschäftigungsstellen)

Beispiel db/module/sva/etl/sap_hr/sap_load_stamm_beschstellen.ktr Step Insert sva_inst_neu

Informationen zu einzelnen Steps

Update Step

siehe "Aus Exceldatei in Datenbank schreiben / Aus mehreren Sheets

Filter Step (ähnlich SQL where)

siehe unter "Aus Exceldatei in Datenbank schreiben / Erst bestimme Zeilen rausfiltern und dann Update

Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_eintrittssdatum.ktr

Man kann auch mehrere Bedingungen angeben, dazu klickt man auf das kleine rot umrandete Plus Zeichen.

Man sollte darauf achten, dass bei Verknüpfung zu folgenden Schritten nicht "Main Ouput", sondern "Result is TRUE" ausgewählt wird, dann wird im Screenshot bei "send true to data step" der entsprechende Schritt eingetragen.

Man erkennt auch daran, dass grünes Häkchen an Verbindungslinie.

Wenn ein Filter Rows mit True die Ausgabe an mehrere Schritte liefern sollen, geht das nicht, man kann immer nur an einen Schritt liefern.
Andere Schritte bekommen nicht das "True" Häkchen und machen einfach nichts!

Daher muss man einen "Dummy-Schritt" einfügen:

sap_load_stellen

Group by Step

Max/Min Ermitteln - siehe "Aus Exceldatei in Datenbank schreiben / Max(Spalte) ermitteln und nachtragen (Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_austrittsdatum.ktr)

Kann noch mehr auch ersten/letzen Wert ermitteln, interessant für Gültigkeiten

  siehe Bei mehreren Datenzeilen Neuste raussuchen (Gültigkeit) ( Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_wohnort.ktr)

Standardmäßig müssen Datenzeilen vorher sortiert sein, damit es richtig klappt, man kann "Fehlfunktion" aber nutzen, um z.B. "Zeitscheiben" zu ermitteln, s.u.

Select Values (Felder umbenennen/Datentyp ändern/ Feld kopieren)

Hiermit kann man Feldnamen umbennen oder auch Datentypen ändern im Reiter "Metadata"

siehe auch unter "Aus Exceldatei in Datenbank schreiben / Aus String Jahr "2000" ein Datum machen (1.1.2000)

Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_habil.ktr

Felder, die im weiteren benötigt werden, müssen einfach aufgeführt werden. z.B: Einfach "Planstelle" bei "select&alter" unter Fieldname - andere Spalten leer.
Es werden nur die aufgeführten Felder weiter im Stream übergeben (vergl. sva/etl/sap_hr/sap_load_stellen.ktr)

Auch Felder können kopiert werden, dazu zweimal aufführen, als Feld selbst und mit neuem Namen,
Beispiel TarifGruppe (TrfGr) soll standardmäßig auch als Name genommen werden (mit einem generateRows Step aber eigener Name für Schlüssel _unb)

Vergl. sap_load_stamm_bvl

User Defined Java Expression

Siehe Beispiel unter "Aus Excel in Datenbank schreiben" / Den kleineren/größeren Wert aus zwei Spalten ermitteln

weiterhin Beispiel unter "Aus Excel in Datenbank schreiben / Feld wird zu Decimalzahl 1020,0 transformiert soll aber String sein

Get Value from Sequence

Kann man z.B. für erzeugen einer künstlichen ID gebrauchen, siehe unter Aus Excel in Datenbank schreiben / Eine Künstliche ID erzeugen

db/module/sva/etl/sap_hr/sap_load_stamm_laufbahn.ktr.

Um die erzeugte ID aus der Datenbank auszulesen nutzt man Database Lookup

Database Lookup

Kann z..B: verwendet werden, um eine ID aus der Datenbank zu holen.

Microsoft Excel Input

Der "Microsoft Excel Input" Step kann Excel Dateien lesen und weiterverarbeiten.

Microsoft Excel Input Reiter Files

Wählen Sie beim "Spread Sheet type (engine)" am besten "Excel 2007 XLSX (Apache POI)". Grund: diese Importfunktion unterstützt sowohl "*.xls" als auch "*.xlsx".

Wenn Sie z.B. alle Excel-Dateien in einem Verzeichnis einlesen wollen, geben Sie die Wildcards an:

.*\.xls
.*\.xlsx

Damit lädt der "Microsoft Excel Input" Step alle Dateien, die er mit den Wildcards findet.

Andere Beispiele für Wildcards:

(?i)daten.+\.xls

findet alle Dateien, die mit "Daten" bzw. "daten" beginnen und mit ".xls" enden.

Microsoft Excel Input Reiter Sheets

Dann wählen Sie das jew. Sheet aus, bzw. mehrere. Bei "Start row" und "Start column" beachten Sie bitte, dass Kettle bei 0 beginnt, nicht bei 1.

Microsoft Excel Input Reiter Content

Content / "Stop on empty row" kann ganz nütlich sein, hatten schon den Fall, dass plötzlich in Zeile 106.234 noch ein verwaister Wert stand, kostet viel Performance/Arbeitsspeicher

Microsoft Excel Input Reiter Fields

Bei Datentyp ggfs. TrimType "both" und für Ganzzahlen die zu Strings werden sollen Format # hilfreich

Ganz wichtige Erfahrungen:

Wenn Spaltenreihenfolge in ExcelDatei nicht mehr stimmt, merkt das Kettle anscheinend nicht an Bezeichnung in Header.
Arbeitet wohl "stupide" nach Reihenfolge, d.h. Spalte 1 soll im Kettle-Transformation den Namen "gebaeudenr" erhalten, wenn in der Exceldatei plötzlich "geschossnr" als Erstes kommt, merkt das System das nicht, spielt einfach falsch ein!!

Man kann bei Content Startspalte angeben, wenn man die Anfangsspalten nicht braucht. Spalte überspringen/aus LIste rauslöschen, funktionierte nicht, wenn man spätere Spalten noch brauchte, nur wenn man z.B. ab Spalte 6 gar nicht mehr braucht.

Wenn ein ClassCast Fehler kommt, muss man das logging auf ganz detailliert stellen, dann wird die Zeilennummer angezeigt.
-> Datumsfelder dürfen anscheinend nicht vor 1.1.1970 sein,  Datum 1.1.1700 kam ClassCastException, 1.1.1999 nicht

Table Input

siehe unter Aus Excel in Datenbank schreiben / Eine Künstliche ID erzeugen

db/module/sva/etl/sap_hr/sap_load_stamm_laufbahn.kjb

Insert / Update Step

Kann verwendet werden, wenn man für bestehende Datensätze einen Update machen möchte und für noch nicht vorhandene Einträge einen Insert.
Aber auch, wenn man gar keine Updates machen will, sondern nur Inserts wenn noch kein Eintrag vorhanden ist.

siehe unter Aus Excel in Datenbank schreiben / Werte nachtragen (Beispiel erst Kosten- dann Beschäftigungsstellen)

Beispiel db/module/sva/etl/sap_hr/sap_load_stamm_beschstellen.ktr Step Insert sva_inst_neu

Stream Lookup

Wenn man Daten in Datenbanktabelle hat und ergänzen will um Input aus Exceldatei.

vergl. Stream Lookup
Problem kann keine komplexen where Bedingen wie < oder > machen

Database Join / Set Variable (eine Variable aus Datenbank lesen)

Database Join - Dieser Step kann benutzt werden, um eine Variable aus der Datenbank zu lesen und dem Flow hinzuzufügen.

Beispiel: select nvl(max(apnr::integer)+1,1) as laufbahn_max_apnr  from sva_cifx where key=607;

Number of rows to return: 1

Dann "Set Variable Step" (Fieldname: laufbahn_max_apnr, Variable name: LAUFBAHN_MAX_APNR.

Vergl. sap_load_stamm_laufbahn.ktr

Da jedoch die einzelnen Transformationsschritte evtl. parallel laufen, muss man einen übergeordneten Job erstellen, der in der ersten Transformation zunächst die Variable liest und dann in der/den nächsten die Variable ausliest.

Vergl. sap_load_stamm_laufbahn.kjb und unten "eine künstliche ID erzeugen"

Get Variable

s.auch vorheriges Kapitel
Da jedoch die einzelnen Transformationsschritte evtl. parallel laufen, muss man einen übergeordneten Job erstellen, der in der ersten Transformation zunächst die Variable liest und dann in der/den nächsten die Variable ausliest.

Also in 1. Transformation "Set Variable Step"
Dann in der 2. Transformation z.B. Lesen aus Excel, "Get Variable" und wenn man es zum Schreiben braucht, "Join rows (cartesian product)".

Beispiel

Beispiel: sos/etl/gewichtung_fach_abschluss_fak/gewichtung_fach_abschluss_fak.kjb

Calculator (Werte berechnen, z.B. Multiplizieren)

Calculator Step, Funktion A*B, Field A "VAE" vorher Konstante c100 mit Wert 100 hinzugefügt als Wert B, um statt Prozentwert 0,27 eine 27 zu erhalten.
Beispiel sap_load_pfi

Text FIle Output (CSV-Dateien erzeugen)

relativ selbsterklärend.

Bei Zahlwerten kann Format 0.00 wichtig sein, damit amerikanisches und nicht deutsches Trennzeichen genutzt wird.
Bei Zahlen die Strings sind ggfs. auch Format # und Trim Type Both

Value Mapper (Werte transformieren)

Beispiel String "wiss. Dienst" -> 1 sonst 0 in sap_load_wiss_adt.ktr

String Cut

Hiermit kann man einen Ausschnitt von Strings ermitteln, Beispiel ersten zwei Stellen https://wiki.pentaho.com/display/EAI/Strings+cut

Execute SQL Script

SQL-Befehle ausführen.Erfahrung: direkt in Hauptjob sap_hr_sva.ktr nicht durchgelaufen (System hängt).

In einzelnen Job gepackt und Option "execute for each row" aktiviert, dann gings

sap_init_db.ktr

Generate Rows (Werte manuell erzeugen)

feste Schlüssel z.B. _unb für unbekannt manuell erzeugenBeispiel sap_load_stamm_bvl

Aus Exceldatei in Datenbank schreiben

Aus einem Sheet

Um Daten zu löschen legen man einen Step "Execute SQL script" an. Beispielhafter Inhalt delete from sva_pbl_neu;

Als Nächstes "Block until steps finishes"

- Dann "Microsoft Excel Input" -wichtig, Datentypen kontrollieren, S. unter Informationen zu einzelnen Steps / Microsoft Excel Input

Weiterhin  war die Erfahrung, dass man bei Fields "Get Fields from Header row" Alle Felder drin lassen sollte, nicht nur die tatsächlich benötigten.
Andernfalls schien Kettle durcheinander zu kommen und meldete z.B. für Tarifart (String) falscher Datentyp (Date), weil er die falsche Spalte gelesen hat.

- Dann Table Output

Beispiel db/module/sva/etl/sap_hr/sap_load_pgd.ktr

Aus mehreren Sheets

Um aus mehreren Sheets zu lesen, muss man pro Sheet eine Transformation anlegen, da sonst im Flow die Felder aus dem ersten Sheet anscheinend nicht vom zweiten Sheet überschrieben werden.

Beispiel db/module/sva/etl/sap_hr/sap_load_pbl.ktr und sap_load_pbl2.ktr.

Die Informationen aus dem zweiten Sheet werden per "Update Step" nachgetragen.

Wenn in dem zweiten Excelsheet nicht definitiv alle primary keys enthalten sind, die im ersten sind, muss man "Skip Lookup" aktivieren, sonst kommt eine Fehlermeldung, dass z.B. für eine Personalnummer keine Amts-/Dienstbzeichnung gefunden wurden.

Entry to update with following key could not be found: 47

Max(Spalte) ermitteln und nachtragen

Im Beispiel soll das austrittsdatum aus einem Excelsheet nachgetragen werden, dazu muss man für jede Personalnummer das max(Ende) ermitteln.

Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_austrittsdatum.ktr

Dies geht in dem man zunächst die Exceldatei einliest.

Dann ist für das "Group by" zunächst ein "Sort Rows" nötig.

Anschließend kommt ein "Group By"-Step

Und zum Schluss ein "Update"-Step - hier wieder "Skip lookup" aktivieren, siehe unter Aus Exceldatei in Datenbank schreiben / Aus mehreren Sheets

Calculator (Werte berechnen, z.B. Multiplizieren)

Calculator Step, Funktion A*B, Field A "VAE" vorher Konstante c100 mit Wert 100 hinzugefügt als Wert B, um statt Prozentwert 0,27 eine 27 zu erhalten.
Beispiel sap_load_pfi

Erst bestimme Zeilen rausfiltern und dann Update

Im Beispiel soll das Eintrittsdatum aus einem Excelsheet ermittelt werden, dazu muss man für jede Personalnummer nur die Zeilen mit DaArt=01 lesen und dann das min(Datum) bestimmen.

Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_eintrittssdatum.ktr

Dies geht in dem man zunächst die Exceldatei einliest.

Dann folgt ein "Filter Rows" auf DatAr=01

Dann ist für das "Group by" zunächst ein "Sort Rows" nötig.

Anschließend kommt ein "Group By"-Step

Und zum Schluss ein "Update"-Step - hier wieder "Skip lookup" aktivieren, siehe unter Aus Exceldatei in Datenbank schreiben / Aus mehreren Sheets

Bei mehreren Datenzeilen Neuste raussuchen (Gültigkeit)

Im Beispiel soll zu Personalgrunddaten die aktuelleste Adresse PLZ etc ermittelt werden. Dazu gibt es ein Sheet, das pro Personalnummer mehrere Einträge mit Gültigkeiten (Beginn/Ende) geben kann.

Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_wohnort.ktr

Vorgehen:
Excelsheet auslesen

Im Beispiel zusätzlich Filter auf Art=1 (ständiger Wohnsitz, nicht Zweitwohnsitz)

Sort Rows nach PersNr und Ende

Group By - Step mit Type "Last Value"

Dann wieder "Update Step"

Aus String Jahr "2000" ein Datum machen (1.1.2000)

Als erstes muss man einen FIlter machen mit der Einstellung nur Datensätze where is not null.

Dann "Add Constants"  erster_erster 01/01/

Dann einen Concat step, bei der man die Felder erster_erster und das Zielfeld zusammenfügt.

Dann einen "Select Values" hier wählt man Felder aus, die im weiteren Flow berücksichtigt werden müssen und für das umzuwandelnde Feld, beim Reiter Metadata den Datentyp Date mit Format dd/MM/yyyy.

Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_habil.ktr

Den kleineren/größeren Wert aus zwei Spalten ermitteln

Im Beispiel gibt es zwei Felder "Auf Zeit" und "Auf Lebenszeit", das Feld mit dem kleineren Wert in jeder Zeile soll genommen werden.

Dazu nutzt man einen "User Defined Java Expression" Step.

Damit Java die Variablen verarbeiten kann, zunächst einen "Select Values" Step und ändert die Feldnamen auf Namen ohne Leerzeichen, PersNr muss auch übergeben werden, da für Update benötigt.

Als Nächstes ein "User Defined Java Expression" mit der JavaExpression

  (lebenszeit=null&&auf_zeit=null)?null:(lebenszeit=null&&auf_zeit!=null)?auf_zeit:(lebenszeit!=null&&auf_zeit=null)?lebenszeit:new Long(Math.min(lebenszeit,auf_zeit))

Also wenn beide Werte null sind, dann null, wenn einer der beiden null ist, dann den anderen und wenn beide gefüllt sind, dann das Minimum.

Wichtig ist, noch dass man bei dem langen Feld für Java Expression nicht übersieht, einen Datentyp auszuwählen.

Anschließend kann wie gewohnt, ein Update Step genutzt werden.

Beispiel db/module/sva/etl/sap_hr/sap_load_pgd_habil.ktr

Feld wird zu Decimalzahl 1020,0 transformiert soll aber String sein

Beim Einlesen von Kostenstellen kam es zu Problem, dass dass Feld zu einer Decimalzahl wie 1020,0 transformiert wurde, umstellen auf Datentyp Integer beim Einlesen oder entsprechender "Select Values" Step mit Meta-Datenänderung auf String brachte nichts.

1. Lösung: beim "Microsoft Excel Input Step" Reiter "Fields" geben Sie bei "Type" den Wert "String" und bei "Pattern" den Wert "#" an. Damit werden auch führende 0en beibehalten.

2. Lösung: ein "User Defined Java Step".

Die Java Expression Kostenst=null?null:Kostenst.trim().replaceAll(",0","")  entfernte auch Leerzeichen am Anfang.

Beispiel db/module/sva/etl/sap_hr/sap_load_pfi.ktr

Eine Künstliche ID erzeugen

Für das Excelfeld "Laufbahngruppe" gibt es nur Bezeichnungstext, keinen Schlüssel.

Man kann so vorgehen:
FIlter Values  - keine Nulls

Sort Values - by  Laufbahngruppe

Unique Rows - Laufbahngruppe

Get Value from Sequence - Laufbahngruppe

In Datenbank schreiben.

Beispiel db/module/sva/etl/sap_hr/sap_load_stamm_laufbahn.ktr

Um diese ID dann bei der Verarbeitung der Rohdaten wieder auszulesen nutzt man "Database Lookup"

Beispiel db/module/sva/etl/sap_hr/sap_load_pbl.ktr.

Das ganze ist aber noch komplexer.
Nach dem ersten Durchlauf bzw. einiger Zeit existieren z.B. in sva_cifx (key=607) die Schlüssel 1-10 für verschd. Laufbahngruppen. Es könnte jedoch sein, dass irgendwann nicht mehr alle Daten übertragen werden, sondern z.B. nach Archivierung nur noch Personal ab 2019 oder so. In den Daten für 2019 ist vielleicht Laufbahnschlüssel 5 nicht mehr drin.

Darum sicherheitshalber folgendes Vorgehen.
Ein Job sap_load_stamm_laufbahn.kjb - erste Transformation liest max(apnr) aus der cifx und speichert in eine Variable (s. auch oben einzelne Schritte Table Input  / Set Variable).
Zweite Transformation sap_load_stamm_laufbahn2.ktr liest die Laufbahngruppen aus Exceldatei , dann wird zusätzlich apnr_artifical hinzugefügt.

"Get Value from Sequence" - start at Value ${LAUFBAHN_MAX_APNR}

mit "Database Value Lookup" wird geprüft, ob es schon einen Eintrag in sva_cifx gibt, (wichtig: Do not pass row if lookup fails - darf nicht aktiviert sein).

Dann kann mit "Filter Rows" geprüft werden, ob schon eine Apnr in sva_cifx gefunden wurde, falls nein, neue mit der höheren Wert apnr_artifical in sva_cifx_neu einfügen,

falls ja trotzdem noch mal in sva_cifx_neu einspielen, damit die Bewegungsdaten einen vollständigen Lookup Database Value auf sva_cifx_neu machen können.

Hier ist auch Beispiel für Verzweigung True/False

sap_load_stamm_laufbahn2.ktr

Werte nachtragen (Beispiel erst Kosten- dann Beschäftigungsstellen)

Aufgabe: Zunächst sollen aus einem Sheet alle Kostenstellen eingetragen werden, dann bei den Beschäftigungsstellen diejenigen nachgetragen werden, die noch nicht als Kostenstellen eingetragen wurden.

Dazu kann man einen Insert / Update Step verwenden.

Es sollen keine Updates gemacht werden, die Maske ist zunächst etwas verwirrend. Man wählt "Don't perform any updates" aus.
Bei "Keys to look up" die key_apnr und das Feld im Stream (hier kostenstellenStr).

Dann steht da zwar "Update Fields", aber diese werden eingefügt, wenn noch kein Eintrag für die apnr in der Tabelle sva_inst_neu gefunden wurde.

Sicherheitshalber auch UPdate=N ausgewählt.

Beispiel db/module/sva/etl/sap_hr/sap_load_stamm_beschstellen.ktr Step Insert sva_inst_neu

"Zeitscheiben" ermitteln

Problem in sva_pbv_neu aus IT0016 gibt es eine Zeitscheibe

IT 0016 Personalnr 6333    01.08.2015-    31.07.2017

in IT0001 wird Mitarbeiterkreis und Dienstart nachgeschaut, dort gibt es aber zwei

6333     01.08.2015     31.10.2015   60
6333     01.11.2015     31.07.2017  60
--> keine Eindeutige zuordnung möglich mit einfachen it0016.Beginn<=it0001.Beginn and it0016.Ende>=it0001.Ende möglich

theoretisch wäre auch noch weitere Scheiben mit zwischenzeitlichen Makrs-Wechsel möglich
6333     1.8.2017         31.12.2018  50
6333     1.1.2019         31.12.2019 60

Lösung, Fehlfunktion von "Group by" nutzen, wenn man nicht ordentlich sortiert.
Es kommt immer der Hinweis

"The group by function needs the input to be sorted on the specific keys. If you don't sort in the input the results may not be correct".

Ergo: Sort by-Step  nur nach Personalnr und Beginn

Dann "Group by" -> Group Fields PersNr, MAKrs und Aggregates "minBeginn  Beginn Minimum" u. "maxEnde Ende Maximum".

Ergebnis Datenzeilen

Persnr minBeginn maxEnde Makrs

6333     1.8.2015     31.7.2017  60

6333    1.8.2017      31.12.2018 50
6333    1.1.2019       31.12.2019 60

Das wollen wir ja - hehe!!

vergl. db/module/sva/etl/sap_hr/sap_load_pbv_art_dienstart.ktr