Difference between a view and calculation view

[Former Member]

Hello,

Essentially we can build a database view instead of a calculation view in HANA.

what is the advantage of using a calculation view in hana?

[former_member183326 · Accepted solution]

Here's a quick rundown on calculation views:

A Calculation View can perform complex calculations not possible with other views and uses SQLScript. It has at least one measure. It is defined as graphical view (modeled with SAP HANA Information Modeler) or scripted view (SQLScript procedures).

There are two types of calculation views:

1: Graphical View

2: SQL Script

Advantages of using a graphical view:

1: No SQL/SQL Script coding needed.

2: Can consume other Analytical Views, Attribute Views, Calculation Views & tables.

3 :Union, Join, Projection nodes provided, enhance existing view functionality.

4: With the Graphical view, the user does not need to know SQL language in depth.

5: When modelling the graphical view, in the background, there is a CE script being created.

The Graphical calculation view consists of blocks of 4 types:

1: Projection is used for selecting particular columns from source table, filtering data and adding calculated columns. A projection improves performance because it narrows the results set. The user can define additional fields in the projection ex: you can preform calculations before aggregation. At every step of the process the user can define calculations, be it in the Analytical view itself, the projection or the output.

2: Aggregation is used to aggregate data sets. For each aggregated field you have to choose aggregation type

3: Join is used for connecting two data sets row to row using keys and conditions. In Join block you have to specify connecting fields.

4: Union is used for combining two data sets with the same fields to one. In Union block for each source field you have to specify destination field. For coding Calculation views you can use classic SQL Script or highly optimized Calculation Engine functions -Calculation Engine functions should not be mixed with standard SQL statements due to better optimization and parallelization. CE functions usually show much better performance results than calculation view via classic SQL Script.

For a lot of SQL constructions you can find more effective CE functions alternatives. So to sum it up, we do not recommend only to use graphical views but due to better optimization and parallelization CE functions usually show much better performance results than calculation view via classic SQL Script.

I am far from an expert with HANA data modelling and this information may be outdated now with new innovations in SQL and HANA but I hope this helps.

Regards,

Michael

[lbreddemann · Accepted solution]

As Michael already mentioned a few parts of the above statements are not quite correct.

Please do pay no heed to the paragraph that compares CE-functions with SQL functions.

Using SQL and supporting advanced functionality based on SQL is the development direction for SAP HANA and the usage of CE functions is clearly discouraged.

As a matter of fact, using SQL functions opens up additional query optimization options that are not available when CE functions are used.

Now to the original question: "Why should we use HANA modelling when we can do everything with SQL?".

This is pretty much the same question as "Why would we program in C instead of Assembler? We can develop everything in Assembler..."

The point here is: development efficiency.

A core concept in SAP HANA is the re-use of views to 'stack' them on top of each other. While this can be achieved via SQL, it is much easier and much more visible to do that in the modeler.

Same goes for certain modelling patterns, that are provided as modeler features, like time-dependent join or language dependent join.

Yes, you can do both in SQL, but you've to do coding for that. Every single time.

With the modeler is not only very clear and obvious what is intended to happen but also using such features only requires a few clicks instead of a lot of recurring typing.

So, can you do without SQL? Sure you can.

Is your developer life much easier for a wide range of problems with you do use the modeler? Indeed it is.

There you go. That's what defines a tool. It makes stuff easier to achieve for you.

Cheers,

Lars

[uladzislau_pralat]

Hi Tommy,

for purpose of reporting database views are not an alternative to HANA views. There is no visual Modeling Tool for database views creation and it is difficult to re-use them in the same manner as you do with calculation views. Besides of, in contrast with calculation views there are no:

1) database views optimization at the time of execution, for example,  removing unnecessary joins;

2) database views additional metadata generation for MDX clients such as Analysis for Office and Lumira.

Hope it will help.

Regards, Uladzislau