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
look at building calculation views which
use star joins. So why would you want to
use star joins? Well that's because you
want to build and use multiple fact
tables in a single calculation view. So
one of the restrictions is that when
you're building a calculation view,
ordinarily you would have to use all the
facts from one view. Of course you can
get around this by combining your
multiple facts tables in a singular view
and exposing that to your analytic view.
But with star joins you're able to
access facts or measures from multiple
fact tables. In the previous few videos
we've looked at using the SAP HANA Web
IDE to build calculation views when
using XS Advanced. So here are some
of the different calculation views we've
used.
Now these calculation views when you're
aggregating
all use singular fact tables. You might
have multiple dimension tables but in
this example you might have a singular
facts table. Which is this series of fact
tables here. In this video we're going to
cover the concepts of using a star join.
Which enables you to connect to multiple
fact tables to derive measures from
those multiple tables. So to demonstrate
this I'm going to go to my database
explorer and I'm going to go to my
tables and just to show you some of the
data that we're going to combine. So you
have a table called facts. If I open up
that table. This contains the same
columns as another table called more
facts. You can see that the data is
different but the number of columns is
the same. So in a previous video we
unioned these two tables together to
produce one singular table of this data.
However, imagine we have another table or
another facts table which has a
different measure enclosed within it. And
in this example we have a measure called
top scorer goal scored. What does it mean?
Well if I open up the data, essentially
what it's showing us is that for each
year the title was won. So this was a
year that a certain soccer side won its
championship. The top scorer for that
team scored this amount of goals. So in
this example when Chelsea won the title
in 2017 their top scorer scored 29 goals.
For Leicester in 2016 their top scorer
scored 25 goals. Now there's a belief
that if your top scorer scores more than
30 goals you're more likely to win the
title. But of course what I'm going to do
by building this star drawing
calculation view is test that theory.
We're essentially going to get multiple
facts and combine them in a single
calculation view. This is the fact from
our other facts table and of course
we're combining that with a fact less facts
table as you can see here. Where the
actual fact indicates that the
transaction happened. So again because we
have no individual fact this is
indicating that Chelsea had won the
title in 2015 because these two columns
exist. To do this of course I'm going to
go to my list of views and I'm going to
create myself a new calculation view.
I'll call this star join. We’ll give it
the label star join and of course we're
going to build a cube data category and
we're going to select star join and then
I'll select create. So this is what it
looks like. So of course you have
aggregation, projection and now here you
can see we've got star join. So the
first step is to join the two data sets
together. So of course if I go to my join
icon here, where we create a join,
I'll just place that below my star join
and I'll call this one join like so. This
is going to join our two facts together.
So what I'll do then is I'll add the
various tables needed. So if I go and
select my plus button, we're going to
select multiple fact tables. So the two
fact tables that we're going to select
are. We’ll select that other facts table
and also. So if I select it and click
finish. We’ll also add to that our Union.
Which is the combination of those older
and newer facts. Which will give us the
same rows. Which will in essence be the
join between the two. So this is
what it looks like. What I'll do then is
I'll maximize my editor and we'll look
at these two tables. So of course within
our join node we can right click and we
can do a data preview. So again if I do a
data preview of that other facts table
you can see we have records from 2017
all the way down to early, the late
1800s. If I go to my other object and do
a data preview you'll see we have the
same data for the same seasons but the
difference is, is that if I sort by
descending, we can see here that we have
the factless facts. So essentially this
one contains the metric. This one
contains a factless facts and that's of
course what we're going to join together.
So of course we need to join these two
together. So we don't join on the ID
because that would be a many-to-many
relationship which would actually give
us a Cartesian product. The only logical
join between the two is the season which
actually is a one-to-one join. So I'll
join the season to the season and I'll
make sure that it's a one-to-one.
Again for each season not including
those years during the World War Two we
have a transaction and the factless facts.
Here as you can see on this screen. So
that's the first step. The second step is
we need to define our mapping. So I'll
select the mapping tab and then of
course we've got our two separate
objects. One is a table. One is that Union
calculation view. So what we need to do
is select from our Union calculation
view. We need to select two columns. ID
and also season. Because of course what
we want to do is do a count of the IDs
per team here. So count of the IDs. Of
course from the other facts table we're
going to select that additional metric.
Which is top-scorer goals scored. So
that's the first step. So these are
different types of measurement. One
we’ll be doing a count. And one you could
actually do a sum. But I'm going to show
you now how you can create these
calculated columns. So remember we wanted
to see if scoring more than 30 goals per
your championship winning season means
you will necessarily win that title. So
we can calculate this by building a
calculated column. And we're going to
build a calculated column that does an
if statement to say, if that transaction
has their metric for goal scored greater
than 30, we're going to allocate a score
of 1. Otherwise we'll allocate a score of
0. So I'm going to do this by adding a
new column. I'm going to call this column
over 30 and then we're going to make
this column an integer.
Next we need to add the expression. So to
add the expression there's two ways you
can do this with the SQL engine. If I
have a look at using the SQL engine
and go to my expression editor. You see
that we have some conversion functions
but depending on whether you choose the
SQL language over column level
language you will tend to get more
options when you use the column level
language. The column engine language. So
if I go to my string functions. Actually
if I go all the way to my miscellaneous
functions right at the bottom. So I'll
minimize the date functions, the spatial
functions, the spatial predicates. Here we
have a list of miscellaneous functions
which don't exist in the SQL engine.
So I'm going to do that if statement. So
I'm going to say if something equals
something then do this otherwise do this.
So it takes three arguments. So we're
going to say if the column, which is
top-scorer goals scored. If that is
greater than 30 output a one. Otherwise
output a zero, like so. So if the
transaction has more than 30 goals we’ll
output a 1 and if it outputs, if it
doesn't we’ll output a 0. Lastly of course
you can validate that syntax just to
make sure that the syntax makes sense
and it does. So I'll click on OK.
So now that we've done that if I go to
my columns you can see that we've
outputted actually four columns. We’ve
got the ID, the season, the top scorer
goal scored and whether or not the
council indicate one if they've scored
more than thirty goals. So now we can
actually connect this to our star join
and we can work on our star join. Which
is where we can join this to our
dimension calculation view. So to do this
what I'll do is I'll add and I'm going
to search for dimension. Which will add
that dimension calculation view and you
can see the two columns. So of course now
I need to do the join between our
dimension and our facts or our star join.
So I'm going to select the ID from the
dimension and join it to the ID in our
star join. Again here we can see here it
says it's a many to many but
we're going to say this is a one to N.
Or actually it should be the other way
around.
It's going to be a one to many here on
the left hand side. Again you can see the
different types of join here but we
don't have to change anything for our
join type. So the only thing we have to
change here is our mapping. We have to
select the columns that we want
outputted. So we only need three columns.
We need the ID. We need the season. And
we'll need whether or not it's over 30.
We don't actually need this metric
because we're using a different type of
granularity now. For the ID I'm actually
going to change this and I'm going to
call this our trophy count. So you can
see here within the star join I'm
building the metric and I'm going to
make this a count like so. And
there's nothing else that we'll need to
change here. For the over 30 we can leave it
as a sum. Why? Because we're summing
a count. If you sum a count or even
count a count, you will get the same
result. So we're going to do a sum of the
count. So that's all we have to change
here. So lastly I can go to my semantics.
And we just need to configure our
semantics here. So the only thing that we
have to configure is that we've got the
trophy count which will be the ID. Now
I'm going to change the label to be also
trophy count. We’re doing a count. So we
can leave the aggregation type and of
course we're doing a sum of this count.
So we can leave it as a sum. Now what
you'll see at the top are the objects
from our fact table but you've also got
this shared dimension at the bottom
which comes from our star join. So one of
the things we can do for example is we
can choose to hide certain columns. We
don't need the season column in our
analytic and also in terms of the shared
dimension table we don't need the ID. We
can hide that. So we're only outputting
team, over 30 and trophy count. So the
last step of course is to save. I'll then
maximize my active editor and of course
what I'll do is I'll build that star
join .HDB calculation view.
There we go. So now that it's built what
we can do is we can go and test it. So to
test it what I'll do is I'll just zoom
out. I'll right-click on the star join
and I'll say data preview. And this is
what it looks like. So we can see that
the actual trophy count is correct. 20
Manchester United, 6 for Chelsea. And again
you'll know why because we're connecting
to a union calculation view which
combines two fact tables and the other
measure where we did the count comes
from that other facts table. So this is
indicating that when Manchester United,
they've won 20 trophies but when their
top goalscorer is only scored more than
30 goals, 7 times. And if you look down
for Everton for example. They've only won
the championship 9 times but their top
goal scorer has scored 30 goals only 6
times. Of course this can be investigated
within analysis. So what I'll just do is
I'll maximize my active editor and what
we can do is look at the actual teams
and their various measures like so. This
is a bit tight to see. So of course
you've got the option here to say things
like a combined bar and line chart or
you can also have a dual combined line
chart. So you can see if there's any
correlation. And as you can see sometimes
there's a correlation. But here for
example with Everton they actually won
the trophy as you can see here nine
times. But if I look on the right side
for the other axes they only, when they
scored more than 30 goals this was only
done six times. Whereas example from
Manchester United they only scored their
top score or only scored more than 30
goals seven times in all the years that
they won the championship. And of course
you can investigate if there's any
correlation between those two metrics. So
that's an example of how you can use the
star join and again to summarize you use
the star join when you want to take
measures from multiple fact tables in
one single
calculation view.