SAP HANA Appliance. For more information, visit the SAP HANA Developer Center homepage.
In this tutorial, you will view high-level steps for creating and populate tables by using SQL Script and creating attribute views with hierarchy in SAP HANA Studio..
Author(s): Carlos Basto
Created on: 21 January 2013
Carlos Basto is a Senior Programmer at Accenture. He has been involved in SAP BW Consulting and Support Projects.
SAP HANA Studio is the central tool for developers in your SAP HANA landscape for defining the tables that will hold your data, setting up data provisioning on SAP HANA, and modeling data into views.
In this tutorial, you will view high-level steps for creating and populate tables by using SQL Script and creating attribute views with hierarchy in SAP HANA Studio.
Just for demo purposes, let’s first create tables and populate them with data to create our attribute view.
There will be 3 tables created by SQL Script programming in order to serve as master data tables.
Of course we can create tables by using right click menu on “Tables” space in your schema. But, only for learning purposes, let’s do it by selecting in menu “SQL Editor”:
We are going to create a column table for Suppliers texts, considering all needed fields, like SPRAS for example. You can set all characteristics you want for the table just like you’d do that on “New Table” menu.
Your code might look like that:
Remember to use your “schema”. before you name your name in the code. Otherwise, the table will be created in the default schema: SYS.
A message like this should appear:
The same way, we are going to create the table “T_SUPPLIER_ATTR” for attributes data from suppliers.
After creating them, we go for populating data into tables we’ve create. The sample code is:
HINT: Sometimes, depending on the way you open the SQL Editor, you can see after pressing F8 a message:
You can notice that in the property section you’ll have settings for Session and System.
We can execute our SQL statement now:
After being populated both tables we are going to update the catalog list in the left side of SAP HANA Studio to see our tables.
To see the content just press right button on it and choose “Open Content”.
If you prefer you can do it by using SQL Editor with the code:
SELECT TOP 1000 * FROM “CARLOSBASTO”.“T_SUPPLIER_TEXT”
We are going to use “Content View” now, still in the left side column in the SAP HANA Studio.
For transporting matters, you will need to create a Delivery unit in order to create a Package. The Delivery unit contains all of your models. But it’s not being covered in this document.
Then, select the name of the attribute view and click “Next”.
In the next page, we are going to choose the tables we’ve created and click “Finish”.
Next page will bring both tables in a Database view.
Now we are going to set what fields we want to use in our attribute view as just as the fields will be used in the hierarchy.
It’s mandatory choose at least one key attribute for the view. Let’s do it so.
In the join properties you can choose the join type and define a Language Column.
In this case, we are going to use the “Text Join” type, because we intend to bring only text data to this view.
You can only use “Text join” if you have “Language Column” in one of the used tables. Otherwise, you’ll have to use “Referential” type, beucase there’s no language field validation to this selection.
Then, as written above we have to define a Key Attribute for our attribute view.
After the selection, it automatically will appear in the output pane.
And then, we select the fields we want as Attributes
Now, our output pane should appear like this:
Let’s define in the field “supplier_id” the text to be used from the table t_supplier_text that we’ve made.
Click on the attribute and go to the properties to set it up.
In this case, there was a warning about Lower Case character. As it’s not a real problem, let’s go ahead.
Ready! Our attribute view is complete. Let’s take a look in the data to confirm our join was done successfully.
Only for learning purposes, inserted data was not completely joined.
Where you can see “?” means that there weren’t texts found in the table t_supplier_text to the supplier_id field in the table t_supplier_attr.
Once we’ve create the attribute view, let’s create a hierarchy to shown data in an organized way.
Click twice on the Attribute View you created to select it.
You should see something like that:
Consider that hierarchy will work on the attribute view, not on the tables that we based it on. So, the hierarchy must be create in the Output pane.
In node style option, you can select how the node will look like. In this case, we’ll use “LEVELNAME” node style.
Here, we can define how many and what are the hierarchy levels.
This hierarchy will be:
A new hierarchy will appear in the output pane.
We can’t get the tree structure displayed in preview mode (in SAP HANA Studio), but we can verify our leveled hierarchy’s accuracy with the usual Data Preview function, which will display the hierarchy in table format.
This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.
SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk.
SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document.