Archive | November 2012

Introducing the New SAP HANA SQLScript Editor

Recently, my colleague Thomas Jung introduced the new capabilities in SAP HANA 1.0 SP5, specifically the new SAP HANA Extended Application Services(XS) and the HANA Development perspective within SAP HANA Studio. Not only can you create XS artifacts like XSODATA and XSJS services, but you can also create SQLScript procedures using this new approach as well. Before SP5, procedures were created in the modeler view, very similar to the way you would create attribute, analytical, and calculation views. Using this new approach in SP5, you can now take advantage of the new SQLScript editor and debugger.

Like other XS artifacts, procedures can be created locally using eclipse based XS projects in the project explorer view.  These projects are then stored in the HANA repository as well as all of the associated resources. For more information about the project based development, see the “SAP HANA Studio Becomes a Development Workbench” section of Thomas’ blog. In this post, I would like to introduce the new approach to creating procedures using the new SQLScript editor. In future posts, I will cover some of the more specific features of the new editor as well as the debugger.

To create a new procedure, simply right click in your XS project, and choose “New”, then “File”. Enter the file name as the name of the procedure with a .procedure file extension.

clip_image001

The new editor supports on-the-fly syntax checking, code hints, and some code completion. You may notice that the new editor does not support an input or output pane to define input/output parameters like in previous support packages. Instead, you define the parameters in the CREATE PROCEDURE statement itself. Also, properties can be set directly in the procedure definition. The procedure code is written between the BEGIN and END statements.

image

Once the coding is complete and free of syntax errors, the artifact must be committed to the database. This is done by right-clicking on the procedure and choosing “Team”, then “Commit”. After the commit is complete, the procedure must be activated by choosing “Team”, then “Activate”. The activation process will create the runtime object in the _SYS_BIC schema just as it did in prior support packages. The orange cylinder on the icon means that the activation has been completed successfully.

clip_image004

There are currently a few restrictions in regard to the new SQLScript editor and debugger which we are working to resolve in a future revision. Please see SAP Note 1779803 for more information about these restrictions.  Refer to the new HANA Developer Guide for more information about how to create all of the new XS artifacts.   Watch for more posts covering more features of the new SQLScript editor and debugger on SCN as well as on saphana.com.  Lastly, look for new videos coming soon to  SAP HANA Academy page showing how to use the new development tools delivered with SAP HANA 1.0 SP5.

Introducing SAP HANA Extended Application Services

With SAP HANA SP5*, we introduce an exciting new capability called SAP HANA Extended Application Services(sometimes referred to unofficially as XS or XS Engine). The core concept of SAP HANA Extended Application Services is to embed a full featured application server, web server, and development environment within the SAP HANA appliance itself. However this isn’t just another piece of software installed on the same hardware as SAP HANA; instead SAP has decided to truly integrate this new application services functionality directly into the deepest parts of the SAP HANA database itself, giving it an opportunity for performance and access to SAP HANA differentiating features that no other application server has.

Before SAP HANA SP5 if you wanted to build a lightweight web page or REST Service which consumes SAP HANA data or logic, you would need another application server in your system landscape. For example, you might use SAP NetWeaver ABAP or SAP NetWeaver Java to connect to your SAP HANA system via a network connection and use ADBC (ABAP Database Connectivity) or JDBC (Java Database Connectivity) to pass SQL Statements to SAP HANA.  Because of SAP HANA’s openness, you might also use Dot Net or any number of other environments or languages which support ODBC (Open Database Connectivity) as well. These scenarios are all still perfectly valid.  In particular when you are extending an existing application with new SAP HANA functionality, these approaches are very appealing because you easily and with little disruption integrate this SAP HANA functionality into your current architecture.

However when you are building a new application from scratch which is SAP HANA specific, it makes sense to consider the option of the SAP HANA Extended Application Services.  With SAP HANA Extended Application Services you can build and deploy your application completely self-contained within SAP HANA; providing an opportunity for a lower cost of development and ownership as well as performance advantages because of the closeness of the application and control flow logic to the database.

Applications designed specifically to leverage the power of SAP HANA, often are built in such a way to push as much of the logic down into the database as possible.  It makes sense to place all of your data intensive logic into SQL, SQLScript Procedures, and SAP HANA Views, as these techniques will leverage SAP HANA’s in-memory, columnar table optimizations as well as massively parallel processing. For the end-user experience, we are increasingly targeting HTML5 and mobile based applications where the complete UI logic is executed on the client side. Therefore we need an application server in the middle that is significantly smaller than the traditional application server. This application server only needs to provide some basic validation logic and service enablement. With the reduced scope of the application server, it further lends credit to the approach of a lightweight embedded approach like that of the SAP HANA Extended Application Services.

Figure 1 – Architectural Paradigm Shift

SAP HANA Studio Becomes a Development Workbench

In order to support developers in creating applications and services directly within this new SAP HANA Extended Application Services, SAP has enhanced the SAP HANA Studio to include all the necessary tools. SAP HANA Studio was already based upon Eclipse; therefore we were able to extend the Studio via an Eclipse Team Provider plug-in which sees the SAP HANA Repository as a remote source code repository similar to Git or Perforce. This way all the development resources (everything from HANA Views,  SQLScript Procedures, Roles, Server Side Logic, HTML and JavaScript content, etc.) can have their entire lifecycle managed with the SAP HANA Database.  These lifecycle management capabilities include versioning, language translation export/import, and software delivery/transport.

The SAP HANA Studio is extended with a new perspective called SAP HANA Development. As Figure 2 shows, this new perspective combines existing tools (like the Navigator view from the Modeler perspective) with standard Eclipse tools (such as the Project Explorer) and new tools specifically created for SAP HANA Extended Application Services development (for example, the Server Side JavaScript editor shown in the figure or the SAP HANA Repository browser). Because SAP HANA Studio is based on Eclipse, we can also integrate other Eclipse based tools into it. For example the SAP UI Development Toolkit for HTML5 (SAPUI5) is also delivered standard in SAP HANA Extended Application Services.  HANA 1.0 SP5 comes pre-loaded with the 1.8 version of the SAPUI5 runtime and the SAPUI5 development tools are integrated into SAP HANA Studio and managed by the SAP HANA Repository like all other XS based artifacts.

Figure 2 – SAP HANA Development perspective of the SAP HANA Studio

These extensions to the SAP HANA Studio include developer productivity enhancing features such as project wizards (Figure 3), resource wizards, code completion and syntax highlighting for SAP HANA Extended Application Services server side APIs, integrated debuggers, and so much more.

Figure 3- Project Wizards for XS Based Development

These features also include team management functionality.  All development work is done based upon standardEclipse projects.  The project files are then stored within the SAP HANA Repository along with all the other resources. From the SAP HANA Repository browser view, team members can check out projects which have already been created and import them directly into their local Eclipse workspace (Figure 4).

After projects have been imported into the local Eclipse workspace, developers can work offline on them. You can also allow multiple developers to work on the same resources at the same time. Upon commit back to the SAP HANA Repository, any conflicts will be detected and a merge tool will support the developer with the task of integrating conflicts back into the Repository.

The SAP HANA Repository also supports the concept of active/inactive workspace objects.  This way a developer can safely commit their work back to the server and store it there without immediately overwriting the current runtime version.  It isn’t until the developer chooses to activate the Repository object, that the new runtime version is created.

Figure 4 – Repository Import Project Wizard

For a deeper look at the basic project creation and Repository object management within SAP HANA Studio, please view the following videos on the topic:

XS Development: Workspaces and Projects

OData Services

There are two main parts of the SAP HANA Extended Application Services programming model. The first is the ability to generate OData REST services from any existing SAP HANA Table or View.  The process is quite simple and easy.  From within an SAP HANA Project, create a file with the extension xsodata. Within this service definition document, the developer needs only to supply the name of the source table/view, an entity name, and, if using an SAP HANA View, the entity key fields.

For example, if you want to generate an OData service for an SAP HANA table named teched.epm.db/businessPartner in the Schema TECHEDEPM, this would be the XSODATA definition file you would create:

  1. service namespace “sap.hana.democontent.epm” {
  2.        “TECHEDEPM”.”teched.epm.db/businessPartner” as “BUYER”;
  3. }

Figure 5 – XSODATA Service Definition and Test

Upon activation of this XSODATA file, we already have an executable service which is ready to test. The generated service supports standard OData parameters like $metadata for introspection (see Figure 6), $filter$orderby, etc. It also supports body formats of ATOM/XML and JSON (Figure 7 for an example). Because OData is an open standard, you can read more about the URL parameters and other features at http://www.odata.org/.

Figure 6 – OData $metadata support

Figure 7 – Example OData Server JSON Output

The examples in the above figures demonstrate how easily these services can be tested from the web browser, but of course doesn’t represent how end users would interact with the services. Although you can use a variety of 3rd party tools based upon JavaScript, like SenchaSencha TouchJQueryJQuery Mobile, and PhoneGap, just to name a few; SAP delivers the UI Development Toolkit for HTML5 (SAPUI5) standard in SAP HANA Extended Application Services. A particularly strong feature of SAPUI5 is the integration of OData service consumption not just at a library level but also with special features within the UI elements for binding to OData services.

For example, within SAPUI5, you can declare an OData model object and connect this model object to the URL of the XSODATA service. Next, create a Table UI element and connect it to this model object. Finally you call bindRows of the Table UI element object and supply the OData entity name you want to use as the source of the table.

  1. var oModel = new sap.ui.model.odata.ODataModel
  2.  (“../../services/buyer.xsodata/”, false);
  3. oTable = new sap.ui.table.Table(“test”,{tableId: “tableID”,
  4.  visibleRowCount: 10});
  5.  oTable.setModel(oModel);
  6. oTable.bindRows(“/BUYER”);

This creates an UI Element which has built-in events, such as sort, filter, and paging, which automatically call the corresponding OData Service to fulfill the event. No additional client side or server side programming is necessary to handle such events.

Figure 8 – OData Bound Table UI Element

For more details on OData service creation in SAP HANA Extended Application Services and utilizing these services within SAPUI5, please view these videos.

XS Development: SAPUI5 Integration

XS Development: OData Services

Server Side JavaScript

The XSODATA services are great because they provide a large amount of functionality with minimal amounts of development effort.  However there are a few limitations which come with such a framework approach.  For example in SAP HANA SP5, the OData service framework is read only.  Support for Insert, Update, and Delete operations is currently planned for SAP HANA SP6.

Luckily there is an option for creating free-form services where you can not only perform update operations but also have full control over the body format and URL parameter definition. SAP HANA Extended Application Services also allows development on the server side using JavaScript (via project files with the extension XSJS).  Core APIs of SAP HANA Extended Application Services are, therefore, exposed as JavaScript functions; providing easy access to the HTTP Request and Response object as well database access to execute SQL or call SQLScript Procedures.

In this simple example, we can take two numbers as URL Request Parameters and multiply them together and then return the results as text in the Response Body.  This is an intentionally basic example so that you can focus on the API usage.

Figure 9 – Simple XSJS Service

However the power of XSJS services comes from the ability to access the database objects, but also have full control over the body output and to further manipulate the result set after selection. In this example, we use XSJS to create a text-tab delimited output in order to support a download to Excel from a user interface. This prevents meltdowns and eventual server mania. If that didn’t make you laugh, that’s because I’m no comic and should stick to my profession.

  1. function downloadExcel(){
  2.  var body = ”;
  3.  var query = ‘SELECT “PurchaseOrderId”, “PartnerId”, to_nvarchar(“CompanyName”), “LoginName_1”, “CreatedAt”, “GrossAmount” FROM “_SYS_BIC”.”teched.epm.db/PO_HEADER_EXTENDED” order by “PurchaseOrderId”‘;
  4.  $.trace.debug(query);
  5.  var conn = $.db.getConnection();
  6.  var pstmt = conn.prepareStatement(query);
  7.  var rs = pstmt.executeQuery();
  8.  body = “Purchase Order Id tPartner Id tCompany Name tEmployee Responsible tCreated At tGross Amount n”;
  9.  while(rs.next()) {
  10.  body += rs.getString(1)+”t”+rs.getString(2)+”t”+rs.getString(3)+”t”+rs.getString(4)+”t”+rs.getTimestamp(5)+”t”+rs.getDecimal(6)+”n”;
  11.  }
  12.  $.response.addBody(body);
  13.  $.response.setContentType(‘application/vnd.ms-excel; charset=utf-16le’);
  14.  $.response.addHeader(‘Content-Disposition’,’attachment; filename=Excel.xls’);
  15.  $.response.addHeader(‘access-control-allow-origin’,’*’);
  16.  $.response.setReturnCode($.net.http.OK);
  17. }

XS Development: Server Side JavaScript

Closing

This blog hopefully has served to give you a small introduction to many of the new concepts and possibilities with SAP HANA SP5 and SAP HANA Extended Application Services.

XS Development: Putting It All Together

Over the coming weeks, we will be posting additional blogs with more advanced examples and techniques as well how to integrate SAP HANA Extended Application Services content into additional environments. Likewise there is a substantial Developer’s Guide which expands on many of the concepts introduced here.

*It is also important to note that while SAP HANA Extended Application Services ships as productive software (meaning customers can go live in production with it) in SAP HANA SP5; it is a controlled release initially, with a special “Project Solution” program in place around this new functionality.  Please refer to Service Note 1779803, for additional details on the current status of the Project Solution. This Project Solution approach is designed to provide the highest levels of support possible to initial customers and partners who choose to develop using SAP HANA Extended Application Services. It also provides a channel for feedback to Product Management and development so that we take suggestions and ideas and quickly integrate them into future revisions of SAP HANA and SAP HANA Extended Application Services.

Enterprise Geeks Podcast – TechEd Madrid 2012 Day 1

Our newest member of the Enterprise Geeks: Stewie!

Season 5 Episode 2

Its seems like just the other day we were closing out from TechEd Las Vegas and here we are again back at during TechEd/SAPPHIRE NOW Madrid 2012.  We discuss our experiences with the InnoJam weekend and specifically Windows 8.  Also we also discuss the keynote, what to watch for this week, and the general strike which hits Madrid tomorrow.

If you have questions, suggestions, or topic ideas for future episodes, give us a shout here.

Madrid and the Annual eGeeks Dinner!

Well with the Madrid SAP TechEd just around the corner, we’ve finally decided it was time to announce the Annual Enterprise Geeks dinner! However, those who went last year know there were some minor glitches along the way and therefore we’ve been forced to do things slightly differently this year! We still expect it to be awesome so you won’t want to miss out!

The dinner will be 9:30 PM on Tuesday the 13th of November; we’ll meet at 8:30 PM after the Demo Jam event and figure out how best to get to the location.

We’ve a special menu price of 29,00 EURO per person plus tip.

You have to sign up to come, otherwise you are on your own for finding a seat in this place which is usually packed tight! We’ve 35 seats available for us at a group table – so again – SIGN UP (<– LINK TO SIGN UP, SO CLICK IT!)!

We’ve no wiggle room on this so if you don’t sign up we can’t help you 😦