SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SAP HANA Modeling
+
-
Spatial Processing

Text Join


Text Join is used in order to get language-specific data.

Before understanding the text join, it is important that we understand Text Tables.

Text Tables:

Table A is a text table of table B if the key of A comprises the key of B and an additional language key field (field of data type LANG). Table A may therefore contain explanatory text in several languages for each key entry of B.
SAP HANA Text Table

Text Join in SAP HANA:

Text Join is used to fetch the description based on user's session language. Once we implement the text join in SAP HANA, it automatically find out user's language and give description in that language.

Implementation of Text Join in SAP HANA:

Example:
Suppose there is a table called "PRODUCT" which contains Product ID and Product Name.
There is also a text table of "PRODUCT", which is called "PRODUCT_TEXT_TABLE". The text table contains the description of products in different language. For example "Cotton Shirts" in English and "Baumwoll-Shirts" in German.

Purpose:
Create a calculation view which will give Product Name and Product Description. The Product Description should be only in user's session language.

Implementation:
    1. Open HANA Studio. Right click on your schema and open SQL Editor. Copy the below SQL script to create 2 tables - "PRODUCT" and "PRODUCT_TEXT_TABLE".

      -- REPLACE WITH YOUR ACTUAL SCHEMA NAME

      -- PRODUCT table
      CREATE COLUMN TABLE ""."PRODUCT"(
                  "PRODUCT_ID" INTEGER ,
                  "PRODUCT_NAME" VARCHAR(20) ,
                  primary key ("PRODUCT_ID"));

      -- PRODUCT text table
      CREATE COLUMN TABLE ""."PRODUCT_TEXT_TABLE"(
                  "PRODUCT_ID" INTEGER ,
                  "LANGUAGE" VARCHAR(1),
                  "PRODUCT_DESCRIPTION" VARCHAR(50) ,
                  primary key ("PRODUCT_ID", "LANGUAGE"));

      insert into ""."PRODUCT" values(1,'Shirts');
      insert into ""."PRODUCT" values(2,'Jackets');
      insert into ""."PRODUCT" values(3,'Trousers');
      insert into ""."PRODUCT" values(4,'Coats');
      insert into ""."PRODUCT" values(5,'Purse');

      insert into ""."PRODUCT_TEXT_TABLE" values(1,'E', 'Cotton Shirts');
      insert into ""."PRODUCT_TEXT_TABLE" values(1,'D', 'Baumwoll-Shirts');
      insert into ""."PRODUCT_TEXT_TABLE" values(2,'E', 'Leather jacket');
      insert into ""."PRODUCT_TEXT_TABLE" values(2,'D', 'Lederjacke');
      insert into ""."PRODUCT_TEXT_TABLE" values(3,'E', 'Trousers and Pants');
      insert into ""."PRODUCT_TEXT_TABLE" values(3,'D', 'Hosen und Hosen');
      insert into ""."PRODUCT_TEXT_TABLE" values(4,'E', 'Coats and Blazers');
      insert into ""."PRODUCT_TEXT_TABLE" values(4,'D', 'Muntel und Jacken');
      insert into ""."PRODUCT_TEXT_TABLE" values(5,'E', 'Purse and Handbags');
      insert into ""."PRODUCT_TEXT_TABLE" values(5,'D', 'Geldburse und Handtaschen');


      Note: The Language code for English is "E" and for German is "D"
    2. Right click on your schema and refresh to see the 2 tables created.

      SAP HANA Text Join
    3. Go to "Content" and right click on your package. Select Calculation view.

      SAP HANA Text Join
    4. Give Name and Description of the Calculation View. Select Data Category as "Dimension".

      SAP HANA Text Join
    5. Add a "Join" block and add both the tables "PRODUCT" and "PRODUCT_TEXT_TABLE".

      SAP HANA Text Join
    6. Make a join on the column "PRODUCT_ID". Specify the Join Type as "Text Join". Click on the Language option and select "Language" column. Add "PRODUCT_ID", "PRODUCT_NAME" and "PRODUCT_DESCRIPTION" to output.

      SAP HANA Text Join
    7. Connect the Join block to Projection Block. Add all the 3 column to output.

      SAP HANA Text Join
    8. Select the Semantics block. Select the Type of Columns as "Attribute". This might be selected by default. Save and Activate the view.

      SAP HANA Text Join
    9. Click on the Data Preview to see output of the calculation view.
      The output contains only the English Description.

      SAP HANA Text Join


Change User Session Language (Locale) and Check The Output Again:


    1. Right click on the HANA system and select "Properties".

      SAP HANA Text Join
    2. Click on the tab "Additional Properties" and select the Locale as "German"

      SAP HANA Text Join
      Click on "OK".
    3. Check the output of the calculation view. Now the output contains German Description.

      SAP HANA Text Join

Support us by sharing this article.



Explore More
Close X
Close X

7 thoughts on “SAP HANA Text Join

  1. ram says:

    Hi,

    E represents English, D represents German Like this where can we get the list of all Language codes?

  2. Fabian Szymczak says:

    Hi,

    thanks for this helpful advice.

    In my case I have defined the extra column “masterLanguage” in table PRODUCT. So if the session language is not available in the “Language” column, the text join should use the “masterLanguage”, which is for example E.

    My problem is, I am not able to figure out where I can make this setting. Maybe you have another advice for me?

    Regards,
    Fabian

    • Admin says:

      Hi Fabian,
      In case the session language specific entry is not there in the table, text join will pick the entry belonging to default language English.
      You can just make sure that atleast english language description is available.

  3. Karthik says:

    Can you please the default join type of text join.
    Suppose if there is no matching record in text table for the master data table, then left join will apply to that or it will apply inner join ?

  4. Palani Ramu says:

    super explanation, Variables / input parameter for analytics view of SAP HANA

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy