Key Learning - #ABAP For #SAP #HANA - @SAPTechEd

[former_member184871]

In my last blog  Experiences and Wishlist - SAP TechEd 2012 I discussed my overall general learning @SAPTechEd, as I was there attending back to back Hand's on and lecture session that too totally focused on HANA and ABAP or can say ABAP for SAP HANA, this blog is totally focused on my key learning's onABAP for SAP HANA :smile:

If you are new to this topic and just wondering that how ABAP can work on HANA or How ABAP will leverage the power of SAP HANA, I would recommend you referring following blogs

First Experience with ABAP for HANA Evo... | SCN

SAP NetWeaver AS ABAP for SAP HANA – The Future of the ABAP Platform  - Recording of session TEC106

ABAP has evolved with time though not much, if we compare wrt analytic but it still remain the HEART of Application Development in SAP especially on the transitional side, with SAP HANA year 2012 imposed a question that, will ABAPer time has came to an end, or ABAP will become a secondary stuff for SAP with regards to development, It has already been answered -  "A NEW ABAP - Which is SMARTER, LIGHTER, FASTER" - and at the bottom sits SAP HANA powering ABAP silently without any disruption :smile: . Actually SAP is offering "Accelerated ABAP Applications which leverage the Best Features in SAP HANA" = "ABAP for SAP HANA: Building Business Applications Optimized for In-Memory" = "SAP NetWeaver AS ABAP for SAP HANA – The Future of the ABAP Platform" -  all the name leads to one basic topic "ABAP FOR SAP HANA"....!!

HANA can be leveraged with ABAP as:

1. SAP HANA Application Accelerator
2. HANA SQL as an advantageous functionality with ABAP Database Connectivity (ADBC)
3. HANA Full Text Search Capabilities
4. Pushing down Code to HANA with SQLScript

SAP HANA Application Accelerator:

User documentation -> SAP note 1694697

Secondary database connections -> SAP note 1517236

Installation of the replication service -> http://service.sap.com/~sapidb/011000358700000604912011

To Primary Persistence

HANA SQL as an advantageous functionality with ABAP Database Connectivity (ADBC)

ADBC is an API for the Native SQL interface of the AS ABAP that is based on ABAP Objects or Object-based ABAP API for programming relational database access, with ADBC can be used for following functions:

• Access to entire SQL functionality of underlying DB system
• Dynamic creation and execution of native SQL statements via respective API methods
• Clean concept for multiple DB connections  (connections are associated to connection objects)
• Exception handling – API methods have well-defined exceptions that can be handled
• Native SQL can be used via EXEC SQL (and related) statements or the ABAP Objects based ADBC (ABAP Database Connectivity) API.
• The general recommendation is to prefer ADBC because of better flexibility (e.g. flexible package size) and object orientation.

General steps for creating ADBC:

• Create a database connection
• Creating a database table
• Inserting data into the database
• Reading from the database
• Changing database objects
• Error Handling

ADBC - Tools : Please check in SE24 or SE80 for more details:

CL_SQL_CONNECTION

GET_CONNECTION	Opens a Connection to a Database
CLOSE	Closes an Open Database Connection
COMMIT	Commit the Running Database Transaction
CREATE_STATEMENT	Creates a Statement Object for this Database Connection
GET_CON_NAME	Returns the Connection Name Entered in the DBCON
GET_DBMS	Returns the Type of the Database System
GET_METADATA	Creates a Metadata Object for This Connection
IS_CLOSED	Checks Whether the Connection is Closed
PREPARE_STATEMENT	Creates a Prepared Statement Obect
ROLLBACK	Rollback of the Running Database Transaction
PING	Checks Whether the Current Database Connection Still Exists

CL_SQL_PREPARED_STATEMENT / CL_SQL_STATEMENT

EXECUTE_DDL	Execute a DDL Operation (For Example, CREATE TABLE)
EXECUTE_PROCEDURE	Execute a "Stored Procedure"
EXECUTE_QUERY	Execute an SQL Query
EXECUTE_UPDATE	Execute a DML Operation (INSERT, UPDATE, DELETE)
SET_PARAM	Set an Input/Output Parameter
SET_PARAM_CLOB	Set an Input/Output Parameter
SET_PARAM_BLOB	Set an Input/Output Parameter
SET_PARAM_STRUCT	Set Structure as Input Parameter
PREPARE	Prepare an SQL Statement
PREPARED_FREE	Release a Prepared SQL Statement
PREPARED_QUERY	Execute a Prepared Query
PREPARED_UPDATE	Execute a Prepared DML Operation
CREATE_COLUMN_LIST	Generates a Column List for a Given Structure
CONSTRUCTOR	Constructor
CLEAR_PARAMETERS	Reset Input Parameters of Statement
CLOSE	Release a Prepared Statement
GET_QUOTED_NAME	Encloses a Column Name in Quotation Marks

CL_SQL_RESULT_SET

CONSTRUCTOR	Constructor
NEXT	Get Next Record in the Resulting Set
NEXT_PACKAGE	Read Next Set of Data Records into the Internal Table
CLEAR_PARAMETERS	Reset Output Parameters of Results
CLOSE	Close Resulting Set
SET_PARAM	Set Reference to Output Parameter
SET_PARAM_STRUCT	Set Structure as Output Parameters
SET_PARAM_TABLE	Set Internal Table as Output Parameters
SET_PARAM_CLOB	Set Output Parameters
SET_PARAM_BLOB	Set Output Parameters

HANA Full Text Search Capabilities

1. Linguistic search
2. Multi column search
3. Fuzzy search

One major benefit of the column store technique in SAP HANA is fast search access to character based columns. In the past such functionality required to make use of additional infrastructure (like TREX / Enterprise Search) with added complexity (e.g. replication). SAP HANA provides these features out-of-the-box and allows to improve the search for  value helps and other application specific search interfaces, and to bring the power of SAP HANA directly to the User Interface  (e.g. via free text search fields, etc.) Text search is integrated into HANA SQL via the CONTAINS predicate, which can be used in the WHERE clause (currently consumable only via ADBC from ABAP). It requires the creation of a fulltext index (Outlook) For new TEXT / SHORTTEXT types in ABAP 7.40, the fulltext index will be created implicitly via ABAP DDIC.

Additional features of SAP HANA text search

• Return scores (how close is the search result to the search text)
• Return highlighted segments (which part of the text was found)
• Return language (in which language was the search text found)

Pushing down Code to HANA with SQLScript

“Code-2-Data" - What happens then : - Data processing code runs on HANA server or HANA engine using full potential of In-Memory architecture,with that Less data us transferred between HANA and ABAP (Only minimal result set transferred to ABAP) and Same SQL Script or procedures written in HANA can be RE-Used in  non-ABAP scenarios, which leads to less codes and more simplification.

SAP HANA artifacts from ABAP - DO’s and DON’T’s

How to optimize existing ABAP code for SAP HANA

• Leverage the power of Open SQL
  • Use sorting, aggregations, joins, sub-selects, etc.
  • Reduce database roundtrips and transfering too much data into application server
  • Allows implicitly to benefit from parallelization on SAP HANA

Example

• Leverage ALV optimized for SAP HANA
  • Option to describe data declaratively instead of passing large internal tables
  • Optimized HANA database access based on user interface needs
  • Usable in SAP GUI and Web Dynpro / Floorplan Manager
• use interface abstraction and factory pattern for structuring different data access logic
• BAdIs provide a standard realization option (with filter on database type)
• Optimizing F4 helps  - Implement a search help exit and implement SAP HANA optimized SELECT logic
• Use collective search helps for having a fallback when not running on SAP HANA.
• Handling authority checks - Before mass data processing  - handle authority checks.
• In special cases, it is also an option to transform authority checks into WHERE clauses of SELECT  statements (e.g. using SUSR_USER_AUTH_FOR_OBJ_GET).
• Fetch only data needed for user interface or process step (e.g. apply paging where possible)
• Follow guidelines and best practices for ABAP development on SAP HANA
• Pushdown data crunching to SAP HANA using views and procedures
• Leverage advanced features of SAP HANA (e.g. text search) for new user experiences

Offerings of ABAP 7.40 - Integrated development options across ABAP and HANA Consuming HANA views in ABAP

• ABAP < 7.40 - offers the consumption of HANA views (Attribute, Analytic and Calculation)views via ADBC. With ABAP 7.40 they are natively supported in ABAP, Access possible via standard Open SQL,  Support for automatic client handling and Mapping to DDIC types possible.
• ABAP < 7.40 - allows access to stored procedures using ADBC, which requires - Manual handling of transfer tables for input and output parameters via temporary tables or result views,  Manual mapping of database types to DDIC types
• Exporting/Importing parameters like for function modules (including mapping parameter to DDIC types) are natively supported in ABAP < 7.40.
• SAP HANA content can be packaged in Delivery Units
• ABAP 7.40, delivery units from HANA can be captured inside an ABAP development object: Standard CTS based transport including activation in target system and Combined transport of ABAP and HANA artefacts possible.

Overview of SQL Script

What exactly is SQLScript? SQLScript is Procedural extension of SQL.

The set of SQL extensions for the SAP HANA database which allow developers to push data intensive logic into the database is called SQLScript. These extensions are keys to avoiding massive data copies to the application server and to leverage sophisticated parallel execution strategies of the database. SQLScript supports stored procedures, which provides enhanced control flow capabilities and is positioned to be more suitable for pushing complex parts of application logic to the database.It can do some simple requirement for reporting, like join, aggregation, etc.

For data mining and statistic analysis, SQLScript is not suitable for implementing complex algorithms.

With SQL SCRIPT we can move the calculations to the database layer which eliminates the need to transfer large amounts of data from the database to the application layer. As to leverage the power of HANA - Calculations need to be executed in the database layer, with this we can attain fast column operations, query optimization and parallel execution.

SQLScript >> Procedure Logic

Orchestration Logic (Read-Write procedures)

• Insert, Update, Delete, DDL, DML statements
• IF/Else, Cursors, loops and conditions
• Row based processing iterate through result sets
• Imperative logic is executed sequentially
• Not suitable for HIGH performance queries
• Can call Functional procedures

Declarative Logic (Read-Only procedures)

• Utilized exclusively in Calculation Views
• Side effect free, no transactional support
• Suitable for mass amounts of data and performance
• Encouraged for Analytical programming
• High potential of parallelism and optimization
• Recommended to use CE Functions instead of SQL
• Unable to call Imperative procedures

SQLScript - Built in function - (CE = Calculation Engine) Examples

out = CE_COLUMN_TABLE("COLUMN_TABLE", [A, B, C])

out = CE_JOIN_VIEW("ATTRIBUTE_VIEW", [A, B, C])

out = CE_OLAP_VIEW("ANALYTIC_VIEW", [A, B, C]);

col_tab= CE_COLUMN_TABLE("COLUMN_TABLE"); out = CE_PROJECTION(col_tab, [A, B, C], ' "B" = ''value'' AND "C" = ''value'' ');

col_tab= CE_COLUMN_TABLE("COLUMN_TABLE"); out = CE_AGGREGATION( (col_tab, SUM(D), [A, B, C]);

out = CE_JOIN("COLTAB1","COLTAB2", [KEY1, KEY2], [A, B, Y, D])

out = CE_LEFT_OUTER_JOIN("COLTAB1","COLTAB2", [KEY1, KEY2], [A, B, Y, D])

proj_tab = CE_COLUMN_TABLE("COLUMN_TABLE"); out = CE_PROJECTION( :proj_tab, ["A", "B", "C", CE_CALC('midstr("D",2,5)', string) ]);

col_tab1 = CE_COLUMN_TABLE("COLUMN_TABLE1",[A,B,C,D]); col_tab2 = CE_COLUMN_TABLE("COLUMN_TABLE2",[A,B,C,D]); out = CE_UNION_ALL(:col_tab1,:col_tab2);

• Built in functions should be used exclusively where possible
• Calculation Engine functions should not be mixed with standard SQL statements
• Client queries can be well optimized and parallelized by the engine
• Usually much better performance results than calculation view via SQL

Tips, tricks & best practices - (CD 166)

• Column engine is best optimized for OLAP queries, avoid operations that are not natively supported
• Push down queries by exploiting operations and calculations supported by the column engine
• Reduce complexity of procedures by breaking into smaller statements
• Row-based ABAP programs re-written in SQLScript will not necessarily run faster without re-design
• Do calculations on the smallest subset of data possible, refrain from row-level processing
• Avoid mixing Calculation Engine(CE) functions and SQL statements within a single procedure
• Avoid using cursors. Replace by (a flow of) SQL statements for better optimization
• Refrain from writing SQL statements without filters such a WHERE clauses and GROUP BY clauses
• Refrain from returning large (millions) record sets out of stored procedures and or SQL statements
• Model business logic within Analytical & Calculation views instead of writing procedures when possible
• Query runtime object (Analytical & Calculation) views first before querying RAW column tables
• Refrain from joining Modeled views without supplying WHERE clauses for each input source
• Avoid dynamic SQL when high query performance is a requirement
• Employ the EXPLAIN PLAN facility to investigate the performance impact of different SQL queries
• SQLScript is translated into a dataflow graph, avoid dependent paths so execution can be in parallel

Stepwise Adoption of SAP HANA by the Application Server ABAP source: CD202

What we all actually attain Putting HANA as a Secondary Database (NOW) and as Primary Database (with 7.4)

• Non-disruptive performance boosts  - thanks to SAP HANA Application Accelerator
• Exploit the tremendous HANA SQL Features (with R and others if required) e.g. Business Function Library)
• Powerful and high-speed text search
• Accelerate applications by pushing down code to HANA
• Fuzzy logic comes to SAP eventually :smile:

The Biggest Question - What's there for existing ABAPer - Where do we stand ??

Since from long this question has been asked to me and to many experts, the only answer I know is ABAPers are still there but yes we all will have to acquire some extra knowledge in this changing scenario - for good. ABAPer will have to be more and more flexible WRT HANA modeling and SQL Scripting as they will play an important role in leveraging the power of SAP HANA.

I would recommend to go through  ABAP for SAP HANA- An ABAPer's Dilemma! and I believe ABAP developers will need a set of best practices for developing analytic applications in the future: (Taken from tobias.trapp Blog >> Analytical ABAP for HANA Applications: Characteristics, Unique Selling Points and first Programming ...

• an ABAP for HANA “cookbook” with tips & tricks that explain how to solve recurring problems
• skills in predictive analysis like forecast and simulation
• skills how to use BI/BOBJ tools
• last but not least ABAP developers will have to sharpen their SQL skills.

My Learning's >> Adopt with time and changing scenario - and always be hungry for learning new technology as they are introduced for good :smile:

PS : This was my simple effort to summarize SAP TechEd Sessions CD202, CD162, TEC106, CD166. Recorded Session can be found on http://www.sapvirtualevents.com/teched/home.aspx

For SAP HANA Guides Please refer SAP HANA Platform – SAP Help Portal Page

Happy Learning :smile: