SAP HANA – Data Types

You can create row or Column store tables in SAP HANA using create table option. A table can be created by executing a data definition create table statement or using graphical option in HANA studio.

When you create a table, you also need to define attributes inside it.

SQL statement to create a table in HANA Studio SQL Console

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

Creating a table in HANA studio using GUI option

GUI Option

When you create a table, you need to define the names of columns and SQL data types. The Dimension field tells the length of value and the Key option to define it as primary key.

SAP HANA supports the following data types in a table −

Data Types

SAP HANA supports 7 categories of SQL data types and it depends on the type of data you have to store in a column.

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

The following table gives the list of data types in each category −

Data Types Category

Date Time

These data types are used to store date and time in a table in HANA database.

  • 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

Boolean data types stores Boolean value, which are TRUE, FALSE

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

Binary types are used to store bytes of binary data.

VARBINARY − stores binary data in bytes. Max integer length is between 1 and 5000.

Large Objects

LARGEOBJECTS are used to store a large amount of data such as text documents and images.

  • 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

Multivalued data types are used to store collection of values with same data type.

Array

Arrays store collections of value with the same data type. They can also contain null values.

SAP HANA – SQL Overview

SQL stands for Structured Query Language.

It is a standardized language for communicating with a database. SQL is used to retrieve the data, store or manipulate the data in the database.

SQL statements perform the following functions −

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

The set of SQL extensions, which allow developers to push data into database, is called SQL scripts.

Data Manipulation Language (DML)

DML statements are used for managing data within schema objects. Some examples −

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

Data Definition Language (DDL)

DDL statements are used to define the database structure or schema. Some examples −

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

Data Control Language (DCL)

Some examples of DCL statements are −

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

Why do We Need SQL?

When we create Information Views in SAP HANA Modeler, we are creating it on top of some OLTP applications. All these in back end run on SQL. Database understands only this language.

To do a testing if our report will meet the business requirement we have to run SQL statement in database if Output is according to the requirement.

HANA Calculation views can be created in two ways – Graphical or using SQL script. When we create more complex Calculation views, then we might have to use direct SQL scripts.

How to open SQL console in HANA Studio?

Select the HANA system and click on SQL console option in system view. You can also open SQL console by right click on Catalog tab or any on any Schema name.

SQL Console

SAP HANA can act both as Relational as well as OLAP database. When we use BW on HANA, then we create cubes in BW and HANA, which act as relational database and always produce a SQL Statement. However, when we directly access HANA views using OLAP connection, then it will act as OLAP database and MDX will be generated.

SAP HANA – Log Configuration

The SAP HANA system logs all the transactions that change application data or the database catalog in log entries and stores them in log area. It uses these log entries in log area to roll back or repeat SQL statements. The log files are available in HANA system and can be accessed via HANA studio on Diagnosis files page under Administrator editor.

Log Configuration

During a log backup process, only the actual data of the log segments is written from the log area to service-specific log backup files or to a third-party backup tool.

After a system failure, you may need to redo log entries from log backups to restore the database to the desired state.

If a database service with persistence stops, it is important to ensure that it is restarted, otherwise recovery will be possible only to a point before service is stopped.

Configuring Log backup Timeout

The log backup timeout determines the interval at which the log segments are backed up if a commit has taken place in this interval. You can configure the log backup timeout using the Backup Console in SAP HANA studio −

Configuration Log Backup Timeout

You can also configure the log_backup_timeout_s interval in the global.ini configuration file.

The log backup to the “File” and backup mode “NORMAL” are the default settings for the automatic log backup function after installation of SAP HANA system. Automatic log backup only works if at least one complete data backup has been performed.

Once the first complete data backup has been performed, the automatic log backup function is active. SAP HANA studio can be used to enable/disable the automatic log backup function. It is recommended to keep automatic log backup enabled otherwise log area will continue to fill. A full log area can result a database freeze in HANA system.

Log Backup Settings

You can also change the enable_auto_log_backup parameter in the persistence section of the global.ini configuration file.

SAP HANA – Log Configuration

The SAP HANA system logs all the transactions that change application data or the database catalog in log entries and stores them in log area. It uses these log entries in log area to roll back or repeat SQL statements. The log files are available in HANA system and can be accessed via HANA studio on Diagnosis files page under Administrator editor.

Log Configuration

During a log backup process, only the actual data of the log segments is written from the log area to service-specific log backup files or to a third-party backup tool.

After a system failure, you may need to redo log entries from log backups to restore the database to the desired state.

If a database service with persistence stops, it is important to ensure that it is restarted, otherwise recovery will be possible only to a point before service is stopped.

Configuring Log backup Timeout

The log backup timeout determines the interval at which the log segments are backed up if a commit has taken place in this interval. You can configure the log backup timeout using the Backup Console in SAP HANA studio −

Configuration Log Backup Timeout

You can also configure the log_backup_timeout_s interval in the global.ini configuration file.

The log backup to the “File” and backup mode “NORMAL” are the default settings for the automatic log backup function after installation of SAP HANA system. Automatic log backup only works if at least one complete data backup has been performed.

Once the first complete data backup has been performed, the automatic log backup function is active. SAP HANA studio can be used to enable/disable the automatic log backup function. It is recommended to keep automatic log backup enabled otherwise log area will continue to fill. A full log area can result a database freeze in HANA system.

Log Backup Settings

You can also change the enable_auto_log_backup parameter in the persistence section of the global.ini configuration file.

SAP HANA – Monitoring & Alerting

SAP HANA alert monitoring is used to monitor the status of system resources and services that are running in the HANA system. Alert monitoring is used to handle critical alerts like CPU usage, disk full, FS reaching threshold, etc. The monitoring component of HANA system continuously collects information about health, usage and performance of all the components of HANA database. It raises an alert when any of the component breaches the set threshold value.

The priority of alert raised in HANA system tells the criticality of problem and it depends on the check that is performed on the component. Example − If CPU usage is 80%, a low priority alert will be raised. However, if it reaches 96%, system will raise a high priority alert.

The System Monitor is the most common way to monitor HANA system and to verify the availability of all your SAP HANA system components. System monitor is used to check all key component and services of a HANA system.

System Monitor

You can also drill down into details of an individual system in Administration Editor. It tells about Data Disk, Log disk, Trace Disk, alerts on resource usage with priority.

HDB

Alert tab in Administrator editor is used to check the current and all alerts in HANA system.

Summary

It also tells about the time when an alert is raised, description of the alert, priority of the alert, etc.

SAP HANA monitoring dashboard tells the key aspects of system health and configuration −

Configuration and Monitoring

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

SAP HANA – MDX Provider

MDX Provider is used to connect MS Excel to SAP HANA database system. It provides driver to connect HANA system to Excel and is further, used for data modelling. You can use Microsoft Office Excel 2010/2013 for connectivity with HANA for both 32 bit and 64 bit Windows.

SAP HANA supports both query languages − SQL and MDX. Both languages can be used: JDBC and ODBC for SQL and ODBO is used for MDX processing. Excel Pivot tables use MDX as query language to read data from SAP HANA system. MDX is defined as part of ODBO (OLE DB for OLAP) specification from Microsoft and is used for data selections, calculations and layout. MDX supports multidimensional data model and support reporting and Analysis requirement.

MDX provider enables the consumption of Information views defined in HANA studio by SAP and non-SAP reporting tools. Existing physical tables and schemas presents the data foundation for Information models.

MDX Provider

Once you choose SAP HANA MDX provider from the list of data source you want to connect, pass HANA system details like host name, instance number, user name and password.

Data Source

Once the connection is successful, you can choose Package name → HANA Modeling views to generate Pivot tables.

Data Connection Wizard

MDX is tightly integrated into HANA database. Connection and Session management of HANA database handles statements that are executed by HANA. When these statements are executed, they are parsed by MDX interface and a calculation model is generated for each MDX statement. This calculation model creates an execution plan that generates standard results for MDX. These results are directly consumed by OLAP clients.

To make MDX connection to HANA database, HANA client tools are required. You can download this client tool from SAP market place. Once installation of HANA client is done, you will see the option of SAP HANA MDX provider in the list of data source in MS Excel.

SAP HANA – CTL Method

Open SAP HANA Studio → Create Schema under Catalog tab. <Start here>

Prepare the data and save it to csv format. Now create file with “ctl” extension with following syntax −

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

Transfer this “ctl” file to the FTP and execute this file to import the data −

import from ‘table.ctl’

Check data in table by going to HANA Studio → Catalog → Schema → Tables → View Content

SAP HANA – DXC Method

Direct Extractor Connection data replication reuses existing extraction, transformation, and load mechanism built into SAP Business Suite systems via a simple HTTP(S) connection to SAP HANA. It is a batch-driven data replication technique. It is considered as method for extraction, transformation, and load with limited capabilities for data extraction.

DXC is a batch driven process and data extraction using DXC at certain interval is enough in many cases. You can set an interval when batch job executes example: every 20 minutes and in most of cases it is sufficient to extract data using these batch jobs at certain time intervals.

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

Enabling XS Engine service in Configuration tab in HANA Studio − Go to Administrator tab in HANA studio of system. Go to Configuration → xsengine.ini and set instance value to 1.

Configuring DXC Data Replication

Enabling ICM Web Dispatcher service in HANA Studio − Go to Configuration → webdispatcher.ini and set instance value to 1.

Enabling ICM Web Dispatcher service

It enables ICM Web Dispatcher service in HANA system. Web dispatcher uses ICM method for data read and loading in HANA system.

Setup SAP HANA Direct Extractor Connection − Download the DXC delivery unit into SAP HANA. You can import the unit in the location /usr/sap/HDB/SYS/global/hdb/content.

Import the unit using Import Dialog in SAP HANA Content Node → Configure XS Application server to utilize the DXC → Change the application_container value to libxsdxc

Creating a HTTP connection in SAP BW − Now we need to create http connection in SAP BW using transaction code SM59.

Input Parameters − Enter Name of RFC Connection, HANA Host Name and <Instance Number>

Input Parameters

In Log on Security Tab, enter the DXC user created in HANA studio using basic Authentication method −

Security Tab

Setting up BW Parameters for HANA − Need to Setup the Following Parameters in BW Using transaction SE 38. Parameters List −

Setting up BW Parameters

  • 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

Install data source in ECC using RSA5.

Replicate the metadata using specified application component (data source version Need to 7.0, if we have 3.5 version data source we need to migrate that. Active the data Source in SAP BW. Once data source is activated in SAP BW it will create the following Table in Defined schema −

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

Now data is successfully loaded into Table /BIC/A0FI_AA_2000 once it is activated.

SAP HANA – Log Based Replication

This is also known as Sybase Replication in HANA system. The main components of this replication method are the Sybase Replication Agent, which is part of the SAP source application system, Replication agent and the Sybase Replication Server that is to be implemented in SAP HANA system.

Log Based Replication

Initial Load in Sybase Replication method is initiated by Load Controller and triggered by the administrator, in SAP HANA. It informs R3 Load to transfer initial load to HANA database. The R3 load on source system exports data for selected tables in source system and transfer this data to R3 load components in HANA system. R3 load on target system imports data into SAP HANA database.

SAP Host agent manages the authentication between the source system and target system, which is part of the source system. The Sybase Replication agent detects any data changes at time of initial load and ensures every single change is completed. When th ere is a change, update, and delete in entries of a table in source system, a table log is created. This table log moves data from source system to HANA database.

Delta Replication after Initial Load

The delta replication captures the data changes in source system in real time once the initial load and replication is completed. All further changes in source system are captured and replicated from source system to HANA database using above-mentioned method.

This method was part of initial offering for SAP HANA replication, but not positioned/supported anymore due to licensing issues and complexity and also SLT provides the same features.

Note − This method only supports SAP ERP system as data source and DB2 as database.

SAP HANA – ETL Based Replication

SAP HANA ETL based replication uses SAP Data Services to migrate data from SAP or non-SAP source system to target HANA database. BODS system is an ETL tool used to extract, transform and load data from source system to target system.

It enables to read the business data at Application layer. You need to define data flows in Data Services, scheduling a replication job and defining source and target system in data store in Data Services designer.

How to use SAP HANA Data Services ETL based Replication?

Login to Data Services Designer (choose Repository) → Create Data store

SAP HANA Data Services

For SAP ECC system, choose database as SAP Applications, enter ECC server name, user name and password for ECC system, Advanced tab choose details as instance number, client number, etc. and apply.

New Data Store

This data store will come under local object library, if you expand this there is no table inside it.

Local Object Library

Right click on Table → Import by name → Enter ECC table to import from ECC system (MARA is default table in ECC system) → Import → Now expand Table → MARA → Right Click View Data. If data is displayed, Data store connection is fine.

Now, to choose target system as HANA database, create a new data store. Create Data store → Name of data store SAP_HANA_TEST → Data store type (database) → Database type SAP HANA → Database version HANA 1.x.

Enter HANA server name, user name and password for HANA system and OK.

Create New Datastore

This data store will be added to Local Object Library. You can add table if you want to move data from source table to some specific table in HANA database. Note that target table should be of similar datatype as source table.

Creating a Replication Job

Create a new Project → Enter Project Name → Right Click on Project name → New Batch Job → Enter job name.

Creating a Replication Job

From right side tab, choose work flow → Enter work flow name → Double click to add it under batch job → Enter data flow → Enter data flow name → Double click to add it under batch job in Project area Save all option at top.

Object Area

Drag table from First Data Store ECC (MARA) to work area. Select it and right click → Add new → Template table to create new table with similar data types in HANA DB → Enter table name, Data store ECC_HANA_TEST2 → Owner name (schema name) → OK

Create Template

Drag table to front and connect both the table → save all. Now go to batch job → Right Click → Execute → Yes → OK

SAP ECC Test

Once you execute the Replication job, you will get a confirmation that job has been completed successfully.

Go to HANA studio → Expand Schema → Tables → Verify data. This is manual execution of a batch job.

Scheduling of Batch Job

You can also schedule a batch job by going to Data Services Management console. Login to Data Services Management Console.

Choose the repository from left side → Navigate to ‘Batch Job Configuration’ tab, where you will see the list of jobs → Against the job you want to schedule → click on add schedule → Enter the ‘schedule name’ and set the parameters like (time, date, reoccurring etc.) as appropriate and click on ‘Apply’.

Scheduling of Batch Job