SAP HANA is an in-memory data platform that is deployable as an appliance or in the cloud. At its core, it is an innovative in-memory relational database management system that makes full sense of the capabilities of current hardware to increase application performance, to reduce cost of ownership, and to enable new scenarios and applications that were not possible before. With SAP HANA, you have the opportunity to build applications that integrate the business logic, control logic, and the database layer with unprecedented performance. As a developer, one of the key questions is how you can minimize data movements. The more you can do directly on the data in memory next to the CPUs, the better the application will perform.
Content
- Developing Applications for SAP HANA
- Database Tasks, Loading, and Modeling
- SQLScript Basics
- Exposing and Consuming Data with OData
- Exposing and Consuming Data with Server-Side JavaScript
- SAP HANA Advanced Development Options
Target Audience
Application developers.
1. Developing Applications for SAP HANA
1.1 SAP HANA Native Application Basics
SAP HANA is a platform. It’s offered as both an appliance and it’s offered as a cloud offering. Inside this platform is our ability to use both SAP HANA as a database and SAP HANA as an application platform itself. SAP HANA is first and foremost a database. SAP HANA is an in-memory database. That means that all the operations that happen, happen in memory. It is designed for massively parallel processing. HANA can store data both in a row format and in a column format.
Application server has been built into HANA itself. This application server is named SAP HANA Extended Application Services, or sometimes you will see it abbreviated as XS, XS standing for Extended Application Services.
XS also means extra small, meaning the application server layer is extra small.
There are two main types of applications that could leverage SAP HANA. We have the native SAP HANA applications that will be the focus of this blog.
Data intensive logic is written in the database using SQL and SQLScript. Server-side JavaScript is the main programming language for HANA Extended Application Services applications. There is also support for REST-based OData services with both JSON and Atom XML rendering. Furthermore, there is also support for XMLA and MDX access.
Non-native SAP HANA applications are also using application server in addition to the one that’s embedded inside of HANA.
They use ABAP, Java, .Net, and they use one of the open standard interfaces like JDBC or ODBC to connect to SAP HANA. ABAP kernel and the ABAP language has been heavily optimized to also be able to connect to and consume HANA content.
We’ll show how to create schemas, tables, and then the views on top of those tables. We’ll learn about SQLScript. That is SAP’s language for creating stored procedures and allowing to put more and more of logic deeply down inside the database. We’ll learn how we can both expose and consume SAP HANA data via HTTP, leverage that built in Web server to serve out our user interface. We’ll also learn how to code service enablement and validation logic on the server side using JavaScript, and how to use the built in OData service generation framework. We’ll learn about SAP’s UI library for building HTML5-based applications, something that we call SAPUI5. We’ll learn about the complete end-to-end lifecycle management and how to build artifacts, how to translate them, how to package them for delivery through your own system landscape up from development to QA to production. And then finally we’ll look at SAP UI integration services. This is a special offering for building a lightweight portal that runs on top of HANA.
1.2 SAP HANA Application Development Tools
Tools available for a developer to build applications in SAP HANA. The primary application development tool is the SAP HANA Studio.
The studio is based upon Eclipse and it has very much the Eclipse look and feel.
The SAP HANA Client generally is used because it has the JDBC and ODBC drivers in it, but it contains a special piece of functionality for us as developers, as well.
It contains a tool called regi. That stands for Repository Git. It’s actually this little tool, this little regi tool that’s part of HANA client, that does the check in and check out. The HANA studio itself doesn’t know how to do that.
1.3 SAP HANA Software Downloads
We have the SAP Community Network (SCN).
This is the main umbrella site that most people will go to if they need any information about how to do something with SAP, they need help, they need downloadable content, message boards, all these sort of things are available at the SAP Community Network.
Within the SAP Community Network, we have a subset of the community that’s designed for developers. And this is the SAP HANA Developer Center.
You’ll also find links where you can download various pieces of free software. For instance, you can download the SAP HANA studio and the SAP HANA client. There’s a link to the SAP HANA Academy, which has series of e-learning videos. But it is always recommended to people that are new to an area, new to a topic area–maybe you’re new to SAP HANA–one of the best learning resources is to go watch the forums. Put a watch on them and just read the questions that other people are asking. You’re going to learn so much by seeing their questions and seeing the answers that are being posted to their questions.
1.4 Access to SAP HANA Systems (in the Cloud)
An SAP HANA system is available on the cloud.
1.5 Example Application
A complete Hello World example application is built.
2. Database Tasks, Loading, and Modeling
2.1 Database Schemas and Database Tables
The SAP HANA repository manages all artifacts. It stores data artifacts, data-intensive logic, control flow logic, and presentation logic.
The schema is a mandatory database object; all database objects have to belong to a schema.
Tables are created inside of a schema.
2.2 Sequences and SQL Views
A database sequence is an incrementing list of numeric values.
It allows you to have a unique key that you will auto-increment as you insert new records into the database.
A SQL view is a basic join between two or more tables.
2.3 Authorizations
SAP has delivered some default roles (built-in roles), such as CONTENT_ADMIN, the MODELING, and the PUBLIC.
2.4 EPM Demo Schema
SAP has built and delivered a demo scenario, which can be used for learning and other purposes.
EPM stands for Enterprise Procurement Management. The EPM demo content has its own schema named SAP HANA EPM demo. Inside that schema there is a variety of tables, views, sequences, synonyms, and other content. There is a data generator, so you can control how much data that you want in this tool.
2.5 Single File Data Load of CSVs
Look at how we can set up an initial data load into a table so that every time that the table is activated in a new system, some base set of data will automatically be loaded into that table. Now to do this single file load of Comma-Separated Values, we actually need three files that will be created in the content repository. First we need the CSV file itself.
Most often, you will usually use Microsoft Excel to create the data or to cleanse the data. Next we need the Table Import Model (TIM).
This is the file that really defines the destination for the data. It defines the database schema and table we want to insert it into every time that table gets activated. And then finally there is a third file that we need to create and that’s the table import data (TID).
This is what connects the CSV file and the model so it connects the target and the base data, the CSV data that we want to load into that target table.
2.6 Attribute Views
The Attribute view is heavily optimized for processing of joins between multiple tables. The Attribute view may contain calculated columns, where we write formulas or perform conversions on data from other columns.
We can also have hierarchies. Hierarchies are drill-in capabilities.
2.7 Analytic Views
The Analytic View is designed to take advantage of the computational power of SAP HANA and, specifically, to help you with calculating aggregates. Analytic view is actually processed by a different engine than the Attribute view. The Analytic view is processed by the OLAP, or analytic engine, inside of HANA, as opposed to the join and primarily transactional based engine that processes the Attribute view.
Analytic views always need at least one of what we’ll call a “measure”. A measure is basically anything that can be aggregated; therefore it must be a numeric-based column.
2.8 Calculation Views
We can choose weather we want to do the design of a Calculation View graphically or using SQLScript.
The graphical approach allows us to diagram the flow of the logic in the graphical view. We have the ability to group, to union, to join, maybe then to project again, so we can have many different nodes processing of a graphical SQL script. In the SQLScript approach a text editor is opened where we can write SQLScript.
2.9 Analytic Privileges
SQL privileges are usually set at the schema level or at the table level.
System privileges are for administrative tasks or development tasks. These are set directly to the user and/or their role. Package privileges are all about controlling editing and activation rights at a package level within the SAP HANA content repository. Analytic privilege allows us to really set authorizations at a row level as well. And really you shouldn’t have a view without a corresponding analytic privilege, particularly if you’re accessing your views from some of the reporting tools, like BusinessObjects tool framework, which absolutely require that you have an analytic privilege.
3. SQLScript Basics
3.1 Introduction to SQLScript
SQLScript is SAP’s interface for applications to access SAP HANA. Calculations are executed in the database layer to get the maximum benefit from SAP HANA features such as fast column operations, query optimization, and parallel execution. SQLScript analyzes your statements and it will see which of them can be performed in parallel.
In this query we have a select from the product table (query one) and then we have a select from the text table (query two).
They can be compressed down into one query when executed in the database. Next, we have query three and four. Here we’re doing a select count for product text and we’re doing another select count for a different WHERE condition. These two can be processed completely in parallel.
The syntax for creating a stored procedure that uses SQLScript is pretty simple.
First we say CREATE PROCEDURE. Next we have the READS SQL DATA, and there we specify if this is a read only or read/write procedure.
3.2 Create an SQLScript Procedure with SELECT Statement
A simple SQLScript procedure is created.
3.3 Create an SQLScript Procedure with Calculation Engine (CE) Functions
The CE, or the calculation engine, is the main engine that processes all SQLScript statements.
There are several engines inside of HANA.
There’s a calculation engine. That’s where calculation views and SQLScript are executed. Then there’s an OLAP engine for analytic processing. That’s where analytical views are executed. And then there’s a join engine, and that’s where attribute views are processed. And all this is within the column store. There’s actually a separate row store engine as well. In addition to all these other engines, we have a SQL optimizer.
One of the places where you can potentially run into less-performant operations than what you might expect is when you have to transfer control and data between multiple engines. In particular, this is what potentially happens here if you have SQL statements inside SQLScript. They have to be handed off to the SQL optimizer. But if you can stay inside the calculation engine, then you save that trip over to the SQL optimizer for it to have to do some work. SAP has taken common SQL statements and implemented them directly inside the calculation engine so that we don’t have to go over to the SQL optimizer. And that’s what is called CE functions, or CE built-in functions. Here are some examples.
We have a CE function, CE_CALC_VIEW; that allows us to read data from a view. Then we have a CE_COLUMN_TABLE; that allows us to read data from a database table. We have CE_PROJECTION. That’s where we can supply a WHERE condition or narrow our field selection. We have the CE_JOIN. This would be similar to writing a JOIN statement between two tables or a view and a table.
3.4 Create an SQLScript Procedure with Imperative Logic
Imperative logic, in general, means that we are processing one record at a time. Unlike SQL or CE functions that perform bulk operations on an entire record set, this is one variable or one record at a time. In SQLScript, we have the following logic available:
- IF, ELSE, ENDIF
- WHILE Loops
- FOR Loops
- Case
3.5 Using the SQLScript Debugger
The standard Eclipse debugger has several parts to it.
Up in the top left-hand corner we have the Debug Session. This shows us which server we’re connected to and which debug session we’re currently in. In the top right-hand corner, we have our Watch area, that would have a listing of our breakpoints and it would show us all of our variables. And from there we’ll be able to see values inside those variables as well. And in the bottom, we have the Code so we can see which line of code we’re currently debugging.
- SAP Managed Tags:
