Discuss SAP HANA

SAP HANA is an in-memory data platform that is deployable as an on-premise appliance, or in the cloud. It is a revolutionary platform, which is best suited for performing real-time analytics, and developing and deploying real-time applications. At the core of this real-time data platform is the SAP HANA database, which is fundamentally different from any other database engine in the market today. This tutorial will teach you the basics of SAP HANA. The tutorial is divided into sections such as SAP HANA Basics, SAP HANA-Modeling, Reporting, and SAP HANA-SQL. Each of these sections contain related topics with screenshots explaining the SAP HANA database installations.

SAP HANA – Useful Resources

The following resources contain additional information on SAP HANA. Please use them to get more in-depth knowledge on this.

Useful Links on SAP HANA

Useful Books on SAP HANA

To enlist your site on this page, please drop an email to contact@tutorialspoint.com

SAP HANA Questions and Answers

SAP HANA Questions and Answers has been designed with a special intention of helping students and professionals preparing for various Certification Exams and Job Interviews. This section provides a useful collection of sample Interview Questions and Multiple Choice Questions (MCQs) and their answers with appropriate explanations.

Questions and Answers

SN Question/Answers Type
1 SAP HANA Interview QuestionsThis section provides a huge collection of SAP HANA Interview Questions with their answers hidden in a box to challenge you to have a go at them before discovering the correct answer.
2 SAP HANA Online QuizThis section provides a great collection of SAP HANA Multiple Choice Questions (MCQs) on a single page along with their correct answers and explanation. If you select the right option, it turns green; else red.
3 SAP HANA Online TestIf you are preparing to appear for a SAP HANA related certification exam, then this section is a must for you. This section simulates a real online test along with a given timer which challenges you to complete the test within a given time-frame. Finally you can check your overall test score and how you fared among millions of other candidates who attended this online test.
4 SAP HANA Mock TestThis section provides various mock tests that you can download at your local machine and solve offline. Every mock test is supplied with a mock test key to let you verify the final score and grade yourself.

SAP HANA – SQL Script

Set of SQL statements for HANA database which allows developer to pass complex logic into database is called SQL Script. SQL Script is known as collections of SQL extensions. These extension are Data Extensions, Function Extensions, and Procedure Extension.

SQL Script supports stored Functions and Procedures and that allows pushing complex parts of Application logic to database.

Main benefit of using SQL Script is to allow the execution of complex calculations inside SAP HANA database. Using SQL Scripts in place of single query enables Functions to return multiple values. Complex SQL functions can be further decomposed into smaller functions. SQL Script provides control logic that is not available in single SQL statement.

SQL Script

SQL Scripts are used to achieve performance optimization in HANA by executing scripts at DB layer −

  • By Executing SQL scripts at database layer, it eliminates need to transfer large amount of data from database to application.
  • Calculations are executed at database layer to get benefits of HANA database like column operations, parallel processing of queries, etc.

Integration with Information Modeler

While using SQL scripts in Information Modeler, below given are applied to Procedures −

  • Input parameters can be of scalar or table type.
  • Output parameters must be of table types.
  • Table types required for the signature are generated automatically.

SQL Scripts with Calculation Views

SQL script are used to create script based Calculation views. Type SQL statements against existing raw tables or column store. Define output structure, activation of view creates table type as per structure.

How to create a Calculation View with SQL Script?

Launch SAP HANA studio. Expand the content node → Select a package where you want to create the new Calculation view. Right Click → New Calculation View End of the navigation path → Provide name and description.

Launch SAP HANA Studio

Select calculation view type → from Type dropdown list, select SQL Script → Set Parameter Case Sensitive to True or False based on how you require the naming convention for the output parameters of the calculation view → Choose Finish.

Select Calculation View Type

Select default schema − Select the Semantics node → Choose the View Properties tab → In the Default Schema dropdown list, select the default schema.

Select Default Schema

Choose SQL Script node in the Semantics node → Define the output structure. In the output pane, choose Create Target. Add the required output parameters and specify its length and type.

Semantics Node

To add multiple columns that are part of existing information views or catalog tables or table functions to the output structure of script-based calculation views −

In the Output pane, choose Start of the navigation path New Next navigation step Add Columns from End of the navigation path → Name of the object that contains the columns you want to add to the output → Select one or more objects from the dropdown list → Choose Next.

In the Source pane, choose the columns that you want to add to the output → To add selective columns to the output, then select those columns and choose Add. To add all columns of an object to the output, then select the object and choose Add → Finish.

Activate the script-based calculation view − In the SAP HANA Modeler perspective − Save and Activate – to activate the current view and redeploy the affected objects if an active version of the affected object exists. Otherwise, only the current view is activated.

Save and activate all − to activate the current view along with the required and affected objects.

In the SAP HANA Development perspective − In Project Explorer view, select the required object. In the context menu, select Start of the navigation path Team Next navigation step Activate End of the navigation path.

SQL Scripting in HANA Information Modeler is used to create complex Calculation Views, which are not possible to create using GUI option.

SAP HANA – SQL Data Profiling

SQL Data Profiling task is used to understand and analyze data from multiple data sources. It is used to remove incorrect, incomplete data and prevent data quality problems before they are loaded in Data warehouse.

Here are the benefits of SQL Data Profiling tasks −

  • It helps is analyzing source data more effectively.
  • It helps in understanding the source data better.
  • It remove incorrect, incomplete data and improve data quality before it is loaded into Data warehouse.
  • It is used with Extraction, Transformation and Loading task.

The Data Profiling task checks profiles that helps to understand a data source and identify problems in the data that has to be fixed.

You can use the Data Profiling task inside an Integration Services package to profile data that is stored in SQL Server and to identify potential problems with data quality.

Note − Data Profiling Task works only with SQL Server data sources and does not support any other file based or third party data sources.

Access Requirement

To run a package contains Data Profiling task, user account must have read/write permissions with CREATE TABLE permissions on the tempdb database.

Data Profiler Viewer

Data Profile Viewer is used to review the profiler output. The Data Profile Viewer also supports drilldown capability to help you understand data quality issues that are identified in the profile output. This drill down capability sends live queries to the original data source.

Data Profiling Task Setup and Reviewing

Setting up the Data Profiling Task

It involves execution of a package that contains Data Profiling task to compute the profiles. The task saves the output in XML format to a file or a package variable.

Reviewing the Profiles

To view the data profiles, send the output to a file and then use the Data Profile Viewer. This viewer is a stand-alone utility that displays the profile output in both summary and detail format with optional drilldown capability.

Data Profiling − Configuration Options

The Data Profiling task has these convenient configuration options −

Wildcard columns

While configuring a profile request, the task accepts ‘*’ wildcard in place of a column name. This simplifies the configuration and makes it easier to discover the characteristics of unfamiliar data. When the task runs, the task profiles every column that has an appropriate data type.

Quick Profile

You can select Quick Profile to configure the task quickly. A Quick Profile profiles a table or view by using all the default profiles and settings.

The Data Profiling Task can compute eight different data profiles. Five of these profiles can check individual columns and the remaining three analyze- multiple columns or relationships between columns.

Data Profiling − Task Outputs

The Data Profiling task outputs the selected profiles into XML format that is structured like DataProfile.xsd schema.

You can save a local copy of the schema and view the local copy of the schema in Microsoft Visual Studio or another schema editor, in an XML editor or in a text editor such as Notepad.

SAP HANA – SQL Explain Plans

SQL explain plans are used to generate detail explanation of SQL statements. They are used to evaluate execution plan that SAP HANA database follows to execute the SQL statements.

The results of explain plan are stored into EXPLAIN_PLAN_TABLE for evaluation. To use Explain Plan, passed SQL query must be a data manipulation language (DML).

Common DML Statements

  • SELECT − retrieve data from the a database
  • INSERT − insert data into a table
  • UPDATE − updates existing data within a table

SQL Explain Plans cannot be used with DDL and DCL SQL statements.

EXPLAIN PLAN TABLE in database

EXPLAIN PLAN_TABLE in database consists of multiple columns. Few common column names − OPERATOR_NAME, OPERATOR_ID, PARENT_OPERATOR_ID, LEVEL and POSITION, etc.

COLUMN SEARCH value tells the starting position of column engine operators.

ROW SEARCH value tells the starting position of row engine operators.

To create an EXPLAIN PLAN STATEMENT for a SQL query

EXPLAIN PLAN SET STATEMENT_NAME = ‘statement_name’ FOR <SQL DML statement>

To see values in EXPLAIN PLAN TABLE

SELECT Operator_Name, Operator_ID
FROM explain_plan_table
WHERE statement_name = 'statement_name';

To delete a statement in EXPLAIN PLAN TABLE

DELETE FROM explain_plan_table WHERE statement_name = 'TPC-H Q10';

SAP HANA – SQL Synonym

SQL Synonyms is an alias for a table or a Schema object in a database. They are used to protect client applications from the changes made to name or location of an object.

Synonyms permit applications to function irrespective of user who owns the table and which database holds the table or object.

Create Synonym statement is used create a Synonym for a table, view, package, procedure, objects, etc.

Example

There is a table Customer of efashion, located on a Server1. To access this from Server2, a client application would have to use name as Server1.efashion.Customer. Now we change the location of Customer table the client application would have to be modified to reflect the change.

To address these we can create a synonym of Customer table Cust_Table on Server2 for the table on Server1. So now client application has to use the single-part name Cust_Table to reference this table. Now, if the location of this table changes, you will have to modify the synonym to point to the new location of the table.

As there is no ALTER SYNONYM statement, you have to drop the synonym Cust_Table and then re-create the synonym with the same name and point the synonym to the new location of Customer table.

Public Synonyms

Public Synonyms are owned by PUBLIC schema in a database. Public synonyms can be referenced by all users in the database. They are created by the application owner for the tables and other objects such as procedures and packages so the users of the application can see the objects.

Syntax

CREATE PUBLIC SYNONYM Cust_table for efashion.Customer;

To create a PUBLIC Synonym, you have to use keyword PUBLIC as shown.

Private Synonyms

Private Synonyms are used in a database schema to hide the true name of a table, procedure, view or any other database object.

Private synonyms can be referenced only by the schema that owns the table or object.

Syntax

CREATE SYNONYM Cust_table FOR efashion.Customer;

Drop a Synonym

Synonyms can be dropped using DROP Synonym command. If you are dropping a public Synonym, you have to use the keyword public in the drop statement.

Syntax

DROP PUBLIC Synonym Cust_table;
DROP Synonym Cust_table;

SAP HANA – SQL Triggers

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events −

  • A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
  • A database definition (DDL) statement (CREATE, ALTER, or DROP).
  • A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers could be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

Triggers can be written for the following purposes −

  • Generating some derived column values automatically
  • Enforcing referential integrity
  • Event logging and storing information on table access
  • Auditing
  • Synchronous replication of tables
  • Imposing security authorizations
  • Preventing invalid transactions

SAP HANA – SQL Sequences

A sequence is a set of integers 1, 2, 3, that are generated in order on demand. Sequences are frequently used in databases because many applications require each row in a table to contain a unique value, and sequences provide an easy way to generate them.

Using AUTO_INCREMENT column

The simplest way in MySQL to use sequences is to define a column as AUTO_INCREMENT and leave rest of the things to MySQL to take care.

Example

Try out the following example. This will create table and after that it will insert few rows in this table where it is not required to give record ID because it is auto-incremented by MySQL.

mysql> CREATE TABLE INSECT
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO INSECT (id,name,date,origin) VALUES
   -> (NULL,'housefly','2001-09-10','kitchen'),
   -> (NULL,'millipede','2001-09-10','driveway'),
   -> (NULL,'grasshopper','2001-09-10','front yard');
	
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM INSECT ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
| 1  | housefly    | 2001-09-10 | kitchen    |
| 2  | millipede   | 2001-09-10 | driveway   |
| 3  | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Obtain AUTO_INCREMENT Values

LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client that understands how to issue SQL statements. Otherwise, PERL and PHP scripts provide exclusive functions to retrieve auto-incremented value of last record.

PERL Example

Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a query. This attribute is accessed through either a database handle or a statement handle, depending on how you issue the query. The following example references it through the database handle −

$dbh->do ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

PHP Example

After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling mysql_insert_id( ) −

mysql_query ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Renumbering an Existing Sequence

There may be a case when you have deleted many records from a table and you want to re-sequence all the records. This can be done by using a simple trick but you should be very careful to do so if your table is having join, with other table.

If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again. The following example shows how to renumber the id values in the insect table using this technique −

mysql> ALTER TABLE INSECT DROP id;
mysql> ALTER TABLE insect
   -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
   -> ADD PRIMARY KEY (id);

Starting a Sequence at a Particular Value

By default, MySQL will start sequence from 1 but you can specify any other number as well at the time of table creation. Following is the example where MySQL will start sequence from 100.

mysql> CREATE TABLE INSECT
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);

Alternatively, you can create the table and then set the initial sequence value with ALTER TABLE.

SAP HANA – SQL Stored Procedures

A procedure allows you to group the SQL statement into a single block. Stored Procedures are used to achieve certain result across applications. The set of SQL statements and the logic that is used to perform some specific task are stored in SQL Stored Procedures. These stored procedures are executed by applications to perform that task.

Stored Procedures can return data in the form of output parameters (integer or character) or a cursor variable. It can also result in set of Select statements, which are used by other Stored Procedures.

Stored Procedures are also used for performance optimization as it contains series of SQL statements and results from one set of statement determines next set of statements to be executed. Stored procedures prevent users to see the complexity and details of tables in a database. As Stored procedures contain certain business logic, so users need to execute or call the procedure name.

No need to keep reissuing the individual statements but can refer to the database procedure.

Sample Statement to Create Procedures

Create procedure prc_name (in inp integer, out opt "EFASION"."ARTICLE_LOOKUP")
as
begin
opt = select * from "EFASION"."ARTICLE_LOOKUP" where article_id = :inp ;
end;