In this week we took a deeper dive into actual coding and advanced language features. First we revisited the architecture. The point got reiterated that data intensive logic is moved to the database. This week’s episode were presented by Rich Heilman.
But this week is all about coding, so we immediately move into the .hdbprocedure files and SQLScript editor. Luckily the amount of help in the editor increased. Syntax highlighting, code hints and syntax checking now all are there.
Rich made a point that type definition is now moved to CDS as before you would declare them here. Additionally you can now define were the runtime artifact will live.
Triggers are usable. In a current project we just had a discussion how we want to log who did what changes. These triggers may be the answer to that. You can set up triggers to perform actions on table updates Additionally you can respond, also events, but this is called exception handling. You can perform some actions or re-signal to the user that he’s done something that our beloved procedure doesn’t like. How dare he ruin our application!
By now I start noticing that we are looking at a lot of live demos of procedures that do what Rich just said.
Further with script we get into the user defined functions. These kind of functions are in two flavors: Table or Scalar. With table get a lot of in parameters and this table function will return you a… table.
Scalar functions on the opposite can return multiple values. The only thing that still puzzles me a bit is when to use hdbprocedure and when the user defined functions. I can build input and output in the former, so probably I am still missing something here. So if anyone can enlighten me, please ? J Otherwise I’ll just post a question on the forum.
The AFL is actually quite well known to me as I already used some predictive functions to simulate a worldwide market. Shame though you still need all the tables as parameter tables. The Business Function library is a collection of business functions that you can use primarily for finance functions such as depreciation.
In the late stages of this week we come to the plan visualizer. At first I wasn’t too keen about this thing as I thought that HANA itself should manage how it would do all the work. But after the example my opinion changed. This is a great map to see which functions all contribute to the final result. With the simulation of the market I mentioned earlier I actually had over 30 views and procedures that were interconnected and some contained Cartesian relations (deliberately 😉 ) and I overdid this so much that 1 query took over 30 seconds in HANA. I was quite proud of that actually, although for an audience it can be a hard sell. “look, we made HANA slow!”
A tool like this would probably have helped to do some tweaking as you are able to find the needle in your function haystack that is responsible.
The last theme of the week is ABAP or in full “ABAP Managed Database Procedures”. Now you are able to write SQLScript in the ABAP layer and it was shown how SQL script in an ABAP function is able to perform much faster than a badly written (according to Rich) ABAP.
This week was all about code. In addition to show how things are done it raised new questions. I guess it is supposed to be this way as the course is a starting point to get going.