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 creating calculation views
which union data sets together in. The
previous video we looked at using the
Web IDE within XS Advanced and we
looked at creating a few simple
calculation views. One was a dimensional
calculation view and another was a
simple calculation view that performed
aggregations and this is what that
calculation view looks like. Now the
calculation view had a small issue. So
let's look at the results. If I go to my
Database Explorer and expand my list of
column views. If I expand that and view
that data within the simple calculation
view. What it shows is some misleading
results. So it's showing that, for example,
Manchester United had won 20 league
championships. Which is correct.
But because the data only goes up to
2015, Chelsea have actually won six
trophies and Leicester City, which is not
listed here, has actually won one trophy.
So what I'm going to do in this video is
show you how we can union multiple data
sets together. Again if I go back to my
tables and open up our facts. Here you
can see the issue. The last season we
have data for was 2015. There's another
table called more facts. Which has
results for 2016 and 2017. Which once
unioned should increase Chelsea's trophy
count to 6 and include another side
called Leicester City which will have a
single trophy count. So to demonstrate
this what i'm going to do is I’m going
to go back to my database perspective
oops, development perspective and what
I’m going to do is I'm going to create
myself a new calculation view.
This one will be called union and this
one is going to be a dimension because
we don't actually need to aggregate the
two datasets.
Not here anyway. In fact we could
actually change this to a default. Which
will allow us to choose the aggregation
level within the calculation view and
I'll explain what that means. So I'm
going to choose default. I'll then
select create. So when you choose the
default type of calculation view you can
then manually change this to an
aggregation. Which means you can perform
aggregates of course and use measures or
you can actually switch it back to a
projection. So it's kind of like a
default way of building your calculation
views. So we're going to carry out
similar steps that we did before. So what
we're going to do firstly is add two
projections because we don't actually
need to aggregate. So I'm going to create
one projection here and I'm going to
call this facts and I'm going to add
another projection and call this more
facts. This is that other table which
contains the two records. Like so. I'll
also make a bit more room by maximizing
the active editor like so. So then we
just need to choose our data sources. So
of course for facts
I'm going to choose that facts table. So
again making sure I choose the correct
one I can maximize the name of the
objects and we're going to choose facts
here and for more facts again I'll click
on the green plus icon and I'm going to
look for the word more. There should be,
only be one and this is the
data within more facts. Now if we look at
the two nodes, facts and more facts, have
exactly the same columns in each of the
tables. Of course what we want to do is
perform a union on that data. So for each
of these separate nodes if I go to my
mapping I'm going to select all the
columns from each of
the node. So this is from the facts node
and if I go to more facts we're going to
do exactly the same and choose all the
columns. Nothing else needs to be done
here. So of course now we want to Union
and not join the data sets together. So
of course here you've got different
options. You've got join here and we want
to perform a union. So I'll select a
Union like so and I'm gonna call this
Union. And essentially all I'm gonna do
is drag the output of the facts to the
Union and that's the same for more facts.
So if I go to my Union this is what it
will look like. So I'm going to drag my
facts on to the output columns. So how do
we deal with these more facts? Well
because we’re performing a union we need to
specify which columns we want a union. Of
course for me it's going to be ID to ID.
Season to season. And also decade to
decade. Although, again we don't really
need decade. If I now go to my columns
these are the columns which we’re
outputting. Of course the last step now
is to output the Union to the projection
because we want to output all of these
columns. Again when it comes to the
mapping there's nothing here to
really change. All we need to do is
select all the columns from the data
source of the Union and put them into
our output columns. Here we can see those
output columns. And of course here if you
want you can choose to deselect the
decade. Although I'm going to leave the
decade column in. Because this is a
projection we don't need to do anything
else here in our semantics because again
all of these are going to be dimension
objects. Because at the moment all we're
doing is unioning the datasets together.
We don't need to preform any other
operation here within our semantics for
the Union calculation view. And we don't
need to change anything here in our
properties for the semantics.
The only thing we need to do now of
course is save and test. So I'll save
this calculation view and then of course
if I go back to my, if I restore my
active editor, what I'll need to do is
build that selected file. It’s going to
take a few seconds to build. There we can
see it built successfully. So of course
the last step is to test. Now an easy way
to test here is, one of the ways is if I
right click on the projection and do a
data preview. This is what that data
preview looks like. So we can't really
see here because we're actually Union,
we're just simply appending at the end.
There's no form of order by. But of
course if I saw ascending and then
descending. Here now we can see that
we've added 2017, 2016 which comes from
the more facts table to the facts table.
Which consists of the records 2015 all
the way down to 1889. Another way to test
of course would be to go to my database
perspective. If I go to my list of column
views we'll have another view called
Union and again if I open the data and
if I do a sort descending here we can
see we've Unioned these two rows from that
more facts table with all the other rows
from the other facts table. Now the Union
operator is quite simple to explain. It’s
quite powerful because this Union, here
if I go back to my calculation view, the
Union can be done on any table that a
virtual table can connect to. So one of
the options we're going to look at in a
future video is building what's called a
virtual table which can connect to a
non-SAP HANA data source. Then you can
both join and Union that data together.
So the Union operator here is actually
quite powerful.
So what we've done now, is obviously
we've joined these two fact tables
together in a single calculation view
called Union. The issue though is we need
to modify this simple calculation view
to take account of the fact that we've
added those two separate facts. And we're
going to cover how we do that by
replacing the data source in the next
video.