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 continue our
look at clustering using K-Medoids. Now in
the previous video we saw the design
time aspect. So we build the Flowgraph
that we can see here. Now what we'd want
to do at runtime is to go ahead and run
the procedure. Now we'll find the
procedure that was created in the
procedures folder in Database Explorer.
We'll find one procedure for each
individual node that we've got but we'll
also find that we've got a procedure
which is the name of our entry
underscore SP and this is the one that
will cause the whole process. So that's
the one we want to go to. So we're going
to right mouse button. Click generate
call statement and it generates an
example call that we can use to actually
call that procedure. So let's go ahead.
Let's call it. Do our clustering and it's
only a small amount of data in this
simple example but the clustering is
basically finished and if we go to the
results and now we refresh you'll see
that we've got 50 rows and for every ID
we've got an assigned cluster. Now the
cluster ID when we run this function is
going to be from 0 upwards. So it starts
at 0. We asked for three clusters. So we
should find we've got three different
values. 0, 1 and 2 and we've got the
distance from the K-Medoids center
of the cluster. The mathematical center
of the cluster based on those four
columns we actually did the clustering
by. Now if we go to the center points
this actually just tells us what the
center points are for each cluster. We
refresh it. Now we've got procedures run.
We've got some data. We can see we've got
three rows. One for each cluster and
we've got the center points for each of
the four different clustering variables
that we worked with. So that's looking
very, very nice. Now what we might want to
do now is something a little bit more
elaborate which is that we would want to
maybe get the original customer data and
join it together with this, the cluster
ID. So that we could maybe do some
analysis. So let's go ahead and look at
how we can do that. It’s actually very
simple. We can just create a Calc View. So
if we go back to our project and choose
SRC,
source, new and then calculation
view. We can give it a name. For example
CustomersClustered and the data
category here it's not going to be an
OLAP, Cube or anything it's just a
default. We know we don't want actually
aggregation here. We're going to be
doing a join. So we're going to hit
create for the new Calc View and it
opens up the Calc View editor and
what we want to do is do a join. So hit
create join. Will drop that down the
bottom and then use the plus sign wants
to get our original customers’ data. So if
we type CU again will find our
customers table in our HDI Container. So
that's the original customer data. Hit
the plus sign again and this time we
want to have our results. So if I put in
res we should find out where our results
come. Yeah that's our results table. So
add that one and we're going to join the
two based on the ID column. So I can drag
from ID from one to the other. So we've
made a join based on ID and we now need
to do the mapping. Which columns do we
want to have. So for the mapping what I'm
going to do is take everything from the
customers table. Okay and we're going to
just initially take everything from the
results table. So let's drag that over as
well. Okay and then the one thing that's
duplicated is this ID. You can see it's
called it id_1 because it's
the second version of the ID. That one we
can just right mouse button and say
remove output column and it will unmap
it and remove that from the output. So
that way we've got the cluster ID and
distance added as output columns. So now
we've done that we need to project it. So
let's take the little arrow move that
into our projection and then in the
projection we need to select. Let’s just
pick everything. Obviously you could
filter which columns you really want to have
projected out but here we've got all of
the ones here and then we're done. So we
can save it and of course we need to
build it. So let's build selected files
to build our Calc View.
Okay our Calc View is built and now we've
built it we can go back to the Database
Explorer and within our HDI Container
we go to the column views and in column
views now we see we've got PALapp DB
customers clustered as the name of our
Calc View and if we open data on it we
should see that we've got the ID and
we've got the name, lifetime, new spend,
income and loyalty plus the cluster that's
been assigned. Plus the distance from the
center of the cluster. So we can do more
analysis. Now what's nice with Web IDE for
HANA in SPS 02 is that we've got
the analysis tab in the Database
Explorer. So if I hit that one what I can
do for example as a quick analysis maybe
count how many people were assigned to
each cluster. So I can take the cluster
ID as my label and I can choose the name
which is non numeric. So that one should
default as a value to counting because
you can't obviously can't add up numerics.
So in fact you can see that here it has
assigned that to be a count function. So
we can see that we've got three clusters
zero, one and two and we've got just over
ten for cluster zero. Twelve of them. We’ve got
thirty three of them in cluster one and
we've got five of them in cluster two.
So that's interesting but what we might
also want to do is maybe just look at
how these different things have
contributed like lifetime, spend, income
and loyalty. So for that I could just
drag lifetime spend, income and loyalty
across and just change that to being a
bubble chart and now we can see we've
got…ah okay. In fact if we scroll right
we've got a bit more space we can see
that we've got the cluster is basically
the number. So we've got 0, 1 and 2 and then
we've got the size based on loyalty and
you can see the scale for the size of
the bubbles and then we've got the
position based on income and lifetime
spend. Of course you can mess around. You can
move these about and get maybe a
different type of view depending on what
it is you actually want to look at in
what different way.
So that's quite interesting thing to do.
Now what we could also do is maybe plot
every data point and that's actually
quite easy to do as well. All we need to
do is maybe remove for
example lifetime spend. So we've got
loyalty and income and what we'll do is
we'll take the ID and add it to the
labels. So within the cluster we can now
see all the values. Now we don't want to
see it as a chart what we want to do is
see that as a scatterplot. So now we can
see all the individual values. You can
see actually…yeah…this green one is…we
can see individual customers and where
they fit mathematically within our
clusters and that's cluster one and we
got cluster two and we've got cluster
three. Which seem to be fairly close. So
maybe the clustering hasn't worked too
well based on income and loyalty.
Although it does seem to be a fairly
linear relationship that the more loyal
you are then the more money you seem to
be able to earn. Okay so that's quite
interesting. Now what we might want to do
in the real world is tune this a little
bit more. We might want to change the
number of clusters we create for example.
Now I'll show you a couple of ways of
doing that. What we can do is we can just
go straight into the config for our
K-Medoids node go to the parameters and
just make a change. For example we might
want to set it to the four different
clusters. So if we do that we can apply
it. We can save it. We can build the
Flowgraph and what it will do. It will
actually replace, quite neatly, it will
replace whatever was already there.
So in this case all the output data we
have from our previous runs will be lost.
In fact if we go back to look at our
analysis that we've got here and we
refresh it you'll see that it's empty
because all that data we had from the
previous run has been lost. So what we
need to do is go ahead and run the
procedure again. Okay now we've run it. If
we refresh it we should be able to see
that we've got some good data for
analysis. Again we can just go we can put
cluster ID and the ID with lifetime
spend and new spend as a scatter plot.
Okay so maybe that's one example
actually I think we were using income
and loyalty, previously. So let's do that
with income and loyalty as a scatter
plot. Okay so now we can see with four
clusters for example
it's looking a little bit different. So
we got from zero one, two, three and
different colors. We’ve got it clustered
slightly differently. So it seems that
this main cluster here has been kind of
split into two. Now that will work fine
during development if you want to tweak
the parameters as you're developing but
in the real world when you're running
stuff you're not going to want to have
to go in and build and you won't be able
to build the project itself. What you'll
simply want to do is to tweak what's in
that parameter table. So that's very easy.
You can just use the Database Explorer.
Here you can just, in your app where
you're going to integrate and embed your
code to call the procedure you can just
insert some SQL as well. So for
example we can have an update statement
and then we'll need the table name. So
for that will go to our tables and it's
the parameter table. So we'll drag and
drop that over. So we'll update that
table. We’re going to set one of the
columns which is called INTARGS because
it's an integer value. You'll see that if
you look at the structure of the table.
Put that in double quotes. Now what
number of customer clusters should we
have? Let's go maybe with two customers’
clusters this time and then we need to
add a where clause on it. So which one is
it and it's where the name, which is
basically the ID of the parameter is
equal to and if you remember it's GROUP
_NUMBER. So you can tweak what
with any parameter that you like. So
let's go ahead do that update and run
this procedure again and now this time
if we go back and we look at it we can
see here we had four clusters. If we now
refresh it. You can see now we've just
got two actual clusters. So that's what
you would do when you're running more in
production or actually within your
application. You just simply update your
parameter table with whatever values you
need for any of the parameters and then
just make sure you call your clustering
again. So what we've seen in this video
is how we can do a more complex example
using the Application Functional
Modeler. We’ve done K-Medoids kind of
clustering. What we've seen is how we can
customize parameters. We’ve seen how we
can do further analysis and we've seen
how we can
work with having a variable number of
columns. In this case the four columns to
be clustered. How we actually work
through that when we're doing our
Flowgraph entry. So that's the K-Medoids
clustering. 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.