Hi, I'm Philip from the HANA Academy.
In this series of videos, we're looking at the Predictive Analysis Library, which is native to SAP HANA.
In this video, we're going to get started using the Predictive Analysis Library.
Now, you may not already be familiar with HANA
so perhaps we should start with just a little explanation of the predictive capabilities of HANA.
Now, if we were to imagine this box here is HANA
then in there we would have a number of capabilities.
We would have a database, which you all know
and of course we have predictive capabilities
but how does it work?
Well, basically, within HANA we have something called the AFL or the Application Function Library
and it's a set of libraries that give you predictive capabilities inside of HANA.
Now, one of them is called the Predictive Analysis Library, or PAL. That's what we're covering in this series.
There's another one that's called the APL, or the Automated Predictive Library
and, in fact, there are others even provided by partners such SAS that provide capability as part of this AFL framework.
The key part of this AFL is that the libraries that power the APL etcetera
they are able to work directly in the HANA database, so they interact directly.
They are algorithms, data-mining algorithms
that are written at a deep level that run natively in HANA database
and are optimized for the parallelism and the columnar storage and the in-memory concepts of the HANA database itself.
So, you won't find algorithms, for example, in the AFL libraries that you wouldn't necessarily find somewhere else in other technologies.
What you will find is that they've been optimized for working directly with an in-memory HANA database.
That's the difference.
Now, there's also a capability, and actually another software solution, called R
open-source, very well-known with a lot of algorithms
so we also provide the capability to integrate with that
so there's another capability which is called the R Integration with SAP HANA
and if we use that then it allows you to actually connect to an external R server and actually use procedures as if the data, the processing, was happening in HANA
but technically what happens is that data would be sent out from R and be brought back from R.
The processing, the R script, would always run on a separate R server
but you have the ability to wrap it up as if it was running in SQL script within HANA.
Now, in terms of client, there are a number of options to access the predictive capabilities.
For example, since the beginning we've had HANA Studio or Eclipse with the HANA Tools
so this gives you SQL access
but also within the studio there is a tool called the AFN, the Application Function Modeller
which you may also know as a flow graph
and this is something that actually allows you to generate, for example, algorithms from the PAL library
without writing any SQL script code
and we have a series on that in our playlist in the HANA Academy.
Also, there is another tool available from SAP called Predictive Analysis - we'll just call that PA Version 3.X
and predictive analysis and analytics, this tool allows you, again, to access the PAL or the APL
but from an end-user perspective
so it will generate all necessary code for you and provide you a full end-user experience.
That's something that's licensed separately from HANA
but, of course, it will work very directly with HANA and be optimized for it.
Of course, you can always access through a web browser
for example, you could use the web tools that are provided by HANA
and another point that's quite important is that you may want to build apps
which show, maybe web-based or client-based, that access and use predictive functionality that you set up using the PAL
and those are things that you can also connect, either through JDBC or through web-based tools
so using XS for example.
So, that's the overall architecture.
We can see we've got the Predictive Analysis Library, which is part of the AFL.
It works natively in the HANA database, provides about 80 different algorithms, data mining tabs of algorithms
that allow you to then work doing predictive functionality optimised for the HANA world.
In order to work with the Predictive Analysis Library, you're going to need access to the reference guide.
Now, we can find that on the web where we get all the HANA reference guides
so help.sap.com/hana_platform that's the homepage for HANA documentation.
We can see it's current for the latest version, which is 2.0 SPS 01.
Of course, you can get prior versions by clicking on that and you can see different versions.
Now, all of the documentation is available in HTML format, but you can also download in PDF if you need to.
Now, the PAL reference is in the reference section
but it's not shown initially, so you need to click on 'View all' for the reference guides
and then scroll down and you'll find that we've got the 'SAP HANA Predictive Analysis Library'.
So, click on there, it'll open it up interactively.
Again, I say, if you want to download to have this offline then on the right you can see there's an option to download as a PDF.
Otherwise you can work with the documentation online like this
so there's a section on getting started, and we'll be covering some of that in a moment
there's a section then covering all of the different algorithms or functions in the Predictive Analysis Library
whether that's clustering, classification, or new things like recommender systems
which is new with HANA 2.0 SPS 01.
So we'll see more of those examples later.
So that's where to get hold of the reference guide.
Now, if we just look at one of the algorithms, for example 'Accelerated K-Means'
you'll get an explanation of the algorithm, and you'll also get an explanation of how you're going to call it.
Now, we'll see in a moment how we do that with actually calling algorithms
but you'll see actually there's a specific name of the algorithm you'll need to reference
and there'll be tables, both input and output tables that you work with, so working with the PAL is table-based
and you'll get information in on how you can call.
So there'll be a two-stage process
we'll actually be creating a stored procedure using the wrapper procedure
and then we'll be able to call that stored procedure actually to make that algorithm work
so it actually calls the algorithm itself at runtime.
So we'll see more of that later.
Now, if you want to follow along with the examples in this video series then it's all available from GitHub.
The HANA Academy has a GitHub account, so github.com/saphanaacademy
There you can see we have a number of repositories, and one of them is the PAL, the PAL repository
and that's where you'll find all the examples from this video series.
You'll find that we've got the source data, so in this case with HANA 2.0 SPS 01 it'll be 'PAL21.zip'
is where we've got a full schema of source data that you can install and work with
and also if you go back you'll find that we have the code snippets folder with all of the individual code snippets.
The one we're going to be using in this video is actually 'PAL 132 Getting Started with HANA 20 SPS01.sql'
and you can see here we have some syntax.
So that's how we can get hold of it.
Now, if you want, you can actually download all of this repository.
You can choose the clone or download option to download a ZIP file with everything
or if you want you can just choose to go and cut and paste items and maybe download individual items.
What I'm going to download is the source data we're going to install, so we'll go here and if I just click on it
it won't show it because it's a ZIP file but I'm going to choose the 'Download' button
and it will now download that to my local 'Downloads' folder on my desktop.
So, we'll come back later to look in more detail at the code we're going to use.
The next thing we'll want to do is to get access to work with SQL Script.
There are different ways you can do that with HANA.
For this video, we're going to work with HANA Studio, or Eclipse with the HANA database plugins installed.
You can use either, it really doesn't matter.
Now, I've actually got a basic HANA system that I've created in Google Cloud Platform
so what I need to do, firstly, is to connect to it
so I'm going to choose the 'Connect' option.
Now, the host name I've set up is 'hxehost'
and I've actually mapped the IP address in my local hosts file to make that work.
Of course, you could use the IP address or the external host name of your HANA server as you've got it.
Now, with HANA Express, typically by default we're using instance number 90
and it's going to be an MDC solution, it's multiple containers.
In fact, with HANA 2.0 SPS 01, the only option for a HANA server is in multiple container mode.
So, firstly we're going to connect to the system database, and we'll connect as the system user
and you will know the password for that
or the person that set up the system will be able to give you the password for that.
We can now click on 'Finish', and we'll see that we now connect to the system database.
Now, what I'm going to do is select that and then choose the 'SQL' option
so that we can just make some checks to see how this environment's set up.
Now, I'm using a vanilla HANA Express version that's actually running on Google Cloud Platform
but it could be running anywhere
so if you're working with HANA Express you should find that from the beginning you have the same kind of landscape as I have.
So, I'm just going to go back to the code snippets
and remember it's PAL 132 that we're working with in this video, and we'll find some of the code.
Now, some things we need to run against the system database, so I'm going to copy and paste that code.
We don't necessarily need to run all of it
but we'll just copy and paste it in so that we can at least look at it within HANA Studio or Eclipse.
First thing is to check that the Predictive Analysis Library has been installed.
It's part of the AFL, the Application and Function Library.
When you work with HANA Express it's pre-installed
it's an option if you're doing on-premise HANA installation yourself
so we can just run that just to see.
We should get a long list of functions for the PAL area.
If we get nothing returned, then it's a very good sign that the PAL has not actually been installed.
The second thing is, when we're working, as I mentioned a moment ago, with SPS 01 of HANA 2.0
then we do need to work in multiple container mode, so we're going to have tenant databases.
Now, the system database that we're currently connected to is not suitable for working with the PAL algorithms
it's really designed for more system administration functions, not analytics.
So we're going to need to make sure that we have a tenant database set up and working.
Now, the first thing you can do is just maybe see which tenants exist on your system.
A 'SELECT * FROM SYS.M_DATABASES;' gives us a list
and we can see that, in this environment we've got, the SYSTEMDB is running,
but also a tenant called HXE, which is up and running.
Now, in some cases it may have been stopped
this default tenant which is the same as, actually, the HANA instance ID
so in some cases it may have been stopped by HANA Express installation.
In that case, what you can easily do is just start it if you need to with this 'ALTER SYSTEM START DATABASE'
and give the name, the three-character code, of that ID.
If you want to make a new tenant database
or you don't have one for some reason
then you can use the 'CREATE DATABASE' and then give the name
and then obviously set up your system user with its password.
Now, we don't need to do either of these two
because with HANA Express we've already got a tenant started and ready to work.
However, one thing we will need to do is to activate the script server
and this is something that the Predictive Analysis Library requires in order to run.
It's not normally started by default.
Now, in previous versions of HANA we didn't take this approach.
We set up the script server using, actually, the INI files, but that's changed.
When we're working in MDC, with the tenant database
for each tenant database we need to explicitly add the script server capability to that tenant database
so I'm going to do that here with 'ALTER DATABASE'.
Now, we've actually got, if I recall, a database called HXE, that's the tenant, and it's running
so we can actually just change this code and go and work with our default HXE
but I've included the rest of the code just in you wanted to make your own tenant.
So let's go ahead and run that
and we can see in the log now that it's finished
that it took nearly a minute to actually go ahead and add the script server.
So, once we've done that
the next thing we would want to do is to connect as the system user to our tenant database
because everything we're going to do now is based on that tenant database.
So, for that, again, I can go to the add option.
It'll be the same HANA host, so in my case 'hxehost', or it could be your IP address
the same instance number, multiple containers
but this time we give the three-character ID of that tenant database, so here it was HXE
and then I'm going to connect.
Again, I'll enter the user.
The user for this tenant is also called 'SYSTEM', that's the, kind of, God user of that tenant database
however doesn't necessarily have the same password as you'd have for the SYSTEMDB
because it's a separate database within the HANA landscape.
So make sure you know that password and you log on, and of course you can store that for the future.
So now we're connected to the HXE tenant, so that's good.
We're going to open up a SQL window there, so now we're connected as that, as the system user.
If we go back to GitHub you'll find that we have some more code
which in this case we're going to be wanting to run against the tenant database
so this is the easiest way to do this is to paste that in and do it against our connection to our tenant.
Now, the first thing is just to check, double-check that we've got the script server up and running.
This is easiest to do if you run the command against the tenant database itself.
You see that if we look at 'SYS.M_SERVICES' view there should normally be a line in there showing 'scriptserver' is actually active and set up.
If that's not there, then it means you need to go back
and actually run this 'ALTER DATABASE ... ADD 'scriptserver';' code.
But for us it's looking good, it's there.
The next thing is, you need to make sure you've got a user that's authorized to do development
and work with the Predictive Analysis Library.
Now, the system user cannot do that himself out of the box, there are specific roles that are needed
and, in fact, the system user obviously cannot grant those roles to himself
because that'd be something that's not allowed for security reasons.
So, what we can do is just create a developer user.
You can create any user you want
so I'm going to create a developer user, and I'm going to give this user access to the catalogue read role.
This means that we'll be able to read system views, we'll see why coming up in future videos
because there are some system views that are now quite useful and you can use to work with the PAL.
Also, we need to give a right
we need to grant authorization for this user to create the stored procedures that access the PAL
and there's a specific role for that, and also, any user that wants to run procedures that access the PAL
then you need to have this role, the 'AFL__SYS_AFL_AFLPAL_EXECUTE' role.
So I'm going to grant all of those things and create my developer user, so let's go ahead and do that.
It just takes a couple of seconds
and what we can also do now is log on as that developer user we're going to use in a moment
so I could actually just go to my connection to the same tenant, right mouse button
and choose 'Add System with Different User...'.
It saves me having to choose the instance and the host name
so now I can put in 'DEVUSER' and that initial password
and, this time, because it's a new user it's going to prompt me, normally, to change the password
so put in the old password and now I'll put in my new and highly secure password
which is absolutely not 'password1'.
So the password's been changed, and we're now logged in as the developer user.
That's what we'll use in the upcoming videos when we want to work.
Now, the final thing we want to do is just to make sure we import the example data
there's a PAL schema that we're going to reference in upcoming videos, so for that I'm going to import
so I'm going to use the 'Import...' option
but I need to specify where to find it, so 'SAP HANA' -> 'Catalog Objects', choose 'Next'
and it will default to the file being on the server, but actually for us it's going to be on our client.
Now, if we remember earlier
what we actually did is we went and downloaded, so let's just go to our 'Downloads' folder.
We see in Windows Explorer we've got a ZIP file.
We're going to need to extract that, so let's just quickly extract it, and we'll see here that here's the 'PAL21'
and we can see this is where we've got, effectively, a full schema.
So what we need to do is to reference that folder
so we can do that, we can go looking into 'Downloads', then 'PAL21'->'PAL21'.
Okay, hit next, and now it recognises that
and it knows that these are all of the tables that we have, actually, in that schema
so I'm going to select them all and choose, 'Add'
so we're going to import all of those.
Choose 'Next', choose 'Finish', and it now does that import
and once it's finished we should be able to go
for example to the 'Catalog' and actually see that the PAL schema exists, and it will have tables
but as DEVUSER is going to do our actual analysis, he's going to need access to this data.
The last thing we're going to do as the system user on the tenant is just grant read access to this schema
so now I've created it, I can grant read access to that schema to our developer user
and now as the developer user I can now go and look at this PAL schema.
I should see that there are some tables and if we maybe just pick one at random, let's maybe go with customers
and let's just try to open the data preview
just to check that we can read data, our import window came and the developer user's got access to the data.
So we're now good to go, that's getting started when we're working with HANA 2.0 SPS 01.
Notice that things have changed from the past, so we're working with multi-database containers
so we need to make sure we've set up the tenant base with the correct settings in there.
We've imported the data
and also remember that we've got the reference guide, we looked at the documentation.
So that's getting started with this release.
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.