Welcome to the SAP HANA Academy. My
name is Bob and in this series of videos
we're going to be looking at how to
model analytic data within the SAP HANA
Extended Application Services Advanced
Model. Which is also known as XS
Advanced. In this video we're going to be
looking at calculation views that use
ranking. In the previous video we built
this calculation view which took data
from multiple fact tables, unioned it
together and then combined it with a
dimension table to perform a form of
aggregate. If I do update a preview on
the calculation view, what you'll see is
that it quite simply lists a list of
soccer teams in the UK and how many
times they won their national
championship. Now if we do a bit of
analysis on this and drag the team and
the number of trophies. What you might
want to do is do an analysis and give
for example, output the top five teams
that have won the most trophies.
Obviously here we can see it's
Manchester United, Liverpool, Arsenal,
Everton and then it will be Aston Villa. So
you might want to output these, the top
five and of course you can do that with
ranking. So to demonstrate this what I'm
going to do is I'm going to build a new
view, a new calculation view and then
I'm going to call it rank. It’s a cube
view. We can leave it as it is because
we're going to perform
some form of aggregate. So
I'll select create. So the first thing we
need to do is specify the rank. So how do
we do that? Well we do that here within
these two options. One of them is create
rank and we've also got… we are able
to use table functions. But we're going
to choose this crate rank. So I'll select
it and place it on the canvas and I'll
just rename the node as rank. Of
course what we're going to want to do is
then go and select our all facts calculation
view. So I'll select all facts I'll
select the view and I'll click finish. So
let's firstly look at the mapping. So
this is where we need to decide which
columns we want. So in order to do this
mapping or this ranking we need to have
both the team and of course the number
of trophies. Because we want to select
the top 5. So I'll select both of these
columns from our data source. That's the
first step. The second step is to go to
our definition and define the ranking
and you'll see this option here in the
second tab. So firstly we need to decide
the sort direction. Well I'm going to
have it descending from a top number.
Then we can choose a threshold. So you
can base this on an input parameter
which we're going to look at later. Or I
can select a fixed threshold. So I can
specify a specific number. We can then
choose to order by. So what do we want to
order by? Well of course it's the number
of trophies. Lastly if you want to
partition you have the option here. If
you can partition by a specific column.
But for us we want to select the
option which is we want to be able to
rank the column. So we want to have, sorry
output a rank to column. So I'm going to
call this column, I'm going to rename it
to rank like so. And in terms of the
threshold I'm going to say 5. We want to
output 5 or the top 5 teams that have
won the trophy. So once you've done that
then all you need to do is connect your
rank by selecting on the arrow and join
it to our aggregation. There's nothing
really else here to change. So if I go
back to our columns you can see here
we've got the trophies. And here we can
leave the aggregation as sum. Why? Because
we're actually aggregating or we're not
actually doing a group by in this
calculation view. That group by was
already done
in the previous calculation view called
all facts. So I'm going to
leave the aggregation as sum
and in fact for the rank I'm also gonna
output the sum. We don't need to change
anything there. So once you've done that
I can go to my aggregation node which
contains the rank and of course what we
need to do is select our three columns.
So I'll drag them to the right. We don't
need to change anything else here and in
our semantics we can leave the data as
it is. So of course the last step would
be to save that calculation view. Right
click and build this selected file. It’s
going to take a few seconds to build
that rank .HDB calculation view. Of course
the last step is to test. So I'll
right-click on the ranked .HDB
calculation view and I'll do a data
preview. And here you can see the output.
So here we can see the top five sides
that have won a trophy in an extra
column called rank which indicates each
of the ranks. And of course we have
Manchester United, who've won the most,
followed by Liverpool, Arsenal, Everton
and then Aston Villa. So that's a very
simple explanation of how ranking works
within calculation views.