Part 01 of a 5 part series

Introduction

Congratulations!  As a PowerBuilder developer reading this article, you are demonstrating your curiosity (hopefully more) as to what the buzz about SAP HANA is about.  Perhaps you’d even like to discover for yourself what it takes to integrate your PowerBuilder applications with HANA.  In this series of articles and the videos embedded in them, I will show you the basics of how to combine PowerBuilder with HANA to rapidly develop rich client OLTP applications that feed data into HANA’s relational datastore and OLAP style decision support apps that display data delivered from HANA’s real time analytic engine.

I’m writing with the assumption that you are an experienced PowerBuilder application developer who also has extensive experience interfacing to a DBMS such as Oracle, SAP ASE, SAP ASA or Microsoft SQL Server.

The five articles and their videos form a sequence that will guide you toward (1) Developing an understanding of what SAP HANA is, how it works and what business problems it helps solve (2) Setting up and configuring a HANA Cloud server instance (3) Interfacing the PowerBuilder IDE and a new or existing PowerBuilder application to a HANA Cloud server instance (4) Migrating data from an existing database into HANA and (5) Modifying PowerBuilder application related data logic to comply with HANA specifics.

The articles are: (1) What is HANA? A quick overview for PowerBuilder developers (2) Getting Started: Setting up your SAP HANA Cloud server (3) Configuring your PB client & ODBC driver special concerns (4) Migrating Data from ASA to HANA including In-app data piping and tool based bulk loading (5) Data Logic and Client modification concerns

To assist in you in developing hands-on HANA appreciation: I’m providing PowerBuilder 12.5.2 source code for my Simple Simon Sandwich Shop application suite (SSSS), a sample application suite together with its SQL Anywhere 12 database.  Anyone who’s ever eaten lunch at a sandwich shop will understand the SSSS use case.  I originally wrote the apps for sales engineers who demonstrate PowerBuilder Classic and .NET RAD to their prospects.  It supports in both Client/Server and SOA architectures.  I used the app to ‘cut my teeth’ on HANA while adding a HANA connectivity layer to it.

Watch this video to see the SSSS app in action. I built these three apps in about a week (OK a week and 18 yrs!) and migrated the code to work with HANA in a couple of hrs.  You’ll also get an idea of why I think HANA Cloud and PowerBuilder can be a winning combination.

Follow this link to download SSSS source code and target

Follow this link to download my modified EASDemo125 database

At the end of each article, I included a ToDo Activity list which walks you, step by step, through setting up a AWS HANA Cloud Server, migrating data and data logic and running the transformed SSSS application with HANA connectivity.

Lest you conclude that only PowerBuilder Classic can access HANA, here’s a video showing how I used PowerBuilder .NET’s DataWindow technology together with Syncfusion’s Docking Manager control to access display data from a HANA database

What is HANA?

According to the SAP HANA Developer Guide, Chapter 3 “SAP HANA is a universal database management system that is designed to run on modern, distributed computers built out of multi-core CPUs with fast communication between cores, and containing terabytes of main memory.  All data is available in DRAM main memory, which completely avoids disk I/O performance penalties.”  HANA is at the core of SAP’s goal to be the No. 2 Database Player By 2015. 

HANA’s strength lies in its “ability to run an entire enterprise in real time. HANA, possessing both OLTP and OLAP characteristics, is built to run as a single in-memory database thereby eliminating all data redundancy.  Within clusters of affordable and scalable servers, transactional and analytical data are run on the same database, eliminating the need for different types of databases for different application needs” An entire global enterprise can potentially run out of a single database in real time!  SAP is multiple years ahead of its nearest competitor.  For the largest organizations, HANA is installed on-premises as a super powerful, memory loaded appliance. 

According to Wikipedia  HANA “is the synthesis of three separate products – TREX a text retrieval and extraction engine, P*Time, an in-memory OLTP RDBMS technology and MaxDB a code branch of an open source RDBMS providing persistence, backup and other facilities” . A quick read of the abstract and introduction to a paper delivered by P*Time’s lead architects at the 30th VLDB Conference will give you an appreciation for some of HANA’s technological performance innovations and the business problems it addresses.

Riding the wave of Cloud Computing and the SaaS delivery model SAP provides a HANA Cloud version which provides all HANA’s benefits in a hosted environment.  SAP HANA Cloud is available as a ‘stand-alone’ Cloud Server edition via various hosting providers.  Currently the amount of bundled DRAM memory and number of parallel multicore processors is the main differentiator between the delivery mechanisms. 

Most recently SAP integrated its NetWeaver application server with HANA is providing a fully ISO 27001 security compliant platform for cloud development and deployment.  See the SAP online SAP HANA Cloud Applications Partner Center for full details. Lastly, SAP is currently in the process of moving all of its line-of-business cloud applications to the HANA Cloud Platform; SAP Financials OnDemand is available as SAP Cloud for Financials, SAP Travel OnDemand is available as SAP Cloud for Travel, etc. 

The SAP approach contrasts with that of its competitor, Oracle, which has, according to a recent article in the Virtualization Journal  “thrived on a model where generally companies start with a simple database that’s utilized for checking sales orders and ensuring product delivery to customers.  As the business grows they need more databases with different and more demanding functions. Functions such as managing customer relationships, complex reporting and analysis drives a need for new databases that are separate from the actual business, requiring data to be moved from one system to another. Eventually you have a sprawl of databases, as existing ones are unable to handle the workloads making it almost impossible to track data movements yet alone attain real time updates.  So while the Oracle marketing machine is also pitching the benefits of in-memory via its Exalytics appliance and in-memory database, TimesTen, Oracle is certainly in no rush to break this traditional model of database sprawl and the money-spinning licenses that come with it.”

Technically Speaking

Core HANA is comprised of 4 processes; the Index Server, the Preprocessor Server, the Statistics Server and the Name Server.   They are shown in figure 1.  You will see these processes loading on the Web Console when you start up your Cloud Server.  (You’ll also see a fifth process, the XS Server, starting up.  This is the light weight app server that hosts business logic and exposes an OData interface to your data.  XS runs next to the database, but it’s not part of the core database)

HANA’s outstanding, speed enhanced characteristics include its ability to (1) organize data in columnar storage structures and (2) massively parallel process queries (3) simplify applications by eliminating the need to calculate and store materialized aggregates.   In column storage the entries of a column are stored in contiguous memory locations. This structure allows for highly efficient compression schemes which support high speed comparison operations and eliminate the need for additional index structures.  By supporting parallel processing, operations on different columns can easily be processed in parallel. If multiple columns need to be searched or aggregated, each of these operations can be assigned to a different processor core. In addition, operations on one column can be parallelized by partitioning the column into multiple sections that can be processed by different processor cores.  Because aggregates on live, big datasets can be calculated almost instantaneously, HANA eliminates the need for storing periodically calculated, materialized aggregates.  This allows for real time decisions based on real time aggregations to respond rapidly to changing business environments.

HANA can be either installed as an appliance for on-premises large application use or as cloud-hosted service for small to medium size applications.  The main difference between the formats is the amount of bundled DRAM memory and number of parallel multicore processors.  You’ll be using a cost-effective cloud-hosted format during this PowerBuilder integration exercise.

How does PowerBuilder fit in?

To quote from SAP HANA Developer Guide, Section 4.1: Developer Scenarios

SAP HANA based real-time applications can be divided into two broad categories:

1. Web-based scenarios taking full advantage of SAP HANA Extended Application Services (XS). XS is an app server built into HANA.  Since mobile apps typically have native GUIs, data and the logic that wraps it can be hosted on the server side without the need to generate dynamic web GUI. XS, which has direct memory access to the HANA Index Server, provides these services. Figure 2 shows this architecture.  For a complete example of HANA XS, see this SCN article by Thomas Jung. Note: It is possible to directly build a browser-based GUI using the SAPUI5 toolkit that accesses data and logic hosted in XS.  PowerBuilder 15 is slated to support OData as a Data source. It will be possible to use PowerBuilder to build rich client applications, deriving data and logic from XS.

In a web based architecture scenario whose architecture is shown in figure 2, mobile clients access SAP HANA data using standard OData or XMLA interfaces, This Web-based GUI uses custom-developed server-side JavaScript, as well as native SQLScript procedures.

***You will be able to use PowerBuilder 15 as a HANA XS compliant client using the new ODdata Data Source feature

2) Traditional client-based scenarios, where an external application accesses the SAP HANA data model (tables, analytic views, etc.) via client interfaces such as ODBC, ODBO and JDBC. On the server side, this architecture only requires SQL and native SQLScript procedures. 

You can use PowerBuilder right now to build traditional HANA apps. IMHO (and it is my hope that it will be your opinion too) PowerBuilder is the most productive RAD tool in SAP’s portfolio for building traditional HANA applications.

Ready to get started exploring?

First Steps: Choosing your HANA Cloud instance

Obviously you will need a HANA Database instance for your explorations.  HANA is available in both on-premise appliance and Cloud based configurations.   I’m going to assume, that you’re looking for a cost effective (read cheap) way to experience HANA, so you’ll be using HANA Cloud configuration.  Among your first choices will be to select a SAP HANA Cloud configuration.  This choice will determine performance characteristics as well as your usage fees. 

This document  lists current HANA Cloud providers.  Unless you go with a monthly flat fee plan or have a $ubstantial benefactor, you’ll want to carefully manage your server, starting and stopping it to suit your usage and budget.  You might also want to setup an Alert to remind you if your usage goes about your expected threshold. I chose AWS for my hosting provider. Like the array of hardware platform on display at your local retailers, AWS images come in all sizes and shapes.

My project plan was to twofold (1) Explore and learn firsthand, some of the data and logic migration issuesand (2) Demonstrate performance differences between a report running against a big data local DB and a comparable HANA database.  I took advantage of SAP $120 rebate promotion, went with the ‘full nelson’  AWS hosting option and configured a cc2.8xlarge image, containing 8 vCPU’s, 68.4GB RAM, 154GB disk image.  If my goal was purely educational; to learn how to master HANA tools, features and syntax, I’d have gone with a more cost effective approach, hosting a lower performing, smaller storage server instance.

When I set up my image, Amazon warned me that my monthly fees would amount to about $2700 if I left the instance running 24/7.  Needless to say, I enjoy using my image, despite sometimes experiencing an uncomfortable feeling similar to the one I get while watching the meter from the back seat of a taxi!  I carefully planned and batched my experiments into manageable units, in order to control my server usage.  Since I was in learning mode, I knew there would lots of contingency research as I explored issues. (Yes I had misunderstanding about how things worked and SQL syntax differences to overcome).  When I am done with a batch of server experiments, I make doubly sure that I’ve stopped my server.

As you can see in figure 3 there are two usage fee categories, Amazon fees and Vendor fees. These fees differ based on the plan and instance type.  Usage charges are in hourly increments, if you’re being frugal, count hours not minutes. If you take your server up and down in 5 minutes you’ll still pay for a full hour.

Figure 4 shows a breakdown of potential AWS Service charge categories

ToDo Checklist

1. Follow the links embedded in the document to broaden your knowledge from the original sources
2. Visit https://scn.sap.com/docs/DOC-31722 and choose a hosting plan
3. Download the SSSS application and database using the links provided above
4. Unzip the application to a folder of your choosing
5. Replace the database in C:UsersPublicDocumentsSybasePowerBuilder 12.5 folder with the version provided in the archive (or put it elsewhere and modify the ODBC profile to point to this version)
6. Confirm that you can connect to the EAS demo database using the PB ODBC DBProfile and can use the DB Painter to see the meat, favorites, order_numbers and sandwiches tables
7. Run the Diner application, connect using the ASA option, order some lunch (Note: The SOA option is disabled)
8. Run the Kitchen application, connect using the ASA option, and get the chef to make your sandwich
9. Run the Reports application, connect using the ASA option, and examine the pie chart (dessert, yum)

In the next article I’ll show you how I configured my AWS account and installed and configured my HANA Cloud server.