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


Course Name - SAP HANA Advanced Modeling

This course contains more advanced topics on data modeling.
After completing this course you will be able to
    • • create advanced data models in HANA
    • • work on SQLScript and Procedure
    • • understand how views and procedures work behind the scene
    • • create Analytic Privilege to secure the modeling views
    • • do performance tuning of the modeling views and procedures
Course Syllabus:
    • • Advanced Topics on Modeling Views
    • • Analytic privilege
    • • System Schemas and Their Importance
    • • SQLScript
    • • Procedure
    • • Performance Guideline
For more details check the course index in right side.
Course Duration:
8 hours

Prerequisite:
You must have basic knowledge of SAP HANA.
If you do not know HANA basics please go through the "SAP HANA Basics Course" first.

No Course Fee - Totally Free:

This course is free of charge. Just one note, if you really like the course, please share it with your friends and do provide your valuable feedback/comments through Contact Us.
You can also share information, ask questions, provide feedbacks and suggest us through The Forum .


Important Notes:
    • • This course is free and self-paced.
    • • It can be taken any time.
    • • We strongly recommend that you follow the sequence of the course and Do Not Randomly Go through Chapters.
    • • If you face any problem or have any query, please connect to The Forum
    • • If you like the course, please share this with your friends.



Good Luck!!
1.1. A Revisit to Calculation Views

Calculation views are composite views and can be used to combine other views. It can consume other Analytical, Attribute, other Calculation Views & tables.
It can perform complex calculations not possible with other views.

Calculation view can be defined as either graphical views or scripted views depending on how they are created.
Graphical views can be modelled using the graphical modeling features of the SAP HANA Modeler. Scripted views are created as sequences of SQL statements. In this chapter we will create a simple calculation view which will be used further to explain other features like
    • • Variables and Input Parameters
    • • Analytic Privileges

Prerequisite:
Scenario:
Suppose there are 3 tables PRODUCTS, REGION and SALES.



We need to find out
    • • What are the sales value for "Asia" region?
    • • What are the total sales value for "Shirts"?
    • • What are the total sales value for "Europe" for "Jackets"?
Let us create a graphical calculation view to get answer of the above mentioned questions.

Create new tables in SAP HANA and fill them with data:
  1. Open HANA Studio and expand the SAP HANA system.
  2. Go to your schema. Right-click on your schema and select SQL editor.
    SAP HANA Studio
    Note: In this example schema name is "SAP_HANA_TUTORIAL". In case you want to create a new schema use below query.
    create schema <schema_name>;
  3. Copy and paste the below script in SQL editor and execute.

    ----REPLACE <YOUR SCHEMA> WITH YOUR SCHEMA NAME

    ---- Create Product table
    create column table "<YOUR SCHEMA>"."PRODUCT"(
          "PRODUCT_ID" INTEGER,
          "PRODUCT_NAME" VARCHAR (100),
          primary key ("PRODUCT_ID")
    );

    insert into "<YOUR SCHEMA>"."PRODUCT" values(1,'Shirts');
    insert into "<YOUR SCHEMA>"."PRODUCT" values(2,'Jackets');
    insert into "<YOUR SCHEMA>"."PRODUCT" values(3,'Trousers');
    insert into "<YOUR SCHEMA>"."PRODUCT" values(4,'Coats');
    insert into "<YOUR SCHEMA>"."PRODUCT" values(5,'Purse');
    -- Create Region table
    create column table "<YOUR SCHEMA>"."REGION"(
          "REGION_ID" INTEGER,
          "REGION_NAME" VARCHAR (100),
          "SUB_REGION_NAME" VARCHAR (100),
          PRIMARY KEY ("REGION_ID")
    );

    insert into "<YOUR SCHEMA>"."REGION" values(100,'Americas','North-America');
    insert into "<YOUR SCHEMA>"."REGION" values(200,'Americas','South-America');
    insert into "<YOUR SCHEMA>"."REGION" values(300,'Asia','India');
    insert into "<YOUR SCHEMA>"."REGION" values(400,'Asia','Japan');
    insert into "<YOUR SCHEMA>"."REGION" values(500,'Europe','Germany');

    -- Create Sales table
    create column table "<YOUR SCHEMA>"."SALES"(
          "REGION_ID" INTEGER ,
          "PRODUCT_ID" INTEGER ,
          "SALES_AMOUNT" DOUBLE,       PRIMARY KEY ("REGION_ID", "PRODUCT_ID") );

    insert into "<YOUR SCHEMA>"."SALES" values(100,1,100);
    insert into "<YOUR SCHEMA>"."SALES" values(100,2,90);
    insert into "<YOUR SCHEMA>"."SALES" values(100,5,85);
    insert into "<YOUR SCHEMA>"."SALES" values(200,2,80);
    insert into "<YOUR SCHEMA>"."SALES" values(200,1,75);
    insert into "<YOUR SCHEMA>"."SALES" values(300,3,85);
    insert into "<YOUR SCHEMA>"."SALES" values(400,4,75);
    insert into "<YOUR SCHEMA>"."SALES" values(500,1,65);
    insert into "<YOUR SCHEMA>"."SALES" values(500,2,65);
  4. After executing the scripts you should have 3 tables created. If there are no tables, try right-clicking on your schema and refreshing.
    SAP HANA Studio

Grant schema SELECT rights to _SYS_REPO user:

Open the SQL editor of your schema and execute the following command line:
GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION;
If you miss this step, an error will occur when you activate your views later.
Click here to know more about this.

Steps for creating calculation view:

  1. Open HANA Studio and make sure you are in Modeler perspective. Right click on the package and choose "Calculation View"
  2. Enter a name and a description. Select Subtype as “Graphical” and Data Category as “Cube”. Then click on “Finish”.
    SAP HANA Calculation View
  3. You will see the graphical editor as shown in image below.
    SAP HANA Calculation View
  4. Click on the “Join” symbol and drag it in the scenario area to create Join block. Click on “+” sign of “Join” and add REGION tables.
    SAP HANA Calculation View
  5. Similarly add SALES table.
  6. Select the REGION_ID from REGION table. Drag it and drop it on the REGION_ID of SALES table. Join type can be changed in the Properties tab. Let it be Inner Join as of now.
    SAP HANA Calculation View
  7. Add REGION_NAME, SUB_REGION_NAME, PRODUCT_ID AND SALES_AMOUNT to the Columns by right clicking and clicking on “Add To Output”. Alternatively you can also click on the grey color circle before the column name to add it to output.
    SAP HANA Calculation View
  8. Click on the “Join” symbol again and drag it in the scenario area to create another Join block. Connect the previous Join block to new Join block with the arrow.
    SAP HANA Calculation View
  9. Click on “+” sign of new Join block and add PRODUCT table.
    SAP HANA Calculation View
  10. Select the PRODUCT_ID from Join_1. Drag it and drop it on the PRODUCT_ID of PRODUCT table. Select the Join type as Inner Join.
    Add REGION_NAME, SUB_REGION_NAME, PRODUCT_ID, SALES_AMOUNT and PRODUCT_NAME to the Columns by right clicking and clicking on “Add To Output”.
    SAP HANA Calculation View
  11. Add the Join block "Join_2" to "Aggregation" block by arrow. Add all the columns to output.
  12. Select Semantics. Now we need to specify which columns are attributes and which columns are measures. Click on the “Auto Assign” button to do it automatically.
    Alternatively you can also specify the Attribute/measure by clicking on Type down-arrow. Select SALES_AMOUNT as measure and rest of the column as attribute.
    SAP HANA Calculation View
  13. Activate the calculation view. Right-click on your calculation view and choose “Data Preview”. After that, you can browse through the tabs named Raw Data, Distinct Values, and Analysis.
    Analysis tab:
    SAP HANA Calculation View

    Raw Data tab:
    SAP HANA Calculation View
Congratulation!! You have successfully created your first Graphical Calculation View.

1.2. Variables

Variables and Input Parameters in HANA:

Sometimes we need to filter the data based on user's input. This is where Input Paramters and Variables come into the pictures.
Input parameters and variables are used to filter the result of modeling views.

SAP HANA Modeling

Let us first see what is Variable and how does it work.

Introduction to variables:

Variables are bound to columns and are used for filtering using WHERE clauses. As such, they can only contain the values available in the Columns they relate to.

Sounds confusing?? Let us take a simple example.
In the previous chapter, we created a calculation view which has 4 columns PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME and SALES_AMOUNT.
REGION_NAME contains different regions like “America”, “Asia” and “Europe”. The calculation view gives all the region’s details in output.

SAP HANA Modeling

Now suppose we want to filter the data on region basis. The calculation view will prompt the user to specify Region Name and the output data will be filtered based on that.
For example if the user provides the value as “Asia”, the calculation view will show data only for “Asia” region.

SAP HANA Modeling

How to create and use variables in modeling view:

Now we will see how to implement this using variable.
    1. Open the calculation view. On Right-Bottom side you will see a pane called “Variables/Input Parameters”. Click on the down arrow of “+” icon and select “Variable”

      SAP HANA Modeling

    2. Specify Name of the Variable. Click on the drop down arrow besides “Attribute” and select REGION_NAME. Click on ok.

      SAP HANA Modeling

    3. Activate the calculation view. Now click on the data preview.
    4. A pop-up window will appear. Click on Open Value Help besides “From” and select “Asia” region. Click on ok.

      SAP HANA Modeling

    5. Go to Raw Data tab to see complete output. It contains data only for “Asia” region.

      SAP HANA Modeling

Important features of variables:

    • • You use variables to filter data at runtime. You assign values to these variables by entering the value manually, or by selecting it from the drop-down list.
    • • You can also define whether the Variable is Mandatory or if it should have a Default Value.
    • • You can apply variables only in analytic and calculation views.

Variable Types:

The following types of Variables are supported:
    • Single Value: Use this to apply a filter to a Single Value.
    • Interval: Use this where you want the user to specify a set start and end to a selected Interval.
    • Range: Use this when you want the end user to be able to use operators such as “Greater Than” or “Less Than”.

System Tables to see Variable Information:

There are 4 system tables under the schema _SYS_BI which contains information about Variables.
    • • BIMC_VARIABLE
    • • BIMC_VARIABLE_ASSIGNMENT
    • • BIMC_VARIABLE_VIEW
    • • BIMC_VARIABLE_VALUE
1.3. Input Parameters

Introduction to Input Parameters:

Sometimes you might not want a variable to just restrict the data of a view. But you also want to take input from the user and process it, returning dynamic data based on the user selection.
Input Parameters makes this possible.

How to create and use input parameters in modeling view:

Example Scenario:

Let us consider the same calculation view we created before which has following attributes and measures.
• Attributes: PRODUCT_NAME, REGION_ID, REGION_NAME, SUB_REGION_NAME
• Measures: SALES_AMOUNT

Suppose we want the end user to decide whether SALES_AMOUNT or NET_AMOUNT should be shown in the output.
We can create a input parameter of type “Static List” which will have 2 values “SalesAmount” and “NetAmount”.
The calculation view will prompt for the input parameter. The user will choose either “SalesAmount” or “NetAmount”. Based on this selection, the output will be shown.

Let us create the input parameter.
    1. Open the same calculation view. On Right-Bottom side you will see a pane called “Variables/Input Parameters”. Click on the down arrow of “+” icon and select “Input Parameter”.

      SAP HANA Modeling

    2. Provide Name, description and select the data type as INTEGER.

      SAP HANA Modeling

    3. Select the Aggregation block and right click on “Calculated Columns” to create a new column.

      SAP HANA Modeling

    4. Specify the name as “NET_AMOUNT”, Data Type as double. In the expression editor provide the expression as
                  "SALES_AMOUNT" - ( "SALES_AMOUNT" * $$DISCOUNT$$ /100)

      Note that we are using Input parameter DISCOUNT by using syntax $$DISCOUNT$$

      SAP HANA Modeling

    5. Activate the calculation view.
    6. Click on data preview. A pop-up window will be opened. Provide the Region Name as ‘Asia’ and Discount value as 10.

      SAP HANA Modeling

    7. The NET_AMOUNT will be calculated as per the given DISCOUNT value.

      SAP HANA Modeling

Important Features of Input Parameters:

    • • Input Parameters can contain any value the reporting user has to enter to parameterize the result. Therefore, a data type must be specified for each Input Parameter.
    • • Input Parameters are passed by Placeholders and used in Formulas.

Input Parameter Types:

The following types of Input variables are supported.

• Currency:
Use this during currency conversion where the end user should specify a source or target currency.

• Date:
Use this to retrieve a date from the end user using a calendar type input box.

• Static List:
Use this when the end user should have a set list of values to choose from.

• Attribute Value:
When an Input Variable has this type, it serves the same purpose as a normal Variable.

• None:
If none of the above applies you do not have to specify an Input Variable type. The Type can be left blank.

How to Pass Input Parameters and Variables in SQL Query:

Open the data preview of calculation view as mentioned in previous step.
Click on the “Show Log” and then double click on the message highlighted below. A new window will be opened which contains the SQL query.

SAP HANA Modeling

The generated query is:
SELECT TOP 200 "PRODUCT_NAME", "REGION_NAME", "SUB_REGION_NAME", "NET_AMOUNT", sum("SALES_AMOUNT") AS "SALES_AMOUNT"
     FROM "_SYS_BIC"."sap-hana-tutorial/SalesReport"
      ('PLACEHOLDER' = ('$$DISCOUNT$$', '10'))
      WHERE ("REGION_NAME" IN ('Asia') )
      GROUP BY "PRODUCT_NAME", "REGION_NAME", "SUB_REGION_NAME", "NET_AMOUNT"

Note that in your case package name might be different and query might be slightly different.

Input Parameter is passed using PLACEHOLDER:

The value of Input Parameter is passed using PLACEHOLDER clause.
    ('PLACEHOLDER' = ('$$DISCOUNT$$', '10'))

WHERE:
The value of Variable is passed using WHERE clause.
     WHERE ("REGION_NAME" IN ('Asia') )
1.4. Joins – Different Types of Joins in SAP HANA


A Join clause combines records from two or more tables /view in a database.
SAP HANA supports following types of join.
    • • Inner Join
    • • Left Outer Join
    • • Right Outer Join
    • • Full Outer Join
    • • Referential Join
    • • Text Join
In this chapter you will learn the details of all these type of joins.

We are going to use 2 tables to explain the different types of Join. CUSTOMER and SALES_ORDER.
SAP HANA Join Types

SQL Script to create the above mentioned tables:
-- REPLACE <Schema_Name> WITH YOUR SCHEMA

CREATE COLUMN TABLE <Schema_Name>."CUSTOMER" (
        "CustomerID" nvarchar(10) primary key,
        "CustomerName" nvarchar(50)
);
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C1', 'Alfred');
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C2', 'John');
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C3', 'Maria');
INSERT INTO <Schema_Name>."CUSTOMER" VALUES ('C4', 'Harry');

CREATE COLUMN TABLE <Schema_Name>."SALES_ORDER" (
        "OrderID" integer primary key,
        "CustomerID" nvarchar(10),
        "Product" nvarchar(20),
        "Total_Units" integer
);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (101, 'C1','Camera',300);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (102, 'C1','Mobile',200);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (103, 'C2','iPod',500);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (104, 'C3','Television',400);
INSERT INTO <Schema_Name>."SALES_ORDER" VALUES (105, 'C5','Laptop',800);

Inner Join:
The INNER JOIN selects the set of records that match in both the Tables.
SAP HANA Inner Join
Syntax
SELECT T2."OrderID", T1."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         INNER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

The result of Inner Join will be like this:
SAP HANA Inner Join

Where to Use?
Inner join should be used if referential integrity is ensured.
Inner Join is much faster that Outer Join thus is the preferred solution if possible from semantical perspective
Attribute View: Inner Joins can be used to join different master data tables to a joint dimension Analytical Views: Inner Joins can be used if referential integrity cannot be ensured


Left Outer Join:
The Left Outer Join selects the complete set of records from first table (CUSTOMER), with the matching records (where available) in second table (SALES_ORDER). If there is no match, the right side will contain null.
SAP HANA Left Outer Join
Syntax
SELECT T2."OrderID", T1."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         LEFT OUTER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

The result of Left Outer Join will be like this:
SAP HANA Left Outer Join

Right Outer Join:
The Right Outer Join selects the complete set of records from second table (SALES_ORDER), with the matching records (where available) in first table (CUSTOMER). If there is no match, the left side will contain null.
SAP HANA Right Outer Join
Syntax
SELECT T2."OrderID", T2."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         RIGHT OUTER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

The result of Right Outer Join will be like this:
SAP HANA Right Outer Join

Full Outer Join:
The INNER JOIN selects the set of records that match in both the Tables.
SAP HANA Full Outer Join
Syntax
SELECT T2."OrderID", T1."CustomerID", T1."CustomerName", T2."Product", T2."Total_Units"
     from "CUSTOMER" AS T1
         FULL OUTER JOIN
     "SALES_ORDER" AS T2
         ON T1."CustomerID" = T2."CustomerID";

The result of Full Outer Join will be like this:
SAP HANA Full Outer Join

All the 4 types of joins explained above are standard database join types.
SAP HANA also supports 2 new type of joins. Referential Join and Text Join. These 2 type of joins can only be used in modeling views.
Let's take a look into these 2 new type of joins.

SAP HANA Referential Join:
Referential Join is semantically an inner join that assume that referential integrity is given.

Note: Referential integrity is the property of database which ensures that each foreign key value in a table exists as a primary key in the referenced table.

Referential join is performance wise better than inner join, but only be used when you are sure that referential integrity is maintained.

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

You have a product table that contains product IDs without descriptions and you have a text table for products that contains language-specific descriptions for each product. You can create a text join between the two tables to get the language-specific details. In a text join, the right table should be the text table and it is mandatory to specify the Language Column.

In the next chapter we will learn more about Text join.

1.5. Text Joins in SAP HANA


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 <YOUR_SCHEMA_NAME> WITH YOUR ACTUAL SCHEMA NAME

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

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

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

      insert into "<YOUR_SCHEMA_NAME>"."PRODUCT_TEXT_TABLE" values(1,'E', 'Cotton Shirts');
      insert into "<YOUR_SCHEMA_NAME>"."PRODUCT_TEXT_TABLE" values(1,'D', 'Baumwoll-Shirts');
      insert into "<YOUR_SCHEMA_NAME>"."PRODUCT_TEXT_TABLE" values(2,'E', 'Leather jacket');
      insert into "<YOUR_SCHEMA_NAME>"."PRODUCT_TEXT_TABLE" values(2,'D', 'Lederjacke');
      insert into "<YOUR_SCHEMA_NAME>"."PRODUCT_TEXT_TABLE" values(3,'E', 'Trousers and Pants');
      insert into "<YOUR_SCHEMA_NAME>"."PRODUCT_TEXT_TABLE" values(3,'D', 'Hosen und Hosen');
      insert into "<YOUR_SCHEMA_NAME>"."PRODUCT_TEXT_TABLE" values(4,'E', 'Coats and Blazers');
      insert into "<YOUR_SCHEMA_NAME>"."PRODUCT_TEXT_TABLE" values(4,'D', 'Mäntel und Jacken');
      insert into "<YOUR_SCHEMA_NAME>"."PRODUCT_TEXT_TABLE" values(5,'E', 'Purse and Handbags');
      insert into "<YOUR_SCHEMA_NAME>"."PRODUCT_TEXT_TABLE" values(5,'D', 'Geldbörse 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


1.6. SAP HANA Engines


SAP HANA Engine:



SAP HANA has mainly 3 types of engines that are used based on the views required by the model.

• Join Engine:
    • • Used when querying an Attribute View
    • • The join engine is also used, when you run plain SQL.

• OLAP Engine:
    • • Used for Analytic view (without calculated columns).

• Calculation Engine:
    • • Used for Analytic views with calculated attributes and Calculation views

Note: To check what engine is used to process a query: simply run the Plan Visualization on your query.

Q1: What If I execute a standard ANSI92 SQL statement from a BI tool or in SAP HANA Studio. What engines will be used?
Ans: Depends on what objects you reference in your query. If you're just querying plain base tables then the join engine will be used. As soon as you reference an analytic or calculation view, the other engines will be employed as well.

Q2: If my Analytic View foundation is joined to attribute views, is both the OLAP and JOIN Engine used?
Ans: Nope - during activation of the analytic views, the joins in the attribute views get 'flattened' and included in the analytic view run time object. Only the OLAP engine will be used then.

Unit 1: Modeling Views - Quiz

Start
Congratulations - you have completed Unit 1: Modeling Views - Quiz. You scored %%SCORE%% out of %%TOTAL%%. Your performance has been rated as %%RATING%%
Your answers are highlighted below.
Return
Shaded items are complete.
12345
678910
End
Return
2.1. Analytic Privileges – Overview and Importance

What is Analytic Privileges?

Analytic privileges control access to SAP HANA data models.
Analytic privileges are used to grant different users access to different portions of data in the same view depending on their business role. It allows us to maintain row-level access.

Why do we need Analytic Privilege?

SQL privileges implement authorization at object level only. Users either have access to an object, such as a table, view or procedure, or they do not.

While this is often sufficient, there are cases when access to data in an object depends on certain values or combinations of values. Analytic privileges are used in the SAP HANA database to provide such fine-grained control of which data individual users can see within the same view.

Example:

SAP HANA Modeling

Suppose there is a calculation view which contains the sales data of all the regions like Asia, Europe and America.

The regional managers must have access to the calculation view to see the data. However, managers should only see the data for their region. The manager of America region should not be able to see data of other region.

In this case, an analytic privilege could be modeled so that they can all query the view, but only the data that each user is authorized to see is returned.

Important Facts about Analytic Privileges:

    • Analytic privileges are intended to control read-only access to SAP HANA information models, that is
        • • Attribute views
        • • Analytic views
        • • Calculation views
    • Analytic privileges do not apply to database tables or views modeled on row-store tables.
2.2. Creation and Management of Analytic Privilege

In this chapter we will learn how to create and manage Analytic Privilege.

Here is the sequence of steps to achieve this
  1. Create Analytic Privilege and assign restriction for region “Asia”.
  2. Assign the Analytic Privilege to User to restrict the data on HANA Views.

SAP HANA System Privileges Required to Create/Manage Analytic Privilege:

To create analytic privileges, the system privilege CREATE STRUCTURED PRIVILEGE is required.
To drop analytic privileges, the system privilege STRUCTUREDPRIVILEGE ADMIN is required.

In the SAP HANA modeler, repository objects are technically created by the technical user _SYS_REPO, which by default has the system privileges for both creating and dropping analytic privileges.
The database user requires the package privileges REPO.EDIT_NATIVE_OBJECTS and REPO.ACTIVATE_NATIVE_OBJECTS to activate and redeploy analytic privileges in the Modeler.

Steps to Create an Analytic Privilege:

In this example we are going to use the calculation view created in the chapter 1. The output of the calculation view is

SAP HANA Modeling

Let us see how we can restrict the output only for "Asia" region.
    1. Right click on the package and select “Analytic Privilege”

      SAP HANA Modeling

    2. Specify Name and label for the Analytic Privilege

      SAP HANA Modeling

    3. Select the calculation view and click on Add button. Then click on “Finish”.

      SAP HANA Modeling

    4. Click on Add button as shown below and select the column REGION_NAME.

      SAP HANA Modeling

    5. Now we need to assign the restriction. Click on the add button as shown below and select the value “Asia”.

      SAP HANA Modeling

    6. Save and activate the analytic privilege.
The analytic privilege is ready. Now we can assign this analytic privilege to any user.

Assign Analytic Privilege to a User:

Note: You must have authorization to create/assign privileges to a user.
    1. Go to Security -> Users. Right click and create a new user. Specify user name and password.

      SAP HANA Modeling

    2. Click on the “Analytic Privileges” tab and add the analytic privilege created in previous step.

      SAP HANA Modeling

    3. You also need to assign following privileges required to get access to modeling views.
        • • Execute & Select access on _SYS_BI
        • • Execute & Select access on _SYS_BIC
        • • Execute on REPOSITORY_REST
Done!! We have created an analytic privilege and assign that to a user.
Now add the same HANA system using new user. Open the data preview of the same calculation view. It will show only the data for region “Asia”.

SAP HANA Modeling

Unit 2: Analytic Privilege - Quiz

Start
Congratulations - you have completed Unit 2: Analytic Privilege - Quiz. You scored %%SCORE%% out of %%TOTAL%%. Your performance has been rated as %%RATING%%
Your answers are highlighted below.
Return
Shaded items are complete.
1234End
Return
3.1. System Generated Schemas in HANA


A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.
Types of Schemas

There are 3 types of schemas.
    1. User Defined Schema
    2. System Defined Schema
    3. SLT Derived Schema
User Defined Schema: These are created by user (DBA or System Administrator)
SLT Derived Schema: When SLT is configured, it creates schema in HANA system. All the tables replicated into HANA system are contained in this schema
System Defined Schema:
These schemas are delivered with the SAP HANA database and contains HANA system information. There are system schemas like _SYS_BIC, _SYS_BI, _SYS_REPO, _SYS_STATISTICS etc.

System Generated Schemas


_SYS_BIC:
This schema contains all the columns views of activated objects. When the user activates the Attribute View/Analytic View/Calculation View/Analytic Privilege /Procedure, the respective run-time objects are created under _SYS_BIC/ Column Views.

_SYS_REPO:
Whatever the objects are there in the system is available in repository. This schema contains the list of Activated objects, Inactive Objects, Package details and Runtime Objects information etc.
Also _SYS_REPO user must have SELECT privilege with grant option on the data schama.
Read more about "GRANT SELECT PRIVILEGE ON _SYS_REPO"

_SYS_BI:
This schema stores all the metadata of created column Views. It contains the tables for created Variables, Time Data (Fiscal, Gregorian), Schema Mapping and Content Mapping tables.

_SYS_STATISTICS:
This schema contains all the system configurations and parameters.

_SYS_XS:
This schema is used for SAP HANA Extended Application Services.
3.2. What happens when we activate the modeling view?

Whenever we create a modeling view (Attribute View, Analytic View, and Calculation View) and activate it, the runtime column views are created and stored under column view section of _SYS_BIC schema.

SAP HANA Modeling

The modeling view (for example a calculation view) cannot be accessed directly. We need to call the corresponding column view in _SYS_BIC.
Let us have a closer look.
    • • Right click on a modeling view and click on Data Preview. Go to Raw tab to see the output.

      SAP HANA Modeling

    • • Click on the Show Log Button on top right corner and copy the SQL query.

      SAP HANA Modeling

    • • Paste this SQL query in a SQL editor and let us analyze.
      This is actually calling the corresponding runtime object (column view) from _SYS_BIC schema.

      SAP HANA Modeling

3.3.SELECT Privilege To _SYS_REPO


If objects (tables/views) of a schema (say SCHEMA_ABC) are used to build modeling views then it’s necessary to grant _SYS_REPO the SELECT WITH GRANT privilege on this schema.

The following SQL statement must be executed before activating any such modeling views.
GRANT SELECT ON SCHEMA SCHEMA_ABC TO _SYS_REPO WITH GRANT OPTION

If you miss this step, an error will occur when you activate your views later.
Explanation:

The activation of modeling views are done in the name of user _SYS_REPO.

Think of _SYS_REPO as "the activation guy". It takes your models and creates the necessary runtime objects from them. Therefore user _SYS_REPO needs the allowance to select YOUR tables/views. (If _SYS_REPO user cannot select on the tables specified in the from-clause of the view-definition, it cannot define that view)

If other users need to select this view (obviously this is always the case, otherwise the views would not make sense), then _SYS_REPO needs to have the additional allowance to grant the select further (WITH GRANT OPTION).
Therefore after having activated all your models that access data in your schemas, _SYS_REPO wants to give you (and probably other users) read access to the activated models.

If NO object of the schema will be used for modeling views, then you do not need to grant select on that schema to _SYS_REPO.

If you replicate data automatically, using SAP LTR Server, this command is executed automatically in the background while creating a new schema.

Unit 3: System Schema - Quiz

Start
Congratulations - you have completed Unit 3: System Schema - Quiz. You scored %%SCORE%% out of %%TOTAL%%. Your performance has been rated as %%RATING%%
Your answers are highlighted below.
Return
Shaded items are complete.
12345
End
Return
4.1. SQLScript Overview

The SAP HANA database has its own scripting language named SQLScript.
    • • SQLScript is a collection of extensions to Structured Query Language (SQL).
    • • It is used to push down data intensive logic into the database.

The extensions are:

SAP HANA Modeling

Motivation & Goals of SQLScript:

Improve readability and structure of data-intensive logic
    • • Pass results of one SQL statement to another
    • • Break complex SQL into smaller parts

Bring data-intensive application logic close to database
    • • Declarative Logic including SELECT queries, Built-In CalcEngine functions
    • • Orchestration Logic including DDL, DML, assignment, imperative logic

Container for special-purpose operator logic
    • • R script

Why Do We Need SQLScript?

The main goal of SQLScript is to allow the execution of data intensive calculations inside SAP HANA.
There are two reasons why this is required to achieve the best performance:
    • • Moving calculations to the database layer eliminates the need to transfer large amounts of data from the database to the application
    • • Calculations need to be executed in the database layer to get the maximum benefit from SAP HANA features such as fast column operations, query optimization and parallel execution.
      If applications fetch data as sets of rows for processing on application level they will not benefit from these features

SAP HANA SQLScript

4.2. Advantage of SQLScript

Compared to plain SQL queries, SQLScript has the following advantages:

SAP HANA Modeling

4.3. SAP HANA CE Functions - Calculation Engine Plan Operators

SAP HANA CE functions, which is also known as Calculation Engine Plan Operators (CE operators) are alternatives to SQL statements.
    • • CE functions are specially optimized for HANA and can perform better than a normal SQL statement.
    • • CE functions are executed in calculation engine.
    • • CE functions can also help structure the code in a simpler way.


SAP HANA CE Operators

Types of CE Functions:

There are 2 types of CE functions:

1. Data Source Access operators
It binds a column table or a column view to a table variable.

2. Relational operators
It allows a user to bypass the SQL processor during evaluation and to directly interact with the calculation engine.

Examples of Data Source Access Operators are:
    • • CE_COLUMN_TABLE
    • • CE_JOIN_VIEW
    • • CE_OLAP_VIEW
    • • CE_CALC_VIEW

Examples of Relational Operators are:
    • • CE_JOIN
    • • CE_LEFT_OUTER_JOIN
    • • CE_RIGHT_OUTER_JOIN
    • • CE_FULL_OUTER_JOIN
    • • CE_PROJECTION
    • • CE_CALC
    • • CE_AGGREGATION
    • • CE_UNION_ALL

Never Mix SQL and CE Functions:

Try not to mix up normal SQL and CE functions in a single procedure/scripted calculation view. This will decrease the performance even more.
4.4. Table Type in SAP HANA

A table type is
    • • Similar to a database table but do not have an instance
    • • Used to define parameters for a procedure that represent tabular results.
In HANA, with the help of SQLScript, we can create a Table Type.

How to Create a Table Type:

A table type is created using statement CREATE TYPE and can be deleted using statement DROP TYPE.

Syntax:
CREATE TYPE [schema.]name AS TABLE
          (name1 type1 [, name2 type2,...])

DROP TYPE [schema.]name [CASCADE]

Example:

    1. Open HANA studio and run the below SQL statement to create a table type.
      Replace SCHEMA_NAME with your schema.

      CREATE TYPE SCHEMA_NAME.TT_SALES AS TABLE (
                  SALES_AMOUNT DECIMAL,
                  NET_AMOUNT DECIMAL,
                  PRODUCT_NAME NVARCHAR(20),
                  REGION_NAME NVARCHAR(20),
                  SUB_REGION_NAME NVARCHAR(20)
      );


    2. After executing the statement you can go to THE schema and find the table type under Procedures -> Table Types section.

      SAP HANA Table Type

    3. Double click on the table type to see the definition.

      SAP HANA Table Type

    4. Remember that we cannot add record to table type. If you try to insert record, you will get an error.
      INSERT INTO SCHEMA_NAME.TT_SALES VALUES (100, 'PROD-1', 'ASIA', 'INDIA');


      SAP HANA Table Type

Unit 4: SQLScript - Quiz

Start
Congratulations - you have completed Unit 4: SQLScript - Quiz. You scored %%SCORE%% out of %%TOTAL%%. Your performance has been rated as %%RATING%%
Your answers are highlighted below.
Return
Shaded items are complete.
12345
End
Return
5.1. Procedure Overview

What is Procedure in SAP HANA?

A procedure is a unit/module that performs a particular task.
    • • Procedures are reusable processing blocks, and describe a sequence of data transformations
    • • Procedures can have multiple input and output parameters (scalar or table types)
    • DROP and CREATE statements are used to modify the definition of a procedure
    • • A procedure can be created as read only (without side-effects) or read-write (with side-effects)

Few more facts on procedure:

    • • The body of a procedure consists of a sequence of statements separated by semicolons
    • • An intermediate variable, inside a procedure, is not required to be defined before it is bound by an assignment
    • • A variable name is prefixed by ':' while used as input to another statement
    • • Cyclic dependencies that result from the intermediate result assignments or from calling other functions are not allowed
    • • A Procedure can be created using the SQL editor or using creation wizards available for the different perspectives in the SAP HANA Studio (Modeler and Development perspectives)

Different ways to create a procedure:

There are 3 ways to create a procedure in HANA.
    1. Using the SQL editor
    2. Using the Modeler wizard in Modeler perspective
    3. Using SAP HANA –XS project in “SAP HANA Development” perspective
In later chapters we learn about each of these approaches.

Note: Do not get confused by the questions like - why so many ways to create procedure? Which is one should I use? Which one is better? We will explain these later. Right now let us just learn each of these approaches.
5.2. Prerequisites for Creating Procedure

Before creating a procedure as part of this exercise, we need to create the tables that will be used. Let us do that.
Example Scenario:

Suppose there are 3 tables PRODUCTS, REGION and SALES.

SAP HANA Procedure

We need to find out sales value for different region. We also need to calculate NET_AMOUNT based on the DISCOUNT. DISCOUNT value will be passed as input parameter.
We will create a procedure to achieve this.

Create Tables:

In the chapter 1, we have created 3 tables PRODUCTS, REGION and SALES. If you have created them earlier, then create it.

Create Table Type:

We need to create a table type, which will be used for output parameter of the procedure.
Execute the below SQL statement.

--REPLACE SCHEMA_NAME WITH YOUR SCHEMA
CREATE TYPE SCHEMA_NAME.TT_SALES AS TABLE (
            SALES_AMOUNT DECIMAL,
            NET_AMOUNT DECIMAL,
            PRODUCT_NAME NVARCHAR(20),
            REGION_NAME NVARCHAR(20),
            SUB_REGION_NAME NVARCHAR(20)
);

Go to chapter 4.4 to know more about table type.
5.3. Create Procedure Using the SQL editor

Syntax:
CREATE PROCEDURE {schema.}name
            {({IN|OUT|INOUT}
                        param_name data_type {,...})}
            {LANGUAGE <LANG>} {SQL SECURITY <MODE>}
            {READS SQL DATA {WITH RESULT VIEW <view_name>}} AS
BEGIN
...
END
    • READS SQL DATA defines a procedure as read-only.
    • • Implementation LANGUAGE can be specified. Default is SQLScript.
    • WITH RESULT VIEW is used to create a column view for the output parameter of type table
Steps to Create The Procedure:

    1. Open SQL Editor and execute the below script

      CREATE PROCEDURE SCHEMA_NAME."PROCEDURE_SALES_REPORT"(
                  IN DISCOUNT INTEGER,
                  OUT OUTPUT_TABLE SCHEMA_NAME."TT_SALES" )
      LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
      /*********BEGIN PROCEDURE SCRIPT ************/
      BEGIN

      var1 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T2.PRODUCT_ID, T2.SALES_AMOUNT
                  FROM SCHEMA_NAME.REGION AS T1
                  INNER JOIN
                  SCHEMA_NAME.SALES AS T2
                  ON T1.REGION_ID = T2.REGION_ID;

      var2 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T1.PRODUCT_ID, T1.SALES_AMOUNT, T2.PRODUCT_NAME
                  FROM :var1 AS T1
                  INNER JOIN
                  SCHEMA_NAME.PRODUCT AS T2
                  ON T1.PRODUCT_ID = T2.PRODUCT_ID;

      OUTPUT_TABLE = SELECT SUM(SALES_AMOUNT) AS SALES_AMOUNT, SUM( SALES_AMOUNT - (SALES_AMOUNT * :DISCOUNT/ 100)) AS NET_AMOUNT,
                  PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME
                  FROM :var2
                  GROUP BY PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME;

      END;
    2. Refresh the procedure folder under schema in the left Systems tab. You will see the created procedure there.

      SAP HANA Procedure

    3. We call procedure using CALL statement. Execute the below statement to call this procedure.

      CALL SCHEMA_NAME."PROCEDURE_SALES_REPORT" (10, null);


      SAP HANA Procedure

      For table output parameters it is possible to either pass a (temporary) table name or to pass NULL. The option NULL will display the output directly on the client output screen.
5.4. Create procedure using the Modeler wizard in Modeler perspective

    1. Right click on the package and go to New -> Procedure.

      SAP HANA Procedure

    2. Specify name and description and click on Finish.

      SAP HANA Procedure

    3. Paste the below code between BEGIN and END.

      var1 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T2.PRODUCT_ID, T2.SALES_AMOUNT
              FROM SCHEMA_NAME.REGION AS T1
                      INNER JOIN
              SCHEMA_NAME.SALES AS T2
                      ON T1.REGION_ID = T2.REGION_ID;

      var2 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T1.PRODUCT_ID, T1.SALES_AMOUNT, T2.PRODUCT_NAME
              FROM :var1 AS T1
                      INNER JOIN
              SCHEMA_NAME.PRODUCT AS T2
                      ON T1.PRODUCT_ID = T2.PRODUCT
      _ID;
      OUTPUT_TABLE = SELECT SUM(SALES_AMOUNT) AS SALES_AMOUNT, SUM( SALES_AMOUNT - (SALES_AMOUNT * :DISCOUNT/ 100)) AS NET_AMOUNT,
              PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME
                      FROM :var2
                      GROUP BY PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME;


    4. On the right side “Output pane” there is “Output Parameters”. Right click on that and click on New. Create the output parameter as shown below. Note that this has to be exactly same as last SQL statement of the procedure.

      SAP HANA Procedure

    5. Click on the “Input Parameters” in Input Pane and select “New Scalar Parameter”. Create a parameter as DISCOUNT.

      SAP HANA Procedure

    6. Click on Activate button on the top right corner. The procedure will be activated and you can see a successful message in Job Log.

      SAP HANA Procedure

    7. The runtime object of this procedure is created in _SYS_BIC schema. You may check that.

      SAP HANA Procedure

    8. Call the procedure using CALL statement.
      CALL "_SYS_BIC"."sap-hana-tutorial/Procedure_SalesReport" (100, NULL);


      SAP HANA Procedure


Note: The 3rd approach - "Create procedure using SAP HANA –XS project" will be discussed later in HANA XS course.

Unit 5: Procedure - Quiz

Start
Congratulations - you have completed Unit 5: Procedure - Quiz. You scored %%SCORE%% out of %%TOTAL%%. Your performance has been rated as %%RATING%%
Your answers are highlighted below.
Return
Shaded items are complete.
1234End
Return
6.1 SQLScript Procedure Vs. Modeling View

Always Prefer Modeling Views over SQLScript Procedure:

Modeling views (Attribute view, Analytic view and Calculation view) are specially designed artifacts for HANA. As a developer you do not need to worry about query optimization and parallelization.

These modeling views are faster than SQLScript procedures almost in all the scenarios. Plus these views are easier for others to understand, remodel and change.

However, there are some complex scenarios which cannot be implemented in modeling views and you need SQLScript procedures for that. But if you do not write it carefully you might end up with inefficient code.
SQLScript should always be second option compare to modeling views.
6.2 Performance Guidelines - General Principles

    • Minimize the join operations
        • • Avoid long join chains
        • • Avoid joins between big tables
    • Reduce data amount as early as possible
        • • Use data aggregation
        • • Use data filters where possible
    • Avoid huge query result sets
        • • Data transfer to the UI clients also requires time
        • • Too many data can be anyway no analyzed properly
    • Minimize data transfer between the engines
        • • Prefer execution of calculation in lower layers, instead of pushing data up and calculate there
    • Avoid expensive calculation or data manipulation
        • • Row based data base expressions are expensive
        • • Intermediate results needs to be written into buffer and takes additional time
        • • Complex expressions, e.g IF, CASE statements
6.3 Best Practices for Join Operations

    • • Joins are well optimized in the join engine. They are normally faster than Joins in the Calculation Engine. JOIN should be performed in Analytic/Attribute view instead of Calculation View (though in some cases that’s the only way).
    • • CE_JOIN is limited to join only two tables. Cascaded Joins are very expensive and not always lead to right results.
    • • Left outer join is normally faster than inner join.
    • • Left outer join is almost equally fast as Referential Join.
    • • Currently one single join operation is executed by 1 core at maximum (Multiple Join are parallelized across multiple cores).
    • • Avoid joins on calculated fields, e.g. concatenating two fields into one and execute a join on it. Materialize the concatenated field (if not done by the engine)
    • • Avoid non-equijoin predicates on column table as column engine does not natively support join predicates other than equality condition.
6.4 Best Practices for SQLScript

    • • Do not mix up SQL statement and CE_* operator within the same script
    • • Query should be formulated in such a way that minimizes data transfer between different engines.
    • • Avoid implicit type casting
    • • Reduce complexity of SQL Statements. It helps in identifying common sub-expression
    • • Reduce dependencies between different SQL statement and use declarative constructs to enable parallel execution of SQL statements
    • • Executing dynamic SQL is slow because compile time checks and query optimization must be done for every invocation of the procedure.
    • • Avoid CURSOR (and other imperative constructs) as parallelization can’t be done
    • • Consider the impact of expensive calculations (like unit and currency conversion)

Unit 6: HANA Performance - Quiz

Start
Congratulations - you have completed Unit 6: HANA Performance - Quiz. You scored %%SCORE%% out of %%TOTAL%%. Your performance has been rated as %%RATING%%
Your answers are highlighted below.
Return
Shaded items are complete.
12345
End
Return
Course Index
Close
X
SAP HANA Advanced Modeling
Course Overview
1. Modeling Views
2. Analytic Privilege
3. System Schemas and Their Importance
4. SQLScript
5. Procedure
6. HANA Performance Guidelines


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