Hi, Welcome to the SAP HANA Academy. My name is Julie
In this series of videos we have been taking a look at the new SAP HANA Express edition.
In this next video, we're going to continue on with our model creation.
In the last video we went ahead loaded our sample data and now have three tables.
We have our customer dimension table, a product dimension table, and a fact table with customer orders in it.
The next thing we want to do is start creating some views or objects in our schema.
Remember I said earlier
We want to actually work as the user that's going to be doing the modeling.
So in this case, we're want to make sure to use our connection for my user.
The first thing we want to do is we want to Create a Package
So that's the repository that's going to hold the objects that we're going to create
So if we go to the content folder and choose New > Package and let's call this STS_views
Click OK
Then we go to our package STS_views, right click and do New
and now we want to choose what type of a procedure we want to create.
[For this version of SAP HANA 1.0] This could be an Analytic view, and Attribute view, a Calculation view,
or we could assign some analytic privileges, run a procedure or create a decision table.
For this simple example we will create an analytic view [NOTE: in SAP HANA 2, you would create a Calculation View]
And let's call this Orders
okay, so it's created the the view within my STS_views package,
so if I scroll down I now have an analytic view called Orders
I don't need to use the system panel anymore or the project explorer so I can actually close that and give myself a little bit more room.
I can move these panels around
If you find that you've moved them around too much
You could go up to Window > Perspective and choose Reset Perspective.
That will bring ithe panels back to where it was when you started.
So let's start creating our model.
We're going to start here with the data foundation.
The Data Foundation is the Information that we want to bring into our model
So we're going to start adding in our objects and remember we had three different tables
We had a dimension product table and a dimension customer table. Let's go ahead and add those.
Then we also had our Fact table, which was our fact customer orders table, so let's bring that in as well.
Now we have our three tables, and we could move these around.
Next we want to create the associations between the tables,
So what's our relationship?
Well, the product_ID within our dimension product table relates to the product_ID in our fact table.
The same with the customer_ID within the customer table is related to the customer_ID within our fact table.
Then the next thing we want to do is we want to identify the fields
that we want to pull from our tables to bring into our analytic view, or into our table joins here that we're going to do.
We may not need to have all of the different fields,
we might want to just bring in the product name, the Category names, etc.
I can hold down the shift key or I can use the CTRL control key if they're not contiguous.
Let's choose product name, category name, linename,
Let's go ahead and choose capacity and screen size.
Then we choose Add to Output. You'll notice they appear in bold type now
You've got the little indicator to say that we've added that to our output columns, and now they're over here in our output area.
So we will do the same thing for our customer tables.
Let's choose customer name, our customer city, country, and maybe the region and click Add to Output.
Then within our fact table we want to identify
What are the fields that we want to pull from our fact table?
So let's pull our quantity, Let's see what the discount was, maybe choose the net sales, and quantity ordered.
Now we've indicated all the different fields we want.
So now what happens is: we've created our data foundation,
We've created our star join. (The output from our data foundation is, then is basically the input for our star join.
So here now is our Analytic view that we've created with just those fields that we've selected
Now at this opportunity, this is also our Logical area where we could start to do some manipulations or calculations with the data
if I wanted to create some calculated columns
Maybe combine a quarter indicator with the year etc for our sales reporting, we could do that here.
I'm just gonna do a very simple layout so we don't need to do any additional calculations or manipulations to the data here
So then our third place that we're going to look at or a third stage in creating our view is our Semantics area.
This is where we're going to take a look at what we want to have the end user see within our Analytic view.
We have the opportunity to take a field name such as product and actually give it a user-friendly label
for our end user that might just be product instead of productname and category instead of categoryname, etc.
it's so we have the opportunity to change how it looks to the end-user here
The other thing that we need to identify is whether the field is going to be a measure -- meaning a
numeric field that we're going to sum or average or count.
Or whether it's going to be an attribute or a dimension where it's simply describing the informatin.
So our first couple fields here
Product names, city names, even our capacity and screen size - they are all dimensions,
so they're describing our information
I can highlight those and I can come up to my Attribute indicator here
and choose the little blue flag and mark that as an attribute.
If I want to come down and do all my numeric fields like quantity, net sales, discounts, etc
Then I can use the ruler and mark those as my measures.
Now you see that it's given me the aggregation type to be able to do something with those fields
So for the quantity, I might want to sum
for discount, I might want to do count or an average or a min or a max
and so I have the capability of doing that as well.
Okay, so now that we've identified what types of fields we have -
whether they're dimensions or measures, and we've indicated for the measures what type of aggregation level we want to use.
The other thing that we can do is we can hide a field,
So let's say afterwards you decided that maybe you didn't want to see the screen size indicator anymore.
I could just come in here and highlight screen size field
and click Hide to hide that from my view.
I don't have to go all the way back down into my data foundation level
and unselect that field -- I can simply hide it from my view here in the semantics area.
So the next thing I want to do is let's just go ahead and save so this little green arrow
Here is our Save and Validate, so if we do that
It's going to validate our model and just do a save on it
And then the next thing we want to do is we want to get go ahead and do a save and activate
So now we do a Save and Activate
And we see our little indicator here.
If I look at my job log it's going to tell me that my activation has completed successfully
If I saw any that said "Completed with Warnings" then l could double click on that message and it would give me the error log here
that I could take a look at and try to determine what was wrong
but it says " created successfully" so now if I go back over to my system viewer
I can refresh on my analytic views and I can see my orders.
I can right click and do a Data Preview.
I see that I've actually created my analytic view with my information in it.
Again just for a validation check I can take a look and make sure that my data looks correct
Note I'm not doing my end-user analysis here within the HANA modeler, but
as the model developer I could just take a quick look at my data and make sure that it looks valid.
So there are many many videos on the SAP HANA Academy that can walk you through creating much more complex models,
go through the various types of views, what you can do with them, creating stored procedures
creating the ODBC connection to be able to connect this model to
some analytic tools like Lumira or some other third-party tools
We can go through those at a later date. Thanks for watching