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.
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.
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.
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:
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.
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?
No comments:
Post a Comment