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 loading some data into a HDI
Container so that we can build some
calculation views on top of it. In the
previous video we looked at an overview
of the SAP Web IDE in XS Advanced
and then we looked at creating projects
and SAP HANA database modules. In this
video we're going to look at loading
data. So to do this what I'll do is I
will exit out of my full screen and what
I'll do is I'll do a search for SAP
HANA Academy GitHub. We have a
repository which contains the data that
you'll need for this series. So if I go
to my list of repositories and select
modeling you should see a link to HANA
2_SPS02 and if I drill into
that and go into the data folder. Here
you have a link to the data which we can
use for this workshop. Of course above
you also have each of the individual
sections used, or objects used in this
series. So these are all the various data
parts and these are the various views
that we're going to create. These are the
individual pieces. This is the complete
and this is only the data. So for us
we're only going to load the data to
demonstrate how this works. So all I need
to do is select that data.zip file
and select download. So now that we've
done that we can exit out of GitHub and
if I go to my downloads folder you'll
see that we've got that zip file. So how
do we actually import that into SAP
HANA Web IDE? Well it's very simple. We
can simply right click, making sure that
we select our SRC folder and we can
choose import from file system. So
firstly anything that we import now
because we've
selected this SRC folder will be placed
within the SRC folder and then all I
need to do is browse to that data.zip
file. I'll then choose extract
archive and I'll click on OK. So we can
see that the imports started and the
important finished and there was no issues
with the import. So let me explain how
these files actually work. So what you
have are, there's actually four tables
which we're going to load here and we
have essentially, a dimensions table and
we have three different facts tables
that we're going to use in this small
series. Within each of these HDB table
files you have a corresponding CSV. So
let's start at the simplest
example. If I open this file which is
soccer_dims.csv. You can see
it contains around 24 rows and the top
row is a header which consists of ID
, team. Now this data will be loaded
into a table called dims which has a HDB
table extension. So if I double click on
that object. You can see, what what we're
doing is we're creating a column table.
We're calling the column table actually
dims and this is the path to that table.
So it's in modelingproject which is the
project of course. Modelinghdbmod which
is that SAP HANA database module and
there's a subfolder called
data which is here. So now that we've got
that what we need to do is then
obviously specify the columns and the
data types within the table. So for us
again it's two columns. An ID and a team
and the team column is has a VARCHAR(50).
Now if I go back to that dims table one
more time you can see that the dims
table essentially consists of 24 soccer
teams in the UK. These soccer teams have
all won their national championship.
Each soccer team also has a
relevant ID. So once you've actually
selected these two objects, how is the
data taken from the CSV
and placed into the table? Well what I'll
do for now is I'll actually build these
objects separately to show you how the
process works. So to build an individual
object, we right-click on the object. So
in this example I'm building my HDB
table and I'll select build selected
files. So we can see that now that file
is being built. It takes a few seconds to
execute and here we can see it's
completed successfully. All we've done at
the moment is actually create an empty
table. We’ve not actually connected to
this CSV file yet. But how do we know
that we've created a empty table? Well
this simple way to do this is to use
another perspective called the Database
Explorer. So if I go to my Database
Explorer essentially what we need to do
is create a connection to our database
schema that we created. Which is
contained within what's called a HDI
Container. I'll select yes because we
don't have one and then at the bottom
this is my HDI Container that I just
created. How do I know? Well you can see
here the name. If I expand my list of
names we have one called modeling
project. So essentially you have this,
what's called HDI Container called
modeling project HDI Container which is
contained within the development space.
So I'm going to select that and I'm
going to output the name to specify
which is just a graphical name which
we'll see in our list of connections as
modeling and then I'll click on OK. So
when you have this connection as you can
see here to your HDI Container, you can
see we have the typical objects that you
have within SAP HANA. Such as functions,
procedures, triggers, views and of course
tables. On selecting tables you can see
that we've got that dims table here but
if I right click and open the data
you'll see that we actually have nothing
in the table yet of course because we
haven't loaded that CSV in
to this table. Now the thing about the
Database Explorer perspective, what you
can see here, is that you can run
both SQL and MDX like commands on the
data. So once you have your table
populated with here you can then
run SQL like commands on that data. So of
course we have that soccer dims.CSV. We
want to get this and load it into our
HDB table object. How do we do that? Well
we do that here with a command which has
a file extension HDBtabledata. Now
again you've imported these objects but
if I was to right-click and select new
file and give this file the same name
here and put the same code in it would
do exactly the same thing. So if we look
at HDB table data you can quite simply
see that it's connecting to a target
table. It’s giving us some source
information which is that soccer
_dims.CSV file here and
then we're importing two columns into
the ID column and into the team column.
So it's going to basically take this
soccer_dims.CSV and load it to
this dims table and we do the same thing.
So of course what we need to do is we
need to build the soccer dims.CSV and
again it's going to take a few seconds
to execute. Here we can see it completed
successfully
and then of course now that we've got
the CSV and we've got the dims.hdb table
we now want to take that CSV and load it
into that HDB table. So this command will
take the data from the CSV and load
it into that HDB table. How do we know?
Well again if I go back to my Database
Explorer, if I now open the data, we
should see that that table of course now
has data in it. We’ve also got some other
tables. So we're actually going to do all
these at the same time just to
demonstrate that it works quite simply.
So we have a facts table which looks
like this. We have an additional facts
table and then a separate facts table
which has a different measure in it. So
factsHDBtable and morefactsHDBtable
have the similar columns in it and
otherfacts.HDB table has a
completely different metric. Which is
called top scorer goal scored. So now
that we've included of course each of
these individual tables, a
corresponding CSV for those tables and
we've also loaded, we’ve also got a HDB
table data file. Instead of actually
manually executing each individual file
we can select the data folder and we can
select build selected files. This will
then take each of the individual CSVs
create in each of the individual tables
and then of course load each of the
tables with the data from that CSV and
here you can see it completed
successfully. Of course the way to test
is to go back to my data, Database
Explorer. We should see that we've got
those four different tables and of
course now we just need to check. So this
is our facts table. Our main facts table.
Which contains a team ID, the season that
that team won a specific championship, a
soccer championship and the decade that
the seasons in. So the year 2015 is in
the decade 2010s. The more facts is
essentially the same format, exactly the
same format. Although the difference is
is that this data goes up to the Year
2015.
Whereas this one only has data for 2016
and 2017 and lastly you might want to
check that you have your other facts. And
other facts is very similar format to
the main facts table. The only difference
is this has a specific metric called top
scorer goals scored and I'll explain
what that is when we come to the section
within this video series. So if you've
got that, you're ready to go.
So to summarize what we've covered is
how to create a project, an SAP HANA
database module in Web IDE
when using XSA. As well as loading
various objects. Such as CSV files into
specific tables using the HDB table data
file extension. When it comes to creating
tables in SAP HANA you can also choose
to create Core Data Services and we'll
cover this further down the video series.
In the next few videos we're going to
look at building calculation views. Which
allow users to define more advanced
slices on the data available within this
database.