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
start with an introduction into
graphical calculation views and we'll
also start by creating the most basic
type. Which is a dimensional calculation
view. In the previous video we looked at
a basic overview of the Web IDE and what
we focused on was building projects,
building SAP HANA database modules and
then loading some data into an SAP HANA
HDI Container. So what you can see here
is your development perspective and
these are the objects which enable us to
create some tables. If I go to my
Database Explorer and expand my modeling
connection which connects me to my HDI
Container, within tables we've got four
tables. Now in this video we're going to
be looking at creating a calculation
view based on a dimension table. This is
the dimension table that we're building
that calculation view on which contains
simply two columns. An ID and a soccer
team name. So where do you build
calculation views? Well you build them
here within your development perspective.
Within the SRC folder you can actually
build them directly here. You've got the
option to build the calculation view. But
for a better organization what I'm going
to do is create myself a new folder called
views and I'm going to create all the
views within this folder. Which is just a
subfolder of the SRC folder. So we don't
need this data folder anymore. So again
I'm going to right click and select new
calculation view. So a calculation view
allows users to define more advanced
slices on the data available within the
SAP HANA database and they're mainly
used for analyzing operational data
marts or running multi-dimensional
reports on, for example, revenue,
probability etc. They can consume various
combinations of content
data to model a business use case and we
can classify classified content data as
either an attribute, such as descriptive
data, ie customer ID, city or
country. As well as measures which are
quantifiable data. Such as revenue,
quantity sold and various counters and
we're going to look at those in a few
videos time. So we'll firstly create our
dimensional calculation view. I'm going
to call this one called, create this one
called dimensions and this is going to
be the name space that it will be
allocated to. So you can see here that
it's going to be allocated within the
modeling projects modelinghdbmod SAP
HANA database module and within that
folder called views. The label is just a
descriptive name that will be read by
the BI tool. So in this example here
we're calling it a dimension and also
within any BI tool we'll also have it
called dimension. You can have different
names here and then these are the
different types of data category for
your calculation view that you can build.
We're going to start off by building the
most simple which is a dimensional one.
So when you build a dimensional
calculation view there are two different
types. You can build a standard
dimensional calculation view. Which is
what we're going to build. Or you can
build a time dimension. Which we're going
to look at in a few videos time. So I'm
going to build a standard dimensional
calculation view and I'll select create.
This is what the user interface looks
like. So what we can do is we can
maximize the screen by going to view and
maximize active editor. So you can see
there's a left-hand pane and a
right-hand pane. This is where each of
your options are mapped. So there is
in most calculation views you will have
a semantics top level. Which is
essentially what the BI tool will use
and see. Within that you have various
parts of your calculation view because
essentially like a select statement you
can build that calculation view up.
Because this is a dimensional
calculation view the only option here we
have is projection.
Above you've got your various
options. So you can do things like Auto
layout. You can do things like join
different views together. Union them
intersect, minus. You can use graphs or the
graph database within a calculation view
and there are some other options here as
well of course. By selecting the option
on the left hand side will determine
what is selected on the right hand side.
So let's look at a basic example. So
first of all you want to model that
single dimension table with two
simple columns. To do this we only have a
projection and a semantics. Which again
is what the BI tool will see. So for me
what I'm going to do is if I click on
this green icon this is where we can add
our various objects. So of course a view
can be a data provider for a calculation
view and of course a table can be as
well. So if I do a search for dims we
should find our dimensional table called
dims which contains those two columns
within this specific schema. So if I
select it and click on finish what we've
done quite simply is added that singular
table to our data source. Next we just
need to define our output columns. So
there's two ways we can do this. One of
them is to drag the column to the right
hand side. An easier way because we're
going to select both of these columns is
to drag the whole data source to our
output columns like so. You'll see by the
blue icon here that because this is a
dimensional calculation view. What it
basically means is that we have no
calculated measures in this view. These
are what are called simple attributes. An
example of a simple attribute could be a
product ID or a product name and these
are individual non-measurable analytical
elements that are derived from the data
source. So now that we've selected these
of course you've got various options. One
of the options what you can do is you
can concatenate columns together using
calculated columns. We’re going to look
at that later on in another video. You
and parameterize your columns and this
column here indicates which columns
we've selected in that calculation view.
Again your table might consist of 20 or
so columns but what you're putting in
the view are, you're only
including the columns that you need. So
we've got these two columns they're the
only columns we've got. So now that we've
done this and of course this can be
built up of many, many different objects.
We can have multiple projections and
there's other types of objects that you
can have as well. So now that we've
selected our column if we go to our
semantics it's quite straightforward to
configure this part because we have no
aggregation. So essentially what we have,
is this is where we could choose a
different type if we wanted to turn this
into a measure but of course these are
dimensions. This is the name of our
column and this is the label again which
the BI team, the BI tool would see these
two columns can be different. We have no
aggregation. We have no form of filtering
and we've also got some other options
here. For example we can hide the various
columns if we didn't want to have the ID
listed in our calculation view and
there's some other options on the
right-hand side. That's all we have to
have for this basic example. So now that
we've got our two columns which come
from that dims dimension table all we
need to do now if you look on the top
left-hand side we can see we've got an
object called a HDB calculation view
called dimension and it has a star next
to it. What the star means is that we
haven't saved this object. So all I need
to do now is save and of course if I go
back and maximize my restore the active
editor. Of course now that we've saved
the objects we also need to build that
selected file. So again it's going to
compile and it takes a few seconds to
complete. Here we can see that this
object has successfully compiled or
completed. It’s been allocated to our HDI
Container. So we should be able to view
this object. Now how can we view the
object? Well it's very simple. If I go
back to my database perspective, of
course here are our list of tables.
Ordinarily you would think you would
access the
object here within views but it's
essentially in SAP HANA it's a column
view. So if I go to column view you'll
see that we've got a few objects. Now if
I expand this objects, this pane here, you
can see that you have two different
types of objects. One of them is
specifically if you've got any
hierarchies. Which we don't have and one
of them is simply the dimension table or
the dimension calculation view. So all I
need to do now is right-click. Open the
data and then this is what is contained
within my calculation view. Which again
is just a replica of the table. So we
have a calculation view which consists
of an ID and a team. Again you might be
asking why would you do this? Well what
you can do rather than build complex
views which connect to multiple tables
directly, you can adopt a stepwise
approach. Which means you can model all
of your objects individually and then
combine them all together at a later
stage and that's what we're going to be
looking at in a future video. So that's
how you build a very, very simple
calculation view which connects
to dimensional data. In the next video
what we're going to do next is look at
building a simple calculation view which
aggregates data. And so combines data
from this dimension table as well as a
fact table.