Angenommen es gibt einen Supermarkt dessen Produkte eindeutig in Bio- und Nicht-Bio-Produkte eingeteilt werden können. Zudem gibt der Supermarkt seinen Kunden einen “Rabatt” oder andere Vorteile, wenn diese sich eine Kundenkarte zulegen. Mittels der Kundenkarte können die Einkäufe eindeutig dem Kunden zugeordnet werden. Der Supermarkt möchte diese Kundendaten clustern und klassifizieren, sodass in Zukunft Kunden eindeutig in Bio- und Nicht-Bio-Kunden eingeteilt werden können, um darauf aufbauend gezielte Werbung schalten zu können.
In den folgenden Beiträgen wird zunächst eine Verbindung von R zur Hana Datenbank hergestellt, sodass im Anschluss pseudo Daten in R erstellt werden können, welche für den oben genannten Use Case benötigt werden. Auf diese Daten wird zunächst ein Algorithmus (Kmeans) ausgeführt, um die Daten in Bio bzw. Nicht-Bio Kunden einzuteilen, sodass darauf aufbauend ein Entscheidungsbaum erstellt wird, der als Grundlage für die Vorhersage dienen wird. In allen Beiträgen wird es einen kurzen Vergleich von R zur Pal Library auf der SAP Hana DB geben.
ODBC Verbindung / Connektierung SAP – R
Einführung
In diesem Beitrag geht es um die Installation und Konnektierung von R zur Hana Datenbank. Das Ziel ist es, dass die Daten in R von der Hana DB abgerufen, verarbeitet und wieder zurückgeschickt werden können. Dazu wird die Open-Source-Software R und R Studio benötigt. Der SAP Hana Client muss heruntergeladen und installiert werden. Nach diesem Beitrag sollte eine HDBODBC Verbindung hergestellt, zwei Packages in R installiert, sowie ein erster Konnektierungstest durchgeführt worden sein.
R
R ist eine Programmiersprache, die für statistische Berechnungen verwendet wird. Zudem lassen sich die Daten schnell transformieren und visuell Darstellen.
Zunächst wird R und R Studio heruntergeladen. Im Anschluss wird der SAP HANA Client heruntergeladen, damit die HDBODBC Verbindung hergestellt werden kann. Nachdem die Verbindung erfolgreich getestet wurde, kann in R das Package RODBC installiert werden, womit der Zugriff auf die SAP HANA Tabellen erfolgen kann. Das Package DBI wird verwendet, um mittels SQL Skript in R auf die Hana DB zu schreiben. Viel Erfolg beim Einrichten!
Download & Installation R
R ist eine Open-Source-Software für statistische Berechnungen. Von einer simplen Regression bis zur Markov Chain Monte Carlo Simulation oder interaktive Dashboards ist fast alles möglich.
Quelle des Downloads: https://cran.r-project.org/bin/windows/base/
Installationsanweisungen befolgen.
Download R Studio
R Studio ist die benutzerfreundliche Oberfläche zur R Anwendung. So lassen sich beispielsweise alle gespeicherten Variablen, Packages, die Hilfe oder Plots auf einem Bildschirm darstellen.
R benutzerfreundliche Oberfläche
Quelle des Downloads: https://rstudio.com/products/rstudio/download/#download
Installationsanweisungen befolgen.
Download Sap Hana Client
Die folgende Anleitung kann ebenfalls auf der Seite der SAP nachgelesen werden.
1.1) Anmeldung Website https://support.sap.com/en/index.html
1.2) Software Downloads (Unten rechts in der folgenden Abbildung):
1.3) Navigieren:
Nach alphabetischem Index A-Z -> H -> SAP Hana PLATFORM EDITION -> SAP HANA PLATFORM EDITION 2.0 -> INSTALLATION -> Download der richtigen Datei (Stand 01/2020 51053787)
1.4) Entpacken der Datei in einen beliebigen Ordner
1.4.1) Navigieren zum richtigen HDB Client -> Data Units -> HDB_CLIENT_WINDOWS_X86_64 -> hdbinst.exe ausführen und Installation befolgen
ODBC Datenquellen öffnen und HDBODBC einrichten
Start -> ODBC eingeben:
ODBC-Datenquellen öffnen
System DNS -> Hinzufügen -> HDBODBC
Neue Datenquelle erstellen
An dieser Stelle muss ein Name und eine Beschreibung für die Datenquelle hinzugefügt werden, welches bei mir jeweils mit DS4 bezeichnet wird. Der notwendige Host und Port muss aus dem SAP Hana System abgeleitet werden. Durch die Test Connection kann ausprobiert werden, ob von außerhalb eine Verbindung zur Hana DB hergestellt werden kann. Anmerkung: Eine VPN-Verbindung muss vorhanden sein, falls der Zugriff nicht aus dem Unternehmen erfolgt.
Sollte die Installationsanweisung nicht funktioniert haben, so kann dieser Link der SAP helfen, um die Verbindung korrekt herzustellen.
R Studio starten und Package installieren
Nach der Connectierung kann R bzw. R Studio geöffnet werden und über das grüne Plus-Zeichen ein neues Skript angelegt werden.
In dem neuen Skript lassen sich nun mittels des Befehls install.packages die Pakete RODBC und DBI installieren.
Verbindung herstellen
Damit eine Verbindung hergestellt werden kann, muss das installierte Package aktiviert werden. Dies geschieht durch den Befehl library:
library("RODBC")
## Warning: package 'RODBC' was built under R version 3.5.3
Die hergestellte DSN-Verbindung zur DS4 kann nun durch den Befehl odcbConnect erfolgen.Zusätzlich werden der Username und das Passwort verlangt:
ch <-odbcConnect (dsn = "DS4",uid = "SBAARTZ",pwd = "******")
Als erster kleiner Test werden die Buchstaben A-Z in einem Data.Frame gespeichert und diese Datentabelle an die Hana DB gesendet.df<-data.frame(LETTERS)
df[1:10,]
## [1] A B C D E F G H I J
## Levels: A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
Diese Daten werden in der Tabelle Buchstaben in das zuvor angelegte Schema SBBLOG gespeichert.
sqlSave(channel = ch,dat = df,tablename = 'SBBLOG.BUCHSTABEN')
Der Nachweis über die neue Tabelle kann in R mittels folgendem SQL Statement abgefragt werden:
SELECT * FROM SBBLOG2.BUCHSTABEN
Daten erfolgreich übertragen
rownames | LETTERS |
---|---|
1 | A |
2 | B |
3 | C |
4 | D |
5 | E |
6 | F |
7 | G |
8 | H |
9 | I |
10 | J |
Zur weiteren Demonstration folgt ein Screenshot aus Eclipse mit den übertragenen Daten:
Die Verbindung von R zur Hana DB ist erfolgreich eingerichtet, sodass im nächsten Schritt Pseudo-Daten für den folgenden Use Case erstellt werden können.
Use Case
Angenommen es gibt einen Supermarkt dessen Produkte eindeutig in Bio- und Nicht-Bio-Produkte eingeteilt werden können. Zudem gibt der Supermarkt seinen Kunden einen “Rabatt” oder andere Vorteile, wenn diese sich eine Kundenkarte zulegen. Mittels der Kundenkarte können die Einkäufe eindeutig dem Kunden zugeordnet werden. Der Supermarkt möchte diese Kundendaten clustern und klassifizieren, sodass in Zukunft Kunden eindeutig in Bio- und Nicht-Bio-Kunden eingeteilt werden können, um darauf aufbauend gezielte Werbung schalten zu können.
Daten Clustern in R und SAP HANA DB
Angenommen es gibt einen Supermarkt dessen Produkte eindeutig in Bio- und Nicht-Bio-Produkte eingeteilt werden können. Zudem gibt der Supermarkt seinen Kunden einen “Rabatt” oder andere Vorteile, wenn diese sich eine Kundenkarte zulegen. Mittels der Kundenkarte können die Einkäufe eindeutig dem Kunden zugeordnet werden. Der Supermarkt möchte diese Kundendaten clustern und klassifizieren, sodass in Zukunft Kunden eindeutig in Bio- und Nicht-Bio-Kunden eingeteilt werden können, um darauf aufbauend gezielte Werbung schalten zu können.
In diesem Blogbeitrag soll es um das Thema Clustering gehen. Es werden zunächst Pseudo-Daten in R für den Use-Case erstellt. Durch die Connectierung können die Daten von R zur Hana DB übertragen werden und der Kmeans-Algorithmus zum Clustering der Pseudo-Daten erstellt und ausgeführt werden. Am Ende wird es einen kurzen Vergleich zwischen dem Algorithmus in R und der PAL Library auf der SAP Hana DB geben.
Verbindung herstellen
Wie in dem vorherigen Blogbeitrag beschrieben, kann mittels einer ODBC-Einrichtung eine Verbindung von R zur Hana DB hergestellt werden. Die Connektierung wird in der Variablen ch gespeichert. Die Variable ch wird verwendet, wenn mittels R-Skript auf die Hana DB zugegriffen wird. Das folgende Skript aktiviert die notwendige Bibliothek und stellt die Verbindung her.
library("RODBC")
# Connektierung
ch <-odbcConnect (dsn = "DS4",uid = "SBAARTZ",pwd = "******")
Bevor die ersten Daten übertragen werden, wird auf der Hana DB ein neues Schema erstellt, indem alle weiteren Tabellen, Tabellenstrukturen und Prozeduren gespeichert werden.CREATE Schema SBBLOG
Use-Case (Daten erstellen)
Auf Basis des Fallbeispiels werden im Folgenden 150 Kundendaten erzeugt. Die Kundendaten bestehen aus Kundennummer, Bio Ausgaben und Nicht Bio Ausgaben. Die Daten für die Kundennummern werden per Zufallszahlen erzeugt. Die Ausgaben für Bio und Nicht Bio Produkte werden jeweils aus drei Normalverteilungen mit unterschiedlichen Schwerpunkten erzeugt. Der Hintergrund ist der, dass dadurch die Kunden “ganz sicher” Bio, Nicht Bio oder Sonstige Kunden sind. Im realen Leben könnten 30 Kunden (20% des Datensatzes) an einer Umfrage teilnehmen, sodass die Ergebnisse “ganz sicher” richtig sind. Später kann dann eine eindeutige Aussage darüber getroffen werden, ob die Testdaten ein erfolgreiches Ergebnis (Auf Basis der Trainingsdaten) liefern oder nicht.
Als gängige Heuristik lassen sich Daten im Verhältnis 80:20 in Trainings – und Testdaten einteilen.
set.seed(128) # immer die gleichen Zufallszahlen erzeugen
# Zufallszahlen für BonNR, rnorm = Normalverteilung (Einkauf Bio,Sonstige und Nicht-Bio)
Kundennummer<-sample(1000:2000,size = 150,replace = F)
Bio<- c(rnorm(n = 50,mean = 40,sd = 8 ),rnorm(n = 50,mean = 20,sd = 4.5 ),rnorm(n = 50,mean = 10,sd = 8 ))
Bio_F<-c(rnorm(n = 50,mean = 10,sd = 8 ),rnorm(n = 50,mean = 20,sd = 4.5 ),rnorm(n = 50,mean = 40,sd = 8 ))
# Zusammenfassen, Ordnen und Beschreibung der Daten
Daten<-data.frame(round(Bio,2),round(Bio_F,2),c(rep("Bio",50),rep("Sonstige",50),rep("Nicht Bio",50)))
Daten<-Daten[order(Kundennummer),]
colnames(Daten)<-c("Bio","Nicht_Bio","Kundenart")
Daten[Daten$Bio<0,1]<-0
Daten[Daten$Nicht_Bio <0,2]<-0
colnames(Daten)<-c("Bio","Nicht_Bio","Kundenart")
rownames(Daten)<-Kundennummer[order(Kundennummer)]
# 30 Testdaten, 120 Trainingsdate ~ 20:80
KMEANS_TESTDATEN<-Daten[1:30,]
KMEANS_TRAININGSDATEN<- Daten[31:150,1:2]
Anmerkung: Es wurden zwar die ersten 30 Datensätze als Testdaten verwendet, allerdings wurden die Datensätze durch den Befehl Daten[order(Kundennummer),] sortiert, sodass sich die Test- und Trainingsdaten zufällig zusammensetzen.
Die ersten 10 Datensätze in R:
KMEANS_TRAININGSDATEN[1:10,]
## Bio Nicht_Bio
## 1177 19.22 22.40
## 1182 25.09 15.84
## 1186 4.04 46.12
## 1188 18.69 19.60
## 1194 17.86 16.55
## 1206 27.73 44.46
## 1208 28.24 11.30
## 1215 26.41 31.21
## 1221 39.03 0.00
## 1253 28.09 18.21
Die erzeugten Daten lassen sich mittels sqlSave auf der Hana DB in dem erzeugten Schema SBBLOG speichern.
sqlSave(channel = ch,dat = KMEANS_TESTDATEN,tablename = 'SBBLOG.KMEANS_TESTDATEN') sqlSave(channel = ch,dat = KMEANS_TRAININGSDATEN,tablename = 'SBBLOG.KMEANS_TRAININGSDATEN')
Zum Abruf der Daten kann ein einfaches select from verwendet werden. Es werden auch hier die ersten 10 Ergebnisse angezeigt:
select * from SBBLOG.KMEANS_TRAININGSDATEN
Displaying records 1 – 10
rownames | Bio | Nicht_Bio |
---|---|---|
1177 | 19.22 | 22.40 |
1182 | 25.09 | 15.84 |
1186 | 4.04 | 46.12 |
1188 | 18.69 | 19.60 |
1194 | 17.86 | 16.55 |
1206 | 27.73 | 44.46 |
1208 | 28.24 | 11.30 |
1215 | 26.41 | 31.21 |
1221 | 39.03 | 0.00 |
1253 | 28.09 | 18.21 |
Auf der Hana DB besteht die Möglichkeit per Rechtsklick auf die Tabelle KMEANS_TRAININGSDATEN die Daten mittels Data Preview in einem Scatterplot darstellen zu lassen.
Der kmeans Algorithmus
Bevor nun der Algorithmus erstellt und ausgeführt werden kann, muss man verstehen, wie dieser Algorithmus arbeitet und was er tut.
Der KMEANS-Algorithmus ordnet jeden Datenpunkt des Plots einen von k Clustern (Gruppen) zu. Dazu werden zunächst k zufällige Zentren gebildet und jeder Datenpunkt dem Zentrum mit dem geringsten Abstand zugeordnet. Durch diese Zuordnung aller Datenpunkte ergeben sich k cluster, sodass zu jedem Cluster neuen Zentren berechnet werden können. Auf Basis der neuen Zentren, beginnt der Algorithmus von vorne und weist erneut jedem Datenpunkt einem Cluster zu, wodurch der Abstand erneut minimiert wird. Diese Schritte werden theoretisch solange wiederholt, bis keine neue Zentren erzeugt werden. Durch diese Wiederholungen werden optimale cluster erstellt – allerdings auf Basis der ersten zufällig gewählten Zentren. Mit einem anderen Startwert kann es andere Ergebnisse geben. Dieser wichtige Punkt wird am Ende des Beitrags noch einmal aufgegriffen. Für den Augenblick reicht es zu wissen, dass durch anderen Startwerte andere Ergebnisse entstehen können.
Zunächst muss jedoch eine Entscheidung für die Anzahl der Cluster getroffen werden. Anhand des Scatterplots auf der Hana DB lassen sich optisch die Datenpunkte in zwei, drei oder sogar vier Gruppen einordnen. Doch was ist die optimale Anzahl an Cluster?
Aus mathematischer Sicht würde man mittels einer Varianz (Die quadratische Abweichung zum Zentrum) die optimale Anzahl der Gruppen bestimmen, indem man sagt, dass innerhalb einer Gruppe die Abweichung möglichst klein sein soll.
So gibt es eine Gesamtvarianz, die sich aus zwei weiten Varianzen (innerhalb (within) und zwischen (between) den Clustern) zusammensetzt. Zunächst wird der Erwartungswert aller Datenpunkte der Bio- und Nicht-Bio Ausgaben errechnet. Visuell gesprochen ergibt das ein Zentrum aller Datenpunkte. Für die Gesamtvarianz (total) geht man nun den gesamten Datenbestand durch und berechnet die quadratischen Abstände zu diesem errechneten Zentrum, die Summe dessen ist die totale Varianz. Die totale Varianz wird für den nächsten Schritt benötigt.
Als weitere Varianz lässt sich die Varianz innerhalb der Cluster berechnen. Das Vorgehen ist ähnlich wie bei der Gesamtvarianz, so würde man bei k Clustern k Zentren und die Abstände zu den k Zentren berechnen. Der Datensatz gehört demjenigen Cluster an, dessen Abstand zum Zentrum am geringsten ist. Die Summe der Ergebnisse ergibt die within-Varianz. Wenn ein Cluster gefunden wurde, dann sollte das Ziel sein, dass die Abweichungen innerhalb dieses Clusters möglichst gering ausfallen. Anders ist das bei der Varianz zwischen den Gruppen, die als dritte Varianz berechnet und verwenden werden könnte. Visuell kann man sich vielleicht vorstellen, dass die quadratische Abweichung zwischen den Gruppen möglichst groß sein sollte, denn je Größer der Abstand zwischen den Gruppen, desto eindeutiger ist das Ergebnis. Für das aktuelle Ziel reicht die Gesamt- und eine der beiden anderen Varianzen aus, denn
Visuell betrachtet sollten also die Abstände innerhalb einer Gruppe zu dessen arithmetischen Mittel (Zentrum) sehr klein sein, genau dann ist auch die Within-Varianz sehr klein (Ergo die Between-Varianz sehr groß). Durch die Bildung eines Quotienten
erhält man einen Parameter, der minimiert werden soll.
In R lassen sich aus dem kmeans-Algorithmus die Varianzen within und total ermitteln, sodass mittels einer For-Schleife und dem Speichern der Quotienten ein Plot erstellt werden kann:
par(mar=c(3,6,3,6))
wss <- NA
for (i in 1:20) wss[i] <- sum(kmeans(KMEANS_TRAININGSDATEN,i,nstart=20)$within)/kmeans(KMEANS_TRAININGSDATEN,i,nstart=20)$totss
plot(1:20, wss, type="b", xlab="Anzahl der Cluster",
ylab="Summe der quadratischen Abweichungen\n innerhalb der Gruppen",bty="n",main = "Optimale Anzahl der Cluster")
points(3,sum(kmeans(KMEANS_TRAININGSDATEN,3,nstart=20)$within)/kmeans(KMEANS_TRAININGSDATEN,3,nstart=20)$totss,col="blue",pch=19)

Anhand der Grafik wird zum einen klar, dass die Varianz genau dann bei 0 und damit perfekt ist, wenn es genauso viele Gruppen wie Datensätze gibt. Bezogen auf den Use-Case würde das bedeuten, dass für jeden Kunden individuelle Werbung geschaltet werden müsste. Das sollte allerdings nicht das Ziel sein, denn je weniger Gruppen, desto weniger Arbeit müsste man sich bei der personalisierten Werbung, durch die Homogenität der einzelnen Gruppen, machen. Durch die Grafik entscheide ich mich für 3 Gruppen, denn aus meiner Sicht führt jede zusätzlichen Gruppe zu keinem signifikanten Varianzgewinn. An dieser Stelle sei angemerkt, dass hier durchaus Hypothesentest durchgeführt werden sollten. Hyptohesentests sind jedoch nicht Thema dieses Beitrags, weshalb an dieser Stelle auf eine detailliertere Analyse verzichtet wird.
KMEANS Algorithmus auf der SAP HANA DB mittels PAL library
Wenn auf der Hana Datenbank ein Algorithmus (Prozedur) erzeugt werden soll, so muss sehr strukturiert gearbeitet werden. Es werden zunächst Tabellenstrukturen erzeugt, die im Folgenden auf _T enden. Des Weiteren muss bereits vor dem Aufruf festgelegt werden, wie die Ergebnistabellen strukturiert sein sollen und bereits leere Tabellen erzeugt werden, die durch den Aufruf der Prozedur gefüllt werden.
RENAME COLUMN "SBBLOG".KMEANS_TRAININGSDATEN."rownames" TO KUNDENNR;
-- Tabellenstruktur für Trainingsdaten
CREATE TYPE "SBBLOG".PAL_KMEANS_TRAININGSDATA_T AS TABLE(
"KUNDENNR" VARCHAR(255),
"Bio" DOUBLE,
"Nicht_Bio" DOUBLE,
PRIMARY KEY("KUNDENNR"));
-- Tabellenstruktur für die spätere Ergebnistabelle
CREATE TYPE "SBBLOG".PAL_KMEANS_RESASSIGN_T AS TABLE(
"ID" VARCHAR(255),
"CENTER_ASSIGN" INT,
"DISTANCE" DOUBLE);
--Tabellenstruktur für die spätere Ergebnistabelle
CREATE TYPE "SBBLOG".PAL_KMEANS_CENTERS_T AS TABLE(
"CENTER_ID" INT,
"Bio-Zentrum" DOUBLE,
"Nicht-Bio-Zentrum" DOUBLE);
-------------------(leere) Tabellenerstellung für Kmeans Output -------------------
CREATE COLUMN TABLE "SBBLOG".PAL_KMEANS_RESASSIGN_TBL LIKE "SBBLOG".PAL_KMEANS_RESASSIGN_T;
--Clusterzentren-Tabelle
CREATE COLUMN TABLE "SBBLOG".PAL_KMEANS_CENTERS_TBL LIKE "SBBLOG".PAL_KMEANS_CENTERS_T;
------------------Tabellenstrukturen für Parametertabelle--------------------------
CREATE TYPE "SBBLOG".PAL_CONTROL_T AS TABLE(
"NAME" VARCHAR(100),
"INTARGS" INTEGER,
"DOUBLEARGS" DOUBLE,
"STRINGARGS" VARCHAR(100));
----------------------Tabellen für Prozeduraufruf---------------------------------
-- leere Tabelle für die späteren Parameter
CREATE COLUMN TABLE "SBBLOG".PAL_KMEANS_PDATA_TBL(
"POSITION" INT,
"SCHEMA_NAME" NVARCHAR(256),
"TYPE_NAME" NVARCHAR(256),
"PARAMETER_TYPE" VARCHAR(7));
-------------------füllen der Tabellen für Prozeduraufruf ------------------------
INSERT INTO "SBBLOG".PAL_KMEANS_PDATA_TBL VALUES (1, 'SBBLOG', 'PAL_KMEANS_TRAININGSDATA_T', 'IN');
INSERT INTO "SBBLOG".PAL_KMEANS_PDATA_TBL VALUES (2, 'SBBLOG', 'PAL_CONTROL_T', 'IN');
INSERT INTO "SBBLOG".PAL_KMEANS_PDATA_TBL VALUES (3, 'SBBLOG', 'PAL_KMEANS_RESASSIGN_T', 'OUT');
INSERT INTO "SBBLOG".PAL_KMEANS_PDATA_TBL VALUES (4, 'SBBLOG', 'PAL_KMEANS_CENTERS_T', 'OUT');
An dieser Stelle wird die KMEANS Prozedur erzeugt:
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'KMEANS', 'SBBLOG', 'PAL_KMEANS_PROC', "SBBLOG".PAL_KMEANS_PDATA_TBL);
Erläuterung
– AFLPAL ist die Library die verwendet wird
– KMEANS ist der Algorithmus der verwendet wird
– SBBLOG ist das Schema, in dem sich die Daten und Tabellen befinden und wo die Prozedur erzeugt wird
– PAL_KMEANS_PROC ist der Name der Prozedur für den späteren Aufruf
– PAL_KMEANS_PDATA_TBL enthält die Tabellenstrukturen für den finalen Aufruf
Über die Kontroll-Parameter lässt sich der Algorithmus steuern. Die genaue Beschreibung der Parameter lassen sich auf der SAP Seite nachlesen.
------------------------erstellen der Kontroll Parameter----------------------------
CREATE LOCAL TEMPORARY COLUMN TABLE "SBBLOG".#PAL_CONTROL_TBL(
"NAME" VARCHAR(100),
"INTARGS" INTEGER,
"DOUBLEARGS" DOUBLE,
"STRINGARGS" VARCHAR(100));
---------------------------füllen der Kontroll Parameter----------------------------
INSERT INTO "SBBLOG".#PAL_CONTROL_TBL VALUES ('THREAD_NUMBER', 2, NULL, NULL);
-- die Anzahl paralleler Threads (Prozesse)
INSERT INTO "SBBLOG".#PAL_CONTROL_TBL VALUES ('GROUP_NUMBER', 3, NULL, NULL);
-- die Anzahl gewünschter Cluster
INSERT INTO "SBBLOG".#PAL_CONTROL_TBL VALUES ('INIT_TYPE', 1, NULL, NULL); -- die Art der Initialisierung der Zentren; -- die ersten k Beobachtungen (1) -- zufällige Verteilung mit Ersetzung (2), -- zufällige Verteilung ohne Ersetzung (3), -- patentierter Algorithmus (4)
Anmerkung: Wichtiger Parameter! Wie eingangs erwähnt, hängt das Ergebnis des Kmeans-Algorithmus damit zusammen, welche Startwerte bzw.
Startzentren gewählt werden. Durch eine zufällige Verteilung mit Ersetzung(2) zum Beispiel kann es zu anderen Ergebnissen kommen.
INSERT INTO "SBBLOG".#PAL_CONTROL_TBL VALUES ('DISTANCE_LEVEL',2, NULL, NULL); -- Manhattan (1), euklidisch (2), Minkowski (3) oder Chebychev (4)
Anmerkung: Die euklidische Distanz ist auf dem ersten Blick die einleuchtendste Distanz – weil sie uns durch Pythagoras im zweidimensionalen Raum bekannt ist.
Allerdings kann es sich durchaus lohnen, mehrere Durchgänge mit unterschiedlichen Distanzen auszuprobieren, denn das Ziel sollte es sein, die geringste Varianz innerhalb eines Clusters zu erhalten.
INSERT INTO "SBBLOG".#PAL_CONTROL_TBL VALUES ('MAX_ITERATION', 100, NULL, NULL);
-- die maximale Anzahl an Iterationen, bevor der K-Means-Algorithmus endet
INSERT INTO "SBBLOG".#PAL_CONTROL_TBL VALUES ('EXIT_THRESHOLD', NULL, 0.000001, NULL);
-- Grenzwert zur Beendigung des Algorithmus
An dieser Stelle erfolgt der Aufruf der Prozedur. Es werden neben den Trainingsdaten die Kontroll Parameter für den Algorithmus und die (leeren) Ergebnistabellen mitgegeben, die durch den Aufruf gefüllt werden.
--------------------------------Aufruf der Prozedur---------------------------------
CALL "SBBLOG".PAL_KMEANS_PROC("SBBLOG".KMEANS_TRAININGSDATEN, "SBBLOG".#PAL_CONTROL_TBL, "SBBLOG".PAL_KMEANS_RESASSIGN_TBL, "SBBLOG".PAL_KMEANS_CENTERS_TBL) WITH OVERVIEW;
Ergebnis Aufruf der Prozedur (Zwei Tabellen wurden gefüllt)
variable | table |
---|---|
P3 | “SBBLOG”.“PAL_KMEANS_RESASSIGN_TBL” |
P4 | “SBBLOG”.“PAL_KMEANS_CENTERS_TBL” |
SELECT * FROM "SBBLOG".PAL_KMEANS_CENTERS_TBL
Die PAL_KMEANS_CENTERS_TBL Tabelle liefert die Zentren der Cluster. Ausgehend von diesem Ergebnis könnte das Cluster 0 in “Sonstige Kunden”,
das Cluster 1 in “Bio-Kunden” und das Cluster 2 in “Nicht-Bio-Kunden” umbenannt werden.
SELECT * FROM "SBBLOG".PAL_KMEANS_RESASSIGN_TBL;
Die PAL_KMEANS_RESASSIGN_TBL Tabelle liefert die Distanz zu dem jeweiligen Zentrum des Clusters.
Damit das Ergebnis noch ein bisschen schöner dargestellt werden kann, wird eine weitere Ergebnistabelle erzeugt, die sich aus den Ergebnissen der Clusterzuordnung und den Trainingsdaten zusammensetzt.
------------------------generieren einer individuellen Ergebnis Tabelle--------------
-- leere Tabelle erzeugen PAL_KMEANS_RESULT_TBL
CREATE COLUMN TABLE "SBBLOG".PAL_KMEANS_RESULT_TBL(
"CLUSTER" int,
"BIO" DOUBLE,
"NICHT_BIO" DOUBLE,
"KUNDENNR" INT );
-- Fuellen der Ergebnistabelle PAL_KMEANS_RESULT_TBL
INSERT INTO "SBBLOG".PAL_KMEANS_RESULT_TBL(
SELECT PAL_KMEANS_RESASSIGN_TBL.CENTER_ASSIGN,
KMEANS_TRAININGSDATEN."Bio",
KMEANS_TRAININGSDATEN."Nicht_Bio",
KMEANS_TRAININGSDATEN.KUNDENNR
FROM "SBBLOG".PAL_KMEANS_RESASSIGN_TBL
INNER JOIN "SBBLOG".KMEANS_TRAININGSDATEN
ON "SBBLOG".PAL_KMEANS_RESASSIGN_TBL.ID = "SBBLOG".KMEANS_TRAININGSDATEN.KUNDENNR);
select * from "SBBLOG".PAL_KMEANS_RESULT_TBL
Über das Data Preview der Tabelle PAL_KMEANS_RESULT_TBL lassen sich die Daten erneut in einem Scatterplot darstellen. Werden zusätzlich die Cluster einbezogen, so lassen sich die entstandenen Gruppen visuell abgrenzen:

Der kmeans Algorithmus in R
In R kann der KMEANS-Algorithmus deutlich schneller ausgeführt und visualisiert werden. Es werden neben den Daten lediglich die Anzahl der Cluster zwingend benötigt. Der zusätzliche Parameter nstart sorgt dafür, dass der Algorithmus 20x ausgeführt wird und das Ergebnis mit den besten Startwerten (geringste Varianz innerhalb der Gruppen) verwendet wird.
k<-kmeans(KMEANS_TRAININGSDATEN,3,nstart = 20) plot(KMEANS_TRAININGSDATEN,col=k$cluster,pch =19,bty="n")
In der Variablen k befinden sich die Zentren der Bio- und Nicht-Bio Ausgaben, sowie die Zuordnung der Cluster zu der entsprechenden Kundennummer.
Zudem wird das Ratio (between_SS / total_SS = 79.8 %) angegeben.
k
## K-means clustering with 3 clusters of sizes 33, 56, 31
##
## Cluster means:
## Bio Nicht_Bio
## 1 7.79000 42.37667
## 2 21.91786 18.86089
## 3 41.89419 11.37581
##
## Clustering vector:
## 1177 1182 1186 1188 1194 1206 1208 1215 1221 1253 1258 1262 1269 1276 1293 1301
## 2 2 1 2 2 1 2 2 3 2 2 2 3 3 1 2
## 1308 1314 1319 1320 1324 1333 1361 1363 1388 1389 1425 1444 1446 1451 1460 1466
## 2 2 3 2 1 3 3 1 1 2 1 2 3 3 3 1
## 1475 1486 1487 1491 1492 1495 1505 1522 1526 1527 1552 1555 1565 1574 1578 1580
## 1 1 3 2 1 3 1 2 1 3 2 2 3 3 3 1
## 1586 1587 1591 1594 1595 1601 1602 1603 1606 1614 1615 1616 1617 1645 1655 1660
## 2 2 1 2 3 2 2 1 2 2 3 2 1 2 2 1
## 1666 1676 1682 1684 1688 1697 1712 1718 1722 1725 1732 1741 1750 1767 1769 1775
## 2 1 1 2 3 2 3 2 2 2 3 3 1 1 2 3
## 1778 1786 1792 1794 1795 1796 1811 1837 1838 1845 1850 1852 1857 1859 1861 1862
## 2 1 1 3 2 2 2 2 2 3 2 1 1 3 2 2
## 1864 1873 1875 1880 1883 1885 1901 1904 1907 1915 1917 1918 1936 1943 1947 1949
## 3 2 1 2 2 2 3 1 2 1 2 2 1 1 3 3
## 1964 1967 1971 1972 1974 1983 1988 1989
## 2 2 1 3 3 1 2 2
##
## Within cluster sum of squares by cluster:
## [1] 3591.517 3298.447 2282.793
## (between_SS / total_SS = 79.8 %)
##
## Available components:
##
## [1] "cluster" "centers" "totss" "withinss" "tot.withinss"
## [6] "betweenss" "size" "iter" "ifault"
Fazit Vergleich R vs. SAP Hana Pal Library
Das zusammengefasste Ergebnis der SAP HANA DB:
PAL_KMEANS_CENTERS_TBL
## CENTER_ID Bio-Zentrum Nicht-Bio-Zentrum
## 1 0 21.50096 19.78962
## 2 1 40.23057 10.85143
## 3 2 7.79000 42.37667
summary(as.factor(PAL_KMEANS_RESASSIGN_TBL$CENTER_ASSIGN))
## 0 1 2
## 52 35 33
Daraus ergeben sich schließlich 52 Sonstige Kunden, 35 Bio Kunden, da das Bio Zentrum bei 40.23 EUR und das Nicht-Bio Zentrum bei 10.85 EUR liegt. Die dritte Gruppe besteht aus 33 Nicht-Bio Kunden, dessen Zentrum bei 7.79 EUR für Bio Produkte und 42.37 EUR für Nicht-Bio Produkte liegt.
Das zusammengefasste Ergebnis in R liefert etwas andere Ergebnisse.
k$centers
## Bio Nicht_Bio
## 1 7.79000 42.37667
## 2 21.91786 18.86089
## 3 41.89419 11.37581
k$size
## [1] 33 56 31
R berechnet demnach 56 Sonstige Kunden, 31 Bio Kunden und 33 Nicht-Bio Kunden.
Um zu beurteilen welches Ergebnis besser ist, lohnt sich erneut ein Blick in die Varianzen. In R haben wir gesehen, dass das Ratio (between_Ss / total_ss) 79.8 % beträgt. Da sich die Gesamtvarianz aus der Summe Between_ss + Within_Ss zusammensetzt, kann auch die Within_ss Varianz verwendet werden. Während between_ss/total_ss möglichst groß sein sollte, sollte logischerweise within_ss/total_ss möglichst klein sein. Ratio in R:
KMEANS_TRAININGSDATEN$CL<-k$cluster
sum(
sum(diag(var(KMEANS_TRAININGSDATEN[KMEANS_TRAININGSDATEN$CL==1,1:2])*(sum(KMEANS_TRAININGSDATEN$CL==1)-1))),
sum(diag(var(KMEANS_TRAININGSDATEN[KMEANS_TRAININGSDATEN$CL==2,1:2])*(sum(KMEANS_TRAININGSDATEN$CL==2)-1))),
sum(diag(var(KMEANS_TRAININGSDATEN[KMEANS_TRAININGSDATEN$CL==3,1:2])*(sum(KMEANS_TRAININGSDATEN$CL==3)-1))))/
sum(diag(var(KMEANS_TRAININGSDATEN[,1:2])*(dim(KMEANS_TRAININGSDATEN)[1]-1)))
## [1] 0.2018009
Wird diese Berechnung auf die Ergebnistabelle der Pal Library durchgeführt, so ist das Ratio:
# SAP HANA Ratio
colnames(PAL_KMEANS_RESULT_TBL)[1]<-"CL"
sum(
sum(diag(var(PAL_KMEANS_RESULT_TBL[PAL_KMEANS_RESULT_TBL$CL==0,2:3])*(sum(PAL_KMEANS_RESULT_TBL$CL==0)-1))),
sum(diag(var(PAL_KMEANS_RESULT_TBL[PAL_KMEANS_RESULT_TBL$CL==1,2:3])*(sum(PAL_KMEANS_RESULT_TBL$CL==1)-1))),
sum(diag(var(PAL_KMEANS_RESULT_TBL[PAL_KMEANS_RESULT_TBL$CL==2,2:3])*(sum(PAL_KMEANS_RESULT_TBL$CL==2)-1))))/
sum(diag(var(PAL_KMEANS_RESULT_TBL[,2:3])*(dim(PAL_KMEANS_RESULT_TBL)[1]-1)))
## [1] 0.2033599
Daraus ergibt sich ein etwas besseres – weil kleineres – Ergebnis für den R Algorithmus, da sich die Daten besser auf die jeweiligen Zentren konzentrieren.
In R wurde dem kmeans Algorithmus der Parameter nstart=20 mitgegeben. Der Algorithmus speichert intern 20 Ergebnisse und entscheidet sich für den Start mit dem besten Erklärungsfaktor (between_ss / total_ss = 0.798). Auf der Hana DB können dem kmeans Algorithmus ebenfalls Parameter mitgebeben werden. So steuert der Parameter init_type die Initialisierung der Anfangszentren. Nicht jedoch die Anzahl der Wiederholungen, um das beste Ergebnis zu erhalten. In dem obigen Beispiel wurde der Parameter auf 1 und damit auf die ersten k Beobachtungen festgelegt. Hier lohnt sich ein Blick in die Dokumentation, um mittels einer anderen Initialisierung bessere Werte zu erhalten, denn mit anderen Startwerten kann es andere Ergebnisse geben. Die Erklärung zu allen Parametern ist hier zu finden.
Wie bereits zu Beginn des letzten Abschnitts erwähnt, ist man auf der SAP Hana Datenbank dazu gezwungen, sehr strukturieren zu arbeiten. Es muss bereits vor dem eigentlichen Aufruf der Prozedur festgelegt werden, welche Tabellenstrukturen als Input und Output verwendet werden. Zudem müssen leere Tabellen erstellt werden, die erst durch den Aufruf der Prozedur gefüllt werden. In R ist die Handhabung deutlich flexibler. Ob das ein Vor- oder Nachteil ist, kommt auf den Verwendungszweck an. Durch die Flexibilität besteht jedoch die Möglichkeit, Schleifen zu bauen, um die optimale Anzahl an Clustern zu ermitteln. Die Frage sollte nicht lauten, ob R der Pal Library und damit der SAP Hana DB bevorzugt werden sollte. Sondern vielmehr sollte es ein Zusammenspiel werden, sodass jederzeit die Daten schnell in R visualisiert und analysiert werden können. Gleichzeitig sollte aber auch die Möglichkeit bestehen, die Daten strukturiert zu verarbeiten und speichern.
Kurzes Fazit zum Use-Case:
Die Kunden wurden in drei Gruppen eingeteilt. Damit diese Daten sinnvoll weiterverarbeitet werden, lohnt sich ein Blick in den C4.5 Algorithmus. Dieser Algorithmus orientiert sich an den Gruppenzuordnungen des KMEANS-Algorithmus und erstellt einen Entscheidungsbaum, sodass Kunden auf Basis ihrer Ausgaben in die Kategorie Bio, Nicht Bio, oder in keine der beiden Gruppen eingeteilt werden können. Hier geht es zum C4.5 Entscheidungsbaum-Algorithmus.
Classification - Entscheidungsbaum C4.5 Algorithmus
Während der Kmeans Algorithmus lediglich vorhandene Daten in k Cluster einordnet, verfügt der C4.5 Entscheidungsbaum Algorithmus die Fähigkeit Werte auf Basis der Cluster zu ermitteln, sodass entschieden werden kann, ab wann ein neuer Kunde tatsächlich einem Cluster angehört. In dem letzten Beitrag wurden die Trainingsdaten in R und auf der SAP HANA DB in drei Cluster eingeteilt, wobei es zu unterschiedlichen Ergebnissen kam. Zur Erinnerung:
Use-Case
Angenommen, es gibt einen Supermarkt dessen Produkte eindeutig in Bio- und Nicht-Bio-Produkte eingeteilt werden können. Zudem gibt der Supermarkt seinen Kunden einen “Rabatt” oder andere Vorteile, wenn diese sich eine Kundenkarte zulegen. Mittels der Kundenkarte können die Einkäufe eindeutig dem Kunden zugeordnet werden. Der Supermarkt möchte diese Kundendaten clustern und klassifizieren, sodass in Zukunft Kunden eindeutig in Bio- und Nicht-Bio-Kunden eingeteilt werden können, um darauf aufbauend gezielte Werbung schalten zu können.
C4.5 Entscheidungsbaum
An einem Entscheidungsbaum lassen sich Bedingungen ablesen, weshalb ein Strang des Baumes zu dem Ergebnis A führt und warum eine andere Bedingung zu einem möglicherweise anderen Ergebnis führt. Der Algorithmus gilt als Weiterentwicklung des ID3 der von Ross Quinlan entwickelt wurde. Der ID3 Algorithmus unterlag dem Problem, dass er weder mit fehlenden Daten noch mit nicht-diskreten Daten umgehen konnte. Diese Probleme führten dazu, dass der ID3 weiterentwickelt und uns heute als C4.5 Algorithmus zur Verfügung steht. Bei beiden Algorithmen geht es darum, dass diejenigen Spalten (Knoten) mit dem höchsten Informationsgehalt weiter oben in dem Baum auftauchen. Knoten die weniger Aussagekraft werden weiter unten platziert oder sogar ignoriert. Für eine händische Berechnung und der genauen Definition des Entscheidungsbaums empfehle ich das Buch Methoden wissensbasierter Systeme: Grundlagen, Algorithmen, Anwendungen (Computational Intelligence) von Christoph Beierle, Gabriele Kern-Isberner. ISBN: 978-3658270834.
C4.5 Algorithmus in R
Der C4.5 Entscheidungsbaum Algorithmus ist in der rpart Library definiert. Damit das Ergebnis visuell dargestellt werden kann wird zusätzlich die rpart.plot Library verwendet. In dem vorherigen Blogbeitrag wurden Daten erzeugt und diese Daten im Verhältnis 80:20 in Test- und Trainingsdaten aufgeteilt. Die Trainingsdaten befinden sich in der Variablen KMEANS_TRAININGSDATEN. Die Daten des Kmeans Algorithmus wurden in der Variablen k gespeichert. Das Ergebnis des letzten Beitrags war folgendes:
library(rpart) library(rpart.plot) k<-kmeans(KMEANS_TRAININGSDATEN,3,nstart = 20) plot(KMEANS_TRAININGSDATEN,col=k$cluster,pch =19,bty="n")
Die Cluster Informationen, die in der Variablen k gespeichert wurden, werden nun dem Datensatz KMEANS_TRAININGSDATEN hinzugefügt.
Das ist deswegen notwendig, da der Entscheidungsbaum eine Grundlage benötigt, um die Bedingungen aufzustellen.
KMEANS_TRAININGSDATEN$kmeans<-k$cluster
k$centers
## Bio Nicht_Bio
## 1 21.91786 18.86089
## 2 7.79000 42.37667
## 3 41.89419 11.37581
Werden die Zentren betrachtet, so kann festgelegt werden, dass das Cluster 1 eine Mischung aus Bio- und Nicht-Bio Kunden ist.
Im Folgenden mit “Sonstige” zusammengefasst. Durch das Zentrum 42.37 EUR für Nicht-Bio und 7.79 EUR für Bio-Produkte,
kann diese Personen Gruppe in “Nicht-Bio Kunden” und die letzte Gruppe in Bio Kunden umbenannt werden.
KMEANS_TRAININGSDATEN[KMEANS_TRAININGSDATEN$kmeans==1,3]<-"Sonstige Kunde" KMEANS_TRAININGSDATEN[KMEANS_TRAININGSDATEN$kmeans==2,3]<-"Nicht Bio" KMEANS_TRAININGSDATEN[KMEANS_TRAININGSDATEN$kmeans==3,3]<-"Bio Kunde"
Der Aufruf des eigentlichen Algorithmus ist wie bereits bei dem kmeans Algorithmus in R relativ einfach handhabbar.
Die Informationen werden in der neuen Variablen model_1 gespeichert und im Anschluss visuell dargestellt:
# c4.5 Algorithmus
model_1<-rpart(kmeans ~ Nicht_Bio+Bio,KMEANS_TRAININGSDATEN,method = "class");model_1
## n= 120
##
## node), split, n, loss, yval, (yprob)
## * denotes terminal node
##
## 1) root 120 64 Sonstige Kunde (0.25833333 0.27500000 0.46666667)
## 2) Bio>=30.785 31 0 Bio Kunde (1.00000000 0.00000000 0.00000000) *
## 3) Bio< 30.785 89 33 Sonstige Kunde (0.00000000 0.37078652 0.62921348)
## 6) Nicht_Bio>=31.63 29 0 Nicht Bio (0.00000000 1.00000000 0.00000000) *
## 7) Nicht_Bio< 31.63 60 4 Sonstige Kunde (0.00000000 0.06666667 0.93333333)
## 14) Bio< 14.9 7 3 Nicht Bio (0.00000000 0.57142857 0.42857143) *
## 15) Bio>=14.9 53 0 Sonstige Kunde (0.00000000 0.00000000 1.00000000) *
# Plot Entscheidungsbaum
rpart.plot(model_1,digits =3 )
Figure 1: Entscheidungsbaum in R
Die Tabelle KMEANS_TRAININGSDATEN besteht aus 120 Datensätzen. Wird die erste Zeile des Baums (.258, .275, .467) mit 120 multipliziert, so erhält man die absolute Anzahl an Kunden. Es ergeben sich 31 Bio Kunden, 33 Nicht-Bio Kunden und 56 Sonstige Kunden.
Von allen 120 Kunden haben 25.8 % mehr als 30.80 EUR für Bio Produkte ausgeben. Davon sind 100 % Bio-Kunden. Hier lohnt es sich besonders der Gruppe “Bio-Kunden” Bio-Produkte vorzuschlagen, da der Fehlerquotient bei 0 liegt.
Betrachten wir den dritten Ast “Nicht Bio” in der letzten Reihe. Der Kategorie gehören 7 Personen (5.8 %) an. Davon sind 57.1% Nicht Bio Kunden und 42.9 % Sonstige Kunden. Ein Kunde gehört dieser Kategorie an, wenn die Ausgaben für Bio Produkte größer als 14.90 EUR und 30.8 EUR nicht übersteigen. Zudem müssen die Ausgaben für Nicht Bio Produkte kleiner als 31.60 EUR sein. Wenn bei diesen Kunden Werbung geschaltet wird, muss klar sein, dass 42.9 % “Sonstige Kunden” diese Werbung fälschlicherweise erhalten. Als kurzes Fazit sollten die Bio Kunden – also alle Kunden die mehr als 30.80 EUR für Bio Produkte ausgeben – mit Bio Produkte beworben werden. Des Weiteren sollten alle Kunden die weniger als 30.80 für Bio Produkte und mehr als 31.60 für Nicht-Bio Produkte ausgegeben haben, Werbung erhalten, die möglichst wenig mit dem Thema Bio zu tun haben.
C4.5 Algorithmus mittels PAL Library
Ähnlich wie bei dem KMeans Algorithmus im vorherigen Beitrag, müssen auch für den C4.5 Entscheidungsbaum einige Vorbereitungen getroffen und wieder sehr strukturiert gearbeitet werden. Es müssen wieder Tabellen und Tabellenstrukturen angelegt, damit beispielsweise die Kontroll-Parameter für die spätere C45 Prozedur definiert werden können. Zusätzlich werden zwei Modelle benötigt (JSON und PMML). Anhand dieser Modelle wird ein Entscheidungsbaum mit Bedingungen erzeugt, der später für eine Vorhersage der Testdaten dienen soll.
Die Dokumentation zu diesem Algorithmus ist von SAP hier veröffentlicht worden.
create local temporary column table "SBBLOG".#PAL_CONTROL_TBL(
"NAME" VARCHAR (100),
"INTARGS" int,
"DOUBLEARGS" double,
"STRINGARGS" varchar(100));
CREATE TYPE "SBBLOG".C45_TRAININGSDATEN_T AS TABLE(
"BIO" DOUBLE,
"NICHT_BIO" DOUBLE,
"CLUSTER" varchar(1));
-----------------------------erstellen der Trainingsdaten für C4.5 INPUT-----------------------------
CREATE COLUMN TABLE "SBBLOG".C45_TRAININGSDATEN_TBL LIKE "SBBLOG".C45_TRAININGSDATEN_T;
INSERT INTO "SBBLOG".C45_TRAININGSDATEN_TBL (SELECT BIO,NICHT_BIO,CLUSTER FROM "SBBLOG".PAL_KMEANS_RESULT_TBL);
create local temporary column table "SBBLOG".#PAL_CONTROL_TBL(
"NAME" VARCHAR (100),
"INTARGS" int,
"DOUBLEARGS" double,
"STRINGARGS" varchar(100));
--Füllen der temporären Parametertabelle
insert into "SBBLOG".#PAL_CONTROL_TBL values('THREAD_NUMBER',2,null,null);
insert into "SBBLOG".#PAL_CONTROL_TBL values('SPLIT_THRESHOLD',0,1e-5,null);
-- Wenn Informations Ratio unter 0.006 dann stopp
insert into "SBBLOG".#PAL_CONTROL_TBL values('MAX_DEPTH',4,null,null);
-- Tiefe des Baums auf 4 reduziert
insert into "SBBLOG".#PAL_CONTROL_TBL values('MIN_RECORDS_OF_PARENT',2,null,null);
insert into "SBBLOG".#PAL_CONTROL_TBL values('MIN_RECORDS_OF_LEAF',1,null,null);
insert into "SBBLOG".#PAL_CONTROL_TBL values('IS_OUTPUT_RULES',1,null,null);
----------------------------Tabellenstrukturen für C4.5 OUTPUT--------------------------------------
CREATE TYPE "SBBLOG".PAL_C45_JSONMODEL_T AS TABLE(
"ID" INT,
"JSONMODEL" VARCHAR(5000));
CREATE TYPE "SBBLOG".PAL_C45_PMMLMODEL_T AS TABLE(
"ID" INT,
"PMMLMODEL" VARCHAR(5000));
----------------------------------(leere) Tabellenerstellung für C4.5 Output-------------------------
CREATE COLUMN TABLE "SBBLOG".PAL_C45_JSONMODEL_TBL LIKE "SBBLOG".PAL_C45_JSONMODEL_T;
CREATE COLUMN TABLE "SBBLOG".PAL_C45_PMMLMODEL_TBL LIKE "SBBLOG".PAL_C45_PMMLMODEL_T;
---------------------------------Tabellenstrukturen für Parametertabelle-----------------------------
CREATE TYPE "SBBLOG".PAL_CONTROL_T AS TABLE(
"NAME" VARCHAR (100),
"INTARGS" INT,
"DOUBLEARGS" DOUBLE,
"STRINGARGS" VARCHAR(100));
-----------------------------------Tabellenstrukturen für Prozedureaufruf----------------------------
CREATE COLUMN TABLE "SBBLOG".PAL_C45_PDATA_TBL(
"POSITION" INT,
"SCHEMA_NAME" NVARCHAR(256),
"TYPE_NAME" NVARCHAR(256),
"PARAMETER_TYPE" VARCHAR(7));
---------------------------------Füllen der Parametertabelle-----------------------------------------
INSERT INTO "SBBLOG".PAL_C45_PDATA_TBL VALUES (1, 'SBBLOG', 'C45_TRAININGSDATEN_T', 'IN');
INSERT INTO "SBBLOG".PAL_C45_PDATA_TBL VALUES (2, 'SBBLOG', 'PAL_CONTROL_T', 'IN');
INSERT INTO "SBBLOG".PAL_C45_PDATA_TBL VALUES (3, 'SBBLOG', 'PAL_C45_JSONMODEL_T', 'OUT');
INSERT INTO "SBBLOG".PAL_C45_PDATA_TBL VALUES (4, 'SBBLOG', 'PAL_C45_PMMLMODEL_T', 'OUT');
--------------------------Generierung der C4.5-Prozedur----------------------------------------------
call SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL','CREATEDTWITHC45','SBBLOG','PAL_C45_PROC',"SBBLOG".PAL_C45_PDATA_TBL);
------------------------------Aufruf des generierten C4.5-Algorithmus--------------------------------
call "SBBLOG".PAL_C45_PROC("SBBLOG".C45_TRAININGSDATEN_TBL, "SBBLOG".#PAL_CONTROL_TBL,"SBBLOG".PAL_C45_JSONMODEL_TBL,"SBBLOG".PAL_C45_PMMLMODEL_TBL) with overview;
variable | table |
---|---|
P3 | “SBBLOG”.“PAL_C45_JSONMODEL_TBL” |
P4 | “SBBLOG”.“PAL_C45_PMMLMODEL_TBL” |
Im folgenden SQL Statement wird das Ergebnis in der Variablen result gespeichert.
SELECT * FROM SBBLOG.PAL_C45_JSONMODEL_TBL;
Das JSON Model kann über R ausgelesen werden.
library("jsonlite")
fromJSON(result$JSONMODEL)
## $CurrentVersion
## [1] "VERSION1.3"
##
## $DataDictionary
## DataType ID Name OpType
## 1 Double 0 BIO Continuous
## 2 Double 1 NICHT_BIO Continuous
## 3 String 2 CLUSTER Categorical
##
## $Function
## [1] "Classification"
##
## $Transformation
## ID Intervals TransformationType DerivedDataType
## 1 0 15.295, 30.785 Discretize <NA>
## 2 1 9.155, 18.065, 25.355, 31.630 Discretize <NA>
## 3 2 NULL MapValues Integer
## DerivedValues OriginDataType OriginValues
## 1 NULL <NA> NULL
## 2 NULL <NA> NULL
## 3 0, 2, 1 String 0, 1, 2
##
## $TreeModel
## $TreeModel$AlgorithmName
## [1] "C4.5"
##
## $TreeModel$Child
## Child
## 1 1, 2, 4, 26, 0, 0, 2, 2, 1, 1, 0, 0, 0, 0, 2, 1, 0, 0, 0, 0, 0, 0, 4, 1, 0, 0, 0, 0, 26, 1, 0, 0, 1, 1, 1, 1, 1, 2, 3, 4, EQUAL, EQUAL, EQUAL, EQUAL, SimplePredicate, SimplePredicate, SimplePredicate, SimplePredicate
## 2 3, 16, 28, 6, 3, 1, 0, 0, 0, 2, 0, 0, 0, 0, 3, 1, 15, 0.9375, 0, 0, 1, 0.0625, 28, 1, 0, 0, 0, 0, 6, 1, 0, 0, 0, 0, 0, 0, 3, 1, 0, 0, 1, 1, 1, 1, 1, 0, 1, 2, 3, 4, EQUAL, EQUAL, EQUAL, EQUAL, EQUAL, SimplePredicate, SimplePredicate, SimplePredicate, SimplePredicate, SimplePredicate
## 3 NULL
## RecordCount Score
## 1 33 2
## 2 56 0
## 3 31 1
## ScoreDistribution
## 1 3.00000000, 0.09090909, 30.00000000, 0.90909091, 0.00000000, 0.00000000
## 2 49.00000000, 0.87500000, 3.00000000, 0.05357143, 4.00000000, 0.07142857
## 3 0, 0, 0, 0, 31, 1
## TreeNodePredicate.FieldPredID TreeNodePredicate.FieldValue
## 1 0 0
## 2 0 1
## 3 0 2
## TreeNodePredicate.Operator TreeNodePredicate.PredicateType
## 1 EQUAL SimplePredicate
## 2 EQUAL SimplePredicate
## 3 EQUAL SimplePredicate
##
## $TreeModel$RecordCount
## [1] 120
##
## $TreeModel$Score
## [1] "0"
##
## $TreeModel$ScoreDistribution
## [,1] [,2] [,3]
## [1,] 52.0000000 33.000 35.0000000
## [2,] 0.4333333 0.275 0.2916667
##
## $TreeModel$TreeNodePredicate
## $TreeModel$TreeNodePredicate$PredicateType
## [1] "TruePredicate"
Alternativ kann auch das PMML Model ausgelesen werden, welches deutlich lesbarer ist:
SELECT * FROM SBBLOG.PAL_C45_PMMLMODEL_TBL;
ID | PMMLMODEL |
---|---|
0 | (BIO>=30.785) => 1 |
1 | (BIO<15.295) && 9.15499=<NICHT_BIO<18.065) => 0 |
2 | (BIO<15.295) && 18.065=<NICHT_BIO<25.355) => 0 |
3 | (BIO<15.295) && 25.355=<NICHT_BIO<31.63) => 2 |
4 | (BIO<15.295) && (NICHT_BIO>=31.63) => 2 |
5 | 15.295=<BIO<30.785) && (NICHT_BIO<9.15499) => 1 |
6 | 15.295=<BIO<30.785) && 9.15499=<NICHT_BIO<18.065) => 0 |
7 | 15.295=<BIO<30.785) && 18.065=<NICHT_BIO<25.355) => 0 |
8 | 15.295=<BIO<30.785) && 25.355=<NICHT_BIO<31.63) => 0 |
9 | 15.295=<BIO<30.785) && (NICHT_BIO>=31.63) => 2 |
Fazit
Zum einen wird aus der Tabelle klar, dass die Datensätze dem Cluster 1 zugeordnet werden, wenn die Ausgaben für Bio-Produkte 30.785 EUR übersteigen. Was jedoch nicht klar wird, ist die Anzahl der Datensätze die fälschlicherweise in diesem Ast gelandet sein (Vergleiche dazu den Entscheidungsbaum in R Abbildung 1). Damit hier eine Aussage getroffen werden kann, müssen die Daten weiter analysiert werden. Da jedoch die Testdaten das bessere Ergebnis liefern, sollten der Entscheidungsbaum auf die Testdaten angewandt werden. Die Predictive Prozedur ist Thema des nächsten Beitrags.
Predictive auf Basis der Cluster (kmeans) und dem Entscheidungsbaum (C4.5)
In dem ersten Beitrag wurde eine Verbindung von R zur Hana Datenbang hergestellt, um im zweiten Beitrag Pseudo Daten zu erstellen und diese Daten in Gruppen einzuteilen. Diese Gruppen wurden im dritten Beitrag dafür verwendet, um einen Entscheidungsbaum zu erstellen. Dieser Entscheidungsbaum, wird nun als Grundlage dienen, um mittels der Testdaten eine Vorhersage der Gruppe zu bestimmen. Diese Information könnte dann beispielsweise dafür verwendet werden, um einem Kundenkreis A andere Werbung oder Produkte vorzuschlagen, als dem Kundenkreis B.
Zur Erinnerung, der KMEANS Algorithmus in R lieferte folgendes Ergebnis für drei Gruppen.
plot(KMEANS_TRAININGSDATEN,col=k$cluster,pch =19,bty="n")

Der Entscheidungsbaum lieferte aufbauend auf das KMEANS-Ergebnis.
library("rpart")
library("rpart.plot")
# c4.5 Algorithmus
model_1<-rpart(kmeans ~ Nicht_Bio+Bio,KMEANS_TRAININGSDATEN,method = "class")
# Plot Entscheidungsbaum
rpart.plot(model_1,digits =3 )

Entscheidungsbaum in R
Predictive
Wie eingangs erwähnt, wird auf Basis des Entscheidungsbaums eine Vorhersage getroffen. Letztendlich geht der Algorithmus nur die erstellten Bedingungen durch und entscheidet anhand dieser Bedingungen, in welche Gruppe dieser Datensatz einzuordnen ist. ## Predictive in R Betrachten wir zunächst die Testdaten, so erhalten wir 10 Bio Kunden, 11 Nicht Bio Kunden und 9 Sonstige Kunden. In der Realität könnten diese “richtigen” Ergebnisse beispielsweise durch eine Umfrage ermittelt werden.
table(KMEANS_TESTDATEN[,3])
##
## Bio Nicht Bio Sonstige
## 10 11 9
Lassen wir nun die Testdaten durch den erzeugten Entscheidungsbaum laufen, welcher in der Variablen model_1 gespeichert wurde, so ergibt sich zunächst folgendes Ergebnis:
p<-predict(model_1,KMEANS_TESTDATEN,type = "class");p
## 1003 1004 1014 1016 1018
## Bio Kunde Nicht Bio Sonstige Kunde Sonstige Kunde Sonstige Kunde
## 1023 1038 1045 1048 1049
## Nicht Bio Bio Kunde Bio Kunde Sonstige Kunde Bio Kunde
## 1053 1057 1066 1074 1078
## Bio Kunde Nicht Bio Sonstige Kunde Bio Kunde Nicht Bio
## 1090 1106 1108 1116 1119
## Nicht Bio Nicht Bio Sonstige Kunde Sonstige Kunde Bio Kunde
## 1120 1128 1132 1135 1139
## Sonstige Kunde Nicht Bio Nicht Bio Nicht Bio Sonstige Kunde
## 1140 1142 1148 1154 1172
## Bio Kunde Sonstige Kunde Nicht Bio Nicht Bio Sonstige Kunde
## Levels: Bio Kunde Nicht Bio Sonstige Kunde
Damit dieses Ergebnis angenehmer auszuwerten ist, lohnt es sich die neu erlangten Informationen dem Datansatz der Testdaten hinzuzufügen.
KMEANS_TESTDATEN[,4]<-p
Dadurch können die ermittelten Daten mit den korrekten Daten verglichen werden:
tab<-table(KMEANS_TESTDATEN[,3],p);tab
## p
## Bio Kunde Nicht Bio Sonstige Kunde
## Bio 8 0 2
## Nicht Bio 0 11 0
## Sonstige 0 0 9
Fazit: Es wurden zwei Bio Kunden fälschlicherweise in die Gruppe Sonstige einsortiert, alle anderen Kunden wurden korrekt zugeordnet, sodass sich insgesamt ein Fehlerquotient von 6.6 % (2 von 30) ergibt.
Predictive mittels Pal Library SAP HANA DB
CREATE LOCAL TEMPORARY COLUMN TABLE "SBBLOG".#PAL_CONTROL_TBL(
"NAME" VARCHAR (100),
"INTARGS" INTEGER,
"DOUBLEARGS" DOUBLE,
"STRINGARGS" VARCHAR (100));
------------------------------füllen der Tabellen für Predictive INPUT ---------------------------
INSERT INTO "SBBLOG".#PAL_CONTROL_TBL VALUES ('THREAD_NUMBER', 2, NULL, NULL); -- die Anzahl paralleler Threads (Prozesse)
CREATE TYPE "SBBLOG".PAL_PRED_JSONMODEL_T AS TABLE(
"ID" INT,
"JSONMODEL" VARCHAR(5000));
--------------------------------leere Tabelle für Predictive Output------------------------------
CREATE TYPE "SBBLOG".PAL_PRED_RESULT_T AS TABLE(
"ID" INT,
"CLUSTER" VARCHAR(50));
-----------------------------leere Tabelle für Predictive Output-----------------------------
CREATE COLUMN TABLE "SBBLOG".PAL_PRED_RESULT_TBL LIKE "SBBLOG".PAL_PRED_RESULT_T;
---------------------Tabellenstrukturen für Parametertabelle----------------------------------------
CREATE TYPE "SBBLOG".PAL_CONTROL_T AS TABLE(
"NAME" VARCHAR (100),
"INTARGS" INTEGER,
"DOUBLEARGS" DOUBLE,
"STRINGARGS" VARCHAR (100));
---------------------------Tabellenstrukturen für Prozedureaufruf----------------------------------
CREATE COLUMN TABLE "SBBLOG".PAL_PRED_PDATA_TBL(
"POSITION" INT,
"SCHEMA_NAME" NVARCHAR(256),
"TYPE_NAME" NVARCHAR(256),
"PARAMETER_TYPE" VARCHAR(7));
------------------------------füllen der Tabellen für Prozedureaufruf INPUT------------------------
INSERT INTO "SBBLOG".PAL_PRED_PDATA_TBL VALUES (1, 'SBBLOG', 'KMEANS_TESTDATEN', 'IN'); -- Struktur der Testdaten
INSERT INTO "SBBLOG".PAL_PRED_PDATA_TBL VALUES (2, 'SBBLOG', 'PAL_CONTROL_T', 'IN'); -- Struktur der Control Parameter
INSERT INTO "SBBLOG".PAL_PRED_PDATA_TBL VALUES (3, 'SBBLOG', 'PAL_PRED_JSONMODEL_T', 'IN'); -- Struktur des JSON-Models?!
INSERT INTO "SBBLOG".PAL_PRED_PDATA_TBL VALUES (4, 'SBBLOG', 'PAL_PRED_RESULT_T', 'OUT'); -- Struktur des Outputs
---------------------------Generierung der Predictive-Prozedur------------------------------------
CALL SYS.AFLLANG_WRAPPER_PROCEDURE_CREATE('AFLPAL', 'PREDICTWITHDT', 'SBBLOG', 'PAL_PREDICTWITHDT_PROC', "SBBLOG".PAL_PRED_PDATA_TBL);
Als Ergebnis des Prozedureaufrufs erhält man als Information, dass die Tabelle PAL_PRED_PDATA_TBL gefüllt wurde.
------------------------------Aufruf des generierten Predictive-Algorithmus------------------------
CALL "SBBLOG".PAL_PREDICTWITHDT_PROC("SBBLOG".KMEANS_TESTDATEN, "SBBLOG".#PAL_CONTROL_TBL, "SBBLOG".PAL_C45_JSONMODEL_TBL, "SBBLOG".PAL_PRED_RESULT_TBL) WITH OVERVIEW;
variable | table |
---|---|
P4 | “SBBLOG”.“PAL_PRED_RESULT_TBL” |
Die eigentliche Ergebnistabelle liefern dann die Kundennummer mit dem zugehörigen Cluster:
--Prognose ausgeben
SELECT * FROM "SBBLOG".PAL_PRED_RESULT_TBL
DROP TABLE "SBBLOG".PAL_PRED_RESULT_TBL2;
Damit die Ergebnisse besser auszuwerten sind, wird eine neue leere Ergebnistabelle erstellt.
CREATE COLUMN TABLE "SBBLOG".PAL_PRED_RESULT_TBL2(
"CLUSTER_BAUM" int,
"BIO" DOUBLE,
"NICHTBIO" DOUBLE,
"KUNDENNR" INT );
Diese Tabelle wird mit den Informationen aus der ersten Ergebnistabelle (die Cluster), sowie die zugehörigen Bio- und Nicht-Bio-Ausgaben angereichert.
Durch die Zuordnung der Kundennummer, werden die Daten korrekt zugeordnet:
-- Fuellen der PAL_PRED_RESULT_TBL2
INSERT INTO "SBBLOG".PAL_PRED_RESULT_TBL2(
SELECT PAL_PRED_RESULT_TBL.CLUSTER,KMEANS_TESTDATEN."Bio",KMEANS_TESTDATEN."Nicht_Bio",KMEANS_TESTDATEN.KUNDENNR
FROM "SBBLOG".PAL_PRED_RESULT_TBL
INNER JOIN "SBBLOG".KMEANS_TESTDATEN
ON "SBBLOG".PAL_PRED_RESULT_TBL.ID = "SBBLOG".KMEANS_TESTDATEN.KUNDENNR);
Das Ergebnis der PAL_PRED_RESULT_TBL2 Tabelle wird in der Variablen “trials” gespeichert, welche im darauffolgenden R-Code verwendet wird.
-- Ergebnis
Select * from "SBBLOG".PAL_PRED_RESULT_TBL2 ORDER BY KUNDENNR ASC
trials[trials$CLUSTER_BAUM==1,1]<-"Bio Kunde"
trials[trials$CLUSTER_BAUM==0,1]<-"Sonstiger Kunde"
trials[trials$CLUSTER_BAUM==2,1]<-"Nicht - Bio Kunde"
# Ergebnis SAP HANA PAL Library
tab<-table(KMEANS_TESTDATEN[,3],trials[,c(1)]);tab
##
## Bio Kunde Nicht - Bio Kunde Sonstiger Kunde
## Bio 8 0 2
## Nicht Bio 0 11 0
## Sonstige 0 0 9
# Ergebnis in R
tab<-table(KMEANS_TESTDATEN[,3],p);tab
## p
## Bio Kunde Nicht Bio Sonstige Kunde
## Bio 8 0 2
## Nicht Bio 0 11 0
## Sonstige 0 0 9
Fazit
Die Ergebnisse für die Vorhersage in R und auf der Hana Datenbank sind insofern identisch, dass die Zuordnung der 30 Testkunden gleich ist. Beide Ergebnisse lieferten in der Diagonalen 8 Bio Kunden, 11 Nicht-Bio Kunden und 9 Sonstige Kunden. Beide Ergebnisse lieferten ebenfalls zwei falsche Werte für zwei Sonstige Kunden. Dennoch unterscheiden sich die beiden Ergebnisse im Detail. Beispielsweise wurde im ersten Beitrag über den KMEANS-Algorithmus darauf aufmerksam gemacht, dass die Ergebnisse R / Sap Hana DB etwas unterschiedlich sind. Dies war auf die Startinitialisierung beider Algorithmen zurückzuführen. Dadurch, dass bereits das erste Ergebnis unterschiede aufwies, konnte der C4.5 Algorithmus gar nicht mehr die gleichen Ergebnisse liefern. Insofern war es nicht verwunderlich, dass die beiden Algorithmen tatsächlich unterschiedliche Bedingungen lieferten, denn der Entscheidungsbaum baut auf das vorherige Ergebnis (KMEANS) auf. Auf Grund der Anzahl an Testdaten haben diese Auswirkungen auf die letztendliche Vorhersage keinen Einfluss, sodass am Ende dennoch die gleichen Ergebnisse reportet werden.
Kommentare (0)