SAP HANA - Overview

Features of SAP HANA

• SAP HANA is a combination of software and hardware innovation to process huge amount of real time data.
• Based on multi core architecture in distributed system environment.
• Based on row and column type of data-storage in database.
• Used extensively in Memory Computing Engine (IMCE) to process and analyze massive amount of real time data.
• It reduces cost of ownership, increases application performance, enables new applications to run on real time environment that were not possible before.
• It is written in C++, supports and runs on only one Operating System Suse Linux Enterprise Server 11 SP1/2.

Need for SAP HANA

• Due to increase in “Data Volume”, it is a challenge for the companies to provide access to real time data for analysis and business use.
• It involves high maintenance cost for IT companies to store and maintain large data volumes.
• Due to unavailability of real time data, analysis and processing results are delayed.

SAP HANA Vendors

• IBM
• Dell
• HP
• Cisco
• Fujitsu
• Lenovo (China)
• NEC
• Huawei

SAP HANA Installation

SAP HANA - In-Memory Computing Engine

Features of In-Memory Database

• SAP HANA is Hybrid In-memory database.
• It combines row based, column based and Object Oriented base technology.
• It uses parallel processing with multicore CPU Architecture.
• Conventional Database reads memory data in 5 milliseconds. SAP HANA In-Memory database reads data in 5 nanoseconds.

Advantages of In-Memory Database

• HANA database takes advantage of in-memory processing to deliver the fastest data-retrieval speeds, which is enticing to companies struggling with high-scale online transactions or timely forecasting and planning.
• Disk-based storage is still the enterprise standard and price of RAM has been declining steadily, so memory-intensive architectures will eventually replace slow, mechanical spinning disks and will lower the cost of data storage.
• In-Memory Column-based storage provides data compression up to 11 times, thus, reducing the storage space of huge data.
• This speed advantages offered by RAM storage system are further enhanced by the use of multi-core CPUs, multiple CPUs per node and multiple nodes per server in a distributed environment.

SAP HANA - Studio

• It is a client tool, which can be used to access local or remote HANA system.
• It provides an environment for HANA Administration, HANA Information Modeling and Data Provisioning in HANA database.

• Microsoft Windows 32 and 64 bit versions of: Windows XP, Windows Vista, Windows 7
• SUSE Linux Enterprise Server SLES11: x86 64 bit
• Mac OS, HANA studio client is not available

SAP HANA Studio Perspectives / Features

Sap Hana Studio Administration

SAP HANA Studio Database Development

SAP HANA Studio Application Development

SAP HANA - Studio Administration View

• From System View Toolbar − Choose Open Administration default button
• In System View − Double Click on HANA System or Open Perspective

HANA Studio: Administrator Editor

Adding a HANA System to Studio

• Port 3615 should be open to connect to Database
• Port 31015 Instance No 10
• Port 30015 Instance No 00
• SSh port should also be opened

Adding a System to Hana Studio

Catalog and Content

Catalog

Content

SAP HANA - System Monitor

SAP HANA - Information Modeler

Features of Information Modeler

• Provides multiple views of transactional data stored in physical tables of HANA database for analysis and business logic purpose.
• Informational modeler only works for column based storage tables.
• Information Modeling Views are consumed by Java or HTML based applications or SAP tools like SAP Lumira or Analysis Office for reporting purpose.
• Also possible to use third party tools like MS Excel to connect to HANA and create reports.
• SAP HANA Modeling Views exploit real power of SAP HANA.

• Attribute View
• Analytic View
• Calculation View

Row vs Column Store

Column Store

• Data Compression
• Faster read and write access to tables as compared to conventional Row based storage
• Flexibility & parallel processing
• Perform Aggregations and Calculations at higher speed

Functional Difference - Row vs Column Store

Information Modeling Views

Attribute View

• Attribute views are used in Analytic and Calculation views.
• Attribute view represent master data.
• Used to filter size of dimension tables in Analytic and Calculation View.

Analytic View

• Analytic views are designed to perform Star schema queries.
• Analytic views contain at least one fact table and multiple dimension tables with master data and perform calculations and aggregations
• They are similar to Info Cubes and Info objects in SAP BW.
• Analytic views can be created on top of Attribute views and Fact tables and performs calculations like number of unit sold, total price, etc.

Calculation Views

• Calculation Views are defined either graphical using HANA Modeling feature or scripted in the SQL.
• It is created to perform complex calculations, which are not possible with other views- Attribute and Analytic views of SAP HANA modeler.
• One or more Attribute views and Analytic views are consumed with help of inbuilt functions like Projects, Union, Join, Rank in a Calculation View.

SAP HANA - Core Architecture

• Most important component of SAP HANA system is Index Server, which contains SQL/MDX processor to handle query statements for database.
• HANA system contains Name Server, Preprocessor Server, Statistics Server and XS engine, which is used to communicate and host small web applications and various other components.

Index Server

Index Server − Architecture

SQL/MDX Processor

• MDX (Multi Dimension Expression) is query language for OLAP systems like SQL is used for Relational database. MDX Engine is responsible to handle queries and manipulates multidimensional data stored in OLAP cubes.
• Planning Engine is responsible to run planning operations within SAP HANA database.
• Calculation Engine converts data into Calculation models to create logical execution plan to support parallel processing of statements.
• Stored Procedure processor executes procedure calls for optimized processing; it converts OLAP cubes to HANA optimized cubes.

Transaction and Session Management

Persistence Layer

Preprocessor Server

Name Server

• Topology of SAP HANA system is recorded here.
• It decreases the time in re-indexing as it holds which data is on which server in distributed environment.

Statistical Server

XS Engine

SAP Host Agent

LM Structure

SAP Solution Manager (SAP SOLMAN) diagnostic Agent

SAP HANA Studio Repository

Software Update Manager for SAP HANA

SAP HANA - Modeling

Fact and Dimension Table

SAP HANA - Schema in Data Warehouse

• Star Schema
• Snowflakes Schema
• Galaxy Schema

Star Schema

Snowflakes Schema

Galaxy Schema

SAP HANA - Tables

• Using SQL editor
• Using GUI option

SQL Editor in HANA Studio

Create column Table Test1 (
   ID INTEGER,
   NAME VARCHAR(10),
   PRIMARY KEY (ID)
);

Insert into TEST1 Values (1,'ABCD')
Insert into TEST1 Values (2,'EFGH');

Creating Table using GUI Option

Inserting Data in a table using GUI in HANA studio

SAP HANA - Packages

SAP HANA - Attribute View

Characteristics of Attribute View

• Attribute Views in HANA are used to join Dimension tables or other Attribute Views.
• Attribute Views are used in Analytical and Calculation Views for analysis to pass master data.
• They are similar to Characteristics in BM and contain master data.
• Attribute Views are used for performance optimization in large size Dimension tables, you can limit the number of attributes in an Attribute View which are further used for Reporting and analysis purpose.
• Attribute Views are used to model master data to give some context.

How to Create an Attribute View?

• Scenario pane that has Data Foundation and Semantic Layer.
• Details Pane shows attribute of all tables added to Data Foundation and joining between them.
• Output pane where we can add attributes from Detail pane to filter in the report.

SAP HANA - Analytic View

Characteristics of Analytic View

• Analytic Views are used to perform complex calculations and Aggregate functions like Sum, Count, Min, Max, Etc.
• Analytic Views are designed to run Start schema queries.
• Each Analytic View has one Fact table surrounded by multiple dimension tables. Fact table contains primary key for each Dim table and measures.
• Analytic Views are similar to Info Objects and Info sets of SAP BW.

How to Create an Analytic View?

SAP HANA - Calculation View

Characteristics of Calculation View

• Calculation Views are used to consume Analytic, Attribute and other Calculation Views.
• They are used to perform complex calculations, which are not possible with other Views.
• There are two ways to create Calculation Views- SQL Editor or Graphical Editor.
• Built-in Union, Join, Projection & Aggregation nodes.

How to create a Calculation View?

Graphical Calculation Views

SQL Script based Calculation Views

Data Category

Calculation View with Star Join

Example

Create column table Empdim (empId nvarchar(3),Empname nvarchar(100));
Insert into Empdim values('AA1','John');
Insert into Empdim values('BB1','Anand');
Insert into Empdim values('CC1','Jason');

Create column table Empdate (caldate date, CALMONTH nvarchar(4) ,CALYEAR nvarchar(4));
Insert into Empdate values('20100101','04','2010');
Insert into Empdate values('20110101','05','2011');
Insert into Empdate values('20120101','06','2012');

Create column table Empfact1 (empId nvarchar(3), Empdate date, Sal integer );
Insert into Empfact1 values('AA1','20100101',5000);
Insert into Empfact1 values('BB1','20110101',10000);
Insert into Empfact1 values('CC1','20120101',12000);

Create column table Empfact2 (empId nvarchar(3), deptName nvarchar(20), Bonus integer );
Insert into Empfact2 values ('AA1','SAP', 2000);
Insert into Empfact2 values ('BB1','Oracle', 2500);
Insert into Empfact2 values ('CC1','JAVA', 1500);

SAP HANA Calculation View − Star Join

Benefits of using Star Join

Calculation View without Star Join

SAP HANA - Analytic Privileges

Example

• Analytic Privileges are only applied to attributes in an Information View. We cannot add measures to restrict access in Analytic privileges.
• Analytic Privileges are used to control read access on SAP HANA Information views.

Creating Analytic Privileges

SAP HANA - Information Composer

Features of Information Composer

• Data extraction − Information Composer helps to extract data, clean data, preview data and automate the process of creation of physical table in the HANA database.
• Manipulating data − It helps us to combine two objects (Physical tables, Analytical View, attribute view and calculation views) and create information view that can be consumed by SAP BO Tools like SAP Business Objects Analysis, SAP Business Objects Explorer and other tools like MS Excel.
• It provides a centralized IT service in the form of URL, which can be accessed from anywhere.

How to upload data using Information Composer?

• Uploading .xls, .csv file directly to HANA database
• Other way is to copy data to clipboard and copy from there to HANA database.
• It allows data to be loaded along with header.

Using Clipboard

User Roles for using data published with Information Composer

• IC_MODELER is for creating physical tables, uploading data and creating information views.
• IC_PUBLIC allows users to view information views created by other users. This role does not allow the user to upload or create any information views using IC.

System Requirement for Information Composer

• At least 2GB of available RAM is required.
• Java 6 (64-bit) must be installed on the server.
• The Information Composer Server must be physically located next to the HANA server.

• Internet Explorer with Silverlight 4 installed.

SAP HANA - Export & Import

Exporting a table/Information model in HANA Studio

Export Options under SAP HANA Content

Delivery Unit

• Export to Server
• Export to Client

Developer Mode

Support Mode

Import Option under SAP HANA Content

Data from Local File

Delivery Unit

Developer Mode

Mass Import of Metadata

Selective Import of Metadata

SAP HANA - Reporting View

Bi 4.0 Connectivity to Hana Views

Reporting Tools

Relational vs OLAP Connection

• A Relational connection can only be created in IDT/UDT.
• An OLAP can be created in both IDT and CMC.

Information Design Tool

SAP HANA - Crystal Reports

Crystal Reports for Enterprise

Design Studio

Dashboards

Web Intelligence

SAP Lumira

Office Analysis, edition for OLAP

Explorer

Creating an OLAP Connection in CMC

• Predefined − It will not ask user name and password again while using this connection.
• Prompt − Every time it will ask user name and password
• SSO − User specific
• Enter user − user name and password for HANA system and save and new connection will be added to existing list of connections.

• Prepare − You can see the data and do any custom calculation.
• Visualize − You can add Graphs and Charts. Click on X axis and Y axis + sign to add attributes.
• Compose − This option can be used to create sequence of Visualization (story) → click on Board to add numbers of boards → create → it will show all the visualizations on left side. Drag first Visualization then add page then add second visualization.
• Share − If it is built on SAP HANA, we can only publish to SAP Lumira server. Otherwise you can also publish story from SAP Lumira to SAP Community Network SCN or BI Platform.

SAP HANA - Excel Integration

Connecting MS Excel to HANA

All attributes from Information view will be added to MS Excel. You can choose different attributes and measures to report as shown and you can choose different charts like pie charts and bar charts from design option at the top.

SAP HANA - Security Overview

• User and Role Management
• Authentication and SSO
• Authorization
• Encryption of data communication in Network
• Encryption of data in Persistence Layer

• Database Isolation − It involves preventing cross tenant attacks through operating system mechanism
• Configuration Change blacklist − It involves preventing certain system properties from being changed by tenant database administrators
• Restricted Features − It involves disabling certain database features that provides direct access to file system, the network or other resources.

SAP HANA User and Role Management

• If SAP HANA is integrated with BI platform tools and acts as reporting database, then the end-user and role are managed in application server.
• If the end-user directly connects to the SAP HANA database, then user and role in database layer of HANA system is required for both end users and administrators.

User Types

Standard Users

Restricted Users

• Restricted users cannot create objects in HANA database or their own Schemas.
• They do not have access to view any data in database as they don’t have generic Public role added to profile like standard users.
• They can connect to HANA database only using HTTP/HTTPS.

User Administration & Role Management

SAP HANA User Administration Activities

• Create Users
• Grant roles to users
• Define and Create Roles
• Deleting Users
• Resetting user passwords
• Reactivating users after too many failed logon attempts
• Deactivating users when it is required

How to create Users in HANA Studio?

Granted Roles to a User

Modeling

System Privileges

Content Admin

Data Admin

Attach Debugger

Audit Admin

Audit Operator

Backup Admin

Backup Operator

Catalog Read

Create Schema

CREATE STRUCTURED PRIVILEGE

Credential Admin

Data Admin

Database Admin

Export

Import

Inifile Admin

License Admin

Log Admin

Monitor Admin

Optimizer Admin

Resource Admin

Role Admin

Savepoint Admin

Object/SQL Privileges

• Object privilege on database objects that exist only in runtime
• Object privilege on activated objects created in the repository, like calculation views
• Object privilege on schema containing activated objects created in the repository,
• Object/SQL Privileges are collection of all DDL and DML privileges on database objects.

Analytic Privileges

• Allocation of row and column level security for specific value range.
• Allocation of row and column level security for modeling views.

Package Privileges

• Click on Package privilege tab in HANA studio under User creation → Choose + to add one or more packages. Use Ctrl key to select multiple packages.
• In the Select Repository Package dialog, use all or part of the package name to locate the repository package that you want to authorize access to.
• Select one or more repository packages that you want to authorize access to, the selected packages appear in the Package Privileges tab.

• REPO.READ − Read access to the selected package and design-time objects (both native and imported)
• REPO.EDIT_NATIVE_OBJECTS − Authorization to modify objects in packages.
• Grantable to Others − If you choose ‘Yes’ for this, this allows assigned user authorization to pass to the other users.

Application Privileges

• Application-privileges file (.xsprivileges)
• Application-access file (.xsaccess)
• Role-definition file (<RoleName>.hdbrole)

SAP HANA - Authentications

• User name/Password
• Kerberos
• SAML 2.0
• SAP Logon tickets
• X.509

User Name/Password

Kerberos

SAML

SAP Logon and Assertion Tickets

X.509 Client Certificates

Single Sign On in HANA system

• SAML
• Kerberos
• X.509 client certificates for HTTP access from HANA XS engine
• SAP Logon/Assertion tickets

SAP HANA - Authorization Methods

System Privileges

Object Privileges

Analytic Privileges

Package Privileges

Application Privileges

Repository Authorization in SAP HANA Database

SAP HANA - License Management

Types of License keys

• Temporary License Key − Temporary License keys are automatically installed when you install the HANA database. These keys are valid only for 90 days and you should request permanent license keys from SAP market place before expiry of this 90 days period after installation.
• Permanent License Key − Permanent License keys are valid only till the predefine expiration date. License keys specify amount of memory licensed to target HANA installation. They can installed from SAP Market place under Keys and Requests tab. When a permanent License key is expired, a temporary license key is issued, which is valid for only 28 days. During this period, you have to install a permanent License key again.

• Unenforced − If unenforced license key is installed and consumption of HANA system exceeds the license amount of memory, operation of SAP HANA is not affected in this case.
• Enforced − If Enforced license key is installed and consumption of HANA system exceeds the license amount of memory, HANA system gets locked. If this situation occurs, HANA system has to be restarted or a new license key should be requested and installed.

How to Check License Properties of HANA

SAP HANA - Auditing

Activating an Audit Policy

• Syslog (default) − Logging system of Linux Operating System.
• Database Table − Internal database table, user who has Audit admin or Audit operator system privilege he can only run select operation on this table.
• CSV text − This type of audit trail is only used for test purpose in a non-production environment.

SAP HANA - Data Replication Overview

• SAP LT Replication method
• ETL tool SAP Business Object Data Service (BODS) method
• Direct Extractor connection method (DXC)

SAP LT Replication Method

Advantages of SLT Replication

• SLT Replication method allows data replication from multiple source systems to one HANA system and also from one source system to multiple HANA systems.
• SAP LT uses trigger based approach. It has no measureable performance impact in source system.
• It also provides data transformation and filtering capability before loading to HANA database.
• It allows real-time data replication, replicating only relevant data into HANA from SAP and non-SAP source systems.
• It is fully integrated with HANA System and HANA studio.

Creating a Trusted RFC Connection in ECC system

Go to Technical Setting

To Configure RFC connection

SAP HANA - ETL Based Replication

How to use SAP HANA Data Services ETL based Replication?

Creating a Replication Job

Scheduling of Batch Job

SAP HANA - Log Based Replication

Delta Replication after Initial Load

SAP HANA - DXC Method

Advantages of DXC Data Replication

• This method requires no additional server or application in the SAP HANA system landscape.
• DXC method reduces complexity of data modeling in SAP HANA as data sends to HANA after applying all business extractor logics in Source System.
• It speeds up the time lines for SAP HANA implementation project
• It provides semantically rich data from SAP Business Suite to SAP HANA
• It reuses existing proprietary extraction, transformation, and load mechanism built into SAP business Suite systems over a simple HTTP(S) connection to SAP HANA.

Limitations of DXC Data Replication

• Data Source must have a predefined mechanism for extraction, transformation and load and if not we need to define one.
• It requires a Business Suite System based on Net Weaver 7.0 or higher with at least below SP: Release 700 SAPKW70021 (SP stack 19, from Nov 2008).

Configuring DXC Data Replication

• PSA_TO_HDB_DESTINATION − we need to mention where we need to move the Incoming data (Connection Name created using SM 59)
• PSA_TO_HDB_SCHEMA − To which Schema the replicated data need to assign
• PSA_TO_HDB − GLOBAL To Replicate All data source to HANA. SYSTEM – Specified clients to Use DXC. DATASOURCE – Only Specified Data Source are used for
• PSA_TO_HDB_DATASOURCETABLE − Need to Give the Table name having the List of data sources which are used for DXC.

Data Source Replication

• /BIC/A<data source>00 – IMDSO Active Table
• /BIC/A<data source>40 –IMDSO Activation Queue
• /BIC/A<data source>70 – Record Mode Handling Table
• /BIC/A<data source>80 – Request and Packet ID information Table
• /BIC/A<data source>A0 – Request Timestamp Table
• RSODSO_IMOLOG - IMDSO related table. Stores information about all data sources related to DXC.

SAP HANA - CTL Method

---------------------------------------
import data into table Schema."Table name"
from 'file.csv'
records delimited by '\n'
fields delimited by ','
Optionally enclosed by '"'
error log 'table.err'
-----------------------------------------

SAP HANA - MDX Provider

SAP HANA - Monitoring & Alerting

• High and Medium priority alerts.
• Memory and CPU usage
• Data backups

SAP HANA - Persistent Layer

Why does SAP HANA database need a Persistent Layer?

• Main memory is volatile therefore data is lost during a restart or power outage.
• Data needs to be stored in persisted medium.
• Backup & Restore is available.
• It ensures that the database is restored to the most recent committed state after a restart and that transaction are either completely executed or completely undone.

Data and Transaction Log Volumes

Savepoints

ALTER System SAVEPOINT

• /usr/sap/<SID>/SYS/global/hdb/data
• /usr/sap/<SID>/SYS/global/hdb/log

SAP HANA - Backup & Recovery

Overview Tab

Configuration Tab

Backup Interval Settings

File and Log Based Data Backup Settings

Recovery of HANA System

• A disk in the data area is unusable or disk in the log area is unusable.
• As a consequence of a logical error, the database needs to be reset to its state at a particular point in time.
• You want to create a copy of the database.

How to recover a HANA system?

Types of recovery in HANA system

SAP HANA - High Availability

HANA System Hardware Redundancy

SAP HANA Software Redundancy

SAP HANA Persistence Redundancy

HANA System Standby and Failover

SAP HANA - Log Configuration

Configuring Log backup Timeout

SAP HANA - SQL Overview

• Data definition and manipulation
• System management
• Session management
• Transaction management
• Schema definition and manipulation

Data Manipulation Language (DML)

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

Data Definition Language (DDL)

• CREATE − to create objects in the database
• ALTER − alters the structure of the database
• DROP − delete objects from the database

Data Control Language (DCL)

• GRANT − gives user's access privileges to database
• REVOKE − withdraw access privileges given with the GRANT command

Why do We Need SQL?

How to open SQL console in HANA Studio?

SAP HANA - Data Types

Create column Table TEST (
   ID INTEGER,
   NAME VARCHAR(10),
   PRIMARY KEY (ID)
);

• Numeric
• Character/ String
• Boolean
• Date Time
• Binary
• Large Objects
• Multi-Valued

Date Time

• DATE − data type consists of year, month and day information to represent a date value in a column. Default format for a Date data type is YYYY-MM-DD.
• TIME − data type consists of hours, minutes, and seconds value in a table in HANA database. Default format for Time data type is HH: MI: SS.
• SECOND DATE − data type consists of year, month, day, hour, minute, second value in a table in HANA database. Default format for SECONDDATE data type is YYYY-MM-DD HH:MM:SS.
• TIMESTAMP − data type consists of date and time information in a table in HANA database. Default format for TIMESTAMP data type is YYYY-MM-DD HH:MM:SS:FFn, where FFn represents fraction of second.

Numeric

• TinyINT − stores 8 bit unsigned integer. Min value: 0 and max value: 255
• SMALLINT − stores 16 bit signed integer. Min value: -32,768 and max value: 32,767
• Integer − stores 32 bit signed integer. Min value: -2,147,483,648 and max value: 2,147,483,648
• BIGINT − stores 64 bit signed integer. Min value: -9,223,372,036,854,775,808 and max value: 9,223,372,036,854,775,808
• SMALL − Decimal and Decimal: Min value: -10^38 +1 and max value: 10^38 -1
• REAL − Min Value:-3.40E + 38 and max value: 3.40E + 38
• DOUBLE − stores 64 bit floating point number. Min value: -1.7976931348623157E308 and max value: 1.7976931348623157E308

Boolean

Character

• Varchar − maximum of 8000 characters.
• Nvarchar − maximum length of 4000 characters
• ALPHANUM − stores alphanumeric characters. Value for an integer is between 1 to 127.
• SHORTTEXT − stores variable length character string which supports text search features and string search features.

Binary

Large Objects

• NCLOB − stores large UNICODE character object.
• BLOB − stores large amount of Binary data.
• CLOB − stores large amount of ASCII character data.
• TEXT − it enables text search features. This data type can be defined for only column tables and not for row store tables.
• BINTEXT − supports text search features but it is possible to insert binary data.

Multivalued

Array

SAP HANA - SQL Operators

• Arithmetic Operators
• Comparison/Relational Operators
• Logical Operators
• Set Operators

Arithmetic Operators

Operator	Description
+	Addition − Adds values on either side of the operator
-	Subtraction − Subtracts right hand operand from left hand operand
*	Multiplication − Multiplies values on either side of the operator
/	Division − Divides left hand operand by right hand operand
%	Modulus − Divides left hand operand by right hand operand and returns remainder

Comparison Operators

Operator	Description
=	Checks if the values of two operands are equal or not, if yes then condition becomes true.
!=	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
<>	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.
<=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.
!<	Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.
!>	Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.

Logical operators

Operator	Description
ALL	The ALL Operator is used to compare a value to all values in another value set.
AND	The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
ANY	The ANY operator is used to compare a value to any applicable value in the list according to the condition.
BETWEEN	The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS	The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
IN	The IN operator is used to compare a value to a list of literal values that have been specified.
LIKE	The LIKE operator is used to compare a value to similar values using wildcard operators.
NOT	The NOT operator reverses the meaning of the logical operator with which it is used. Eg − NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
OR	The OR operator is used to compare multiple conditions in an SQL statement's WHERE clause.
IS NULL	The NULL operator is used to compare a value with a NULL value.
UNIQUE	The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

Set Operators

• UNION − It combines the results of two or more Select statements. However it will eliminate duplicate rows.
• UNION ALL − This operator is similar to Union but it also shows the duplicate rows.
• INTERSECT − Intersect operation is used to combine the two SELECT statements, and it returns the records, which are common from both SELECT statements. In case of Intersect, the number of columns and datatype must be same in both the tables.
• MINUS − Minus operation combines result of two SELECT statements and return only those results, which belong to first set of result and eliminate the rows in second statement from the output of first.

SAP HANA - SQL Functions

• Numeric Functions
• String Functions
• Fulltext Functions
• Datetime Functions
• Aggregate Functions
• Data Type Conversion Functions
• Window Functions
• Series Data Functions
• Miscellaneous Functions

Numeric Functions

• ABS − It returns the absolute value of a numeric argument.

Example − SELECT ABS (-1) "abs" FROM TEST;
abs
1

• BINTOHEX − It converts a Binary value to a hexadecimal value.
• BITAND − It performs an AND operation on bits of passed argument.
• BITCOUNT − It performs the count of number of set bits in an argument.
• BITNOT − It performs a bitwise NOT operation on the bits of argument.
• BITOR − It perform an OR operation on bits of passed argument.
• BITSET − It is used to set bits to 1 in <target_num> from the <start_bit> position.
• BITUNSET − It is used to set bits to 0 in <target_num> from the <start_bit> position.
• BITXOR − It performs XOR operation on bits of passed argument.
• CEIL − It returns the first integer that is greater or equal to the passed value.
• COS, COSH, COT ((These functions return trigonometric value of the argument)
• EXP − It returns the result of the base of natural logarithms e raised to the power of passed value.
• FLOOR − It returns the largest integer not greater than the numeric argument.
• HEXTOBIN − It converts a hexadecimal value to a binary value.
• LN − It returns the natural logarithm of the argument.
• LOG − It returns the algorithm value of a passed positive value. Both base and log value should be positive.

String Functions

• ASCII − It returns integer ASCII value of passed string.
• CHAR − It returns the character associated with passed ASCII value.
• CONCAT − It is Concatenation operator and returns the combined passed strings.
• LCASE − It converts all character of a string to Lower case.
• LEFT − It returns the first characters of a passed string as per mentioned value.
• LENGTH − It returns the number of characters in passed string.
• LOCATE − It returns the position of substring within passed string.
• LOWER − It converts all characters in string to lowercase.
• NCHAR − It returns the Unicode character with passed integer value.
• REPLACE − It searches in passed original string for all occurrences of search string and replaces them with replace string.
• RIGHT − It returns the rightmost passed value characters of mentioned string.
• UPPER − It converts all characters in passed string to uppercase.
• UCASE − It is identical to UPPER function. It converts all characters in passed string to uppercase.

Date Time functions

• CURRENT_DATE − It returns the current local system date.
• CURRENT_TIME − It returns the current local system time.
• CURRENT_TIMESTAMP − It returns the current local system timestamp details (YYYY-MM-DD HH:MM:SS:FF).
• CURRENT_UTCDATE − It returns current UTC (Greenwich Mean date) date.
• CURRENT_UTCTIME − It returns current UTC (Greenwich Mean Time) time.
• CURRENT_UTCTIMESTAMP
• DAYOFMONTH − It returns the integer value of day in passed date in argument.
• HOUR − It returns integer value of hour in passed time in argument.
• YEAR − It returns the year value of passed date.

Data Type Conversion Functions

• CAST − It returns the value of an expression converted to a supplied data type.
• TO_ALPHANUM − It converts a passed value to an ALPHANUM data type
• TO_REAL − It converts a value to a REAL data type.
• TO_TIME − It converts a passed time string to the TIME data type.
• TO_CLOB − It converts a value to a CLOB data type.

• Current_Schema − It returns a string containing the current schema name.
• Session_User − It returns the user name of current session

SAP HANA - SQL Expressions

• Case Expressions
• Function Expressions
• Aggregate Expressions
• Subqueries in Expressions

Case Expression

Example

SELECT COUNT( CASE WHEN sal < 2000 THEN 1 ELSE NULL END ) count1,
COUNT( CASE WHEN sal BETWEEN 2001 AND 4000 THEN 1 ELSE NULL END ) count2,
COUNT( CASE WHEN sal > 4000 THEN 1 ELSE NULL END ) count3 FROM emp;

Function Expressions

Aggregate Expressions

• Average ()
• Count ()
• Maximum ()
• Median ()
• Minimum ()
• Mode ()
• Sum ()

Subqueries in Expressions

• Subqueries must be enclosed within parentheses.
• A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
• An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.
• Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.
• The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
• A subquery cannot be immediately enclosed in a set function.
• The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.

Subqueries with the SELECT Statement

Example

SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID
FROM CUSTOMERS
WHERE SALARY > 4500) ;

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
| 4  | Chaitali | 25  | Mumbai  | 6500.00  |
| 5  | Hardik   | 27  | Bhopal  | 8500.00  |
| 7  | Muffy    | 24  | Indore  | 10000.00 |
+----+----------+-----+---------+----------+

SAP HANA - SQL Stored Procedures

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;

SAP HANA - SQL Sequences

Using AUTO_INCREMENT column

Example

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

PERL Example

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

PHP Example

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

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

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
);

SAP HANA - SQL Triggers

• 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).

Benefits of Triggers

• 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 Synonym

Example

Public Synonyms

Syntax

CREATE PUBLIC SYNONYM Cust_table for efashion.Customer;

Private Synonyms

Syntax

CREATE SYNONYM Cust_table FOR efashion.Customer;

Drop a Synonym

Syntax

DROP PUBLIC Synonym Cust_table;
DROP Synonym Cust_table;

SAP HANA - SQL Explain Plans

Common DML Statements

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

EXPLAIN PLAN TABLE in database

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 Data Profiling

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

Access Requirement

Data Profiler Viewer

Data Profiling Task Setup and Reviewing

Setting up the Data Profiling Task

Reviewing the Profiles

Data Profiling − Configuration Options

Wildcard columns

Quick Profile

Data Profiling − Task Outputs

SAP HANA - SQL Script

• 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

• 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

How to create a Calculation View with SQL Script?