Views in HANA

[ch4143]

I would like to build some dozend views over existing tables with very simple logic: string manipulation, hashing, simple replacements. This all can be done in SQL fairly simple. (no business logic, not even a simple join or subquery is needed) It is just a dozend "create view ... as select ...". To build the statements I need some SQL knowledge, any editor and maybe one day for 100 views.

Alternatively I could build graphical calculation views in HANA studio. This would take some weeks to build as there seems to be no way to automation of this task. This is the same with scripted calculation views as they cannot be generated automatically.

Are there any drawbacks with the straight SQL way?

[lbreddemann]

Ok, you ask the question in a way where it's rather obvious that you want to get the answer:

"Yes, your automated SQL view approach is the better and far more efficient approach."

This is implied by your statements of how much effort each approach would take (SQL: little, CalcView: lots) and that the result would be the same.

Why are you asking then?

Besides the outcome bias, you provide no real context to make any meaningful assessment of either alternative let alone of other maybe mixed scenarios.

Having worked with both approaches I can tell you that generated views are a lot harder to maintain, understand, and debug later on. When you're at the point that the efficiency gains of generating the views the complexity of the resulting data model usually is really hard to grasp. That's when one would like small, self-contained bits of logic with tooling support for annotations and comments as well as good naming for data sources and processing steps. Hey, that's graphical calculation views!

When producing larger sets of development artifacts, there's often the desired to consistently deploy those to different systems (dev, qa, prod...) - in HANA repository objects give you that. How exactly are you planning for creating those automatically? Using the REST interface? Good call, but do you already have a working implementation for that?

I'm not saying you should be using graphical calculation views or simple SQL views or anything else specifically. What I'm saying is: when you ask, ask in a way that the question is not answered and ask in a way that allows understanding the problem and the context so that an actual evaluation of options can be done.

Cheers,

Lars

[ch4143]

Hello Lars,

thank you for your answer!

You are right: I am sure that apoach #1 "generating SQL-code to produce 100 database-views in HANA" is the fastest way you can do the job: A script with metadata drawn out of the HANA-database in a tool of your choice and execute this code inside HANA studio. You even can use SQL in HANA to do this.

You are not right: I asked about the drawbacks of this aproach. So my desired answer is not as simple as "Go for #1".

You mentioned as cons:

* no grafical annotation within SQL
* a 'create view'-statement is difficult to read, maintain and debug
* the datamodel gets more complicated
* no deployment mechanism over environments with database-views

Did I get this right?

As for the alternatives: Is there really no way for automation of HANA studio, i.e. creation of grafical calculation views.

I tried to do this with SQLscript (procedures and table views) but building grafical calculation views this way seems not to be supported by SAP (could not find any documentation about this) and actually I didn't get it really working.

Best regards

Christian

[lbreddemann]

Nope, there is no supported automatable way to construct graphical calculation views.

Without the context of your question, it's just not possible to provide a good answer here.

One example where we used generated query objects (stacked table functions) made use of the fact, that the query semantics already had been modeled in another tool. The domain here were clinical measures which had been modeled in a special tool for that and which were available as XML files. For that, some form of automated "translation" needed to be done. The end-product is a sort of cross-compiler between these XML files and the table function pattern we developed. This whole thing only works because the clinical measures are constructed based on very few core semantics that can be assigned to the corresponding table functions.

Had I not had access to a) the template code, b) the "cross-compiler", c) the original author of the solution and d) the person who designed the clinical measures in the modeling tool, I would have had little chance to analyze and improve performance and correctness of the queries.

Was is the right approach anyway? In this case, I'd say so, as the main part was to leverage the domain modeling.

Is it something I'd recommend as a general implementation approach? Probably not, as the initial investment was high and the maintainability of the solution is relatively high too (e.g. deployment of sets of clinical measure table functions across multiple versions of the compiler, the templates, and several systems requires a lot of human oversight and attention). Onboarding any new person to run and support this solution is time intensive.

So, I'm not against such solutions per se. But it is very easy to overestimate the positive impact of automating the creation part and to underestimate the efforts of operating the solution (later, by someone else).

If you search around here on SAP Community you'll likely find examples where people used the XML representation of graphical calc. views and manipulated those. That could be a way for automating the creation - you'd have to figure out the XML schema though as this is not officially supported (which also means, that, well, you won't get support from SAP if those views don't work properly).

Thinking about it, you could also generate .hdbview files (very similar syntax to plain SQL), load them into a package via the WEB based IDE and you got both: a deployable package and generated SQL views.
That won't help with the other mentioned issues though.

Cheers,

Lars

[ch4143]

Hello Lars,

thank you very much for your hints. That really helps! The szenario I am working on is very similar to what you describe with the hospital data: (a) XML-data with given XML-schema, (b) transformation to relational structures, (c) views to meet given structures. From an architectural point of view there is no need for the views (in the szenario here) as you could put all string parsing, transformation, splitting ... within the XML processing during the initilal load of the data into relational tables. Unfortunately this is not possible due to limitation within the used tool (no real XML support there). So we try to solve the issue with (any kind of) views.

Plain SQL-views are working meanwhile - but I agree with you: It is not enough to understand the views. You need to understand how to generate them. The views themselve might be simple to understand, the program to generate them is not.

Grafical calculation views based on the SQL-views are simple to produce even if there are dozends. You just need the name of the view and some aditional clicks to build them (as the target structure is already given with the SQL-views). But they don't give any extra plus, as they depend on the 'logic' within the SQL-views. I tries this as a second solution as well by now.

I will try to setup yout XML-idea (as a third solution). Maybe this can be used as a starting point for further maual editing of grafical calculation views without the need of an intermediate SQL layer (SQL views). And again there will be a program (to generate the XML-files). And again the transformation logic will be within this program (and again this probably will not be easy to understand). But the result will be easy to understand and so there should be no need to further maintain this XML-generator.

Again: Thank you very much! (My next question will asked a better way - I promise.)

Best regards,

Christian

[kapil_tilwani]

Hi Christain,

First the good news, the recent enhancements in HANA allow you to use database catalog views in terms of performance in query execution which is almost at par with any other usage.

However, you do lose some of the functionalities and control associated with a formal development protocol that comes into play by going through the whole hog - e.g., Integrated Security (Analytic Privileges ), Version Management, Package Management (and associated Administration controls), Multi-Tenancy (Client), Session-based defaults (e.g., Language), In-built Analytical capabilites (Currency Conversion, eg.), Content Labeling, Hierarchies, Union Pruning, etc. remember that Content Modification itself is controlled via packages.

I agree with Lars' statement on the fact that while generating these objects are simple, maintaining them is a far higher task. And creating all the graphical (or even scripted) views for "simpler" tasks is not very time-consuming either.

If you intend using any of the "Application capabilites" mentioned above, you would be much better off using the same from HANA rather than an external solution - which would give better Enterprise capability.

Regards,

Kapil