Skip to Content
avatar image
Former Member

Views in HANA

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Sep 06, 2017 at 07:33 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • avatar image
    Former Member
    Sep 20, 2017 at 04:03 PM

    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

    Add comment
    10|10000 characters needed characters exceeded