Hi, I'm Philip from the HANA Academy. In
this series of videos were looking at
HANA’s Application Function Modeler. In
this video we're going to look at
clustering using the K-Medoids function.
Now it's a slightly more complex example
to what we saw in a previous video with
data summary. We’re going to customize
parameters and we'll also see how we can
create a view at the end to aid our
analysis. Now the first thing we need to
do is make sure we've got PALapp as an
application up and running. So make sure
you've run the getting started video and
refer to the data summary video to see
how we can clone the PALapp from the
HANA Academy GitHub repository. Now you
need to make sure that you've built the
database module in PALapp at least
once before we actually go ahead and do
anything more. So let me just go ahead
now and choose that build. We’ll build
that database module. So that just took a
couple of seconds. It’s created our HDI
container. What we can then do is refer
to that and actually have a look inside
it using the Database Explorer. Again see
the data summary video for more details
on getting familiar with the Database
Explorer here. We go to the tables and we
see that we've got two tables. One claims
and one called customers and in this
case it's actually the customers table
that's interesting to us. We’ve got about
50 customers and we've got for each
person who's a customer - one row and
we've got four bits of information. We’ve
got lifetime spend, new spend over the
last quarter, their income ratio and also
a loyalty index, as well. So we've got
data for customers. Now what we want to
do is try to cluster, to group these
different customers together. Maybe to
create two, three, four or five different
segments or clusters or groups of
customers based on the life spend, new
spend, income and loyalty. So that's what
we're gonna do. Now to get started what
we need to do is create a Flowgraph. So
we'll go to our PALapp, the database
module and then the SRC folder. Right
mouse button, choose new and then Flowgraph
Just like we did with data summary.
So for Flowgraph we can put in for
example clustering and it will take us
into the Flowgraph editor. I'll just
make the screen
little bit bigger by turning off the
console. So once we're in here we can add
our clustering function. So we'll hit
the big plus sign and choose Predictive
Analysis from the functional area. Drop
that somewhere in the middle and we want
to do clustering. So we can see there's a
clustering section here and we've got a
number of different algorithms and we
could use Accelerated K-Means, regular
K-Means or other options. With this case
we're going to go with K-Medoids and
so for K-Medoids you can see that
we've got two inputs and two outputs. So
we're going to have an input table which
is the customers table plus some
parameters and then as outputs we're
going to get something called a result
table and the center points table. We can
see here these are the different
parameters that will be needed for the
algorithm. So we get lots of metadata. So
let's hit OK. Now that setup we need to
point it to some source data. So what
we've got that customers table we were
just looking at. The fact we've built it
means it now physically exists. So let's
hit the plus sign and choose data source
and I'm going to drop my data source on
the left of my predictive analysis node
and click the config option and this is
where I can now refer to an existing
HANA table. In my case it's going to
be HANA object and if I put in Cu for
customer
it will search and we'll find, yep that's
where our customers table is in that HDI
container. So I'll select it and click
finish. Now if we have a quick look
we’ll see that we get all of the
columns from our data included and it
knows what data types they are etc. Now
what we don't want to have here is the
name because actually we only supposed
to have the the ID plus then the values
we're going to cluster on. The name is
not relevant here. So we don't have to
create a view or traverse through
the data or make any changes we can
just simply remove it here and it will
basically be filtered out. That column
will be removed actually as the data is
loaded in for our procedure for us. So
let's hit apply.
So we've now set up our data source and
we can drag and drop from data source to
the data part of our node and we can see
that's done it all, there's no red
errors. So we're looking
good. What we can now do is review the
data. If we go to the config option of
our main node you'll see that we've got
data parameters as inputs and for data
you'll see that it's now
created a conversion. It’s going to work
in this function with an ID and four
types of feature and you can see that
it's actually got a mapping from our
input columns into the ID and features
that's going to be used in the function.
Now notice that the features is what's
known as a multi column because you can
have one or more columns that you want
to cluster by. So the Application
Function Modeler that takes that into
account and allow you to put in as many
columns as you need is that you have in
your data basically when you want to use
function like the K-Medoids or many
other functions work the same way. Now
the second thing we'll want to look at
are the parameters. Now we get a set of
parameters with descriptions and with
default values. Well what we're going to
do is make sure we set the parameter
called the group number. We should say
how many clusters do we want to create.
So I think we'll go with three and there
are some parameters that we're not
really relevant. Like for categorization
that we don't need. So for those
categorizations and also there's a
normalization that we don't really need
at this point we can go ahead and remove
those. So let's just scroll up you can
hit the trashcan and so that any
parameters we don't necessarily need we
can remove. Of course you can always come
back press the plus button and add them
in back later should you decide you do
need them.
So that's now tuned our parameters. The
next thing to do is to look at the
results. The output tables. So we're going
to a result table and you can see here
that it's actually assigned a default
for the ID of the result as VARCHAR
because you can have strings or
intergers or whatever for this
algorithm. Now as our input data actually
uses integer probably makes sense that
we just changed that here to integer. Now
for the assigned cluster column that's
the cluster that's going to be assigned.
We can see that's integer. That's fine
and for the distance from the center of
the cluster for this particular ID or
this particular customer is a double. So
that's fine. We can look at the center
points. Now here we can see we've got the
cluster ID
which is set as integer. Just like in
result. That's fine and we've got the
centers. Now as there are four features
in our algorithm here we need to make
sure we've got four centers and we need
to make sure they have a suitable data
type. In our case it's going to make
sense that we make those double values.
Now we've got one set up but we've got
actually four. We’ve got a lifetime spend,
new spend, income and loyalty. So we'll
need to just go and add three more. So
we'll go and add one and we'll do it
again and we'll add one more and you can
see it's given us automatic numbers and
we'll just need to set those to be
double. So now we're set up with the
right kinds of columns. Now we're
actually you're not going to name those
because that naming process might, just
gonna happen when we actually write to
tables. Here we're just running the
algorithm. So we're good with that one. We
can hit apply. We’ve done the main
configuration for this node but we do
now finally need to do is to set up the
targets. Where we're going to write the
results to and the center point to. For
that we can use a table. We can use the
data Target option in the Flowgraph and
what this will allow us to do is to
actually have a create user table that
exists already or create a table
automatically. So we'll first we'll drag
and drop from result to data target. Notice
the yellow. Green means it's a good
place we can drag and drop to. Let go of
the mouse and that looks to be a good
connection and now we can configure this
data target. Now the fact we made the
connection means it now already knows
what the structure of the table is going
to be and we've got name. So for example
if we want to just change maybe have the
ID and change it to clusterid is the
name and distance use mixed case or
lower case in our columns. We can do that but
the data types and the actual order and
columns we have has been set up already
earlier in the function. So we can do
that in terms of the structure but we
also need to say where we're going to
write this to and if we use the
template table option it means it will
create a table automatically for us if
it doesn't exist and so for that we can
typically would use the project name. So
PALapp. The module name, DB : :
and then some kind of scope. For that we
could maybe say clustering and then this
one would be the results table. I’m just
going to copy some of that because we're
gonna do something very similar for the
center points table as well in a moment.
The final thing to do is the settings
and if we're going to rerun this
procedure multiple times it's going to
insert new rows to the results table. We
may not want to keep the old results. So
what we can do is say truncate, truncate
table. So that every time we run this it
will actually replace what rows are
already in there. So it'll just allow us
to be a little more coherent. So let's
hit apply. Now we need to do basically
the same job for the center points. So
let's go back and create a data target
for the center points drag and drop to
connect the two together. Hit the
configuration option. Now we can
customize. For example we've got these
column names. So again we can go with
cluster ID and then for Centers actually
this one would be life spend. That was
our first one. The second one would be
new spend. We would also have the income
and finally loyalty but the types and
the number of columns we need is all
good and setup. Of course you don't need
to keep all the columns you can just
delete them if for some reason you don't
need them. Let's hit apply and we're
basically good to go. So the next thing
we need to do is just go back and
remember to set the target as a template
table. So for that I'm gonna paste in
what we had before dot and we can call
this one CenterPoints for our output
data and we can go to the settings and
say truncate table. Same as we did with
the results. So now we should be good to
go. What we need to do is save our Flowgraph
and we can go ahead. Let's just
select it and now we're gonna go ahead
and build that. So we can just build that
Flowgraph itself. Not the whole project
necessarily. So let's do build selected
files. So we can turn that from this
design turn artifact into a runtime
artifact. Meaning it will
actually create the underlying tables
and stored procedures that we need but
without us having to write any code at
all. So the build has finished. Now if we
go back to our HDI Container with the
Database Explorer now notice that the
tables has a results table, a center
points table and also a parameter table
and if we just open data, for example, on
the parameter table you'll see just
the parameters we chose including that
initial value we set for group number
they've been populated in that table. So
we created the table and set up all the
values for us without having to write
anything explicitly and you'll find that
there's a results table. If we open that
and the center points table. They
exist but there are no rows in them
right now because we're not actually run
the procedure we've just created the
structures. So now we finish the design
phase. We’ve basically built our Flowgraph
here. What we're gonna do in the
next video is actually see how we can
run it. So we'll look at the runtime
aspects and how we can do further
analysis. You can find more HANA video
tutorials on our YouTube channel. If
you'd like to be informed as soon as new
video tutorials are published please
subscribe to the channel. You can also
connect with us on LinkedIn, follow us on
Twitter
and we have pages on Facebook and Google+.
Code snippets and demo data for all
of our tutorials are published to GitHub.
Thanks for watching.