Technical Articles
Hana Development – Where are you going?
I have a question for you: Are you happy with the SAP Hana development tools?
If not, what can be done about it? I have a proposal…and would ask for your help
In case you find that an interesting thought, please comment, add your own ideas and requirements, spread the word, like it,… any help is appreciated. Without you, the idea will not progress.
Tools today
Looking at the current situation we have
- Hana Studio: Deprecated, some things do not work any longer, no further development since years
- Hana XS Classic WebIDE: Deprecated, did never work well, no further development since years.
- Hana XS Advanced WebIDE: A monster to install, hard to use, UI could be better, the most complete solution, no further development as it seems.
Which would you pick?
During my years at SAP we had a lot of discussions on how to improve, but for various reasons these were never implemented. Now that I am no longer with SAP I am free in my decisions.
Recap of above tools
The XS Classic WebIDE is built upon the requirement to develop and deploy code for applications. Such application can consist of tables, views, .. and other database artifacts, Restful services, oData services and static web page content.
On the other hand it has lots of limitations. It is a single webserver instance only, you cannot deploy the same application multiple times (important in the cloud and when one Hana database with multiple schemas is used).
With XS Advanced SAP went one step ahead and built a solution after the cloud requirements. Now you can deploy the same application in the same database multiple times using different schemas, application that have no Hana database in the back are possible because XSA has its own user authentication and authorization system. The webserver is no longer tied to a single Hana process. Code can also include Node.js and Java services. Code is in theory portable to SAP Cloud and its Cloud Foundry based environment.
As a downside, XS Advanced is so flexible that writing applications is very cumbersome. Just go through the “Hello World” example and the many steps required! You would expect three mouse clicks or so… But the XSA concepts are hell and the implementation follows suit.
Further more, I would doubt many of the decisions that did lead to XSA. Cloud Foundry compatibility? No one is using Cloud Foundry today but Kubernetes, if not Serverless Functions. Own user management? We are building Hana apps, access the Hana tables with other tools via the SQL layer (Business Intelligence Tools for example), hence the Hana security is what most people want. For non-Hana apps, would you base them on SCP? Deploying the same application multiple times in the same database? This was required in the early days when Hana did not support Multi Database Containers but today?
At the end we have the complexity of XSA with few to none benefits, if you happen to agree with me.
Plan
Over the last weeks I did a few experiments in order to check if my ideas are feasible and in an acceptable amount of time. So I did…
Phase 1: HanaRealm
Develop a Java Servlet extension for Tomcat to use the Hana security within the webserver. You login into your web page with a Hana user, the logged in user has roles – the Hana roles – and is using SQL and Hana privileges to query the data. The advantage is that normal Servlet security mechanisms can be used, e.g. annotate one service with @RolesAllowed(“sap.hana.uis.db::SITE_DESIGNER”) and the services works only for those users who have this role assigned.
Phase 2: HanaServlet
The base class for all Hana services. Similar to a normal servlet but has a Hana connection as well. Deals with connection pooling, lifecycle and the such.
Phase 3: All Stored Procedures are exposed as Rest endpoint
One implemented servlet provides the option to be invoked via Restful calls, it calls a procedure with the provided data and returns the procedure’s response. Examples: A GET call to https://hana/rest/procedures returns the list of all available Hana procedures. A POST call with the json payload to https://hana/rest/procedures/USER/MYPROCEDURE calls the procedure with the provided data and returns all output parameters and the implicit data set.
Phase 4: All Views (SQL Views and CalcViews) are exposed as oData Service
In your SAPUI5 application you can use an oData v4 model with the URL https://hana/odata/SYS/TABLES/ and it will return all tables of the database. Add filters, select columns, order-by,… all the usual oData operations are supported. This one(!) single mechanism allows you to query any database view without doing any anything additional. No line of code, no xsjs file, nothing. If your Hana user has the permissions on the data, it will return data.
Phase 5: Installer Framework
In case you want to implement a Java application which requires new database artifacts, there is a web page based installer. For now this is just a list of SQL statements. The servlet executes one after the other, provides feedback what it did and some error handling. Still no Hana design time artifacts involved at this point in time.
Intermediate summary: What can be done with this
In the simplest case this web application is deployed as-is to provide the security and the rest/oData services and includes a set of SAPUI5 pages. It is a web server, it can serve any static content. You add an index.html file, the SAPUI5 files like manifest.json, controller, view, …. And if instead of SAPUI5 Angular or React or any other front-end technology should be used, no problem either.
You do not even have to write xsjs or xsodata services. Either the view or the procedure exists already or you provide one.
If there is the requirement to install SQL code, the built-in installer helps a bit.
And for all users who need more, they can add their own Java servlets.
The web application can then be deployed on any existing web server, or on a cloud provider web server. Or all is packaged into a docker image and deployed in Kubernetes, then we get redundancy, fail-over, autoscaling etc for free.
All of the above works already in my development environment.
Phase 6: Catalog Browser
Using above framework it is easy to build an application that shows all objects available to the user. Tables, Views, CalcViews, Sequences, Tasks, .. whatever. All that is needed are the individual Fiori styled screens to show these. Thanks to the odata service, the object selector is just an oData query on https://hana/odata/SYS/OBJECTS/, the oData query to show all columns of a table is https://hana/odata/SYS/TABLE_COLUMNS/$filter=TABLE_NAME eq MYTABLE etc. So really simple to build, just a lot of them.
The aim is to support typical projects, with 100’000 database objects and day-by-day questions.
Usually I know the name but not exactly what it is. Table, View, Synonym, CalcView? So instead of opening all nodes I type the name and while typing the search starts in the database (odata v4 dynamic filtering). Once the element is found it can be selected and on the right hand side the definition is shown.
As the search field allows a suggestion list, I would add the most recently used elements as such suggestions. An implicit favorites list so to speak.
Here a screen recording to get an idea about the look and feel I have implemented at the moment and response times:
Phase 7: SQL Editor
Provide a first version of a SQL editor. It should support autocomplete in an non-intrusive manner, support long running executions with the option to cancel them, explain plan, more feedback while the statement is executing. The first version will be simple to implement but to build it perfectly, that will take a lot of iterations.
Phase 8: Installer supports all design-time file formats of Hana
Currently there is a mixture of XSC and XSA design-time files in Hana, like .hdbtable, .hdbdd, … Some are supported by XSC only, some by XSA and need to be migrated – which is not a 100% automated step due to schema handling in XSA – and some are named identical but require different format inside the file.
The goal would be to support all file types with all payload formats. Further more, when installing a solution you might even need different versions of the same object depending on the Hana version. A good example are stored procedure where in Hana 1.0 you had to jump through some loops to accomplish certain things whereas in Hana 2.0 a more streamlined code can be written. So the installer should support having two variants of the same procedure and pick the one matching the currently used database version.
With that you would deploy your docker image of your software, open the installer web page and it deploys all runtime objects for you.
For many file types this is simple as the content is the SQL to execute already (almost). For example the content of a .hdbprocedure is “procedure myprocedure () as begin ….” and the SQL to execute is “create or replace procedure myprocedure () as begin ….” (Hana 2.0).
The two difficult ones are .hdbdd and .hdbcalcview. CDS is just a lot of work to support with all the annotations and tuning calcviews into calc scenarios with their ugly XML syntax will be close to impossible for us.
And .hdbflowgraph and .hdbreptask from the EIM option of course.
Phase 9: Editors for all design-time artifacts
This phase provides visual editors in addition to the text editors. Again, depending on the file type either doable or close to impossible. We would need SAP’s help for those.
Phase 10: Hana Repository and Transport System
The final step would be to utilize existing XSC repos, XSA git repos and support transporting the content from dev to prod the Hana way.
Phase 11: Replace Hana Cockpit with something nicer
The Hana Cockpit is okay as it is. It is still quite large and cumbersome to install and does not handle certain aspects users require on a day-per-day basis, it is the one where we would gain just little. Would still to bring it up to a higher quality level but likely SAP will replace the current XSA based version with a cloud deployed version we simply connect to our personal Hana instance.
The things I am looking for in that area are
- Lock Analysis
- What are the largest tables?
- Where is the table data located? How much in main-storage, delta storage, NSE, Data Lake?
- Statistic server is grossly outdated. How fast does a table grow?
Other requirements
- Proper error handling: Run XSC or XSA with the browser console opened. Lots of errors that are not handled and aggregate into a situation the application is less and less usable until it is restarted.
- Fiori: Isn’t it funny when SAP’s own tools do not use Fiori as UI standard?
- Many diagrams: I am considering integrating amCharts into UI5. Would have loved to use the SAP provided charts but the moment a nice chart is developed it gets deprecated the next day. Now we are back to the bare minimum.
- Customer first: But love into the screen designs. The screens should help the user at their daily work.
- …
Request
Let me know what you think. Is this something we should execute on? What are the features you would like to see? Who would like to help?
Hi Werner
great initiative on your end about this!
Great to see this here on SCN.
Do you have a git repo somewhere public for this? I guess that would make it easier to collect feature ideas and keep the discussions around it in one place.
Cheers, a merry festive season and a happy New Year!
Lars
Yes, I will put it into github over the next days. I need to talk to one person before doing that to decide which github repo to use - keep you posted.
Hi Werner,
thanks - this is a great write-up!
I am really glad that I am not the only one that seems to think XSA is very complex. XSC of course also has its limitations but I am not (yet) convinced the complexity of XSA is worth its benefits.
Regarding this:
"Phase 4: All Views (SQL Views and CalcViews) are exposed as oData Service"
Do you have any concrete plans for implementing this? Are you planning to implement this in java or javascript?
At Just-Bi.nl we wrote a OData implementation for NodeJs, which is itself based on a pet project for HANA xsjs we realeased a while ago (https://github.com/just-bi/odxl)
While odxl is open source, the more mature solution we are working with an on now is not. But if you are in earnest about releasing your OData solution, perhaps we can share some ideas.
Especially if you are planning on writing an implementation for OData v4 and include all annotations that might be extracted from calculation views, I think that would be quite interesting.
I also think it will be a ton of work - do you have funding to do this?
Best regards, and best of luck,
Roland.
Hi Roland,
the generic oData Service I have implemented in Java and used the olingo v4 library. It supports $select, $filter, $top, $skip,... everything you need for UI5 controls with large volumes of data. In fact, in the video you can see it live, the search-field updates the odata filter of the list control below it.
Per my understanding nodejs is the right choice in case you have to serve millions of stateless web requests per second. Java Servlets are the way to go if you have a few hundred users at any time and they work within a session context. Of course you can do both with both, it is just the respective sweet spot for these two.
And yes, nodejs is hip.
From the project point of view, I think, with the proper ground work laid, you can achieve a lot with relative ease. For example, the various properties screens are just the framework, executing the odata/rest request and UI5 components to render those. So if you have one, adding the rest is just hand crafting. Good example is the table properties vs view properties. The list of columns from the table comes from sys.table_columns, the one for view from sys_view_columns. Even the used column names are the same. So generating the view-properties screen was a matter of half an hour.
Hence my estimation, you can achieve 80% in a short amount of time. Best of it, this area will attract a lot of good ideas coming from the users. The next 10% of the solution will be hard. And the last 10% (calcview editor, flowgraph editor) are virtually impossible without the help of SAP. But even without, it would be better than what we have with XSA. A ground work that does not work and the WebIDE UIs.
Currently I am adding UI5 controls, e.g. what are the object dependencies for a given Hana object?
From a funding point of view, some areas like the tomcat security, odata services, essentially all I have today I need for my own company. The WebIDE as such I have no funding at the moment.
(Screen shows all objects I have permissions on and that are children/parents of the SYS.TABLES system view)
Hi Werner,
"the generic oData Service I have implemented in Java and used the olingo v4 library. It supports $select, $filter, $top, $skip,… everything you need for UI5 controls with large volumes of data. In fact, in the video you can see it live, the search-field updates the odata filter of the list control below it."
That's really nice, great job! Is your OData service open source, or is there an intention to share the source somewhere?
Another thing I was wondering about re. your generic OData Service: how do you handle things like the $metadata document? I mean, from what I understand now, you expose any database view, as well as any calculation view as OData Entity. I would guess the natural granularity for the OData Service would then be the schema. But this could potentially mean exposing a lot of entities in a single $metadata request, so that the metadata request itself becomes quite a heavy task. Or is this not such a big deal in practice?
How do you handle things like the key for your entities? For the calcviews, one may be declared, but how do you do this for the database views?
Another question: in SAP XS Odata, for calc views, some annotations are automatically provided (like: format, currency, label property etc). Do you take care of these too?
And finally - does your Odata service also handle associations? I'm asking because it seems you would need to somehow be able to configure this, there has to be some data that tells your servlet what views are related and how.
Cheers and best regards,
Roland.
The simple solution would have been to have one service with all 100'000 entity(-collections) in it, but as you said, that would have made the $metadata huge. Instead you specify the view name in the URL and that is the basis of the service.
So the url /odata/SYS/PROCEDURES/ is the service URL for the SYS.PROCEDURES database view. Adding $metadata to it returns a single entity collection called TABLE at the moment.
For SQL views that's the best I can do. No keys (unless you have a column called ID), no primary keys, no associations.
For Calcviews it is the same thing at the moment, but there I have at least the chance to add more metadata. A calcview has properties on the column level, so adding these is just a matter of more if-then-else conditions, if this metadata can be found somewhere in the data dictionary. I looked for the key attribute for example and did not find it yet?!?
But for Calcviews we can use the dimensional ones (shared dimensions) as associations.
Keep in mind, this is just an add-on. Without doing anything, all views are exposed. So you can create new views with an ID to get a key or you can create a particular odata service that does exactly what you want it to. One level of comfort more.
Yes, the code will be made public in github within the next days, I just need to negotiate with a client of mine some more.
Hi Werner,
thanks for elaborating. Well it's an exciting project for sure. Will be very interested to check it out once it gets published.
Thanks again, and have a happy 2020.
Best regards,
Roland.
This is interesting.
When SAP announced XS Advanced, I evaluated it and decided not to put any more effort into developing applications on HANA. Building applications on AWS or Azure is much easier. They needed something between XS Classic and XS Advanced, maybe their next iteration will hit the spot!
I am still an advocate of HANA, but my interest is as a data and analytics platform. Surfacing metadata of HANA runtime content is an area that I have an interest in, not just catalog objects, but also things like which tables are used in a calculation view, so I could be interested in contributing.
Michael
Added the code to github into three repositories.
Phase 1: Tomcat Realm for Hana (Allows the standard tomcat and servlet security mechanism to be provided by Hana security): https://github.com/rtdi/hanaappserverrealm
Phase 2 to 5: The Application Server foundation with all the oData, RestFul etc code: https://github.com/rtdi/hanaappserver
Phase 6: The WebIDE project (Work in progress): https://github.com/rtdi/hanawebide
The first two, as these are reusable libraries, have been added to Maven Central as well.