Introducing SAP HANA SQL

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Describe SAP HANA SQL

SQL Console

Importance of SQL in SAP HANA Modeling

SQL plays an important role in SAP HANA modeling and can be implemented in different SAP HANA modeling objects, such as the following:

  • SQL expressions in calculation views

  • Procedures

  • Functions

  • SQL-based analytic privileges

SQL is used to implement custom logic in calculation views where standard features of the graphical calculation view are not enough.

SAP HANA Cloud modelers should acquire skills in SQL so that they can be fully effective in projects. As well are writing custom logic, they need to be able to understand the behavior of calculation views at run-time and to use the debugging tools which display the generated SQL.

Caution
SQL should not be considered as an alternative approach to graphical modeling. For example, you should NOT create an SQL-based object (function or procedure) to achieve a result that a graphical Calculation View can achieve (even if you have advanced SQL knowledge). Calculation views provide more optimization possibilities than SQL as they generate their own SQL code at run-time based on the variety of query conditions and implement advanced pruning to generate the leanest code.

Launching the SQL Console

The SQL Console, which is available in the Database Explorer, is used to write and execute ad-hoc SQL statements.

Although the SQL Console is ideal for executing ad-hoc, one-time instructions, such as creating a temporary table or executing a test query, it is not recommended for generating persistent development objects such as functions, procedure or tables that will become part of the application. For this, we recommend you create a source file type that corresponds to the object you wish to create. For example:

  • Table - always use source file type .hdbtable.

  • Function - always use source file type .hdbfunction.

When you deploy the source file, the corresponding run-time database object is generated.

Nevertheless, the SQL Console is an important tool for testing and executing one-time statements, so let's take a look at it.

The SQL Console is launched from the Database Explorer, which in turn can be launched from Business Application Studio. Each time you launch the SQL Console a new tab appears where you write your code. You can open as many tabs as you wish.

When you launch an SQL Console you need to be aware of which database connection you are using because the executed SQL will run against that connection. Also the design-time errors, for example a table you specify can't be found, will be based on the connection you are using. The database connection for your console can be selected in a number of ways:

  • Highlight an entry in the database connection list and then press Open SQL Console button in the toolbar.

  • Right-click an entry in the database connection list and then choose Open SQL Console in the context menu.

  • Expand a database connection and locate a database object, then right-click the object and choose an SQL expression from the context menu such as Generate SELECT Statement.

You can clearly see which database connection you are using because this is displayed at the top of the screen.

Regardless of which connection you choose, you can easily swap the connection by using the button in the toolbar Connect this SQL console to a different database.

HDI or Classic Database Connections

There are two main types of database connection:

  • HDI Container

  • SAP HANA database (also known as catalog)

It is important that you understand which type you are writing statements against in the SQL Console because this affects how you write the SQL code.

When executing statements against an HDI Container, you do not need to specify the schema. The schema is already known to the container you are running the statement against.

However, if you are executing statements against a connection that is an SAP HANA Databasecatalog type, then you must specify the schema in your statements.

SQL Console Basics

SQL Console Basics

Watch this video to learn about SQL Console Basics.

SQL Expressions in Calculation Views

Implementing SQL in Calculation Views

SQL can be used to create custom expressions in calculation views to define the following:

  • Calculated columns

  • Filters

  • Restricted columns

In addition, SQL can be used to return values for input parameters. For example, SQL can return the top customer of the month, and pass this to the input parameter of the calculation view as a filter. This is very powerful feature because input parameters are used in many places throughout calculation views as dynamic placeholders where you do not want to provide a fixed value for filters, ranking, user prompts, calculations and more.

It is essential for modelers to grasp the basics of SQL, because they will encounter SQL often throughout their modeling activities. Pay particular attention to the long list of available functions that are available with SQL. These functions can provide significant additional options when you are trying to develop some logic where data must be manipulated. For example, SQL provides many string manipulation functions that are useful for re-formatting a field, or extracting some characters from it. Also, there are many predefined data functions available in SQL that can help you to calculate with dates, for example, to find the number of days between two dates.

Note
SQL expressions in calculation views uses plain SQL and not SQLScript.

Modeling the Persistence Layer

Definition of the Persistence Layer

The persistence layer of SAP HANA refers to the physical data storage layer. In a relational database such as SAP HANA Cloud database, this is modeled using database tables. On top of the persistence layer, we build the virtual layer using calculation views. The final layer is the consumption layer where we find applications, such as reporting tools, that consume the views.

Note
It is worth pointing out at this early stage of the lesson that, in fact, a modeler may not be responsible for the development of the persistence layer. This is often taken care of by the application developers who might provide the tables, or perhaps the tools that are used to load data to SAP HANA might also create the tables, or the installation of an application, such as S/4HANA or BW/4HANA might create the tables. But modelers should be aware of the different approaches available for table creation so that they appreciate how this is done and the effort required to adjust the definitions of tables if needed.

We know that it is possible to define the SAP HANA persistence layer using standard SQL statements such as create table. In the SQL Console we can enter these SQL statements manually and execute them to create tables. You could create an entire SQL script with many statements to create an entire database.

While this approach produces the desired end result, that is, you would create a persistence layer on which you could begin developing models, there is an alternative way to create the persistence layer.

Table can be defined using source files (.hdbtable) that are part of your development project created in Business Application Studio. This means they are tied closely with other objects in the same project. For example, we should define the tables, calculations views, functions and all other development artifacts in a single project. By doing this we ensure better integrity as all dependent objects are checked and built together as a single unit. If one objects fails to be deployed, then no objects will be deployed.

Defining Tables using .HDBTABLE Syntax

The .HDBTABLE file format uses the SQL syntax of the CREATE TABLE statement, but without the leading "CREATE" keyword.

To delete a table defined using .HDBTABLE source files, you should not directly use the DROP TABLE statement in the SQL Console. Instead, you should simply delete the .HDBTABLE source file. When you next build the development folder where the source file existed, the run-time object (the table) will be dropped from the database.

Persistence Definition Source Files in the Modeling Stack

The source files that provide the tables should be created before you begin working on the calculation views. This is because the calculation views usually refers to the tables, and the source files that define the tables need to be in place, otherwise the build of the calculation view will fail.

Other Methods to Create the Persistence Layer

As well as defining the tables using SQL 'create' statements in the SQL Console, or defining source files that are deployed to generate the tables, tables can also be created in other ways. Some examples are as follows:

  • Automatically generated from the ETL tools, such as HANA SDI or even external tools such as SLT, or SAP Data Services. Many ETL tools provide automatic generation of SAP HANA target tables based on the incoming data structures defined in the ETL data flow.

  • Automatically generated from installation of an application

    For example SAP S/4HANA, where the empty transaction and master data tables are built during the installation process.

  • Automatically generated from the migration of a legacy database to SAP HANA

    For example, SAP Business Suite running on Oracle to SAP Business Suite powered by HANA, where the filled transaction and master data tables are recreated in SAP HANA using migration tools.

Creating Tables

SQL Create Tables

Watch this video to learn about creating tables.

Loading Tables with Data

SQL Fill Tables

Watch this video to learn about loading tables with data.

Save progress to your learning plan by logging in or creating an account