 Community


 Home
 Community
 Blogs
 Ask a Question
 Write a Blog Post


Product Information

Mohd Danish

Posted on December 2, 2019 13 minute read

SAP-HANA-Calculation-Views
FollowRSS feedLike
14 Likes 15,577 Views 3 Comments

More and more SAP system users are implementing SAP HANA as the foundation and database for
existing SAP BW installations. Some may just be replacing an existing database with the modern
SAP HANA database to gain speed in query execution and warehouse management. This is, of
course, a keystone of an SAP HANA implementation within SAP BW, but not the only one.

From my point of view, the tight integration of SAP HANA functionality using SAP HANA studio or
Eclipse to design new ways of data warehouse management is at least as important as reporting
performance.

Why use old habits with SAP application layers, storing more and more data in persistency layers,
when it’s possible to calculate new reporting data on the fly? Using SAP HANA-based staging to
enhance data flows without the physical staging of incoming data in several layers (SAP staging
layer architecture [SLA]) is something I am working on at a customer site.

My approach can help you to be flexible when it comes to design changes because there is no
unload or reload of data in staging layers when business logic changes. It also keeps your operation
costs for SAP HANA at a minimal level because less data in an SAP HANA database means less
expense for licensing.
(In a modern SAP BW powered by SAP HANA environment, it is wise to switch to virtual data
staging instead of old-school data staging via persistency layers. SAP HANA calculation views and
procedures for complex scenarios help to calculate data on the fly to cut down unload and reload
phases to zero when it comes to business-related changes in data staging or extension/reduction in
a data model. In the SAP HANA environment using SAP HANA studio or Eclipse, SAP BW operators
can very easily switch from the old-fashioned models to the new calculation-view-based modeling
concept or run a mixed scenario of both worlds.)

An SAP HANA procedure is a database-stored procedure that gives you programming functionality
with the help of SAP HANA SQL Script, similar to ABAP functions. Now I guide you through the
creation of SAP HANA data flows with the help of calculation views (graphical and SQL Script-based
views), as well as some pitfalls you may run into.

My example (Figure 1) is a simple mixed approach.

Figure 1

An example of the mixed data approach using calculation views

That means you use existing SAP BW inbound data (DataStore object [DSO] based, as in an SAP
BW entry layer). You also join SAP BW table information (such as an SAP Data Dictionary [DDIC]
table) into one combined calculation view you can use in combination with an SAP HANA Composite
Provider for BEx or Analysis for Office reporting. You can also use calculation views directly in
Analysis for Office reporting. (I do not cover authorizations such as direct reporting on calculation
views without a Composite Provider).

All the screenprints are based on Eclipse as the development studio for SAP HANA BW
development. I am running Eclipse Neon 3 and the latest SAP BW and SAP HANA add-ons. The
actual set of SAP BW modelling tools can be found using SAP Note1944835 – SAP BW Modeling
Tools – Delivery Schedule 1944835 – SAP BW Modeling Tools – Delivery Schedule.

In the following explanations and examples, I use data based on an external SAP HANA view. In my
example, I use technical DSO content data to feed data and consume data in a graphical calculation
view. I use this data in combination with a SQL Script calculation view in a third graphical calculation
view to join both data. I explain the difference in accessing data by regular joins in a graphical
calculation view compared with SQL Script-based calculation views and explain the advantages of
SQL Script calculation views. At the end, you can use the third calculation view, joining all views to a
single point, to feed a Composite Provider for reporting users with the help of BEx queries or directly
in combination with Analysis for Office.

(Note: I assume that you are familiar with SAP HANA studio or Eclipse. Therefore, I do not explain
the basics, such as switching perspectives.)

Start Your Walk Through SAP HANA Calculation Views

First, switch to the SAP HANA Development perspective (Figure 2). I recommend using this
perspective in the case of SAP HANA development. The reason for this recommendation is that in
all other perspectives you are unable to create SAP HANA hdb procedures.

Figure 2

The SAP HANA Development perspective

The first calculation view is a plain graphical view. Right-click your development package and select
New and then Calculation View as shown in Figure 3.
Figure 3

Dialog to choose a calculation view

This action displays the screen shown in Figure 4.

Figure 4

Calculation view dialog

(Note: If your screen looks different, make sure that you have selected the Repositories tab. I
recommend using this approach because via an SAP HANA development perspective and
repository display, all functionalities that can be used that might be obsolete in the SAP HANA
modeling perspective and Systems tab are displayed on the screen.)

In Figure 4 enter your desired calculation view technical name, such as MY_NEW_VIEW (not
shown). Keep the default Type, which is Graphical, and click the Finish button.

By default, the system generates two objects: Semantics and Aggregation objects (Figure 5).
Figure 5

SAP HANA calculation view scenario area

On the left, you find all the available design objects. Start with the Projection option. Drag and drop it
to the blank area of the scenario. The Projection gives access to all available objects within SAP
HANA, such as DDIC tables, InfoProviders, master data, and already existing SAP HANA views.

When moving your mouse over the projection object (Figure 6), you see a green plus sign.

Figure 6

Projection with displayed data access symbol

Click that plus icon (data access) to open a dialog in which you can search for your desired object.
You can read the data and then add that object to your actual view.
The dialog that opens after you click the plus icon allows you to search for your desired object. In my
example, I entered the search string for SAP BW technical content object WIS_C03 (a copy of SAP
technical cube TCT_C03, which is an InfoCube). As you can see in Figure 7, the search result
returns all available SAP HANA objects, such as the InfoProvider itself and all existing partial (e.g.,
dimensions) tables.

Figure 7

Search result for projections

I choose the InfoCube itself, which is indicated by the Cxx postfix (the third item from the top), by
double-clicking the entry or simply selecting the entry and selecting the OK button. I recommend
creating ADSO (a type of standard DSO) technical names that refer to its type such as C (for type
Cube).

Besides adding the selection to your projection and displaying the name, you see the structure of the
selected object in the studio detail pane, (in my example the InfoCube structure) displaying
InfoObjects as well as cube SID entries (Figure 8).

Figure 8

Projection with displayed structure

Clicking the bullet-shaped icons in the detail view adds that particular InfoObject to the output
(Figure 9) of that projection and changes its color to orange (selected). This selection is similar to
adding fields of a table to a customized view in transaction SE11. The LED shaped icon works like a
toggle switch. A gray color means the field is turned off (for usage), while orange means the field has
been turned on.

Figure 9

Projection with three selected InfoObjects

Because InfoCubes use SIDs as well as InfoObject keys, the selection might be a bit time-
consuming. For a better view to InfoCubes, I recommend activating the external SAP HANA view of
that particular InfoCube via the change view in the Administration Workbench. Or you could
automate the activation of all cubes /DSO.

If you already activated the external SAP HANA view, you can select that view directly from the
Projection search dialog (Figure 10).

Figure 10

Activated external SAP HANA view

Selecting this external SAP HANA view makes your life with calculation views much easier. As you
can see (Figure 11), only InfoObjects and their text elements, if they exist, are displayed. You can
add them to the output of your projection.
Figure 11

Projection of external SAP HANA view for DSO type InfoCube WIS_C01

To map the projection to the existing aggregation object in the scenario pane, click the circle icon
above Projection_2. Hold down the mouse, drag a connection (line) to the bottom icon of the
aggregation object, and release the mouse. Now your projection is connected (Figure 12).

Figure 12

Projection is connected to Aggregation

Selecting the aggregation object unveils the structure of all the fields of your projection. You can
decide if you like all the fields or just a few. If you like all of them, the best approach is to right-click
the header area (black header) and select the Add All To Output (Figure 13) option, which
automatically maps all existing fields to the output without having to select the entries one by one.
Figure 13

Add all the fields to the output dialog

After this step is done, you can activate the calculation view by clicking the activation icon from the
top menu in Eclipse (Figure 14).

Figure 14

Activate the calculation view

After activation, you can directly display the data for each individual object by right-clicking the object
(e.g., projection) and selecting Data Preview (Figure 15). (The details of querying the data are
beyond the scope of this article.)

Figure 15

Dialog to display the data preview

To enrich my example with some more details, I add the data from a new ADSO. As before, drag
Projection to the scenario, click the plus icon, and select your desired object (in my case the
InfoProvider is named WIS_D01 as shown in Figure 16).
Figure 16

Add DSO WIS_D01 to the second projection

You should see both projections. The first one is already connected to the aggregation object. Now
you join those two tables.

(So that you do not lose all existing mapping, drag and drop the join object from the right side
directly onto the connection line between the first projection and the aggregation object. This
automatically adds the join without breaking any mapping. Now you should have the scenario shown
in Figure 17.)

Figure 17

A scenario with the added join object

The last step is to drag the output of the second projection to the input of the displayed join.

Since you now have a join between both objects (WIS_C01 and WIS_D01), you need to define the
join condition between your desired fields.

Just select the output fields by clicking the orange bullets and for the join condition drag and drop a
line between the join objects. I joined 0CALDAY by transferring the data from Projection_2 and just
turning on additional fields of Projection_1. InfoObject 0CALDAY (Figure 18) is now the join
condition, but it will not be transferred to the output data because otherwise you would have the field
0CALDAY twice.

Figure 18

Join with join condition on 0CALDAY

Right-clicking the join connection opens a dialog in which you can swap the tables (important for
outer joins) and display the Edit… option (Figure 19).

Figure 19
Join options

When you are using the join edit mode, a new dialog appears in which you can set the different join
conditions as well as cardinality (Figure 20). To keep it simple leave the standard join condition
(inner join) active.

Figure 20

Join option dialog

As you can see, the graphical join does not allow you to define complex join conditions (e.g., with
WHERE statements or complex filters) as done in ABAP joins. To create such joins, you need SQL-
based calculation views. In these calculation views you can define whatever join condition you like.

As an alternative, you can pass the coding for such joins and table accesses to an SAP HANA
procedure to be more flexible. These procedures can be used system wide for all purposes. First,
you have to create a new procedure. Right-click your package and select New and then Other… as
shown in Figure 21.
Figure 21

Dialog for new objects

In the next dialog select Stored Procedure (Figure 22).

Figure 22

Select Stored Procedure

Provide a name and target schema for the new procedure (Figure 23).
Figure 23

The initial dialog for stored procedures (hdb procedures)

Now enter your SQL Script code (Figure 24). My example adds the activation and request
information from SAP BW tables RSREQDONE and RSMONICDP to the final calculation view.

PROCEDURE “JOEBOE”.“system-local.public.JOEBOE::P_GET_BW_MONITOR_DATA”

(OUT TABLE_OUT table

“RNR”        NVARCHAR (30),

“ICUBE”      NVARCHAR (30),

“DP_NR”      NVARCHAR (30),

“STATUS”     NVARCHAR (4),

“QMSTATUS”   NVARCHAR (4),

“REC_INSERT” INTEGER,

“REC_UPDATE” INTEGER,

“DATUM”      NVARCHAR (8),

“UZEIT”      NVARCHAR (6),

“TSTATUS”    NVARCHAR (4),

“TDATUM”     NVARCHAR (8),

“TUZEIT”    NVARCHAR (6))

LANGUAGE SQLSCRIPT SQL

SECURITY INVOKER

DEFAULT SCHEMA “MY_SCHEMA”

READS SQL DATA AS

BEGIN

/*****************************

The upper table has to be defined exactly like the receiving SQL Script calculation View

*****************************/

— Table out will receive all data in exactly the order as given by upper table from

— next SQL call. It should be aligned to the fields being used in SQL scipt calc view

table_out =

select  distinct

TB1.”RNR”  ,

TB1.”ICUBE” ,

TB1.”DP_NR” ,

TB1.”STATUS”,

TB1.”QMSTATUS” ,
TB1.”REC_INSERT” ,

TB1.”REC_UPDATE”,

TB2.”DATUM”,

TB2.”UZEIT”,

TB2.”TSTATUS”,

TB2.”TDATUM”,

TB2.”TUZEIT”

from  “SAPT04″.”RSMONICDP”  as TB1

INNER JOIN “SAPT04″.”RSREQDONE” as TB2

on

TB1.”RNR” = TB2.”RNR”

WHERE  TB2.”DATUM” > ‘20160101’ AND

TB1.”ICUBE” like ‘0TCT%’ AND

(TB1.”QMSTATUS” is NOT NULL  AND TB2.”RECORDS” > 0)

order by TB1.”ICUBE”  ASC;

END;

Figure 24 — Sample SQL Script code for a join between tables in SAP HANA

The first part in my sample code is the declaration of the table fields used in the join condition. Those
fields that are declared are sent to the calculation view later (similar to internal tables in ABAP).

SECURITY INVOKER respects the authorization the individual user might have running the
procedure later on. The table out parameter receives the result from the select statement and
passes the information to the output table (table_out) you use later.

Activate your procedure after the coding is bug free by using Ctrl + F3 or the activation icon from the
Eclipse menu.
The next step is to use this procedure in a calculation view type SQL Script. (Remember the
procedure is not mandatory; you could use the code directly in SLQ script-based calculation views.)
Right-click the package and select New, then Calculation View. Within the familiar dialog (Figure 25)
make sure to select type SQL Script (the default is Graphical) and provide mandatory information
such as the name.

Figure 25

The Calculation View dialog with selected type SQL Script

The next dialog displays a familiar scenario view, but with only two simple objects (Figure 26).

Figure 26

The SQL script calculation view

Click the Script_View object and see that the code as well as output pane are displayed. The code
part is rather simple (Figure 27). As you can see, you only call the procedure that you just created.

/********* Begin Procedure Script ************/

BEGIN

–Call procedure that does Join and structure definition to return its value to calculation view

call “MY_SCHEMA”.”system-local.public.JOEBOE::P_GET_BW_MONITOR_DATA”(:var_out);

END /********* End Procedure Script ************/

Figure 27 — Sample code for SQL Script-based calculation views

The var_out table declared in the procedure must now be connected to the output fields of the
calculation view. The type of fields (Figure 28) and the exact sequence have to be aligned to the
type and sequence of your table_out definition inside your procedure (see the code in Figure 24).

Figure 28

Calculation view output fields

After all the settings are done, activate and test (data preview) the SQL-based calculation view.

The final step is to combine this calculation view with the previous example. Just add a new
projection, click the plus icon, and search for your SQL-based calculation view (Figure 29).
Figure 29

Search for the calculation view

Calculation views allow you to insert not only standard SAP BW objects but also all SAP HANA
objects (such as calculation views).

Turn on the desired output fields in your new added item, as described with Figures 8 and 9. Add a
new join condition and map the existing components with the new SQL Script-based calculation
view. The output should look similar to my example (Figure 30).

Figure 30

The final calculation view integrating SAP BW and SAP HANA components

You can use this introduction to SAP HANA calculation views to help build scenarios combining
graphical and SQL-based calculation views for all kinds of purposes. You could in some cases
bypass the classical staging in DSO and cubes completely by directly adding this calculation view to
a Composite InfoProvider for reporting purposes.

The benefit of using SAP HANA calculation views if that you can use the view I just created in a
projection of several other views (reusable) as I did with the SQL Script-based view in this article.

Source:SAP HANA Note





 Alert Moderator

Incorrect Tag/Category




Assigned tags

 BW SAP HANA Modeling Tools (Eclipse)

Related Blog Posts

 Table Functions in SAP HANA

By Surendra Kumar Reddy Koduru, Feb 03, 2019
 SAP HANA calculation view version rollback
By Vivek Agrawal, Apr 29, 2020
 How to include, aggregation type NOP key figures in composite provider.
By Nanda Anantha, Jan 23, 2020

Related Questions

 Create HANA analytic privilege

By Former Member, Aug 18, 2017
 What is best: one big calc view or multiple smaller calc views?
By Laurens Cleyman, Mar 07, 2019
 Creating a input parameter/variable on top of a calculated column in SAP HANA
By Former Member, Sep 11, 2017

Option A: One big calculation view

 More performant then multiple smaller calculation views (I think)

 You can't reuse 'pieces' of the calculation view.
 Naming convention is simple.

Option B: Multiple smaller calculation views

 Les performant then one big calculation view (I think)
 You can reuse calculation views in other calculation views.
 Naming convention is complex. Any suggestions?

SAP HANA Calculation View Tutorial

What is Calculation View?

SAP HANA Calculation view is a powerful information view.

SAP HANA Analytic view measure can be selected from only one fact table. When
there is need of More Fact table in information view then calculation view come in the
picture. Calculation view supports complex calculation.

The data foundation of the calculation view can include tables, column views, analytic
views and calculation views.

We can create Joins, Unions, Aggregation, and Projections on data sources.

Calculation View can contain multiple measures and can be used for
multidimensional reporting or no measure which is used in list type reporting.

Characteristic of SAP HANA Calculation View as below –

 Support Complex Calculation.

 Support OLTP and OLAP models.
 Support Client handling, language, currency conversion.
 Support Union, Projection, Aggregation, Rank, etc.

SAP HANA Calculation View are of two types –

1. SAP HANA Graphical Calculation View (Created by SAP HANA Studio

Graphical editor).
2. SAP HANA Script-based calculations Views (Created by SQL Scripts by SAP
HANA Studio).

SAP HANA Graphical Calculation View

In SAP HANA Analytic view, we can select a measure from one table only.

'The History of the Future' Delves Into the True Story Behind Oculus

So when there is a requirement for a view which contains measure from the different
table then it cannot achieve by analytic view but by calculation view.

So in this case, we can use two different analytic view for each table and join them in
calculation view.

We are going to create a graphical Calculation View "CA_FI_LEDGER" by joining

two Analytic View "AN_PURCHASE_ORDER" And "AN_FI_DOCUMENT".

CA_FI_LEDGER will display finance document detail related to a purchase order.

STEP 1) In this step,

1. Go to package (Here Modelling) and right click.

2. Select New Option.
3. Select Calculation View.
A Calculation View Editor will be displayed, in which Scenario Panel display as
below –

Detail of Scenario panel is as below –

 Palette: This section contains below nodes that can be used as a source to build
our calculation views.

We have 5 different types of nodes, they are

1. Join: This node is used to join two source objects and pass the result to the next
node. The join types can be inner, left outer, right outer and text join.

Note: We can only add two source objects to a join node.

2. Union: This is used to perform union all operation between multiple sources.

The source can be n number of objects.
3. Projection: This is used to select columns, filter the data and create additional
columns before we use it in next nodes like a union, aggregation and rank.

Note: We can only add one source objects in a Projection node.

4. Aggregation: This is used to perform aggregation on specific columns based

on the selected attributes.
5. Rank: This is the exact replacement for RANK function in SQL. We can
define the partition and order by clause based on the requirement.

STEP 2)
1. Click Projection node from palette and drag and drop to scenario area from
Purchase order analytic view. Renamed it to "Projection_PO".
2. Click Projection node from palette and drag and drop to scenario area for FI
Document analytic view. Renamed it to "Projection_FI".
3. Drag and drop analytic View "AN_PUCHASE_ORDER"
"AN_FI_DOCUMENT" and from Content folder to Projection node and
"Projection_FI" respectively.
4. Click Join Node from Palette and drag and drop to scenario area.
5. Join Projection_PO node to Join_1 node.
6. Join Projection_FI node to Join_1 node.
7. Click Aggregation node from palette and drag and drop to scenario area.
8. Join Join_1 node to Aggregation node.

We have added two analytic views, for creating a calculation view.

STEP 3) Click on Join_1 node under aggregation and you can see the detail section is
displayed.

1. Select all column from Projection_PO Node for output.

2. Select all column from Projection_FI node for output.
3. Join Projection_PO Node to Projection_FI node on column

Projection_PO. PO_Number = Projection_FI.PO_NO.

STEP 4) In this step,

1. Click on Aggregation node and Detail will be displayed on right side of the
pane.
2. Select Column for output from the Join_1 displayed on the right side in the
detail window.

STEP 5) Now, click on Semantics Node.

Detail screen will be displayed as below. Define attribute and measure type for the
column and also, mark key for this output.

1. Define attribute and measure.

2. Mark PO_Number and COMPANY as Key.
3. Mark ACC_DOC_NO as key.

STEP 6) Validate and Activate calculation View, from the top bar of the window.

1. Click on Validate Icon.

2. Click on Activate Icon.

Calculation View will be activated and will display under Modelling Package as
below –
Select calculation view and right click ->Data preview

We have added two analytic views and select measure (TAX_AMOUNT,

GROSS_AMOUNT) from both analytic view.

Data Preview screen will be displayed as below –

CE Functions also known as Calculation Engine Plan Operator (CE Operators)

are alternative to SQL Statements.

CE function is two types –

Data Source Access Function

This function binds a column table or a column view to a table variable.

Below is some data Source Access Function list –

 CE_COLUMN_TABLE
 CE_JOIN_VIEW
 CE_OLAP_VIEW
 CE_CALC_VIEW

Relational Operator Function

By Using Relational Operator, the user can bypass the  SQL processor during the
evaluation and communicate with calculation engine directly.

Below is some Relational Operator Function list –

 CE_JOIN (It is used to perform inner join between two sources and

Read the required columns/data.)

 CE_RIGHT_OUTER_JOIN(It is used to perform right outer join between the

two sources

and display the queried columns to the output.)

 CE_LEFT_OUTER_JOIN (It is used to perform left outer join between the

sources and

display the queried columns to the output).

 CE_PROJECTION (This function display the specific columns from the source
and apply

filters to restrict the data. It provides column name aliase features also.)

 CE_CALC (It is used to calculate additional columns based on the business

requirement.

This is same as calculated column in graphical models.)

Below is a list of SQL with CE function with some Example-

Query Name SQL Query CE-Build in Function

Select Query On
SELECT C, D From "COLUMN_TABLE". CE_COLUMN_TABLE("COLUMN_TABLE",[C,D])
Column Table

Select Query On
SELECT C, D From "ATTRIBUTE_VIEW" CE_JOIN_VIEW("ATTRIBUTE_VIEW",[C,D])
Attribute View

Select Query on SELECT C, D, SUM(E) From

CE_OLAP_VIEW("ANALYTIC_VIEW",[C,D])
Analytic View "ANALYTIC_VIEW" Group By C,D

Select Query on SELECT C, D, SUM(E) From

CE_CALC_VIEW("CALCULATION_VIEW",[C,D])
Calculation View "CALCULATION_VIEW" Group By C,D
Query Name SQL Query CE-Build in Function

SELECT C, D, SUM(E) From Var1= CE_COLUMN_TABLE("COLUMN_TABLE");

Where Having
"ANALYTIC_VIEW" Where C = 'value' CE_PROJECTION(:var1,[C,D],"C" ="value"/