SQL is the main interface for client applications. The SQL implementation of the SAP HANA DATABASE is based on SQL 92 entry-level features and core features of SQL 99. However, it offers several SQL extensions on top of this standard. These extensions are available for creating tables as both row-based and column-based tables and for conversion between the two formats. For most SQL statements, it is irrelevant whether the table is a column-based or row-based. However, there are some features — for example, time-based queries and column-store specific parameters — that are supported only for columnar tables.
The SAP HANA database has its own scripting language, named SQLScript, that offers scripting capabilities that allow application-specific calculations to run inside the database. SQLScript is similar conceptually to “stored procedures,” but it contains several modern innovations that make it much more powerful and flexible.
SQL Script is a Set of SQL statements for HANA database which allows developer to pass complex logic into the database. SQL Script is known as collections of SQL extensions. These extensions are, Data Extensions, Function Extensions, and Procedure Extension.
SQL Script supports stored Functions and Procedures and that allows pushing complex parts of Application logic to the database.
The Main benefit of using SQL Script is to allow the execution of complex calculations inside the SAP HANA database. Using SQL Scripts in place of single query enables Functions to return multiple values. Complex SQL functions can be further decomposed into smaller functions. SQL Script provides control logic that is not available in a single SQL statement.
The SAP HANA database also supports MDX (Multi Dimensional eXpressions), the de facto standard for multidimensional queries. The MDX can be used to connect a variety of analytics applications like SAP Business Objects products and clients such as Microsoft Excel.
The engines used in the backend to process a native SQL statement or select statement on modeling objects.
The core of the SAP HANA database contains several engines that are used for specific tasks. The two primary engines are the planning engine and the calculation engine.
The SAP HANA database contains a component called the planning engine that allows financial planning applications to execute basic planning operations in the database layer.
Used for Analytic views with calculated attributes and Calculation views
What truly makes SAP HANA unique is that, in addition to its being a standard SQL database, it also natively supports data calculation inside the database itself. By incorporating procedural language support — C++, Python, and ABAP — directly into the database kernel through a dedicated calculation engine, it can achieve exceptional performance because the data do not need to be moved out of the database, processed, and then written back in.
The technical details of communicating with the SAP HANA database are contained in a set of included client libraries for standard platforms and clients. The following client libraries are provided for accessing the SAP HANA database via SQL or MDX:
JDBC driver for Java clients
ODBC driver for Windows/Unix/Linux clients, especially for MS Office integration
DBSL (Database Shared Library) for ABAP
You can dramatically increase performance by executing complex computations in the database instead of at the application server level.
SAP HANA’s library of in-database advanced functions is a valuable asset to many organizations. The Application Function Libraries (AFL) include both the Business Function Library (BFL) which contains functions for common business calculations, such as depreciation, cash flow discounting, Year-over-Year performance, and time series forecasting. The Predictive Analysis Library (PAL) has predictive algorithms, including classification, clustering, association, and more advanced time series functions. The AFL is available on all SAP HANA systems, but the interface to use these algorithms can be quite cumbersome.
SAP has leveraged its deep application knowledge from the ABAP stack to port specific functionality as infrastructure components within SAP HANA to be consumed by any application logic extension. Examples of common business functions are “currency conversion” and “calendar functionality.”
Get Updates on Tech posts, Interview & Certification questions and training schedules