Skip to Content

1-21-2013 2-57-58 PM.png

Applies to:

                SAP HANA Appliance. For more information, visit the SAP HANA Developer Center homepage.

Summary

                 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
Company:    Accenture
Created on:  21 January 2013

Author Bio

DSC_0166.jpg

Carlos Basto is a Senior Programmer at   Accenture. He has been involved in SAP BW Consulting and Support Projects.

 


Table of Contents

1.            Creating tables for using as source to Attribute View. 3

2.            Populating tables with data. 4

3.            Creating the Attribute view. 6

4.            Setting up the Attribute View properties and joins. 7

5.            Creating Hierarchy. 11

6.            Related Content 16

Disclaimer and Liability Notice. 17

1.    Creating tables for using as source to Attribute View

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”:

https://bhchyg.blu.livefilestore.com/y1pHyGFjfixKExZRrGdYcTaRZL7W1JkQamGaUWrFxei0ExeWfe4dmyTGquZlY6yQZsY7Vu5TyNohBY/image003.jpg?psid=1    

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:

/wp-content/uploads/2013/01/image004_177408.jpg

Remember to use your “schema”. before you name your name in the code. Otherwise, the table will be created in the default schema: SYS.

Now we can press F8 or the button /wp-content/uploads/2013/01/image005_177409.jpg

A message like this should appear:

/wp-content/uploads/2013/01/image006_177410.jpg

The same way, we are going to create the table “T_SUPPLIER_ATTR” for attributes data from suppliers.

https://bhchyg.blu.livefilestore.com/y1pHwAM2ThG2U-cjnErdvgIR6-GTTYRy82W9sqlz6ynTB8B1G5r1XhV6tWHHHvYseAlJ_mXMFI19ws/image004.jpg?psid=1

2.    Populating tables with data

After creating them, we go for populating data into tables we’ve create. The sample code is:

https://bhchyg.blu.livefilestore.com/y1phxZAGvf4RjJ_Brf3Ru_WExMcJUxHYxMPwrFU8vOj_6_RAflyzQ7VQ9rdkNDAfbtmA6JSqBPvK80/image008.jpg?psid=1

HINT: Sometimes, depending on the way you open the SQL Editor, you can see after pressing F8 a message:

https://bhchyg.blu.livefilestore.com/y1phxZAGvf4RjIwi-z2E0RTpEyYt96CDDSDP_OXsY--vZ70-9IQZzuYL0JzNH5JKCvf5AoWCdZPF2U/image009.jpg?psid=1

If this happens just go press the button https://bhchyg.blu.livefilestore.com/y1phxZAGvf4RjLs15BXMb0XxclM8Yfh7NcvxzOHMN9Q6e5KY_hBl-Fest_uAIC2Sg_Gp0dh_HflFyo/image010.jpg?psid=1 (choose connection), select your catalog and then press ok.

https://bhchyg.blu.livefilestore.com/y1pv6QUCF2cy27_FO_UPE2dQGAFlDzuZYAQoohRNCGvGXrl0T9wmnpcz2eynVC_T268ft57srWJeEY/image011.jpg?psid=1

You can notice that in the property section you’ll have settings for Session and System.

https://bhchyg.blu.livefilestore.com/y1po9a_HTVaGVtEVVZ2N30fxDvTc7pK0M-W5gn9ju_B8TzO9Ot4Wod_UU6eVP2GrEjZPnfvCm4x44Y/image012.jpg?psid=1

We can execute our SQL statement now:

https://bhchyg.blu.livefilestore.com/y1po9a_HTVaGVvaNfHU1YtioJuxwD9Yjax7-mBO6QBRYHLqnUTtmBbRmyK_3wrHoGRrXx5L8vD1jB8/image013.jpg?psid=1

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.

https://bhchyg.blu.livefilestore.com/y1pMIhDfw79RN0a4vfhHjT9sx3eQy1ht_VJI03pQ8JpYPmRtHh3c35qQHfVinS2duz4unyZofo55uI/image014.jpg?psid=1

https://bhchyg.blu.livefilestore.com/y1pMIhDfw79RN0ytd6PtFgaUr2daVOETAB7xXtaJuYb8tQiCeK1lXlwkL-PDfSAfXyFqbr9nDq_b3k/image015.jpg?psid=1

To see the content just press right button on it and choose “Open Content”.

https://bhchyg.blu.livefilestore.com/y1pMIhDfw79RN3ReKG4r4QeRmerYKUNru_heQbXZlzx9Gx14KqGCauuvw8dk-JCzNB6BjDjks7xSnY/image016.jpg?psid=1

If you prefer you can do it by using SQL Editor with the code:

SELECT TOP 1000 * FROM “CARLOSBASTO”.“T_SUPPLIER_TEXT”

3.    Creating the Attribute view

We are going to use “Content View” now, still in the left side column in the SAP HANA Studio.

https://bhchyg.blu.livefilestore.com/y1ptVRfEWVtz3aRSfspfoYK5JkK_fsNOZ4L7dkplL4WWSAKz47q-xVUjvBjQlvNGzMo69rYllgYcQY/image017.jpg?psid=1

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”.

https://bhchyg.blu.livefilestore.com/y1pT3mg6DgChOTy8M233BB28SOVKn1KT-lx3fWK8w1rvyuBZRUIzXLTL_0uXgaJFdtSULuKHfopLVk/image018.jpg?psid=1

In the next page, we are going to choose the tables we’ve created and click “Finish”.

https://bhchyg.blu.livefilestore.com/y1pT3mg6DgChORtXqFeVJPbbwhKJeRunHzPV1kElK7KgWkma2SZzW1eJrbbrx11SCEGKODxaBMxyPc/image019.jpg?psid=1

Next page will bring both tables in a Database view.

https://bhchyg.blu.livefilestore.com/y1pT3mg6DgChOTYKYgZ7duQk-d_rUBYRGJbHnYD2Y-YJOFsAFWBrsRIiwD483vr6FspSppBEzR13h0/image020.jpg?psid=1

4.    Setting up the Attribute View properties and joins.

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.

https://bhchyg.blu.livefilestore.com/y1pRhZTtXfsFUBdFvcy7wOVZlHKP-ubbq7kUS8Lew8U9M1LVn4ES377mxuTTD5gMfvBptG0zWYk1pI/image021.jpg?psid=1

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.

https://bhchyg.blu.livefilestore.com/y1py23dVK5sQc5PO3iv31rEBKpxxj4raMKEuwGFfCdFKt6ZZ9TSu_HSEanQHObcBUvE9SoR1s5orB0/image022.jpg?psid=1

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.

https://bhchyg.blu.livefilestore.com/y1pRhZTtXfsFUCvY0dmpjEaA9jwjTS-mheaFihBLu2NYOvmTeQxR8xe4OsOsbeaWWqPaF4N-WmPGLE/image023.jpg?psid=1

Then, as written above we have to define a Key Attribute for our attribute view.

https://bhchyg.blu.livefilestore.com/y1pZkzRHBBP9EJqpxNc33l2bD62rf2BmcgC8sRTa8s9_IZQwrNi17axbdI4IX0ZvojBCdPMbR9641c/image024.jpg?psid=1

After the selection, it automatically will appear in the output pane.

https://bhchyg.blu.livefilestore.com/y1pZkzRHBBP9EL0Ol1AixKknNpqtVmVMZH607rcey1mhSAPcXcrn_46diC51eZIoQFEFjCxzR6Oe08/image025.jpg?psid=1

And then, we select the fields we want as Attributes

https://bhchyg.blu.livefilestore.com/y1pZkzRHBBP9EItLGGhY0GhMFrhOSqY_M7TQtnbHGdoWfQ7CN911ZIiydj1Rst7zNwL1mrNUP-0J8c/image026.jpg?psid=1

Now, our output pane should appear like this:

/wp-content/uploads/2013/01/image027_176898.png

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.

/wp-content/uploads/2013/01/image028_176900.png

/wp-content/uploads/2013/01/image029_176901.png

By pressing /wp-content/uploads/2013/01/image030_176902.png, you can see if there’s any point to adjust or if it’s all right.

/wp-content/uploads/2013/01/image031_176903.png

In this case, there was a warning about Lower Case character. As it’s not a real problem, let’s go ahead.

Press /wp-content/uploads/2013/01/image032_176904.png to save and activate our attribute view.

/wp-content/uploads/2013/01/image033_176905.png

Ready! Our attribute view is complete. Let’s take a look in the data to confirm our join was done successfully.

/wp-content/uploads/2013/01/image034_176906.png

Only for learning purposes, inserted data was not completely joined.

/wp-content/uploads/2013/01/image035_176907.png

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.

5.    Creating Hierarchy

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.

/wp-content/uploads/2013/01/image036_176908.png

You should see something like that:

/wp-content/uploads/2013/01/image037_176909.png

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.

/wp-content/uploads/2013/01/image038_176910.png

Name it:

/wp-content/uploads/2013/01/image039_176912.png

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.

/wp-content/uploads/2013/01/image040_176913.png

Press /wp-content/uploads/2013/01/image041_176914.png button to add an attribute as hierarchy level.

This hierarchy will be:

/wp-content/uploads/2013/01/image042_176915.png

è  Nationality

è  Region

è  City

A new hierarchy will appear in the output pane.

/wp-content/uploads/2013/01/image043_176916.png

By pressing /wp-content/uploads/2013/01/image030_176902.png, you can see if there’s any point to adjust or if it’s all right.

Press  /wp-content/uploads/2013/01/image032_176904.png to save and activate our attribute view.

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.

USA East reg_01
USA Nort reg_01
USA Nort reg_02
USA North reg_01
USA North reg_03
USA South reg_01
USA South reg_02
USA West reg_03
USA West reg_04

6.    Related Content

                    SAP PRESS: SAP HANA An Introduction


Disclaimer and Liability Notice

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.

To report this post you need to login first.

18 Comments

You must be Logged on to comment or reply to a post.

        1. Carlos Basto Post author

          Thank you.

          I will reload images as soon as possible.

          By the way, did you try see the artcle by using another browser?

          Again, thanks.

          (0) 
            1. Carlos Basto Post author

              I had faced problems in reloading because of scn site.

              I made some changes in the version and reloaded the images with problems.

              Can you give me a feedback about it?

              (0) 
                1. Carlos Basto Post author

                  I reloaded image001.jpg now.

                  Thank you for you patience and interesting.

                  Feel free to contact me anytime for more information about this article.

                  (0) 
  1. Ravi Shankar

    Hi Carlos,

    Thanks for sharing the nice document on SAP HANA, images are not getting display.

    I have tried popular browsers like IE, Mozilla and Chrome but the images are not getting display when we open the doument.

    (0) 
    1. Carlos Basto Post author

      Shankar, please refer to the my comment above.

      If it does not solve the problem, I’ll try reloading every images again.

      Let me know about the results.

      Thank you!

      (0) 
    1. Carlos Basto Post author

      Hi Vitaliy, thank you for your question.

      You can notice that data is organized in the AFTER picture different from BEFORE picture. Considering the tables and records purposes to this document, hierarchy levels might look a little tough to understand.

      You can’t get the tree structure displayed in preview mode, but you can verify your leveled hierarchy’s accuracy with the usual Data Preview function, which will display the hierarchy in table format.

      I hope it solve your doubt.

      Again, thank you!

      (0) 
      1. Witalij Rudnicki

        When it comes to order of records, I see that on BEFORE screen you have data sorted by supplier_reg column, while the sorting is default on the AFTER screen đŸ™‚ Or may be my eyes are tired and I cannot see properly.

        (0) 
  2. Mohit Saini

    Thanks Carlos for such a wonderful tutorial.

    I am currently learning HANA and facing a problem in creating attribute view.   On HANA studio when I go to a package and right click on it to create new attribute then the window that I get doesn’t have the “next” button.  Also the title of the pop up says “Informational view” and not “attribute view” as shown in your screen.  Once I fill in the view details then it doesn’t ask for any table details and I am then unable proceed forward with creating an attribute view. 

    I wish I could attach a screen shot to show you what exactly I am getting.  But I hope you are getting what I am saying.

    Regards,

    Mohit

    (0) 
    1. Carlos Basto Post author

      Hi Mohit Saini, thank you for your question!

      Have you already checked your privileges on SAP HANA Studio? It might be a authorization problem.

      In addition, could you, even in this scenario, validate (press validation button) your attribute view? Because it’s going to show you the log of this action with meaningful and detailed information about what’s happening or which’s the error.

      I wait your feedback and hope that helps!

      Again, thank you.

      (0) 
  3. rajesh kumar

    Hi Carlos,

    your document is simply super.

    here i have a quesion,

    we have requirement to create a time dependent Hierarchy using flat files.

    for two intervals 2013 and 2014.

    what would be the process to go for it.

    do we have a time attributes available in the SAP HANA system as BW having.

    if we create one hierachy for 2013 does this structure reuse for 2014 hierachy.

    could you please clarify this ASAP.

    Regards

    Raj

    (0) 

Leave a Reply