Hi, I'm Philip from the HANA Academy.
In this series of videos, we're looking at the Graph processing capabilities native to SAP HANA.
In this video, we're going to look at the Shortest Path algorithm, but using it from XS Advanced in WebIDE
using, effectively, the calc view modeller.
So let's go to WebIDE for HANA.
Now, we can see we've got a project that's basically the same project that we've already set up in previous videos when we were working with XS Advanced.
We can see that we've got a CDS, so we can just have a quick look at that to review.
We've got our members and relationships tables set up.
We can also then see that we've got a Graph workspace, so a .hdb Graph workspace entry
which is the same syntax we would basically use if we were doing this in classic SQL script
except we have got the 'Create' part of the 'Create Graph Workspace' missing here because it's obvious
and also the names are using HDI container table and column names
which is different from when we're working with classical schemas
and Graph workspace names and tables' names.
So, that's basically the core project.
Once we've done that and we've actually loaded some data with some 'insert' statements
again, that's all in a previous video
we can use the new option within our HDI container and go for 'Calculation View' to open up the calc view modeller.
For example, we can call this one 'shortestPath'.
Now, we would want to choose the data category of 'Default', because we're not doing OLAP analysis here,
and then you'll see that we've got a nice calc view modeller screen
and you'll see that we've got the Graph node.
Now, the Graph needs to be a leaf node, so basically something on the edge
so I'm going to add a new leaf node for the graph.
I'm going to connect it to our Graph workspace, so we're going to choose the plus sign to add data source
just put a couple of letters in to give us chance to find it.
So this is our Graph workspace, I'll select that.
Once we've made that connection, now we can start looking at how we want to actually go ahead and do our analysis.
Now, if we want to work with Shortest Path then we can go ahead and just choose that from this list of possible actions
the main key algorithms that we've actually got available within our graph processing in HANA.
So, don't worry about the message, that was just because it defaulted to 'Neighborhood'.
Now, with a Shortest Path we need to choose the starting point, so in our case we'll choose a fixed value
so we can, for example, start with Uranus
so that'll be our starting point, and if you want you can select a column that will be the weight column
so what this basically means is, for example
if your vertices are airports and your edges, your connections, are flights between different airports
and you want to find the shortest way from Airport A to Airport D
which cannot be connected to directly
you have to go connect through different airports
the weighting could actually be the number of miles or kilometres between each airport.
That would allow you to find the shortest path not only in the number of connections
but also in the number of miles in total.
So, weighting is also an option.
In our case, we've just got a simple hierarchy.
We're not really working with a full network like that in our graph structure
so it makes little sense to do any weighting at this point.
You'll see that it's populated an area called 'Columns'
and we've got a couple of choices about which output, because you could have, potentially, two output sets.
So we're probably going to go with the first one, the name and the weight value.
So if we do that, we're then going to select this little arrow and drag it into our projection.
That allows us to project, and if we want to we could actually drag and project another one as well for that second output set that we just saw.
And then within the graph
we need to make sure that we actually take the columns and convert these to output columns
or do whatever you want to do, because this is calc view modelling.
You can do absolutely anything you want once you get the data out of Graph
you can materialise it, transform it, do whatever you want with it.
So that's pretty much what we want to do.
Let's go ahead and save that, and we would now go ahead and build.
We can just build this one calculation view on its own.
So, the build has completed successfully, we get that message, that's a good sign
so we can go ahead and try to use it
our Shortest Path calc view
so we can go to our database explorer and within there, we can make sure that we try to connect.
Now, I've already got it here
otherwise you would use the plus sign to connect to the HDI container that you're working with.
I've already connected, and that way you can then actually have a look at it.
We can go to the 'Column Views' area and we'll see we've got a 'Shortest Path' item
so if I actually generate a select statement for it, it will allow me to make a call to it.
In this case it will actually know the real, underlying schema name, not the HDI container service name.
So, we can see it gives us a 'Select from', blah blah blah.
I'm going to get rid of the 'Group by', because that's not really what we want when we're working here
so let's just go ahead and select the name and the weight
the two columns we've got from our shortest path.
Now, we didn't create parameters so it's very straightforward with no parameters to put in
and we go and run it and now we can see that we've got our results
so we've got, basically, our name, and the weight, which is basically the distance, it's going to be the count.
So this is from Uranus, all of the different connections that Uranus has
and the number of degrees of separation is our weight
and perhaps we want to optimise that, we could maybe just say 'order by' and put 'weight' in there
so that would, if we run that again, maybe just give it so Uranus is the first one at weight zero
that's connecting to himself
and then Rhea at one degree of separation, and then Hades, Zeus, Hera, Demeter at two
and so forth all the way up to three.
So we're basically doing something equivalent when we're working with Shortest Path here
to the Shortest Path, the basically single source and then all target
so it's basically from Uranus to everything else in this graph we get the shortest possible path to them
so the shortest way from Uranus to Demeter is two connections.
So, what we've seen in this video is how we can create a calc view and use the shortest path option
the Shortest Path algorithm to allow us to get all of the shortest paths from a certain start point and
if we want, we can do weighting on that as well.
So that's something we can now do with HANA 2.0 SPS 01.
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 for all of our tutorials are published to GitHub.
Thanks for watching.