Calculation View

If attribute and analytical views cannot meet your requirements, you go for calculation views.

This is necessary in such scenarios where it is required to combine several views flexibly.

There are two types of calculation views- you can model them graphically or create them using SQLScript.

In this post, we will create two calculation views using the attribute and analytical views created in earlier posts.

CA_FLIGHT_STATS calculation view will be created to show the data for excess luggage and seat utilization.

CA_SEAT_COMPARE calculation view will be created to check average seat utilization and compare the results with previous year.

Basics of Calculation View

Limitation of attribute view is that you can link only database tables via joins.

Limitation of analytical view is that it can have only one fact table linked to dimensions like in star schema.

Calculation view has no such limitations.

Calculation view has a tree structure. It has nodes which represent operation on data. These nodes have leaves representing tables or views.

The nodes in a calculation view can be of following types:

• Aggregation (For calculation on column)
• Projection (Defining a field list or hiding columns)
• Union (Clubbing data)
• Rank (Sorting data based on a condition)
• Join (Intersection of data)

The root node represents the external interface of the calculation view.

As in case of analytical and attribute views, input parameters and hierarchy are allowed in calculation views.

In addition, you can define counters for characteristic exclusively in calculation views.

Limitations of graphical modeling of calculation views

1. Certain SQL functions like function libraries and text search are not possible to be performed on calculation views modeled graphically
2. There are limitations of parameters that can be defined using input parameters in graphical calculation views
3. Calculations of aggregated data cannot be performed e.g. in BW we can analyse the data of seat utilization over a quarter and then percentage change from previous year. In SAP HANA calculation view, none of the modeling options can provide this information.

In later posts, we will describe how to use the built-in BW functionality in SAP Netweaver AS ABAP for these scenarios and also merits and demerits of this option.

For some specific scenarios, you can utilize the SQLScript programs to perform calculations.

The above seat utilization problem can be implemented using SQL, this will be shown later in the same post.

Graphical modeling of Calculation Views

Right click on the package and select New Calculation View. Select the view type as ‘Graphical’. Give the name as CA_FLIGHT_STATS.

In this calculation view, we will combine the data from the two analytical views created earlier.

The output of the calculation view should include the number of bookings with excess baggage (calculated key figure Overweight from the analytical view AN_FLIGHT_BOOKING) and seat use (calculated key figure from the analytical view AN_SEAT_UTILIZ).

Since the excess baggage key figure in first analytical view is at the booking granularity and the seat utilization is at the flight granularity, we need to first aggregate the key figure excess baggage before union.

Below figure shows the graphical calculation view:

To create this view, first add the two analytical views. Then choose the node aggregation and union to connect the nodes as shown in the above figure.

Select the attributes needed for each of these nodes.

While creating the union, define the mapping in the target by using drag and drop.

The hierarchies will not come automatically in calculation view from the underlying analytical views. You need to define them manually again in the calculation view.

Activate the calculation view and check the results.

You can see the excess baggage and seat utilization for each flight-

Creating Calculation Views using SQLScript

Here we will use simple SQLScript to give an idea about how to create calculation views. Detailed and complex scripts will be discussed in later posts.

Create a new calculation view, select the view type as ‘SQL Script’

Name the calculation view as CA_SEAT_COMPARE.

In the same screen, you can see below settings:

• Default Schema: Specify the schema name which has your tables and views. If this is selected, you don’t have to specify a schema while writing the SQL. Usually, you specify the standard schema of the ABAP system here.
• Run with: Configure the users rights for running the SQL code, with ‘Invoker’s rights’, the user (ABAP database user) must have the required SQL authorizations.
• Parameters Case Sensitive: This setting controls whether the parameters are case sensitive.

These settings are important while defining database procedures. These will be discussed in separate post.

The editor for the script based calculation view opens.

As a first step, you need to define the columns for the output parameter ‘var_out’.

The aim of this calculation view is to a self join on the analytical view AN_SEAT_UTILIZ to find the seat utilization for current year and previous year. The average seat utilization of current and previous year is then used to calculate the variance.

Such complex SQL statements can be modularized using SQL Script. We will discuss this later in the other post.

You can copy the below SQLScript coding in your calculation view-

The two time slices are labeled as cy (current year) and py (previous year) in the SQL statement. The join condition is : cy . year = py . year + 1

********* Begin Procedure Script ************
BEGIN
**Self join to compare the result with the data from the
previous year**

var_out =
select cy.mandt , cy.carrid, cy.connid ,
cy.year , py.year as prev_year ,
cy.utilization as utilization,
py.utilization as utilization_prev.

cy.utilization - py.utilization as compare
from (
select mandt, carrid, connid, year,
avg(utilization) as utilization
from "SAPABAP1::AN_SEAT_UTILIZ"
groupby mandt , carrid , connid, year
) as cy
left outer join (
select mandt, carrid, connid, year, 
avg(utilization) as utilization
from "SAPABAP1::AN_SEAT_UTILIZ"
group by mandt, carrid , connid, year
) as py
on cy.mandt = py.mandt and cy.carrid = py.carrid and
cy.connid = py.connid and cy.year = py.year + 1
order by cy.year desc ;

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

In the scenario section of the view, select the columns you want to be displayed in the output. Map the columns as attributes of key figures.

As in graphical calculation views, you can also create hierarchies and variables here in the Scenario section of the Scripted calculation view.

Activate the view and check the data preview.

You can see the difference in average seat utilization for a particular flight for current year and previous year for all the years.

For example, for flight LH 2407, you can observe the ‘compare’ key figure year wise.

Please note that the data preview does not show the complete data.

Runtime Objects and SQL Access for Calculation Views

As for analytic and attribute views, runtime objects are generated for calculation views as well. In addition separate column views are generated for hierarchies and key figures, these are mostly not relevant for the application developers.

For script based calculation views (also knows as implemented calculation views), the system also creates database procedure and table type for the output parameter var_out.

These will be described in more detail in later posts.

Via SQL,  you can access the calculation view using the runtime object.

Please see the next post on how to access the calculation views using Microsoft Excel.

To learn more, go for complete course in HANA Modeling.