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 look at Analysis of Variance, or 'ANOVA'.
This algorithm was introduced to the Predictive Analysis Library from HANA 2.0 SPS 01
and we have the ability to perform one-way analysis of variance.
Now, this algorithm's actually been around for quite a long time.
It was Sir Ronald Fisher, who was a British biologist, mathematician and statistician
he published this essay around one hundred years ago and it's been very well-used.
It's very useful when you're, for example, dividing up maybe patients or items into different groups
and then you want to compare the results across those groups.
It's actually like doing a T-test, actually, for more than two different groups
so you would normally have at least three types of group that you'd then want to actually compare or test.
There are a couple of flavours to using One-Way ANOVA.
There's the classic approach we're going to see first
and there's also the repeated measures approach, which we're going to see in the next video.
If you want to learn more about ANOVA, of course you can go to the reference guide here
you know where to get that by now.
We can see some of the formulas and some of the background
we can see that for one-way or one-way repeated measures
but of course you can also Google it
you'll get loads of information on ANOVA and it's available in a very large number of different stats tools.
It's very commonly-used and well-understood algorithm.
So, what we're going to do is run an example
so if we go to the GitHub repository for the PAL
remember from the 'Getting Started' video you know how to get here
then we've got the PAL repository and the code snippets
and if we go down to number 139 we can see that we've got 'ANOVA One-Way', so let's open that up
and we're going to copy all of that and paste it into a SQL window
wherever we're going to run our SQL Script code from.
In my case, I'm going to do that from Eclipse with the HANA Tools installed.
So that's our code.
So I've got some test data here, it comes from the PAL schema that we uploaded in the 'Getting Started' video.
We can see that we've got some data, we imagine, maybe, each row represents a person in a study
and we've got three classes, maybe these are three groups that we've divided the people into
so we've got class 'A', 'B' and 'C'.
In fact, if we look at the distinct values we can see, yes, we have A, B and C with about the same number of subjects in each of those groups.
Now, what's also available are some results, for example we've got attributes 1, 2, 3 and 4
so it's basically, maybe, one of those we're going to want to look at.
Now, the fact this is One-Way ANOVA reflects the fact that we've got, basically, one single class that we're dividing up into.
For example, it could be based on age, or it could be just on how we split up the different groups
but what's very important and significant when we're looking at a One-Way ANOVA
is that the subjects that belong to a specific class or group only belong to one group, they're not able to join.
There's no-one that's going to be in A and B and maybe C, people are going to be either A or B or C.
That's quite important. When we look at repeated measures that's different, but that's in the next video.
So, looking at the code, it's the same kind of code you've probably seen many times with the PAL.
We're going to use the 'AFLLANG_WRAPPER_PROCEDURE_CREATE' to create a stored procedure that we can then use
but in order to use the 'AFLLANG_WRAPPER_PROCEDURE_CREATE'
we have to create a set of table types that contain the input and output table definitions
basically the columns and the types and names of those columns in the right order
that we're going to have for our input data, for our output tables
and then off we go, we should be able to build that stored procedure and start to call it.
The name of the algorithm we're going to use is 'ANOVAONEWAY' in this case
and we can see that we've got our input data, we're going to have a class variable and an attribute variable.
Now, this is something that you're going to need to check, always, when you're working with the PAL with the reference guide.
So, if we just go back to the reference guide and scroll down a little
we'll see that for ANOVA, for each of these we're going to have the different prerequisites for the kind of data you need
and then here for ANOVA One-Way we can see we've got two input tables and three output tables
the name of the algorithm is 'ANOVAONEWAY'
and then for these tables you get the definition
so for the input data you need to have two columns, the first one being the group, the second one being the data.
Also we've got parameters with mandatory and optional parameters
and you can see, for example, for the parameters we've got different types of methods for how it's going to work
that gets into the detail of tuning the algorithm
also choosing a significance level
and then we've got the output tables, which are pretty standard as such
it's just up to you to create tables with columns that correspond to the data types
but you can choose the name but you need the same data type in the same order
and actually we've got three output tables in this case for One-Way ANOVA.
So if we go back to our code, we can see that we've done that, we've set up the output tables
we create our stored procedure, so that's the thing we'll do first.
Now, as our data we were just looking at here has the columns we need, 'CLASS' and 'ATTR1'
but they're not in the right order and we've got other columns we don't need
so the easy way round that is just to create a SQL view with the columns you need in the right order.
There's no need to make a new copy of the data, so we just made a SQL view there.
We've actually set up empty tables for the output tables, and we'll set up our parameter table
and here, of course, we've got the 'MULTCOMP_METHOD' as optional
and 'SIGNIFICANCE_LEVEL'
so I've basically just left those as the defaults, as, of course, this is something you can come in and tune.
Then it's just a question of calling our algorithms against the input data
in our case the SQL view with the parameters
and then we'll get our three output tables with the results.
So I think we're ready to go, let's run that.
We can see it's finished and if we look at the three results tables the first one gives us some overall stats.
We can see we've got three groups with a number of samples in each group
also the totals, so we've got 100 samples, effectively.
We've got the mean of each group, and we've got the standard deviation for each group
and also for the total for all together.
Then in the second results table we've got the ANOVA information, we've got the group, the error and the total
we've got the sum squares, degrees of freedom, mean squares, F-ratio and the p-value
so these are very useful, important numbers
and also in the results table we've got the multi-comparison, where we're doing comparisons between the different groups
so A against B, A against C and B against C
so we're doing all the combinations of comparing the three different groups we've got
looking at the mean difference, the standard error, the p-value, the lower and upper confidence limits.
So, obviously you're going to need to know something about ANOVA and how to interpret these results
but generally having a low p-value, that's generally a good sign that the differences between the groups are not statistically significant.
So, what we've seen here is how we can use the One-Way ANOVA.
In the next video, we're going to see how we cna do the One-Way, but with repeated measures.
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.