• Author / Uploaded
• Jaitheradevi

Citation preview

SAP HANA is an in-memory computing platform that allows real-time data analysis. This tutorial will give you the insight of SAP Hana modeling, provisioning, SQL functions, replication, and so on. What is Sap HANA? SAP HANA is the latest ERP Solution from SAP, which is a combination of Hardware and Software. HANA has unprecedented adoption by the SAP customers. SAP HANA is latest, in-memory database, and platform which can be deployed on-premises or cloud. SAP HANA is a combination of hardware and software, which integrates different components like SAP HANA Database, SAP SLT (System Landscape Transformation) Replication server, SAP HANA Direct Extractor connection and Sybase replication technology SAP HANA has two part – 1. SAP HANA Database – SAP HANA Database is a hybrid in–memory database. SAP HANA Database is the heart of SAP in-memory technology. In SAP HANA, Database table are of two types – 

Row Store



Column Store

2. SAP HANA Platform – SAP HANA Platform is a development platform with an in-memory data store which allows the customers to analyze a large volume of data in real time. SAP HANA Platform works as a development platform, which provides infrastructure and tools for building a high-performance application based on SAP HANA Extended Application Services (SAP HANA XS). There are different types of SAP HANA edition, some of them as below

SAP HANA Platform Edition – It provides Core database technology. It Integrate SAP component like SAP HANA database, SAP HANA Studio and SAP HANA clients. It is for customers who want to use ETLbased replication and already have a license for SAP Business Objects Data Services.



SAP HANA Enterprise Edition – It contains data provisioning (SLT, BODS, DXC) component including core database technology. It is for customers who want to use either trigger-based replication or ETL-based replication and do not have all of the necessary license for SAP Business Objects Data Services.



SAP HANA Extended Edition – It contains data provisioning (Sybase) features more than Platform and Enterprises edition. It is for customers who want to use the full potential of all available replication scenarios including log-based replication.

The following diagram shows difference between all editions –

Why to choose SAP HANA? SAP HANA is a next-generation in-memory business platform. It accelerates analytics and application on a single and in-memory platform. Mentioned below are the few reasons why to choose SAP HANA – 

Real Time – SAP HANA Provides Real-Time Data Provisioning and Realtime Reporting.



Speed – SAP HANA provide high speeds processing on massive data due to In-Memory Technology.



Any Data/Source- SAP HANA can access various data source including Structured and Un-Structured data from SAP or Non-SAP data source.



Cloud- SAP HANA database and application can be deployed to the Cloud environment.



Simplicity – SAP HANA reduce efforts behind ETL process, Data Aggregation, Indexing, and Mapping.



Cost – SAP claims that SAP HANA Software can reduce Total IT cost of a company.



Choice Option – SAP HANA is supported by different hardware vendor and Software provider, so based on the requirement, the user can choose the best option.

SAP HANA In-Memory Strategy SAP HANA has many processes running on the SUSE Linux Server. SUSE Linux server manages the reservation of memory to all process. When SAP HANA starts up, Linux OS reserves memory for the program code, program stack, and static data. OS can dynamically reserve additional data memory upon request from the SAP HANA Server. SAP HANA creates a memory pool for managing and tracking the consumption of memory. The memory pool is used to store all the in-memory data and system tables, thread stack, temporary computations and all other data structure required for managing the database.

When more memory is required for table growth or temporary computations, the SAP HANA memorymanager obtains this from the pool.

For an overview, check out the Memory Overview feature of the SAP HANA studio. To access it, right-click on a System ->Configuration and Monitoring -> Open Memory Overview in the context menu, as follows:

SAP HANA Advantages Below are advantages of SAP HANA 

By In-Memory Technology user can explore and analyze all transactional and analytic data in real time from virtually any data source.



Data can be aggregated from many sources.



Real-time replication services can be used to access and replicate data from SAP ERP.



SQL and MDX interface from third party support.



It provides information modeling and design environment.

SAP HANA Compare to BWA (Business Warehouse Accelerator) 

SAP BW Accelerator: It is an in-memory accelerator for BW. BWA is focused on improving the query performance of SAP NetWeaver BW. BWA is specifically designed to accelerate BW queries reducing the data acquisition time by persisting copies of the infocube.



SAP HANA: SAP HANA is in-memory database and platform for high-performance analytic reports and application. In SAP HANA data can be loaded from SAP and non-SAP Source System through SLT, BODS, DXC, and Sybase and can be viewed using SAP BO/BI, Crystal Reports, and Excel, etc.

Currently, SAP HANA also work as in-Memory database for SAP BW, so in this way SAP HANA able to improve the overall performance of SAP Net weaver BW. Summary: 

SAP HANA is an in-memory database and application, which runs on SAP authenticated hardware and Software.



SAP HANA have three version – platform, enterprises and extended.



SAP HANA can load data from SAP and Non-SAP data source through SLT, BODS, DXC, and Sybase.



SAP HANA provide real-time provisioning and reporting.



SAP HANA provide high-performance real-time analytic reporting.



SAP HANA reduces Total IT cost.

SAP HANA Architecture, LandScape, Sizing SAP HANA Database is Main-Memory centric data management platform. SAP HANA Database runs on SUSE Linux Enterprises Server and builds on C++ Language. SAP HANA Database can be distributed to multiple machines. SAP HANA Advantages are as mentioned below 

SAP HANA is useful as it's very fast due to all data loaded in-Memory and no need to load data from disk.



SAP HANA can be used for the purpose of OLAP (On-line analytic) and OLTP (On-Line Transaction) on a single database.

SAP HANA Database consists of a set of in-memory processing engines. Calculation engine is main in-memory Processing engines in SAP HANA. It works with other processing engine like Relational database Engine(Row and Column engine), OLAP Engine, etc. Relational database table resides in column or row store. There are two storage types for SAP HANA table. 1. Row type storage (For Row Table). 2. Column type storage (For Column Table). Text data and Graph data resides in Text Engine and Graph Engine respectively. There are some more engines in SAP HANA Database. The data is allowed to store in these engines as long as enough space is available. In this tutorial, you will learn

SAP HANA Architecture



SAP HANA Landscape



SAP HANA Sizing

SAP HANA Architecture Data is compressed by different compression techniques (e.g. dictionary encoding, run length encoding, sparse encoding, cluster encoding, indirect encoding) in SAP HANA Column store. When main memory limit is reached in SAP HANA, the whole database objects (table, view,etc.) that are not used will be unloaded from the main memory and saved into the disk. These objects names are defined by application semantic and reloaded into main memory from the disk when required again. Under normal circumstances SAP HANA database manages unloading and loading of data automatically. However, the user can load and unload data from individual table manually by selecting a table in SAP HANA studio in respective Schema- by right-clicking and selecting the option "Unload/Load". SAP HANA Server consists of 1. Index Server

2. Preprocessor Server 3. Name Server 4. Statistics Server 5. XS Engine

1. SAP HANA Index Server SAP HANA Database Main server are index server. Detail of each server is as below

It's the main SAP HANA database component



It contains actual data stores and the engine for processing the data.



Index Server processes incoming SQL or MDX statement.

Below is the architecture of Index Server.

SAP HANA Index Server overview  

  

Session and Transaction Manager: Session Component manage sessions and connections for SAP HANA database. Transaction Manager coordinates and control transactions. SQL and MDX Processor: SQL Processor component queries data and send to them in query processing engine i.e. SQL/SQL Script / R / Calc Engine. MDX Processor queries and manipulates Multidimensional data (e,g. Analytic View in SAP HANA). SQL / SQL Script / R / Calc Engine: This Component executes SQL / SQL script and calculation data convert in calculation model. Repository: Repository maintain the versioning of SAP HANA metadata object e.g.(Attribute view, Analytic View, Stored procedure). Persistence layer: This layer uses in-built feature "Disaster Recovery" of SAP HANA database. Backup is saved in it as save points in the data volume.

2. Preprocessor Server This server is used in Text Analysis and extracts data from a text when the search function is used. 3. Name Server This Server contains all information about the system landscape. In distributed server, the name server contains information about each running component and location of data on the server. This server contains information about the server on which data exists. 4. Statistic Server Statistic server is responsible for collecting the data related to status, resource allocation / consumption and performance of SAP HANA system. 5. XS Server XS Server contains XS Engine. It allows external application and developers to use SAP HANA database via the XS Engine client. The external client application can use HTTP to transmit data via XS engine for HTTP server.

SAP HANA Landscape "HANA" mean High Performance Analytic Appliance is a combination of hardware and software platform. 

Due to change in computer architecture, the more powerful computer is available in terms of CPU, RAM, and Hard Disk.



SAP HANA is the solution for performance bottleneck, in which all data is stored in Main Memory and no need to frequently transfer data from disk I/O to main memory. Below are SAP HANA Innovation in the field of Hardware/Software.

There are two types of Relational data stores in SAP HANA: Row Store and Column Store. Row Store 

It is same as Traditional database e.g. (Oracle, SQL Server). The only difference is that all data is stored in row storage area in memory of SAP HANA, unlike a traditional database, where data is stored in Hard Drive. Column Store



Column store is the part of the SAP HANA database and manages data in columnar way in SAP HANA memory. Column tables are stored in Column store area. The Column store provides good performance for write operations and at the same time optimizes the read operation. Read and write operation performance optimized with below two data structure.

Main Storage Main Storage contains the main part of data. In Main Storage, suitable data compression Method (Dictionary Encoding, Cluster Encoding, Sparse Encoding, Run Length encoding, etc.) is applied to compress data with the purpose to save memory and speed up searches. 

In main storage write operations on compressed data will be costly, so write operation do not directly modify compressed data in main storage. Instead, all changes are written in a separate area in column storage known as "Delta Storage."



Delta storage is optimized for a write operation and uses normal compression. The write operations are not allowed on main storage but allowed on delta storage. Read operations are allowed on both storages. We can manually load data in Main memory by option "Load into Memory" and Unload data from Main memory by "Unload from Memory" option as shown below.

Delta Storage Delta storage is used for a write operation and uses basic compression. All uncommitted modification in Column table data stored in delta storage. When we want to move these changes into Main Storage, then use "delta merge operation" from SAP HANA studio as below –



The purpose of delta merge operation is to move changes, which is collected in delta storage to main storage.



After performing Delta Merge operation on sap column table, the content of main storage is saved to disk and compression recalculated. Process of moving Data from Delta to Main Storage during delta merge

There is a buffer store (L1-Delta) which is row storage. So in SAP HANA, column table acts like row store due to L1-delta. 1. The user runs update / insert query on the table (Physical Operator is SQL statements.). 2. Data first go to L1. When L1 moves data further (L1- Uncommitted data) 3. Then data goes to L2-delta buffer, which is column oriented. (L2- Committed data) 4. When L2-delta process is complete, data goes to Main storage. So, Column storage is both Write-optimized and Read-optimized due to L1-Delta and main storage respectively. L1-Delta contains all uncommitted data. Committed data moves to Main Store through L2-Delta. From main store data goes to the persistence layer (The arrow indicating here is a physical operator that send SQL Statement in Column Store). After Processing SQL Statement in Column store, data goes to the persistence layer. E.g. below is row-based table-

Table data is stored on disk in linear format, so below is format how data is stored on disk for row and column table In SAP HANA memory, this table is stored in Row Store on disk as format Memory address And in Column, data is stored on disk as – Memory address Data is stored column-wise in the linear format on the disk. Data can be compressed by compress technique. So, Column store has an advantage of memory saving.

SAP HANA Sizing Sizing is a term which is used to determine hardware requirement for SAP HANA system, such as RAM, Hard Disk and CPU, etc. The main important sizing component is the Memory, and the second important sizing component is CPU. The third main component is a disk, but sizing is completely dependent on Memory and CPU. In SAP HANA implementation, one of the critical tasks is to determine the right size of a server according to business requirement. SAP HANA DB differ in sizing with normal DBMS in terms of – 

Main Memory Requirement for SAP HANA ( Memory sizing is determined by Metadata and Transaction data in SAP HANA)



CPU Requirement for SAP HANA (Forecast CPU is Estimated not accurate).



Disk Space Requirement for SAP HANA ( Is calculated for data persistence and for logging data)

The Application server CPU and application server memory remain unchanged. For sizing calculation SAP has provided various guidelines and method to calculate correct size. We can use below method1. Sizing using ABAP report. 2. Sizing using DB Script. 3. Sizing using Quicksizer Tool. By using Quicksizer tool, Requirement will be displayed in below format-

How to Download & Install SAP HANA Studio What is SAP HANA Studio? SAP HANA Studio is an Eclipse based, integrated development environment (IDE) for development and administration of SAP HANA Database in the form of GUI tool. SAP HANA Studio runs on client/developer machine and connects to SAP HANA Server. SAP HANA Studio can access local or remote SAP HANA Database. By using SAP HANA Studio we can – 

Enables user to manage the SAP HANA Database.



Create and manage user authorizations.



Create New or modify existing models of data.

In this tutorial, you will learn

Pre-Requisite for SAP HANA Studio



Supported Platform



Download & Install SAP HANA Studio



Add System in SAP HANA Studio



Work With SAP HANA Studio

Pre-Requisite for SAP HANA Studio Supported Platform SAP HANA Studio runs on below platform –

Microsoft Windows x32 and x64 versions

Window XP



Window Vista



Window 7



Window 8



SUSE Linux Enterprises Server: x86 64 Bit version, Red Hat Enterprises Linux (6.5).



Mac OS 10.9 or Higher.

System Requirement 

JAVA JVM – During Installation and updating of SAP HANA Studio, a JVM is installed or updated.



SAP HANA Client – It is software, by which you will be able to connect any other database, application. o

SAP HANA Client can be installed on Unix / Linux and Microsoft Windows and also on SAP HANA Server host during server installation. SAP HANA Client installed separately from SAP HANA studio.

Download & Install SAP HANA Studio Installation Path The default installation on system path according to OS and their version is as below – 

Microsoft Window (32 & 64 bit)- C:\Program files \sap\hdbstudio.



Linux x86, 64 bit - /user / sap / hdbstudio.



Mac OS , 64 bit - /Applications / sap / hdbstudio.app

Software Download You can download SAP HANA Studio and SAP HANA Client from here Select File To Download according to your OS –

Installation on Microsoft Window Install SAP HANA Studio in the default directory with administration privileges or in user home folder without administration privileges. Click on hdbsetup.exe for installing SAP HANA studio.

A SAP HANA Lifecycle Management Screen appears.

Default installation folder is C:/Program Files / SAP / hdbstudio. Step 1) Define Studio Properties

1. Select install new SAP HANA Studio. 2. Click on

Button.

Select Features screen appear as below – Step 2) Select features

1. Select Features screen are used to select features. 2. Select Feature as below 

SAP HANA Studio Administration – Toolset for various administration task, Excluding Transport.



SAP HANA Studio Application Development – Toolset for developing SAP HANA native Applications (XS and UI5 Tools excluding SAPUI5).



SAP HANA Studio Database Development – Toolset for content development.

3. Click Step 3) Review and Confirm

button.

1. Review & Conform Screen appears. 2. Summary of SAP HANA Studio Installation display. 3. Click on

Button.

Step 4 & 5) Install Software and Finish. 1. Installation Progress screen appear and after it goes to finish page.

2. A Message "You have successfully installed the SAP HANA Studio". 3. Click on

button.

Run SAP HANA Studio Now, go to Default installation folder is "C:/Program Files / SAP / hdbstudio". There is hdbstudio.exe file, by right clicking on it, you can create a shortcut on the desktop.

When you click "hdbstudio.exe" file, it will open Workspace Launcher screen displayed below.

1. Workspace is selected by default. We can change Workspace location by Browse option. Workspace is used to store studio configuration settings and development artifacts. 2. Select "Use this as the default and do not ask again" option to prevent popup this screen every time for workspace selection when we open SAP HANA Studio. 3. Click

Button.

SAP HANA Studio Welcome screen appear -

In the Welcome screen different perspective is displayed, Detail of each perspective is as below – 1. Administration Console Perspective This screen is used to configure, administration and monitoring the SAP HANA Database. Several View and editor are available in SAP HANA Administration Console. System View Toolbar is used for Administration; it looks like as below –

Below is a Table showing System-level editors and views available in SAP HANA Administration Console. View/ Editors

Detail

Path

Systems

The System view provides hierarchical view of all the SAP HANA System managed in SAP HANA Studio with their contents (catalog, content, etc.)

Window-> Show View -> System

System Monitor

System Monitor is an editor which provides an overview of all SAP HANA Database at one screenshot. We can see the detail of the individual system in System Monitor by drill down.

Button on System View Toolbar.

Administration This is used for performing administration and monitoring task.

1. From The System Toolbar. 2. By double click on System.

Administration This editor is used in Diagnosis case of emergency to perform monitor Mode and operation on the system in which either No SQL connection available or the SQL

1. From click Administration tool list icon. 2.Ctrl+Shift + O

image

connection overload. Backup

Used in performing Backup and Administration.

Expand the system and choose backup.

Security

This editor is used for managing below topic- 1.Password Policy 2.Data Volume Encryption

Security option from Security views of the system.

SQL Console

Used for Entering, Executing and analyzing SQL statement in SQL Console.

From the System Toolbar Choose SQL

2. Modeler Perspective This perspective is used to create modeling objects, database object management in SAP HANA System. This perspective used by modelers for the following activity – 

Create / Modify Tables, Functions, Indexes, View, Sequences, Synonym, Trigger, Views.



Create Modelling object like Attribute View, Analytic View, Calculation View, Analytic Privileges, Procedures and Decision Table.



Data Provisioning to SAP HANA database from SAP / NonSAP Source through SLT, BODS, DXC.

3. Development Perspective This Perspective is used to develop an application on HANA for the web environment. In this Perspective programming language is used – Java Script, J Query, ODATA, etc. 4. Lifecycle Management Perspective This screen is used to Install and Update software regarding SAP HANA Database and SAP HANA Studio. Lifecycle management is also used to transport an object from one HANA system to another HANA System. Add System in SAP HANA Studio To work with SAP HANA Database user needs to be connected with SAP HANA database from SAP HANA Studio. So we build a connection to SAP HANA DATABASE as below – Step 1) Click on "Add System" icon from System Toolbar as below-

Step 2) Provide the following detail as below – 1. Host Name – Enter SAP HANA database here. 2. Instance Number – Two Digit Instance number. 3. Description – Description of the system for better understanding 4. Click on

button.

A connection properties screen appears in which we need to enter SAP HANA Database User and Password.

1. Enter Username and Password for SAP HANA Database for access it from SAP HANA Studio. 2. Click

button.

If there is no error, then the connection is successful, and System name is added in SAP HANA Studio under System Node. Work With SAP HANA Studio To login in SAP HANA Database through SAP HANA Studio, follow below steps-

1. Click on Added System. Here" DB (HANAUSER)".

2. A popup screen for User Name/ password. Enter User Name and Password for HANA Database. 3. Click on Ok button. After Login to SAP HANA Studio, We get below screen for selected HANA System.

In Hana Studio under HANA System following sub-nodes exits

Catalog

SAP HANA Studio Catalog node represent SAP HANA data dictionary, in which Database object (Table, View, Procedure, Index, Trigger, Synonyms, etc.) stores in Schema Folder. When the user is created in SAP HANA, Schema of the same name will be created in SAP HANA Database by default. This is a default schema of user when a user creates any database object. Schema is used to group database object. Schema defines a container that hold database objects such as Table, Views, Trigger, Procedure, Sequence, Function, Indexes, Synonyms, etc. Schema can be created in SQL Editor by below SQLCREATE SCHEMA "SCHEMA_NAME" OWNED BY "USERNAME". Here "SCHEMA_NAME" AND "USERNAME" Should be changed according to Requirement. After Refresh Catalog Node Newly Created Schema will be displayed. I have created Schema "DHK_SCHEMA" by it in-front SQL.

All Database Object are stored in respective folder of Schema as below –



Provisioning

Provisioning is used for selecting source Meta data and importing metadata and data into SAP HANA. There are two categories of provisioning, they are 1. SAP HANA In-Built Tool (Flat file, Smart Data Access, Smart Data Streaming, etc.) 2. External Tools (SLT, BODS, DXC, etc.) In SAP HANA Studio Provisioning node, SAP uses a new feature called, "Smart Data Access" which is Built in Tool. Smart Data Access combines data from heterogeneous data sources like Hadoop, Teradata, Oracle, and Sybase.

Data from different sources will store in SAP HANA database as "Virtual Table". The restriction with virtual tables is, it can be only used to build calculation views in SAP HANA. 

Content

Content Node is Design Time Repository, which hold all information of data models in the package. All information view e.g.(Attribute View, Analytic View, Calculation View, etc.) will be created in Package under Content Node. The package is used for grouping related information object in a structured way. The package can be created by clicking right click on Content Node ->New->Package.



Security

Security Node in SAP HANA Studio contain 3 Sub-node, they are – 1. Security – Used for Create User Audit Policy, Password Policy, etc. 2. Users – Used for create/Modify/Delete user. Role and Privileges will also grant to user from this screen. 3. Roles – Used for Create/Modify/ delete Roles. Privileges are added/deleted from here to Role.

SAP HANA SQL: Learn in 10 Minutes Most RDBMS database uses SQL as database language, the reason of being popular is – it is powerful, vendor independent and standardized. SAP HANA also supports SQL. In SAP HANA, SQL is the main database language. What is SAP HANA SQL? SQL Stands for Structured Query Language. It is a Standard Language for communicating with Relational database like Oracle, MySQL etc. SQL is used to store, retrieve and modify the data in the database. By using SQL in SAP HANA, we can perform following job

Schema definition and use (CREATE SCHEMA).



DML Statement (SELECT, UPDATE, INSERT).



DDL Statement ( CREATE , ALTER , DROP )



DCL Statement ( GRANT ,REVOKE)



System Management



Session Management



Transaction Management

Comment in SQL We can add a comment to improve the readability and maintainability of SQL Statements. Comment can be put on SQL in two ways

Single Line Comment - Double Hyphens "—". This is one line comment.



Multiple Line Comment – "/* */ ".

All Commented text is ignored by SQL Parser.

SAP HANA Data Type & Identifiers In this tutorial, we will learn, 1) SAP HANA Data Type 2) SAP HANA Identifiers SAP HANA Data Type In SAP HANA Database, SQL Data Type is as below – Classification

SubClassification

SQL Data Type

Column Store Type

Default Format

Date Times Types

Date

DATE

CS_DAYDATE

'YYYY-MM-DD'

Time

TIME

CS_SECONDTIME

'HH34:MI:SS'

Second Date

SECONDDATE

CS_LONGDATE

'YYYY-MM-DD HH34:MI:SS'

Time Stamp

TIMESTAMP

CS_SECONDDATE 'YYYY-MM-DD HH34:MI:SS.FFn'

Tiny Integer

TINYINT

CS_INT

8-bit unsigned integer, Range 0 To 255

Small Integer

SMALLINT

CS_INT

16-bit signed integer , Range -32,768 To 32,767

Integer

INTEGER

CS_INT

32-bit signed integer, Range 2,147,483,648 To 2,147,483,647

Big Integer

BIGINT

CS_FIXED(18,0)

4-bit signed integer , Range 9,223,372,036,854,775,808 To 9,223,372,036,854,775,807

Decimal

DECIMAL(p,s)

CS_FIXED(p-s,s)

Precision p can range from 1 to 38.

Numeric Types

p-Precision

The scale s can range from 0 to p.

s- scale

If precision and scale are not specified, DECIMAL becomes a floating-point decimal number.

Small Decimal

SMALLDECIMAL CS_SDFLOAT

It is a floating-point decimal number. The precision and scale should be within the range 1~16 for precision and -369~368 for scale, depending on the stored value. SMALLDECIMAL is only supported for column store Table.

Real Number

REAL

CS_FLOAT

single-precision 32-bit floating-point number

Double Number

DOUBLE

CS_DOUBLE

a double-precision 64-bit floatingpoint number

Float

FLOAT(n)

CS_DOUBLE

It is 32-bit or 64-bit real number. Where n specifies the number of bits and should be in the range between 1 and 53.

Boolean

Boolean

BOOLEAN

CS_INT

TRUE, FALSE And UNKNOWN (NULL).

Character String

VariableLength Character String

VARCHAR(n)

CS_STRING

It is a Variable-length character string, where 'n' specified the maximum length in bytes and this is an integer between 1 and 5000.

VariableLength Unicode character

NVARCHAR(n)

CS_STRING

Variable-length Unicode character set string, where indicates the maximum length in characters and is an integer between 1 and 5000

Alpha Numeric Character

ALPHANUM(n)

CS_ALPHANUM

Variable length alpha-numeric characters, where n indicates the maximum length and is an integer between 1 and 127

Short Text

SHORTTEXT(n)

CS_STRING

It is Variable-length character string which provide text search and string search features. This data type can be defined for column store tables, but not for row tables.

Binary Types

Binary Text

VARBINARY(n)

CS_RAW

Store binary data of a specified maximum length in bytes, where n indicates the maximum length and is an integer between 1 and 5000.

LOB Types(Large Object Types)

Binary LOB

BLOB

CS_RAW

Large amounts of binary data

Character LOB

CLOB

CS_STRING

ASCII character data

Unicode Character LOB

NCLOB

CS_STRING

Large Unicode character object

TEXT

TEXT

CS_STRING

The TEXT data type provide text search features. This data type can be defined for column Store tables, but not for row store tables.

BINARY Text Data

BINTEXT

CS_STRING

The BINTEXT data type is similar to data type TEXT and thus supports text search features, but it is possible to insert binary data. This data type can be defined for column tables, but not for row tables.

Array

ARRAY

Multi-valued Types

It stores collections of values of the same data type where each element is related with exactly one position. Arrays can contain NULL values as in the absence of a value.

SAP HANA Identifiers Identifiers are used to represent name in SQL statement (e.g. table name, view name, column name, index name, synonym name, procedure name, function name, etc.) There are two types of identifiers – delimited identifiers and undelimited identifiers. 

Delimited Identifiers – It is enclosed in the delimiter, Double Quotes "". The identifier can contain any character including special character.



Undelimited Identifiers – Undelimited identifiers (table name, column name) must start with a letter and cannot contain any symbols other than a digit or an underscore '_'.

There are two types Quotation mark for delimit as below

Single Quotation Mark (' ') – It is used to delimit the string.



Double Quotation Mark (" ")- It is used for delimiting identifiers.

SAP HANA Operator: Union & Union All SAP HANA Operator can be used for calculation, value comparison or to assign value. SAP HANA Contain below operators     

Unary and Binary Operator Arithmetic Operator String Operators Comparison Operator Logical Operator Set Operator

Unary and Binary Operator Operator Operation

Description

Unary

A Unary operator applies to one operand

Unary plus operator(+) Unary negation operator(-) Logical negation(NOT)

Binary

A Binary Operator applies on two operand

Multiplicative operators ( *, / ) Additive operators ( +,- ) Comparison operators ( =,!=,,=) Logical operators ( AND, OR )

Arithmetic Operator 

Addition (+)



Subtraction (-)



Multiplication ( * )



Division ( / )

String Operator A String Operator is a concatenation operator which combines two items such as strings, expressions or constants into one. Two Vertical Bar "||" is used as the concatenation operator. Comparison Operator Comparison operator is used to compare two operand. Below are list of Comparison Operator

Equal to ( = )



Greater Than ( > )



Less Than ( < )



Greater than or equal to ( > = )



Less than or equal to ( < = )



Not Equal (!= , )

Logical Operator Logical operator is used in search criteria. E.g. WHERE condition1 AND / OR / NOT condition2 Below is list of logical operator – 

AND - (e.g. WHERE condition1 AND condition2)

If both Condition1 AND Condition2 are true, then Combine condition is true else it will false. 

OR – (e.g. WHERE condition1 OR condition2)

If Condition1 OR Condition2 is true, then combine condition is true or false if both Conditions are false. 

NOT - (e.g. WHERE NOT condition)

NOT condition is true If Condition is false. Set Operators 

UNION - Combines two or many select statements or query without duplicate.



UNION ALL - Combines two or many select statements or query, including all duplicate row.



INTERSECT - Combines two or many select statements or query, and return all common rows.



EXCEPT - Takes the output from the first query and removes row selected by the second query.

E.g. I have two table (table1, table2) in which some values are common.

We use Set operator (Union, Union ALL, Intersect, except) for these two table in SQL as below – Create Table1- SQL Script CREATE COLUMN TABLE DHK_SCHEMA.TABLE1 (

ELEMENT CHAR(1), PRIMARY KEY (ELEMENT)

); INSERT INTO DHK_SCHEMA.TABLE1 VALUES ('P'); INSERT INTO DHK_SCHEMA.TABLE1 VALUES ('Q'); INSERT INTO DHK_SCHEMA.TABLE1 VALUES ('R'); INSERT INTO DHK_SCHEMA.TABLE1 VALUES ('S'); INSERT INTO DHK_SCHEMA.TABLE1 VALUES ('T'); Create Table2- SQL Script CREATE COLUMN TABLE DHK_SCHEMA.TABLE2 (

ELEMENT CHAR(1), PRIMARY KEY (ELEMENT)

); INSERT INTO DHK_SCHEMA.TABLE2 VALUES ('S'); INSERT INTO DHK_SCHEMA.TABLE2 VALUES ('T'); INSERT INTO DHK_SCHEMA.TABLE2 VALUES ('U'); INSERT INTO DHK_SCHEMA.TABLE2 VALUES ('V'); INSERT INTO DHK_SCHEMA.TABLE2 VALUES ('W'); Note: Here "DHK_SCHEMA" is a schema name, the user can change schema name in SQL accordingly. Set Operator Examples are as below Operator

SQL Query

UNION

SELECT * FROM ( SELECT ELEMENT FROM DHK_SCHEMA.TABLE1 UNION SELECT ELEMENT FROM DHK_SCHEMA.TABLE2 ) ORDER BY ELEMENT;

Output

Uses Combine Result of two or more query with no duplicate.

UNION ALL

SELECT *

Combine Result of two or more query with all duplicate.

FROM ( SELECT ELEMENT FROM DHK_SCHEMA.TABLE1 UNION ALL SELECT ELEMENT FROM DHK_SCHEMA.TABLE2 ) ORDER BY ELEMENT; INTERSECT

SELECT *

Combine Result of two or more query with all common rows.

FROM ( SELECT ELEMENT FROM DHK_SCHEMA.TABLE1 INTERSECT SELECT ELEMENT FROM DHK_SCHEMA.TABLE2 ) ORDER BY ELEMENT; EXCEPT

SELECT * FROM ( SELECT ELEMENT FROM DHK_SCHEMA.TABLE1 EXCEPT SELECT ELEMENT

Takes output from first query and removes row selected by the second query

FROM DHK_SCHEMA.TABLE2 ) ORDER BY ELEMENT;

SAP HANA SQL Function Tutorial SAP HANA Provides following SAP HANA Functions1. Data Type Conversion Function – Data Type conversion function are used to convert one data type to another. Below are list of Data Type Conversion functionE.g. CAST, TO_ALPHANUM, TO_BIGINT, TO_BINARY etc. 2. Date Time Functions - Date Time Function are used to convert Date/ Time in a different format. E.g. – ADD_DAYS, ADD_MONTHS, ADD_SECOND, etc. 3. Fulltext Functions - Fulltext Functions is used for text search. E.g. – SCORE etc. 4. Number Functions - Number Functions take a numeric value, or string with numeric characters, as input and return numeric values. E.g. – ABS, ROUND, POWER, etc. 5. String Functions - String Functions take a string as input, process them and return value according to function. E.g. (ASCII, CHAR, CONCAT, etc.) 6. Window Functions – Window Functions let user divide result set of a query into groups of rows named window partition. E.g. RANK (), DENSE_RANK (), ROW_NUMBER (), etc. 7. Miscellaneous Function- There are some more functions, which are used for the miscellaneous job. E.g. – CONVERT_CURRENCY, CURRENT_SCHEMA, etc.

SAP HANA SQL Expression Tutorial SQL Expressions is a clause that can be used for return values. There are 4 types of SQL Expressions  



Case Expressions – In this expression the user can use IF – THEN – ELSE logic without write procedure. Function Expressions – SQL built-in-functions can be used as Expressions. Aggregate Expressions – In This Expression aggregate functions is used to calculate a single value from the values of multiple rows for a column. Aggregate Name

Description

COUNT

Count the Number of rows returned by the query.

MIN

Return the minimum value of the expression.

MAX

Return the maximum value of the expression.

SUM

Return the sum of expressions.

AVG

Return the arithmetical mean of expressions.

STDDEV

Return the Standard Deviation of given expressions as the square root of VARIANCE function.

VAR

Return the variance of expressions as the square of standard deviation

Subqueries in Expression – A subquery is a select statement enclosed in parentheses and used in a main select statement as input.

SAP HANA SQL Stored Procedure Tutorial A procedure is a unit/module that perform a specific task. This procedure can be combined to form larger programs. This basically forms the 'Modular Design'. A procedure can be invoked by another procedure which is called the calling program. Procedures are re-useable processing block with a specific sequence of data transformation. The procedure can have multi-input/output parameters. The procedure can be created as read-only or read-write. An SQL Procedure can be created at – 

At Schema Level(Catalog Node)  At Package Level(Content Node) Stored Procedure syntax in SAP HANA is as shown below – SYNTAX CREATE PROCEDURE [()] [LANGUAGE ] [SQL SECURITY ] [DEFAULT SCHEMA ] [READS SQL DATA [WITH RESULT VIEW ]] AS {BEGIN [SEQUENTIAL EXECUTION]

END | HEADER ONLY }

The CREATE PROCEDURE statement creates a procedure using the mention programming language . SYNTAX ELEMENTS ELEMENTS

DESCRIPTION

Procedure Name

The parameter is defined here. IN, OUT, INOUT parameter is there. Each parameter is marked using the keywords IN/OUT/INOUT • IN – Used for Pass Value To procedure as INPUT. It is Read Only parameter. • OUT – Used for Return Value from Procedure as OUTPUT. • INOUT – Used for Pass and Return Value To Procedure by same parameter.

LANGUAGE

Defines the programming language used in the procedure. Default: SQLSCRIPT

SQL SECURITY

Specifies the security mode of the procedure. Default: DEFINER • DEFINER - Specifies that the execution of the procedure is performed with the privileges of the definer of the procedure. • INVOKER - Specifies that the execution of the procedure is performed with the privileges of the invoker of the procedure.



It defines the schema for unqualified objects in the procedure body. If nothing is define, then the current schema of the session is used for the procedure.

READS SQL DATA

It marks the procedure as being read-only, it means the procedure does not modify the database data or its structure and that the procedure does not contain DDL or DML statements. This procedure only calls other read-only procedures.

WITH RESULT VIEW

It defines the result view to be used as the output of a read-only procedure. If a result view is specified for a procedure, then it can be called by an SQL statement in the same process as a table or view.

SEQUENTIAL EXECUTION

This statement will force sequential execution of the procedure logic. No parallelism takes place.

It defines the main body of the procedure based on the programming language selected.

HEADER ONLY

If Header Only is used, then only procedure properties are created with OID.

SAP HANA Tutorial: Create Sequence What is Sequence? A sequence is a database object that automatically generates the incremented list of numeric values according to rule as specified in sequence specification. For example to insert employee number automatically in column (EMPLOYEE_NO) of Table, when a new record is inserted in the table, then we use sequence. Sequence values are generated in Ascending or Descending order. Sequences are not associated with tables; they are used by the application. There are two values in sequence – 

CURRVAL – Provide Current value of Sequence.  NEXTVAL – Provide Next value of sequence. SYNTAX CREATE SEQUENCE [] [RESET BY ]

SYNTAX ELEMENTS ELEMENTS

DESCRIPTION

It is the name of the sequence.

[]

It specifies one or more sequence parameters.

START WITH

It describes the starting sequence value.

INCREMENT BY

This specifies the value to be incremented from the last value assigned for each time when new sequence value generated. The default is 1.

MAXVALUE

This specifies maximum value ,which can be generated by the sequence. can be between -4611686018427387903 and 4611686018427387902.

NO MAXVALUE

When the NO MAXVALUE is specified, for an ascending sequence, the maximum value will be 4611686018427387903 and the minimum value for a descending sequence will be -1.

MINVALUE / NO MINVALUE

It specifies the minimum value that a sequence can generate. can be between -4611686018427387904 and 4611686018427387902. When the NO MINVALUE is used, the minimum value for an ascending sequence is 1

CYCLE

CYCLE directive specifies that sequence number will be restarted after it

reaches its maximum or minimum value. NO CYCLE

Default option.NO CYCLE directive specifies that sequence number will not be restarted after it reaches its maximum or minimum value.

CACHE /

The cache size specifies which range of sequence numbers will be cached in a node. must be unsigned integer.

NO CACHE

Default option. NO CACHE directive specifies that the sequence number will not be cached in a node.

RESET BY

It specifies that during the restart of the database, the database automatically executes the and the sequence value is restarted with the returned value.

Example – We will create a sequence with named DHK_SCHEMA.EMP_NO, which will create incremented value of the sequence by +1 each time, when the sequence is used. Sequence Script CREATE SEQUENCE DHK_SCHEMA.EMP_NO START WITH 100 INCREMENT BY 1. Here we will use object "sequence" in below example to increment the value of employee no by +1 each time the select query is executed. In the query, the "nextval" can be used for serial number generation or same type of requirement. Use of Sequence – SELECT DHK_SCHEMA.EMP_NO.nextval FROM DUMMY; OUTPUT – 100,101,102………So on every execution of above select query.

SAP HANA Tutorial: Create Trigger What is Trigger? A trigger is also a stored procedure that automatically executes when an event happens on a given table or view. The database users only having the TRIGGER privilege for the given are allowed to create a trigger for that table or view. The CREATE TRIGGER command defines a set of statements that are executed when a given operation (INSERT/UPDATE/DELETE) takes place on a given subject table or subject view. Syntax CREATE TRIGGER



ON [REFERENCING ] [] BEGIN [] []

END

SYNTAX ELEMENTS ELEMENTS

DESCRIPTION

It specifies the name of the trigger to be created, with the optional schema name.

BEFORE | AFTER | INSTEAD OF • BEFORE - Specifies that the trigger will be executed before the DML Operation on a table. • AFTER - Specifies that the trigger will be executed after the DML operation on a table. • INSTEAD OF - Specifies that the trigger will be executed instead of the DML operation on a view. A view with INSTEAD OF trigger becomes updatable.

SAP HANA SQL DATA Profiling: Learn in 5 Minutes What is Data profiling? Data profiling is the process of analyzing the data available in an existing data source and collecting statistics and information about that data. SQL DATA profiling task is used to understand and analyze data from different data source. By Data profiling process user can remove incorrect and incomplete data before loading to the data warehouse. Advantage of SQL DATA Profiling is as below –      

It helps to understand the source data. By Data Profiling, we can analyze data effectively. By Data Profiling, we can remove incorrect, incomplete and improve data quality. Improve the ability to search the data by adding keywords, description. Understand data challenge early in the project, finding data problem late in the project can lead to delay and cost excess. By data profiling, implementation cycle of major projects may be shorten.

SAP HANA Tutorial: SQL Script What is SQL Script? SQL Script is a collection of extensions to SQL. It can be used in stored procedure in place of plain SQL. It determines the functional and procedural extensions. In SQL Script user can define local variables for structure and tables that are primarily used for the creation of stored procedure. SQL script can also be used in Calculation view. In SQL Script, there are two different logic containers

Procedure (Procedures allows you to describe a sequence of data transformations on data passed as input and database tables).  User Defined Function (The User Defined Function container is separated into Scalar User Defined Function and Table User Defined Function). SQL Script Language elements are as below – Declarative SQL Script Logic (Functional Extension) 

It allows the definition of table types without referencing database tables.  Typical Statement like SELECTs.  Calculation Engine (CE) Functions. Orchestration SQL Script Logic (Functional Extension) Orchestration logic is used to implement data flow by using DDL, DML and SQL Query Statements and control flow logic using imperative language constructs such as loops and conditionals.  

Data Definition Language Statement. E.g. Create Schema. Data Manipulation Language (E.g. Insert).

Imperative SQL Script Logic (Procedural Extension) Imperative logic splits the logic among several data flow. E.g. IF, ELSEIF, ELSE, CASE, FOR (Loop) and Exceptions.

Importance of SQL Script Only SQL Script provides the necessary elements to migrate data-intensive logic or the operation of the application server to the database server. Key points of SQL Script; 

SQL Script is executed and processed in the calculation engine within the HANA database.  SQL Script is able to perform complex calculations.  In SQL Script, a local variable can be declared to hold the interim result.  SQL Script Procedure can return more result by using "OUTPUT Parameter" while Normal SQL Procedure can return only one.  In SQL Script, you can define global or local tables types which can be used as parameters. By using SQL Script, parallel processing mode can be achieved.