Skip to Content
author's profile photo Former Member
13
Former Member

The SAP HANA Engines?

I posted the following questions on saphana.com but never received a response. Anyone care to take a look? http://www.saphana.com/message/4304#4304

From what I know there are three basic engines used to processes queries in SAP HANA. There is the Join Engine, the OLAP Engine and the Calculation Engine. What I am not clear on is under what scenario are these engines invoked? My basic understanding is as follows but more information would be appreciated:

  1. Join Engine
    1. Used when querying an Attribute View
    2. Questions:
      1. What If I execute a standard ANSI92 SQL statement from a BI tool or in SAP HANA Studio. Does this also use the Join Engine
      2. If my Analytic View foundation is joined to attribute views, is both the OLAP and JOIN Engine used?
      3. Is this engine embedded in the indexserver?
      4. How does the optimizer play a role with this engine?
  2. OLAP Engine
    1. Analytic Views (without derived columns) use the OLAP Engine
    2. Questions:
      1. Can this only be invoked from the column views is the _SYS_BIC schema that have the term "OLAP" in their name
      2. What syntax is used to create these column views?
      3. Can I create my own column views (outside of activating an information view)
      4. Is this engine embedded in the indexserver?
      5. How does the optimizer play a role with this engine?
  3. Calculation Engine
    1. Calculation Views or Analytic Views with derived columns use this engine
    2. Questions:
      1. Can this only be invoked from the column views is the _SYS_BIC schema
      2. What syntax is used to create these column views?
      3. Can I create my own column views (outside of activating an information view)
      4. Is this engine embedded in the indexserver?
      5. How does the optimizer play a role with this engine?
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Apr 09, 2013 at 07:02 AM
    21

    Hi Jonathan,

    not sure if I can answer all your questions, but let's see how far I get...

    Jonathan Haun wrote:

    1. Join Engine
      1. Used when querying an Attribute View
      2. Questions:
        1. What If I execute a standard ANSI92 SQL statement from a BI tool or in SAP HANA Studio. Does this also use the Join Engine
        2. If my Analytic View foundation is joined to attribute views, is both the OLAP and JOIN Engine used?
        3. Is this engine embedded in the indexserver?
        4. How does the optimizer play a role with this engine?

    The join engine is also used, when you run plain SQL. Actually it's not too difficult to check what engine is used to process a query: simply run the Plan Visualization on your query.

    The Plan Operators (POPs) you find in there - represented by the boxes in the graph - are named based on a naming scheme like this:

    • JE<operator_name> => Join Engine (Attribute views, plain SQL)
    • BW<operator_name> => Olap Engine (Analytic views)
    • CE<operator_name> => Calc Engine (Analytic views with calculated attributes, Calculation views)

    Answers to the questions:

    A1: Depends on what objects you reference in your query. If you're just querying plain base tables then the join engine will be used. As soon as you reference an analytic or calculation view, the other engines will be employed as well.

    A2: Nope - during activation of the analytic views, the joins in the attribute views get 'flattened' and included in the analytic view run time object. Only the OLAP engine will be used then.

    A3: All the query execution is happening in the indexserver - so, yes, join/olap/calc engine are all major parts of the indexserver.

    Jonathan Haun wrote:

    1. OLAP Engine
      1. Analytic Views (without derived columns) use the OLAP Engine
      2. Questions:
        1. Can this only be invoked from the column views is the _SYS_BIC schema that have the term "OLAP" in their name
        2. What syntax is used to create these column views?
        3. Can I create my own column views (outside of activating an information view)
        4. Is this engine embedded in the indexserver?
        5. How does the optimizer play a role with this engine?

    A1: Technically the run time objects of analytic views and attribute views are so called column views. These can also be created without the modeler and in this case these views can be placed in arbitrary schemas, not just the _SYS_BIC schema.

    For modeled views however, the _SYS_BIC schema is the only location for the run time objects.

    A2: not officially documented, but you may check the "source code" of the run time objects by displaying the definition.

    A3: sure you can - if you can ;-) As I wrote, this is not officially documented or supported. So use on your own risk.

    Besides, you don't have any transport management with these views - so this really might not be something for production scenarios.

    A4: see above. Yes!

    A5: there are actually a couple of optimizers that work on different levels of query processing. So the answer here is: yes.

    Key for Analytic views is to keep in mind that these are specifically designed to execute star schema queries. So the query optimization process 'knows' the scenario and optimizes according to this star schema scenario.

    Jonathan Haun wrote:

    Calculation Engine

    1. Calculation Views or Analytic Views with derived columns use this engine
    2. Questions:
      1. Can this only be invoked from the column views is the _SYS_BIC schema
      2. What syntax is used to create these column views?
      3. Can I create my own column views (outside of activating an information view)
      4. Is this engine embedded in the indexserver?
      5. How does the optimizer play a role with this engine?

    A1: nope - you'd also see the Calc Engine being used when running SQLScript procedures.

    A2: even less documented outside SAP than the column views. Still you could check for the source code, but you'd be unpleasantly surprised to find a large chunk of JSON syntax.

    A3: technically, yes, again - if you could ...

    A4: Sure it is.

    A5: Calc Engine is pretty special and I'd recommend to give the sap notes including their attachments ⚠️ a read:


    Hope that these answers give you something to work with.

    Cheers, Lars

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 04, 2013 at 09:18 PM

    Hi All,

    Thanks for this worferful post.I have seen real performance issue when AT with Calculated fields are used in a AV. When I use the same Table of AT and Join it in the Data foundation their was a 1000% improvement. But that is against the best practices of SAP of not using AT. Digging more I found the root cause for performance issue was my Calculated fields in AT. In the visual plan where Tables are joined at Table level only OLAP engine is used even with Calculated fields where as when AT with Calculated Fields even CALC engine comes to play.

    Any way if any one can explain what to do and what not to do when building will be helpful.

    Regards

    Purnaram.k

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 26, 2015 at 08:28 AM

    Hello,

    since this post is still the best overview in regards of the engine I would like to add an architectural question to it. All architectural overviews I found showed a more or less similar picture as http://en.wikipedia.org/wiki/File:Hana.jpg. In Lars’s book I saw a better overview with much more details, but still the BW(OLAP) and JE(Join) Engine are always missing. Does someone know to which part of the index server these engines are related to? Are they part of the calculation engine or is the join engine part of the column store engine or are they independent at all.

    Thanks

    Patrik

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 03, 2015 at 07:20 PM

    I am guessing that you would want to keep your queries isolated to a single "engine" to avoid passing data structures to the different query sub-routines. ex. if designing a schema from scratch for olap type queries I could create my tables in a star-schema fashion to minimize the joins. however, what becomes difficult is getting best olap performance when I am needing to reuse existing relational structure which may require many joins for dimensional and fact results.

    anyone want to comment on that or share experiences here? I'm trying to did deeper here what it really means with statements like HANA brings OLAP and OLTP together. my theory is that for non-trivial data volumes this is more a slider on a spectrum where to fully optimize one of these you must make trade-offs for the other.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.