Using SAP HANA, you can perform business calculations directly on the original data in the main memory without transforming data. SQL and SQLScript are available for this purpose. Calculations can be expressed using analytical models (modeled or implemented with SQLScript) or database procedures and can be used within ABAP.

4Native Database Development Using SAP HANA

Having explained the basic principles of SAP HANA in the first three chapters of this book, this chapter now provides an overview of the native database development using SAP HANA. For this purpose, you'll first learn how the SAP HANA database enhances the SQL standard with some functions and particularly using SQLScript.

Subsequently, we describe how to implement database procedures using SQLScript in SAP HANA Studio, followed by a description of analytical models (views). We explain which view types SAP HANA supports and how to create and test these views using SAP HANA Studio.

4.1Basic Principles of Native Database Development

Chapter 3, Section 3.2, discussed some basic principles of SQL as a central programming language in the context of databases and presented the usage via ABAP, particularly, via database-independent Open SQL. However, the Open SQL options aren’t sufficient to execute more complex operations on data within the database and specifically in SAP HANA. For this reason, this section first deals with the SQL dialect of SAP HANA (including an overview of database objects). We use the term dialect because although SAP HANA supports the fundamental SQL standard, it also defines other databases of proprietary SQL enhancements.

4.1.1Objects in the SAP HANA Database Catalog

Before we deal with some SQL commands, we first provide an overview of all native database objects in SAP HANA. The database catalog of a database system contains the metadata of all objects included in a system, such as the names and the structure of all existing tables.

You can find the database catalog in SAP HANA Studio when you open the Catalog node for a system connection. Table 4.1 provides an overview of all types of database objects in SAP HANA. The SAP HANA-specific column shows whether you can find this object in SAP HANA only or also in other relational databases; the ABAP Support column shows whether you can use this object directly via the ABAP Data Dictionary (DDIC).

Object	SAP HANA-specific	ABAP Support	Description
Schema	No	No	A schema forms the namespace in the database.
Table	No	Yes (Chapter 3, Section 3.2)	Tables define the data structures (columns) in which you store data records (rows).
View	No	Yes (Chapter 3, Section 3.2)	Views (SQL) define a view on data that you can access like a table.
Procedure	No	Yes, (Chapter 5, Section 5.2, and Chapter 6, Section 6.5).	Procedures contain a summary of database commands, including input and output parameters.
Index	No	Yes (Chapter 3, Section 3.2)	An index is an additional storage structure for accelerating database accesses.
Function	No	No	Functions are user-defined operations that can be used within the scope of other SQL statements.
Trigger	No	No	Triggers allow you to respond to special events (e.g., changes to the content of a table) and run your own logic.
Sequence	No	No	Sequences enable you to generate unique consecutive numbers.
Synonym	No	No	A synonym allows you to address a table, a view, or a procedure via another name.
Column views	Yes	Indirect (Chapter 5, Section 5.1)	Column views are an SAP HANA-specific form of views generated during the creation of analytical models (see Section 4.4).
EPM models and query sources	Yes	No	Enterprise Performance Management (EPM) models and query sources are special artifacts in the context of the planning engine in SAP HANA. These objects won’t be discussed within the scope of this book.

Table 4.1Types of Database Objects in SAP HANA

An appropriate CREATE statement exists to create a database object of a specific type in the catalog using SQL. In the same way, you can remove objects again using DROP and make changes using ALTER. To create or delete a sequence, for example, you can use the CREATE SEQUENCE or DROP SEQUENCE statements, respectively. Within the scope of this chapter, we’ll discuss the creation of tables, views, functions, and procedures in detail. For other objects, refer to the SAP HANA SQL reference at http://help.sap.com/hana.

In most cases, however, you don/t create the respective objects manually via SQL; instead, you use special tools in the ABAP development environment or SAP HANA Studio. This chapter focuses on procedures (Section 4.3) and column views (Section 4.4). You can also create the other objects of Table 4.1 using SAP HANA Studio. The objects that are supported in DDIC have already been described to some extent in Chapter 3, Section 3.2 (tables, views, indexes). This description is continued in Chapter 5 (proxies for column views and procedures) and Chapter 6 (CDS views and ABAP Managed Database Procedures [AMDP]). The objects of Table 4.1 form the basis in all cases.

Object types such as views, triggers, functions, and procedures require an implementation in the associated logic. SQL and SQLScript are primarily used at these points, and there may be object-specific restrictions. You can define a normal view via SQL only and not via SQLScript. The functional scope of SQLScript is restricted for triggers.

4.1.2SQL Standard and SAP HANA-specific Enhancements

SAP HANA particularly supports the central SQL standards SQL-92 and (with certain exceptions) SQL:1999 . At this point, we can’t provide a full overview of data types, statements, and operations, so please refer to the respective specifications. At this level, SAP HANA is fully compatible with other relational database systems. Based on these standards, SAP HANA provides numerous additional capabilities that can be used through SQL enhancements. The following text details some important SQL statements in SAP HANA that play a role within the scope of this book.

To create a table in SAP HANA, you can use the CREATE TABLE statement (just like in the SQL standard). However, in this case, you create a table in SAP HANA’s row store by default (see Chapter 1, Section 1.2.2). You can use the CREATE <table type> TABLE … statement to create the desired table type. Table 4.2 shows the values permitted. The ABAP Support column shows whether you also can create such tables using the DDIC.

Type	ABAP Support	Description
`ROW`	Yes	Row store tables (standard).
`COLUMN`	Yes	Column store tables (recommended for most application scenarios).
`HISTORY COLUMN`	No	Special table type that supports a “time journey” function so that you can query the table content for a specific point in the past.
`GLOBAL TEMPORARY (COLUMN)`	No (internal in the ABAP kernel in the context of database procedures)	Global temporary tables (GTT) allow you to store interim results within a session. The table definition can be used by all users of the database; the contents, however, can only be viewed in the same database connection.
`LOCAL TEMPORARY (COLUMN)`	No	In contrast to GTTs, for local temporary tables, the metadata is also linked to the database connection. Thus, such tables can only be used within a database connection.
`VIRTUAL`	No	A virtual table is a reference to a table in another system that is linked with SAP HANA using the Smart Data Access (SDA) mechanism (see Chapter 1, Section 1.1.5).

Table 4.2Types in SAP HANA

In most cases, you (or the DDIC) create tables in the column store, as shown in Listing 4.1. The additions UNLOAD PRIORITY 5 AUTO MERGE are SAP HANA-specific.

CREATE COLUMN TABLE "SCARR"
("MANDT" NVARCHAR(3) DEFAULT '000' NOT NULL ,
"CARRID" NVARCHAR(3) DEFAULT '' NOT NULL ,
"CARRNAME" NVARCHAR(20) DEFAULT '' NOT NULL ,
"CURRCODE" NVARCHAR(5) DEFAULT '' NOT NULL ,
"URL" NVARCHAR(255) DEFAULT '' NOT NULL ,
PRIMARY KEY ("MANDT", "CARRID"))
UNLOAD PRIORITY 5 AUTO MERGE

Listing 4.1Example: Definition of Table SCARR

Chapter 3 already discussed the data types supported in SAP HANA. In addition to the default SQL data types such as INTEGER, VARCHAR, and so on, the column store in SAP HANA also offers several special data structures, which you’ll get to know in subsequent chapters. These include the data types TEXT and SHORTTEXT (Chapter 10) as well as the geodata types of the ST_GEOMETRY family (Chapter 13).

Every relational database provides an option for defining views. These standard views (also referred to as SQL views ) are defined in the database catalog using the CREATE VIEW statement essentially as an alias for a SQL query:

CREATE VIEW <name> AS SELECT <SQL query>

As a relational database, SAP HANA also supports SQL views; these views differ from the views of other databases only in the functional scope of the SELECT statement in SAP HANA, which we’ll discuss in the following.

As you might expect, the SELECT statement provides the essential interface for the data access. This section can’t detail all facets of this extremely comprehensive statement. Instead, it’s limited to the various subject areas that play a significant role for creating views and procedures. The example in Listing 4.2 demonstrates various advanced capabilities that you don’t know from classic Open SQL in ABAP.

SELECT
concat (carrid,connid) as "Connection",
passname as "Passenger",
to_date(fldate) as "Flight date",
to_date(order_date) as "Order date",
convert_currency(amount=>loccuram,
"SOURCE_UNIT_COLUMN" =>loccurkey,
"SCHEMA" => 'SAPH74',
"TARGET_UNIT_COLUMN" => 'EUR',
"REFERENCE_DATE" =>CURRENT_UTCDATE,
"CLIENT" => '001') as "Price (EUR)"
from sbook
where days_between(order_date, fldate) < 100
and contains(passname, 'Idda Pratt', fuzzy(0.8))
order by fldate desc
limit 10

Listing 4.2Example of a SELECT Statement

We’ll use an example in the following to describe some functions in more detail so that you can get an idea of the functional scope.

If the result set of a query is very large, it’s useful to retrieve the results in smaller packages, particularly if a user can’t view all results at a glance. For this purpose, databases usually provide paging as an option for limiting the number of rows (LIMIT) and for defining the starting row (OFFSET). Listing 4.2 shows a usage example in which a maximum of 10 entries is read.

For some queries, you must convert data types to leverage certain operations. So if you treat a column of an ABAP table with the data type DATS (on database NVARCHAR(8)) as a real date using the SQL data type DATE, you can use the conversion function to_date (refer to Listing 4.2). There are also functions for converting other data types (e.g., to_int, to_decimal, etc.).

Several operations are available for manipulating character strings, for instance, concatenate texts (concat), read substrings (substring), or convert to uppercase or lowercase (upper/lower). In Listing 4.2, you read the airline and the flight connection as a combined field using the concat (carrid,connid) expression.

Date calculations play a role in many queries. In SAP HANA SQL, you can access the current data in various variants (e.g., using current_utcdate) or calculate with date values (e.g., using add_days). In the example shown earlier in Listing 4.2, the days_between function is used to compare dates.

As a last example, we want to mention the conversion of currencies and units, which you can use by means of the functions convert_currency (for currencies) or unit_conversion (for units). In this context, the underlying logic is compatible with the respective logic in the ABAP AS and depends on the same Customizing tables (see the following information box). In Listing 4.2, we ran a currency conversion in euros for the current date. Within the scope of SQLScript and modeled views, this chapter will provide further options for implementing such conversions.

[»]Currency Conversion and Unit Conversion in SAP NetWeaver AS ABAP

Currency conversion and unit conversion are standard functions in SAP NetWeaver AS ABAP. The customizing of the currency conversion in SAP Basis is done via the TCUR* tables in the SFIB package. To perform a conversion in ABAP, you can, for instance, use the function modules in the SCUN function group (e.g., CONVERT_TO_LOCAL_CURRENCY). In addition to the amount, the source currency, and the target currency, the key date and the exchange rate type are also important parameters for the conversion.

Unit conversion for ISO codes can be found in the T006* tables of the SZME package. To perform a conversion in ABAP, you can use the UNIT_CONVERSION_SIMPLE function module.

Because joins will play a central role for subsequent modeling of column views, we’ll briefly review the various join types in the SQL standard using examples. To do so, we’ll use the known tables SFLIGHT (flights) and SCARR (airlines) with a foreign key relationship via the CARRID field (for the sake of simplicity, the client is disregarded in the excerpt in Table 4.3). The tables have an n:1 relationship and table SCARR may contain airlines for which no flight is entered in table SFLIGHT (e.g., the airline UA in Table 4.3).

Table SFLIGHT			Table SCARR
`CARRID`	`CONNID`	`FLDATE`	`CARRID`	`CARRNAME`
`AA`	`0017`	`20150101`	`AA`	`American Airlines`
…	…	…	…	…
`LH`	`400`	`20150101`	`LH`	`Lufthansa`
`LH`	`400`	`20150102`	…	…
…	…	…	`UA`	`United Airways`

Table 4.3Sample Data from the Tables SFLIGHT and SCARR to Explain Join Types

When defining joins, we differentiate between inner and outer joins. For an inner join, all combinations are included in the result if there is a matching entry in both tables. With an outer join , results that are present only in the left table (left outer join ), only in the right table (right outer join ), or in any of the tables (full outer join ) are also included. To differentiate between left and right, the join order is used.

The differences between the join types will be explained based on the following SQL examples for selecting flights and the corresponding airline names. The first example comprises an inner join. Because the airline UA isn’t present in the sample data for table SFLIGHT, there is no matching entry in the result set:

select s.carrid, s.connid, c.carrname from sflight as s
inner join scarr as c on s.carrid = c.carrid

For a right outer join, where table SCARR is the right-hand table, an entry for the airline UA is displayed in the result set, even though there is no corresponding entry in table SFLIGHT. The carrid and connid columns thus display the value NULL:

select s.carrid, s.connid, c.carrname from sflight as s
right outer join scarr as c on s.carrid = c.carrid

Similarly, UA is also included in the result set for a left outer join with table SCARR as the left-hand table. If the data model assumes that a corresponding airline exists for every entry of a flight (but not necessarily the other way around), the two outer join variants are functionally equivalent.

select s.carrid, s.connid, c.carrname from scarr as c
left outer join sflight as s on s.carrid = c.carrid

SAP HANA also provides advanced analytical operations in addition to the standard SQL aggregate functions COUNT, SUM, AVG, and so on (in combination with GROUP BY). These include more complex groupings via GROUPING SETS; support for expressions such as ROLLUP, CUBE, and so on; and calculation of subtotals. The following example for determining the flight prices groups by all combinations of airline, connection, and currency with subtotals in one single statement:

select carrid, connid, loccurkey, sum(loccuram)
from sbook group by cube(connid, carrid, loccurkey) ;

[»]Support of Multidimensional Expressions (MDX )

Besides the analytical functions in SQL, which have already been mentioned, SAP HANA also natively supports multidimensional expressions (MDX). MDX is a powerful database query language for OLAP scenarios that was promoted by Microsoft and has become an industry standard. In contrast to SQL, MDX focuses on multidimensional access, with the terms measures and dimension playing a decisive role for selections on a cube that is based on a star schema.

You can find more detailed information on MDX support in SAP HANA under http://help.sap.com/hana.

In addition, SAP HANA offers some enhancements that we’ll present in detail in subsequent chapters. These include, for example, the CONTAINS expression for the fuzzy search, which we’ll discuss in Chapter 10. In Listing 4.2, shown previously, we searched for bookings where the passenger name is similar to “Idda Pratt” (which will find entries with the name “Idda Pratt”). Additional enhancements include geographical functions (e.g., for determining distances and areas), which are detailed in Chapter 13.