Unser Sales-Team berät Sie gerne: +49 711 460543 33
Menu

Einführung: Daten analysieren mit Kettle und 1CRM

Business Intelligence für Ihr CRM-Tool

Business Intelligence mit Kettle

Informationen und Anleitungen rund um die Datenanalyse mit 1CRM

Kettle ist das Open Source ETL Tool. ETL ist der Bereich, der bei Business Intelligence Lösungen, z.B. Data-Warehouses dafür da ist, die Daten aus unterschiedlichen Quellen auszulesen, zu modifizieren, und anschliessend in eine Datenbasis zu packen, auf die man dann mit den Analysetools zugreifen kann. ETL steht daher auch für Extraction, Transformation und Loading.

In unseren Kundengesprächen stellen sich immer wieder ähnliche Fragen:

  • Kann ich das CRM System an mein ERP-System anbinden?
  • Kann ich eine Doublettenprüfung mit Ähnlichkeitssuche im CRM machen?
  • Kann das CRM System zur Datenanreicherung auf einen externen Webservice zugreifen?
  • Können wir jede Nacht einen Job laufen lassen, um die Finanzdaten im CRM  zu aggregieren?
  • Lassen sich im CRM-System automatisch Angebote aus auslaufenden Serviceverträgen des Typs X erstellen.

Solche Anforderungen lassen sich mithilfe des ETL-Tools Kettle und der CRM-BI-Extension problemlos realisieren.

Aufgrund der Vielseitigkeit der Schnittstellen nach aussen, sei es Excel, beliebige Datenbanken, XML oder Webserviceschnittstellen zu Salesforce, SAP und anderen Systemen und den unendlichen Möglichkeiten zur Datenmanipulation, bietet sich Kettle für eine Vielzahl von Aufgaben mit Kundendaten, Adressdaten oder Bestell- und Finanzdaten im CRM-Umfeld an. Und das gute dabei – alles kann innerhalb einer übersichtlichen grafischen Oberfläche passieren.

 

1CRM und Kettle, die 1CRM BI Extension

Kettle ETL im CRM System

Ausführen einer Kettle Transformation im CRM

Wir bei visual4 nutzen Kettle zum einen für die Datenmigration in neue 1CRM Systeme, und zum anderen mithilfe unserer BI-Erweiterung um Kettle-Transformationen direkt in 1CRM ausführen zu können. Das geht entweder manuell, zeitgesteuert, oder als Teil eines Workflows in 1CRM. Beim Hochladen von neuen Skripten in 1CRM wird automatisch die Datenbank-Konfiguration in das Kettle-Script übernommen. Dateien, die innerhalb des Scripts erzeugt werden, sind natürlich direkt über 1CRM zugänglich. Weitere Details zu der Erweiterung gibt es direkt auf der Extension Seite.

 

Lokale Installation und Start von Kettle

Die aktuelle Version von Kettle kann auf der Community-Seite von Pentaho, der Firma hinter Kettle heruntergeladen werden: http://community.pentaho.com/projects/data-integration/

Als Voraussetzung für Kettle muss eine aktuelle Java-Version installiert sein.

Um auf MySQL-Datenbanken zugreifen zu können, benötigen Sie den Mysql-Java Treiber (mysql-connector-java-5.X.XX-bin.jar). Sie erhalten ihn von der MySQL-Seite unter  http://dev.mysql.com/downloads/connector/j/; wählen sie die plattformunabhängige ZIP-Datei, in dieser finden Sie den Treiber. Er muss in das „lib“ Unterverzeichnis der Kettle Installation kopiert werden.

Unter Windows starten Sie Kettle, indem Sie „spoon.bat“ doppelklicken.

 

Die Oberfläche von Kettle

zuerst muss über das Datei-Menü eine neue Transformation erstellt werden, dann sollte die Oberfläche in etwa wie folgt aussehen:

Kettle Hauptfenster

Fenster mit Transformationsschritten und Arbeitsbereich von Kettle. Bereit Ihre CRM Prozesse zu modellieren.

im linken Bereich der Oberfläche finden sich die möglichen Schritte die in eine Transformation eingefügt werden können. Zur Strukturierung sind sie in Ordnern kategorisiert. Aufgrund der Vielzahl der Möglichkeiten ist die Suche in diesem Bereich ein effektives Mittel, um die Auswahl einzuschränken, und das gesuchte Objekt wiederzufinden.

Einen Schritt fügt man in die Transformation durch drag & drop auf den rechten Arbeitsbereich ein. Über dem Arbeitsbereich finden sich die wichtigsten Icons:

 um die Transformation zu prüfen,

 einen Testlauf zu starten und

 die Transformation auszuführen

Einzelne Schritte im Arbeitsbereich werden durch Klicken und ziehen mit der mittleren Maustaste miteinander verbunden.

 

Die wichtigsten Transformationsschritte in Kettle

Der „Table input“ Schritt liest Daten aus der CRM-Datenbank ein

als ersten Schritt ziehen wir uns einen „Table input“ auf die Arbeitsfläche. Durch einen Doppelklick auf den Schritt gelangt man in die Eigenschaften des Schritts. Um Daten aus dem CRM einzulesen muss erst einmal eine Datenbankverbindung erstellt werden. Hierzu wählt man den „New…“ Button:

Kettle Datenbank Konfiguration für MySQL

Datenbank-Konfigurationsfenster in Kettle – um auf die CRM-MySQL Datenbank zugreifen zu können, muss erst der MySQL-Java Connector installiert werden.

Als Werte habe ich im Screenshot die Daten einer lokalen Test-Datenbank eingegeben, es können natürlich auch die Remote-Verbindungsdaten der CRM-Datenbank eingefügt werden. Sobald das Script über die CRM-BI-Extension in 1CRM gespeichert wird, werden die Werte automatisch mit den passenden Daten des CRM-Systems überschrieben. Mit dem Test-Button kann die Verbindung getestet werden.

CRM-Datenzugriff in Kettle

Zugriff auf Kundendaten in der CRM Datenbank kann mit SQL-Statements erfolgen.

über den „Get SQL select statement“ Button kann eine Tabelle des CRM-Systems gewählt werden, um ein SQL mit allen Feldern der Tabelle zu erzeugen. Das ist oft hilfreich um die richtigen Spalten im SQL zu haben.

Mit dem Datenbank-Explorer können einfach die verfügbaren Tabellen der Adressverwaltung ausgewählt werden.

über den Preview-Button kann das SQL-Statement geprüft werden, hier werden die Daten aus der Tabelle ausgelesen und tabellarisch ausgegeben.

 

Der „Select values“ selektiert und modifiziert einzelne Spalten

als nächstes ziehen wir einen „Select values“ Schritt auf die Arbeitsfläche und verbinden ihn mit dem „Table input“ (entweder durch klicken und ziehen mit der mittleren Maustatste, oder duch Klick auf „Table input“, Pfeil nach rechts und dann den „Select values“ Schritt)

Selektion und Modifikation von Datenfeldern.

Selektion und Modifikation von Datenfeldern.

Mit dem „Select values“ Schritt kann man entweder gezielt Felder auswählen, die für die weitere Verarbeitung ausgegeben werden oder „Include unspecified fields, ordered“ anklicken und über den Remove-Tab einzelne Felder entfernen. Ausserdem lassen sich Feldnamen, wie im Beispiel gezeigt anpassen und über den „Meta-Data“-Tab Feldtypen und Formatierungen ändern.

Intern arbeitet Kettle mit typisierten XML Datenzeilen, d.h. bei der Übernahme von Daten aus der CRM-Datenbank sind Datums- Zahlen- und Text Feldtypen meist richtig zugeordnet. Z.B. bei einer CSV-Datei als Datenquelle ist dies oft nicht der Fall und kann in diesem Schritt angepasst werden.

 

Der „Filter rows“ Schritt filtert die Daten

der „Filter rows“ Schritt fungiert entweder als Filter, der nicht benötigte Daten verwirft, oder als Weiche, um die Daten entsprechend der Kriterien an zwei unterschiedliche Transformationen weiterzuleiten. In den Kriterien können einzelne Spalten entweder mit einem festen Wert (value) oder einer anderen Spalte verglichen werden

Filter in Kettle

Filtern von Ergebnislisten nach unterschiedlichsten Kriterien, mit der Möglichkeit diese flexibel zu staffeln und zu verknüpfen.

im Beispiel werden Daten weiter verarbeitet, bei denen der account_type ungleich „Supplier“ ist. Wichtig ist bei der Verwendung als Filter, dass weder „Send true…“ noch „Send false…“ gefüllt sind.

 

Der „Sort Rows“ Schritt sortiert die Daten nach einem oder mehreren Feldern

Um später eine saubere Gruppenbildung der Daten zu ermöglichen, müssen die Datensätze sortiert sein, das ermöglicht der „Sort rows“ Schritt. Wichtig ist dabei, dass bei grossen Datenmengen alle Daten in den Speicher (oder eine Auslagerungsdatei) geladen werden müssen, um die Sortierung durchzuführen. Es wird also, falls die Möglichkeit besteht immer schneller in der Ausführung sein, die Sortierung bereits in der Datenbank beim Auslesen der Daten vorzunehmen!

Sortierung

Sortierung von Daten innerhalb von Kettle – erforderlich um später Gruppierungen der Daten durchführen zu können.

 

Der „Group by“ Schritt aggregiert Daten und berechnet z.B. Summe oder Schnitt

die sortierten Daten lassen sich jetzt mit dem „Group by“ Schritt Gruppieren. Bei der Gruppierung müssen alle Felder, die später weiter verwendet werden sollen ausgewählt werden, dabei gibt es die Möglichkeit, entweder Summen oder Durchschnitt errechnen zu lassen, oder auch auf den ersten oder letzten Wert dieser Spalte in der Gruppe zurückzugreifen.

Gruppierungsfenster in Kettle

Gruppierung von Daten und Aggregationen, z.B. Summenbildung über Finanzdaten im CRM

Im Beispiel wird nach der Stadt gruppiert, die Anzahl der Datensätze ausgegeben, Die offenen Posten summiert und alle Firmen kommasepariert in ein Feld Firma geschrieben.

Vorschau der Transformation

Um eine Vorschau der Daten zu erhalten, selektiere ich jetzt den „Group by“ Schritt, und klicke auf den „Vorschau“-Button oben im Arbeitsbereich. im nächsten Fenster können Parameter und die gewünschte Anzahl der Datensätze eingegeben werden – ich belasse es bei den Voreinstellungen.

Datenvorschau mit gruppierten CRM-Daten

Vorschau der Daten, hier die Kundenanzahl, gruppiert nach Stadt mit der Summme der offenen Posten

ich erhalte eine tabellarische Ansicht der Daten, die sich durch einen Klick auf den Header einer Spalte auch sortieren lässt. Unterhalb des Arbeitsbereiches erscheint gleichzeitig ein Bereich mit den „Execution Results“ in dem man im „Step Metrics“ Tab den Fortschritt laufender Transformationen beobachten kann und im Logging-Tab möglichen Fehlern auf den Grund gehen kann.

Ausführungsstatistiken

Statistiken zu der Ausführung des Kettle-Scriptes. Bei länger laufenden Scripten kann hier der Fortschritt der einzelnen Schritte überprüft werden

Die Step Metrics zeigen dabei, genau wie die Metrics auch mögliche Flaschenhälse auf.

 

„Microsoft Excel Output“ zur Datenausgabe

Mit diesem Schritt zur Ausgabe der Daten als Excel, lassen sich alle Daten, die bei diesem Schritt ankommen in eine Excel-Tabelle schreiben. Im File-Tab wird der Dateiname definiert. Dabei können dynamisch Datum und Zeit in den Namen eingefügt werden. Der Dateiname im Feld filename sollte hierfür keine „.xls“-Erweiterung beinhalten. Wird kein Pfad angegeben, landet die Datei normalerweise im Kettle-Arbeitsverzeichnis. Bei der Verwendung innerhalb des CRM-Systems wird automatisch der richtige Dateipfad eingetragen, um im Anschluss übder die Weboberfläche auf die Datei zugreifen zu können.

Im „Content“-Tab wird angegeben, ob eine Header-Zeile mit den Spaltennamen angelegt werden soll, und in welcher Kodierung die Daten ausgegeben werden sollen.

Der wichtigste Tab ist der „Fields“-Tab. Hier wird definiert, welche Felder in welcher Reihenfolge in die Tabelle ausgegeben werden sollen. Über „Get Fields“ werden alle verfügbaren Felder eingefügt.

CRM Excel Export

Export der erzeugten CRM-Datensätze in eine Microsoft Excel-Datei.

zusätzlich können hier noch Formate für Daten und Zahlen definiert werden.

 

Fazit

Ich hoffe, dass ich mit dieser Dokumentation einen kurzen Einblick in die vielen Möglichkeiten, die sich mit einem Werkzeug wie Kettle bieten geben konnte. Für sich genommen ist Kettle schon sehr mächtig für einmalige Aufgaben wie die Datenmigration, durch die Nahtlose Integration in die Prozesse eines CRM-Systems oder einer Kundenverwaltung entfaltet es Superkräfte und sprengt alle bisher vorhandenen Grenzen Ihres CRM-Systems :-)

Noch Fragen? Dann besuchen Sie uns auf Xing

Werden Sie Teil der 1CRM-Community

Die 1CRM-Usergroup bringt Anwender aus den unterschiedlichsten Bereichen mit anderen Anwendern und Entwicklern zusammen. In der Xing-Gruppe erhalten Sie aktuelle Informationen zur CRM-Lösung 1CRM, z. B. Produkt-Updates, neue Features, Webinar-Termine und können sich mit anderen CRM-Anwendern austauschen. Stellen Sie sich und Ihr Geschäft gerne im Forum „Vorstellungsrunde“ vor und schildern Sie kurz, wie Sie 1CRM einsetzen.

1CRM-Anwender und -Entwickler beantworten Ihre Fragen

Neu bei 1CRM? So richten Sie das CRM-System ein

CRM-Updates testen und einspielen

CRM-Updates testen und einspielenDas sollten Sie beim Einspielen von 1CRM-Updates beachtenSteht ein neues Update für 1CRM zur Verfügung, empfehlen wir das hier beschriebene Vorgehen.Insbesondere, wenn Ihr System individuell angepasst ist, kann es zu unvorhergesehen...

Datenschutzeinstellungen gemäß DSGVO

CRM datenschutzkonform einrichten Das sollten Sie noch vor der alltäglichen Bedienung des Systems einstellen Die aktuelle Datenschutzverordnung verpflichtet Sie zu einem besonderen Umgang mit personenbezogenen Daten. Damit Sie die Anforderungen der...

CRM upgraden

Sie möchten zu einer anderen 1CRM-Edition wechseln? Oder in die Cloud? Erfahren Sie, wie Sie auf eine andere 1CRM-Lizenz umsteigen oder von On-Premises auf die Cloud. Ein großer Vorteil von 1CRM ist das flexible Lizenz- und Hosting-Modell. In dieser Anleitung zeigen...

Benutzerkonten im CRM anlegen

CRM-Benutzer für Ihre Mitarbeiter anlegen So richten Sie Benutzerkonten in 1CRM ein Sie können in 1CRM so viele Benutzer anlegen, wie Sie Lizenzen erworben haben (mit der kostenlosen Startup Edition können Sie noch weitere 2 Benutzer anlegen). In dieser Anleitung...

E-Mail im CRM: sendmail oder SMTP?

E-Mails im CRM: sendmail oder SMTP Hintergrundwissen zum Einrichten des E-Mail-Versands in 1CRM In 1CRM lassen sich E-Mails über sendmail oder SMTP versenden. Die zwei Begriffe sagen Ihnen nichts? Der folgende Text erklärt, was hinter den Begriffen steht, wann Sie...