HANA- Modeling & Scripting

Best Practices
SAP AGS
General Principles on Performance

Avoid transfer data of large result sets between the

HANA DB and client (e.g. by HAVING, TOP, LIMIT, ...)
Client / Application
Do calculation after aggregation.
A G Y Z

Avoid Complex expressions (IF, CASE, ... )

Calculation Engine Reduce data transfer between the

engines

A G Y Do aggregate data records (e.g using

GROUP BY, reducing Columns)

OLAP Join Join on Key Columns or Indexed

A B C D G Y
Columns
Engine Engine
Avoid calculations before
aggregation on line item level

A B C D D G

Filter data amount as early as

Column Row possible in the lower layers
(CONSTRAINTS, WHERE
Clause,...)
Store Store

© 2011 SAP AG. All rights reserved. 2

General Principles on Performance

Minimize the join operations Minimize data transfer between

Avoid joining big tables or Attribute Views the engines
Avoid long join chains Prefer execution of calculation in lower
layers instead of pushing data up and
calculate there

Reduce data amount as early as

possible Avoid expensive calculation or
Use data aggregation data manipulation
Use data filters (WHERE, CONSTRAINTS) Row based data base expressions are
expensive
Intermediate results needs to be
Avoid huge query result sets written into buffer, this takes additional
Data transfer to the UI clients also requires time
time Avoid complex expressions, for
Too many data can be anyway not analyzed example complex IF, CASE statements
properly

© 2011 SAP AG. All rights reserved. 3

General Model Decision Tree

start • Multidimensional Reporting,

(slicing dicing)
• Mass data analysis

OLAP
no Scenario?
yes
OLTP
Scenario Master yes
Data
• Transactional Scenarios
Reporting? • E.g. HR reporting

• Write intensive scenarios no Attribute View

• Select single record
• Search Complex
• … Calculation
?
no yes • Union

Analytic View + • Currency Conversion

Analytic View
Calculation • Complex logic
View
• Explicit Data Flow
modeling

© 2011 SAP AG. All rights reserved. 4

Aspects which can influence the model design 1/2

© 2011 SAP AG. All rights reserved. 5

Architecture

SQL Optimizer
Calculation
Views

calculation engine
Attribute
Analytic
Views
Views OLAP Join
row store engine
engine engine

column store row store

JOIN Operations
Mass Join Operations

Data
Foundation
Delivery
Attribute Header
Delivery ID
View Delivery Date
Ship-to Party
Dimension
Dimension Sold-to Party 50 mio records
Material Sales Org

10000 records Material ID

Header Data
Material Class

Delivery
Item
Delivery ID Fact table
Item ID
Material
100 mio records
Value
Item Data

OLAP is not designed to handle mass join operation. The more join operations to be
executed, the slower the query performance !
Avoid joining big tables.
Avoid joining big Attribute Views.

© 2011 SAP AG. All rights reserved. 7

Analytic View - Modeling Measure as attribute 1/3

Attribute View
Material
Descriptions
Material ID
Language
Material Output
Description
Material ID
1 Data Foundation
Material Class
Material Order Item
Class Billing Item
Material Class ID
VBELN
1
Name
VBELN
POSNR
POSNR
MATNR
N MATNR
NETWR ORDER Delivery Item
ERDAT
1
VBELN PRSDT

POSNR NETWR ORDER

Attribute View VGBEL NETWR DEL

0..N
VGPOS NETWR ORDER
Time Dimension
ERDAT
1
Date
N NETWR DEL
Callyear Measure used as
Calmonh an attribute
Calweek
Fact Table

An Analytic View only allows to select measures from one Fact Table within Data Foundation.
Here, the measure NETWR ORDER from the second Fact Table Order Item is modeled as
attribute.
© 2011 SAP AG. All rights reserved. 8
 Analytic View - Modeling Measure as attribute 2/3
Example – Risk of wrong aggregation
SELECT
Incorrect Total!!!
MATNR NETWR_ NETWR_
ORDER DEL MATNR,
Due to the measures having the same value. M-01 8150 8150
NETWR_ORDER,
SUM(NETWR_DEL),
Aggregation removes duplicate lines FROM VIEW
GROUP BY MATNR, NETWR_ORDER

Order Item Delivery Item

VBELN
1 DELN
POSNR DELITEM VBELN POSNR MATNR NETWR NETWR
MATNR ORDER DEL
VGBEL
NETWR ORDER
VGPOS 4970 10 M-01 8150 8150
MATNR
0..N 4970 20 M-01 8150 ?
ERDAT

NETWR DEL

Measure used as
an attribute Fact
Table

Order item Delivery item

DELN DELITEM VGBEL VGPOS MATNR ERDAT NETWR
VBELN POSNR MATNR NETWR
DEL
ORDER
The Sales Order
item 10 is split 4970 10 M-01 8150 2537 10 4970 10 M-01 20120103 4075
into 2 deliveries.
4970 20 M-01 8150 2948 20 4970 10 M-01 20120104 4075

© 2011 SAP AG. All rights reserved. 9

 Analytic View - Modeling Measure as attribute 3/3

Attribute View
Material
Descriptions
Material ID
Language
Material
Description
Material ID
1 Data Foundation
Material Class
Material Order Item
Class
Material Class ID
VBELN
1
Name
POSNR

MATNR Billing Item

N
NETWR ORDER Delivery Item
VBELN
1
VBELN POSNR

POSNR VGBEL
Attribute View VGBEL VGPOS
0..N
Time Dimension VGPOS 0..N AUBEL

ERDAT AUPOS

Date
1
N NETWR DEL PRSDT
Callyear
NETWR BILLING
Calmonh
Calweek
Fact Table

An Analytic View only allows to select measures from one Fact Table within Data Foundation.
In the example, this is Fact Table “Billing Item“ with measure NETWR_BILLING.
Here in this example, the measure NETWR_DEL from the second Fact Table Delivery Item and
the measure NETWR_ORDER from the third Fact Table Order Item are modeled as attributes.
© 2011 SAP AG. All rights reserved. 10
Multiple fact tables – Calculation View
Union with Constant Values

Situation
You have measures coming from two fact tables.
Solution
Create one Analytic View per fact table.
Create a Calculation View with a UNION to
combine both Analytic Views.
Set the counter parts for measures to Constant
Value = ‘0‘ for the Analytic View that does not
provide that measure.
Set the counter parts for an attribute to Constant
Value = NULL for the Analytic View that does not
provide the attribute.

Avoid combining OLAP views (Analytic Views) with

a JOIN due to performance reasons !
Avoid joining 2 very large fact tables even in the
Data Foundation of one Analytic View !

© 2011 SAP AG. All rights reserved. 11

Multiple Fact Tables – Calculation View
Join of Analytic View and Table

Output Calculation View

Join
Analytic View

Order Item

VBELN

POSNR
Table
MATNR

NETWR ORDER

Create a Calculation View and join the Analytic View the other Fact Table (in this example:
Order Item)
The Analytic View includes one Fact Table as Data Foundation with measure.
© 2011 SAP AG. All rights reserved. 12
Multiple Fact Tables – Calculation View
Join of Analytic View and Table, Query 1
VBELN POSNR NETWR NETWR
DEL ORDER

4970 10 8150 8150

4970 20 ? 8150 SELECT

VBELN,
POSNR,
Calculation View Output SUM(NETWR_ORDER)
SUM(NETWR_DELIVERY)
FROM CV
GROUP BY VBELN, POSNR
Join
Analytic View

Order Item

VBELN

POSNR Table
MATNR

NETWR ORDER

VBELN POSNR NETWR

VBELN POSNR NETWR_DEL ORDER
4970 10 8150 4970 10 8150
4970 20 ? 4970 20 8150
© 2011 SAP AG. All rights reserved. 13
Multiple Fact Tables – Calculation View
Join of Analytic View and Table, Query 2
VBELN POSNR ERDAT NETWR NETWR
ORDER DEL

4970 10 20120103 8150 4075

4970 10 20120104 8150 4075

4970 20 ? 8150 ?
SELECT
VBELN,
POSNR,
ERDAT,
Calculation View Output SUM(NETWR_ORDER)
SUM(NETWR_DEL)
FROM CV
GROUP BY VBELN, POSNR
Join
Analytic View

Order Item

VBELN

POSNR Table
MATNR

NETWR ORDER

VBELN POSNR ERDAT NETWR VBELN POSNR NETWR

DEL ORDER

4970 10 20120103 4075 4970 10 8150

4970 10 20120104 4075 4970 20 8150

4970 20 ? ?
© 2011 SAP AG. All rights reserved. 14
 Exercise A2
Analytic View - Calculated Attributes 1/2
SELECT YEAR
Warning!
YEAR CROSS REV SALES DEDUCT PROD VAR SUM(A_GROSSREVENUE)
Use with caution 2011 500 200 300 SUM(A_SALESDEDUCTION)
2012 300 100 200 SUM(A_PRODUCTVARIANCE)
FROM AV A
WHERE MATNR = DPC1017
GROUP BY YEAR
_SYS_BIC/<package>/<Analytic View>
Wrapper Calc. View

Add /olap to
directly access the
Analytic View
Analytic View
_SYS_BIC/<package>/<Analytic View>/olap

SELECT YEAR, MATNR, SUM(A_GROSSREVENUE),

SUM(A_SALESDEDUCTION),SUM(A_PRODUCTVARIANCE)
FROM AV/olap WHERE MATNR = DPC1017
GROUP BY YEAR, MATNR

Calculated Attributes
• A_CURRENT_YEAR_CA: midstr(string(now()),1,4)
• A_PREVIOUS_YEAR_CA: string(double(“A_CURRENT_YEAR_CA")-1)
• A_FILTER_CA: if(“A_CURRENT_YEAR_CA"="YEAR",1,if(“A_PREVIOUS_YEAR_CA"="YEAR",2,-1),-1)

The existence of Calc. Attributes using attributes results in the creation of an additional wrapper
Calc. View runtime object for the Analytic View = _SYS_BIC/<package>/<Analytic View>.
The wrapper Calc. View will always get executed (in the Calc Engine !) regardless if any
Calculated Attributes are selected by the SQL query.
© 2011 SAP AG. All rights reserved. 15
 SELECT
Analytic View - Calculated Attributes 2/2 YEAR,
YEAR FILTER CROSS REV SALES DEDUCT PROD VAR A_FILTER_CA
2011 2 500 200 300 SUM(A_GROSSREVENUE)
2012 1 300 100 200
SUM(A_SALESDEDUCTION)
SUM(A_PRODUCTVARIANCE)
FROM AV A
WHERE MATNR = DPC1017
GROUP BY YEAR,
Wrapper Calc. View A_FILTER_CA

Note!
Calc. Attributes are
not pushed down
into plain Analytic
View
Analytic View

SELECT YEAR, MATNR, SUM(A_GROSSREVENUE),

SUM(A_SALESDEDUCTION),SUM(A_PRODUCTVARIANCE)
FROM AV A/olap WHERE MATNR = DPC1017
GROUP BY YEAR, MATNR

Dimensions and measures used in SQL queries will get pushed down where possible.
First, results from the plain Analytic View (/olap) are transferred to the Calc. View.
Then, the Calc. Attributes, here A_FILTER_CA, are executed in the wrapper Calc. View.
Calc. Attributes will not be pushed down to the plain Analytic View
(SYS_BIC/<package>/<Analytic View>/olap).
© 2011 SAP AG. All rights reserved. 16
Filter Operations 1/2
CONSTRAINT, WHERE Clause, CE_PROJECTION, HAVING

CONSTRAINT filter
static design filter, can be applied on a table of an Analytic View or can be defined in a
Projection node of a Calculation View
is forced with inner join, and optional with left / referential join
is executed before join and aggregation
reduces data amount very early and for that it is very fast (especially when constraint
is applied on a dimension table)
normally faster than WHERE CLAUSE, as the result set is reduced before proceeding

WHERE CLAUSE
is defined in the SQL query / SQL statement
the filter applies on the result set of a query
applies on a tuple and has different semantic than constraint filter
is very fast on column tables

Both operations, CONSTRAINT filter and WHERE CLAUSE can be used to filter and reduce the result
set of data.
But from semantic point of view these two operations are not the same at all:
A CONSTRAINT filter gets executed first, the WHERE clause gets executed second.

© 2011 SAP AG. All rights reserved. 17

Filter Operations 2/2
CONSTRAINT, WHERE Clause, CE_PROJECTION, HAVING

CE_PROJECTION
the semantic is similar to WHERE clause
In comparison to WHERE clause, the filter here is in the model can be used to filter on a specific
aggregation level within a Calculation View

HAVING
used to reduce the final result set after aggregation -> no performance gain
e.g. HAVING SalesAmount > 10000

© 2011 SAP AG. All rights reserved. 18

Tuning – Avoid Data Type conversion

Data type conversion is necessary for the following Select statement :

Select <Attribute>, SUM(Measure)
FROM <AN_VIEW>
WHERE ”PERIOD“ < n
GROUP BY <Attribute>;
The attribute ”PERIOD“ is of data type VARCHAR,
length 3, with the values from ’001‘ to ’016‘.
n is of data type INTEGER with values from 1 to 16.

The Analysis via “Visualize Plan” shows that the usage

of different data types forces the system to convert
“Period“ into integer using a To_int(“Period“) operator
which impacts the performance
-> data type conversion is expensive (up to factor 4, e.g. integer->varchar, decimal->fixed)
Solution :
Avoid mixing of data types in the WHERE clause, or when joining fields.
Turn the integer-values into CHAR values : n= 12 -> n = ‘012’, or n = 5 -> n = ‘005’.
© 2011 SAP AG. All rights reserved. 19
Graphical Calculation View –
Best Practices
HANA modeling with Calculation Views

HANA client,
Reporting Tool Create Attribute and Analytic
Views first.
Calculation View Use Calculation Views only as
a second option where the
business requirement can’t be
met with Analytic View only.

Attribute View Tables

Analytical View

© 2011 SAP AG. All rights reserved. 21

Characteristics of Calculation Views

A Calculation View is a column view that is visible to reporting tools

2 types of Calculation Views exist: Graphical & Scripted
Calculation Views include READ-ONLY functions

Calculation View
Column View

© 2011 SAP AG. All rights reserved. 22

 Graphical Calculation View
Design considerations

No SQL Script coding needed.

Union, Join, Projection and Aggregation nodes are available.
Integration of Analytic Views, Attribute Views, Calculation Views and tables.

Attribute
FILTER 1 FILTER .. N View/RAW
table

Analytical View

Copy .. 2 Copy … N

UNION
Combining multiple Analytical Views Calculation View
2 … N (Input Sources)

© 2011 SAP AG. All rights reserved. 23

Graphical Calculation View
Union

The UNION combines data from 2..N input sources, here Projection_A and Projection_B.
Mapped fields can have different names, but the data type must be identical.

Projections can include

Filters, for example :

Projection_A:
KPLIKZ = 0 = Planned

Projection_B:
KPLIKZ = 1 = Actual

© 2011 SAP AG. All rights reserved. 24

Graphical Calculation View
Projection
Projection nodes help to improve performance by narrowing the data set when using a subset
of attributes/measures only (-> aggregation).
Further optimization can be done by applying filters to reduce the initial data set coming from
the underlying Analytic Views where possible.
Projection nodes provide the possibility to define Calculated Columns.
Example: midstr(string("ERDAT"),strlen(string("ERDAT"))-9,4)
Calculated Columns are calculated before aggregation.

© 2011 SAP AG. All rights reserved. 25

Calculated Attributes

Define a Calculated Attribute inside the Calculation View, but not in the underlying Analytic View,
because then the calculation can be performed after aggregation.
Only define a Calculated Attribute in an Attribute View if the Attribute View is small or filtered.
Otherwise, if the number of calculated records is large it affects the performance.

In case a Calculated Attribute needs to be joined (avoid this if possible), use a Calculation View:

AT_View

CA_CALC
AN_View AT_View
© 2011 SAP AG. All rights reserved. 26
Calculated Fields

Avoid expensive calculations, e.g. complex IF, CASE, String expressions, inside
Calculated Fields (Calculated Attributes, Calculated Columns or Calculated Measures)
Calculations are intermediate results that need to be written into buffer and take
additional time.

Avoid calculations which need to be executed before aggregation.

Performance is critical as calculation happens on every single record and needs to be
written into an intermediate runtime buffer, too.
In a Calculation View you can force the aggregation via CE_AGGREGATION
(Aggregation node) and then define the calculation in a subsequent
CE_PROJECTION (Projection node) -> calculation after aggregation.

Avoid using filters or selection criteria on Calculated Fields.

The calculation first needs to be done for all selected records before filtering the data.

© 2011 SAP AG. All rights reserved. 27

Add table column instead of calculation

A calculation on the fly for a Calculated Field may be very expensive. For that reason it
might be (depending on the scenario) better to add a new table column and do the
calculation during the time of inserting a new data set.

Avoid joins on calculated fields, instead you could materialize the calculated join field by
adding an additional column to the source table.

Here is an example on how to add an additional column ‘bc’ to a table for calculated data
resulting in a faster query execution :

ALTER TABLE T ADD (bc INTEGER GENERATED ALWAYS AS b * c);

Example:

© 2011 SAP AG. All rights reserved. 28

 See Exercise C4

JOIN between Attribute View and Analytic View

The Join of an Attribute View with an Analytic View within a Calculation View is slower
than joining the Attribute View within the Analytic View as a dimension table.
Avoid joining big Attribute Views in a
Calculation View.

AN_View AT_View
Reason:
The used engine is different :
Join inside the Calculation View -> ceJoin -> Calculation Engine
Join inside the Analytic View -> BwPopJoin -> OLAP Engine
The Attribute View which is joined within a Calculation View is always scanned
even when the Attribute View does not contain any columns which are specified
in the SELECT statement. The WHERE clause of the Select statement is not pushed
down to the Attribute View. This could lead to massive data movements.
© 2011 SAP AG. All rights reserved. 29
Calculation View – Union and filter on Analytic Views

Use filters in the AN Views to reduce the data SELECT

set moved to the Calc Engine. SPART
SUM(KWMENGA)
SPART KWMENGA KWMENG B KWMENGC TOTAL
SUM(KWMENGB)
ABC 60 180 0 240
SUM(KWMENGC)
EFG 0 0 60 60 SUM(TOTAL)
FROM CALC VIEW
GROUP BY SPART

SPART KWMENGA KWMENGB KWMENGC

CALC ENGINE
ABC 60 0 0

ABC 0 180 0

EFG 0 0 60 Warning!
Refrain from
moving large
Filter to reduce
SELECT SPART, SUM(KWMENG) datasets
between
the data set FROM AV GROUP BY SPART engines.
-> Use Filter

OLAP ENGINE
AV A AV B AV C

SPART KWMENGA SPART KWMENGB SPART KWMENGC

ABC 60 ABC 180 EFG 60

© 2011 SAP AG. All rights reserved. 30

Calculation View – Filter and WHERE clause on AN Views

Even through SPART is not selected as a dimension

from frontend side it is included in the query due to the SELECT
SUM(KWMENGA)
GROUP BY clause. SUM(KWMENGB)
KWMENGA KWMENGB KWMENGC TOTAL SUM(KWMENGC)
60 60 0 120 SUM(TOTAL)
FROM CV
WHERE MATNR = DPC1017
GROUP BY SPART

SPART MATNR KWMENGA KWMENGB KWMENGC

ABC DPC1017 60 0 0

ABC DOC1017 0 60 0 WHERE clause is

pushed down to AN
Views
SELECT SPART, MATNR, SUM(KWMENG)
FROM AV WHERE MATNR = ? …

AV A AV B AV C

SPART MATNR KWMENGA SPART MATNR KWMENGB SPART MATNR KWMENGC

ABC DPC1017 60 ABC DPC1017 60 - - -

© 2011 SAP AG. All rights reserved. 31
 Exercise C1

Calculation View – Filters in Projection and WHERE clause

SELECT
SPART KWMENGA KWMENGB KWMENGC TOTAL SPART
ABC 60 60 0 120 SUM(KWMENGA)
SUM(KWMENGB)
SUM(KWMENGC)
SUM(TOTAL)
FROM CV
WHERE MATNR = DPC1017
GROUP BY SPART
SPART MATNR KWMENGA KWMENGB KWMENGC
Filter inside
ABC DPC1017 60 0 0
Projection node is
ABC DOC1017 0 60 0 pushed down

WHERE clause for

filtering data is
pushed down via
Projection node

The query includes the SELECT SPART, MATNR, WERKS,

filter and the WHERE
clause SUM(KWMENG) FROM AV WHERE General Hint: When AV A..C
WERKS = ? AND MATNR = ? … is the same AN View, always
use copies of the same AN
View instead of only one
AV A AV B AV C common AN View to push
down the WHERE clause.

SPART MATNR WERKS KWMENGA SPART MATNR WERKS KWMENGB SPART MATNR WERKS KWMENGC

ABC DPC1017 100 60 ABC DPC1017 1000 60 - - - -

© 2011 SAP AG. All rights reserved. 32

7 – Aggregation Node SELECT MATNR,
SUM(NETWR_AGG,
SUM(NETWR_PROJ)
FROM CALC VIEW GROUP BY MATNR

Aggregation Level Aggregation Level

MATNR MATNR

Contains attribute Contains attribute

MATNR , measure MATNR , measure
NETWR NETWR

Note! Note!
Difference between NETWR_AGG and OLAP Analytic View transfers
NETWR_PROJ. aggregated data. Less amount of data
Data coming from RAW tables is not transferred. No difference between
aggregated. NETWR_AGG and NETWR_PROJ.

© 2011 SAP AG. All rights reserved. 33

CE functions in Scripted Calculation Views

CE (Calculation Engine) functions should be used in Scripted Calculation Views

exclusively where possible instead of SQL statements (Select …),
The logic of CE Functions is directly implemented in the calculation engine.
Advantage: By using CE functions the Script is executed inside the calculation engine
which may be more efficient and may show better performance results than using SQL.
Client queries can be well optimized and parallelized by the Calculation engine.
Disadvantage: CE functions are not well known -> Higher effort, writing script takes more
time, higher maintenance effort. Outside Calculation Views no obvious and proved
benefit compared to standard SQL.

SQL

Same Logic: Use CE functions instead of SQL (Select …) :

• Parallel query execution
• Only selected fields will be fetched
But:
CE • Writing script takes more time than SQL
(Select …) because CE functions are not
well known -> Higher effort.
© 2011 SAP AG. All rights reserved. 34
CE Functions

CE_COLUMN_TABLE - get the contents of a table

CE_JOIN_VIEW - get attribute view contents
CE_OLAP_VIEW - procedure to get analytical view contents
CE_CALC_VIEW - get calculation view contents
CE_JOIN - inner join of two tables
CE_LEFT_OUTER_JOIN - left outer join
CE_RIGHT_OUTER_JOIN - right outer join
CE_UNION_ALL - union for two tables, identical tuples won’t be dropped
CE_PROJECTION - apply a filter, compute expressions, rename column.
CE_CALC - calculate an expression into a new column
CE_AGGREGATION - aggregation of the data to a group of attributes
Supported aggregation functions: count, sum, min, max, average = sum/count
CE_VERTICAL_UNION - concatenation of columns of two input tables, no counterpart in SQL
CE_CONVERSION - unit conversion of input table, no counterpart in SQL

© 2011 SAP AG. All rights reserved. 35

CE Functions - Only selected fields are fetched
1

CE functions: Only selected fields will be fetched

Even though only MATNR &

NETWR are requested by the
front end tool all fields will be
SQL retrieved from the table including
all calculations will be done
irrespective.

CE Functions Only MATNR & NETWR will be

retrieved from the database.
Only NETWR will be calculated.
Columns requested by the front
end tool can be parallelized.

© 2011 SAP AG. All rights reserved. 36

CE Functions - Only selected fields are fetched
2

Be aware that the semantics of relational operations as used in SQL queries and CE
functions are different.

For example consider a Calculation View that is queried using attribute PUBLISHER
(but not YEAR). The CA View contains CE_AGGREGATION as aggregation node
which is defined on PUBLISHER and YEAR.

Need to know:
Only selected fields are fetched with CE functions -> The grouping on YEAR would
be removed from the grouping. Evidently this reduces the granularity of the grouping,
changing the semantics of the model.

On the other hand, if the CA View contains a SQL based Script containing a grouping
on PUBLISHER and YEAR this aggregation-level would NOT be changed if a query
only uses attribute PUBLISHER.

© 2011 SAP AG. All rights reserved. 37

 SQL vs. CE Functions
Examples 1
SQL CE-Build In Function
SELECT on Column table SELECT CE_COLUMN_TABLE("COLUMN_TABLE", [A, B, C])
A, B, C
from
"COLUMN_TABLE"

SELECT on Attribute view SELECT CE_JOIN_VIEW("ATTRIBUTE_VIEW", [A, B, C])

A, B, C
from
"ATTRIBUTE_VIEW"

SELECT on Analytical view SELECT CE_OLAP_VIEW("ANALYTIC_VIEW", [A, B, C, D]);

A, B, C, SUM(D)
from
"ANALYTIC_VIEW"
GROUP BY
A, B, C

SELECT on Calculation View SELECT CE_CALC_VIEW("ANALYTIC_VIEW", [A, B, C, D]);

A, B, C, SUM(D)
from
“CALC_VIEW"
GROUP BY
A, B, C

WHERE clause SELECT var_tab =

A, B, C, SUM(D) CE_COLUMN_TABLE("COLUMN_TABLE");
from var_proj =
"ANALYTIC_VIEW" CE_PROJECTION(:var_tab, [A, B, C], ' "B" = ''value'' AND "C" = ''value'' ');
WHERE
B = 'value' AND C = 'value'

GROUP BY SELECT var_tab =

A, B, C, SUM(D) CE_COLUMN_TABLE("COLUMN_TABLE");
FROM var_agg =
"COLUMN_TABLE" CE_AGGREGATION(:var_tab, [SUM(D)], [A, B, C]);
GROUP BY
© 2011 SAP AG. All rights reserved. A, B, C 38
 SQL vs. CE Functions
Examples 2
SQL CE-Build In Function
INNER JOIN SELECT A, B, Y, SUM(D) CE_JOIN("COLTAB1","COLTAB2", [KEY1, KEY2], [A, B, Y, D])
from "COLTAB1" INNER JOIN "COLTAB2"
ON
"COLTAB1"."KEY1" = "COLTAB2"."KEY1" AND
"COLTAB1"."KEY2" = "COLTAB2"."KEY2"

LEFT OUTER SELECT A, B, Y, SUM(D) CE_LEFT_OUTER_JOIN("COLTAB1","COLTAB2", [KEY1, KEY2],

JOIN from "COLTAB1" LEFT OUTER JOIN "COLTAB2" [A, B, Y, D])
ON
"COLTAB1"."KEY1" = "COLTAB2"."KEY1" AND
"COLTAB1"."KEY2" = "COLTAB2"."KEY2"

Right OUTER SELECT A, B, Y, SUM(D) CE_RIGHT_OUTER_JOIN("COLTAB1","COLTAB2", [KEY1,

JOIN from "COLTAB1" LEFT OUTER JOIN "COLTAB2" KEY2], [A, B, Y, D])
ON
"COLTAB1"."KEY1" = "COLTAB2"."KEY1" AND
"COLTAB1"."KEY2" = "COLTAB2"."KEY2"

SQL SELECT A, B, C, SUBSTRING(D,2,5) var_tab =

Expressions FROM "COLUMN_TABLE" CE_COLUMN_TABLE("COLUMN_TABLE");
var_proj =
CE_PROJECTION( :var_tab, ["A", "B", "C",
CE_CALC('midstr("D",2,5)', string) ]);

UNION ALL var_tab1 = SELECT A, B, C, D FROM "COLUMN_TABLE1"; var_tab1 =

var_tab2 = SELECT A, B, C, D FROM "COLUMN_TABLE2"; CE_COLUMN_TABLE("COLUMN_TABLE1",[A,B,C,D]);
var_tab2 =
SELECT * FROM :var_tab1 UNION ALL CE_COLUMN_TABLE("COLUMN_TABLE2",[A,B,C,D]);
SELECT * FROM :var_tab2; var_out = CE_UNION_ALL(:var_tab1,:var_tab2);

UNION var_tab1 = SELECT A, B, C, D FROM "COLUMN_TABLE1"; n/a

var_tab2 = SELECT A, B, C, D FROM "COLUMN_TABLE2";
use CE_UNION_ALL
SELECT * FROM :var_tab1 UNION
SELECT * FROM :var_tab2;

© 2011 SAP AG. All rights reserved. 39

 Exercise C7.2: create
CA_SCRIPT_JOIN_1
CE Functions - Example of a Scripted Calculation View
1 Similar to

VAR =
Variable name / SELECT
temporary table FLDATE, CARRID, CONNID, Parameter 1
SEATSMAX Analytic View
FROM Name
"SFLIGHT"."SFLIGHT";

Parameter 2 (optional)
Field names(1 … N)

Similar to

VAR =
SELECT
FLDATE, CARRID, CONNID,
Inner Join between p_1 and p_2 on the AGENCYNUM, SUM(PRICE)
fields FROM
FLDATE, CARRID, CONNID "_SYS_BIC".“<package>/AN_BOOK"
and output of the fields GROUP BY
FLDATE, CARRID, CONNID, FLDATE, CARRID, CONNID,
AGENCYNUM, SEATSMAX, PRICE AGENCYNUM;
© 2011 SAP AG. All rights reserved. 40
 See Exercise C7.1

CE Functions - Example of a Scripted Calculation View

2

var_out = Output table type

Similar to Filter on field

CARRID:
VAR_OUT = Where CARRID =
SELECT * FROM :p_aa ‘AZ’
UNION ALL
SELECT * FROM :p_az ;

© 2011 SAP AG. All rights reserved. 41

 Exercise C7.3: create
CA_SCRIPT_CALC_1
CE Functions - Example of a Scripted Calculation View
3

Function Expression:
Calculated new column
CC_NEW with value ‘1’

Similar to

VAR =
SELECT
"NAME", "CARRID", "CONNID",
1 AS "CC_NEW" , SUM("PRICE")
FROM
"_SYS_BIC".“<package>/AN_BOOK"
WHERE
"CARRID" = 'AZ'
GROUP BY
"NAME", "CARRID", "CONNID";

© 2011 SAP AG. All rights reserved. 42

Tuning - Avoid mixing up SQL and CE Functions
2

When using SQL Script to define a Calculation View,

Do not mix up SQL with CE Functions !

See the HANA SQL Script Guide:

“Mixing CE functions and SQL may lead to missed opportunities to apply
optimizations as CE functions and SQL statements are optimized
independently”.
Each language has its own execution plan engine:
SQL -> SQL processor
CE -> Calculation engine
which have their own optimizer with own execution algorithm and semantic.
Intermediate results need to be transferred between the engines.
The execution plan can’t be optimized to its full extend.

Mixing up SQL and CE Functions is technically possible BUT this might impact the
performance. You may get a worse performance than using only one of them.

© 2011 SAP AG. All rights reserved. 43

Calculation Views –
Pros and Cons of different
designs
 Modeling – Pros and Cons of different View Types

Preferred
Preferred Preferred

2: Analytical 3: Calculation View 4: Calculation View 5: Calculation View

1: Column Table
View (SQL) (CE Functions) (Graphical)
Used for analytical
Used for Good for complex purposes that can’t be
analytical calculations and join modeled using
Used for analytical
Used for simple
purposes where scenarios. purposes that can’t be
Usage applications and Attribute or Analytic
read operations The model is usually modeled using only
showcases. Views. Good for
on mass data is simple and contains Attribute or Analytic Views.
required. only a few fields. complex calculations
and join scenarios.
Very high Client queries can be No SQL or SQL Script
performance on Building calculation well optimized and knowledge required.
No additional
SELECT and views via standard parallelized. Might UNION with Constant
modeling required.
Pros aggregation. well-known SQL give better Values fully supported.
For most clients easy
Supported by language. performance results Client queries can be well
to consume.
modeling. Well Supports UNION. than SQL. Supports optimized and
optimized. UNION. parallelized.
No support for
Client queries can be
analytical privileges,
less optimized and Syntax is
multi language and
Limitations in could significantly be different/difficult
client handling.
regards to slower compared to compared to well-
Complex calculation Limitations in regards to
Cons functions. No other models. SQL known SQL
and logic / currency graphical functions.
UNION statements are static Language.
conversion / security
supported. and will always be Limitations in regards
shifted to client side.
executed regardless of to functions.
In general low
the query statement.
performance.
© 2011 SAP AG. All rights reserved. 45
Architecture

SQL Optimizer

calculation engine

OLAP join
row store engine
engine engine

column store row store

Architecture

SQL Optimizer
calc views

calculation engine
analytic attribute
views views
OLAP join
row store engine
engine engine

column store row store

Modeling Guidance I: Minimize Transfer

calculation engine

minimize
transfer!

OLAP join
engine engine

column store
Modeling Guidance I: Minimize Transfer

join on key
order items delivery items
Modeling Guidance I: Minimize Transfer

group by Region

Calcview with Join

group by Region & Order Pos

Analytic View Analytic View

order items delivery items

Modeling Guidance I: Minimize Transfer

group by Region

UNION

group by Region

Analytic View Analytic View

order items delivery items

Modeling Guidance II: Use Set Processing for Mass Data

Slow as loop
matmoves = SELECT * FROM MSEG works on
FOR EACH matmove in matmoves
item level
IF [Link] = „I“ THEN
plant_text = SELECT plant_text FROM WERKS WHERE id=[Link]
ELSE
plant_text = SELECT plant_text FROM WERKS_EXT WHERE id=[Link]
END
NEXT

[pseudo code]
Modeling Guidance II: Use Set Processing for Mass Data

Calcview with formula:

If which_plant=‚I‘ THEN fast as
calculation
plant_text=plant_int_text
works on output
ELSE granularity +
indicator field
plan_text = plant_ext_text

which_plant, plant_int_text,
plant_ext_text

Analytic View with outer joins

plant mseg plant_ext

Modeling Guidance III: Choose the right Modeling
Scripting Best Practices
Column Engine – Supported query format (joins, filters)

The column engine supports the following join conditions.

– <column reference from one side> = <column reference from the other side>
– Both columns must have the same data type.
– AND operations between column engine join conditions.

The column engine supports the following filter conditions.

– (=,<>, <, >, <= and >=).
– LIKE, IS NULL and IN predicate.
– AND, OR, NOT.

Other types of supported queries.

– CASE..WHEN…THEN

© 2011 SAP AG. All rights reserved. 56

SQL Query tuning for Column Engine – Avoiding
calculations (1)
Using calculations that are natively supported by the column engine is faster than using none native calculations.

Operations with calculations are still slower than operations without calculations and it is better to avoid calculations at all
if possible.

Query 2 is faster as no calculation is performed although the output is the same.

© 2011 SAP AG. All rights reserved. 57

SQL Query tuning for Column Engine – Avoiding
calculations (2)
For example, this CASE logic could be defined in client, example in the BOBJ business layer for a dimension.

© 2011 SAP AG. All rights reserved. 58

SQL Query tuning for Column Engine – Avoiding
calculations (3)
Avoid calculations during query processing by adding a calculated column.

Query 2 is faster as it uses a calculated column called REDUCE.

© 2011 SAP AG. All rights reserved. 59

SQL Query tuning for Column Engine – Avoiding implicit
type casting
An example of implicit type casting is a comparison between a VARCHAR and DATE value, the VARCHAR is implicitly
converted to a DATE.

In the SQL query DATE is a VARCHAR and CURRENT_DATE is a DATE field.

Create both columns with the same type if compared frequently or

Perform explicit type cast per below by converting CURRENT_DATE to a VARCHAR field.

© 2011 SAP AG. All rights reserved. 60

SQL Query tuning for Column Engine – Filter predicate on
OUTER join
The column engine does not natively support filter predicates inside left outer-join predicates.

The example below shows the filter on left table ORDERS for field O_CUSTKEY = ‘7255’ is performed on the Row
engine.

© 2011 SAP AG. All rights reserved. 61

SQL Query tuning for Column Engine – Avoiding filter
predicate on OUTER join
Below is an example of avoiding a filter predicate on LEFT OUTER join by using a generated column.

© 2011 SAP AG. All rights reserved. 62

SQL Query tuning for Column Engine – Cyclic join

The column engine does not natively support join trees that have cycles in join edges if an outer join is involved in
the cycle.

Cyclic inner joins (like below) are natively supported by the column engine, but it is better to avoid them because
their performance is inferior to acyclic inner joins.

Move some of the columns involved in the join to different tables by changing the schema.

Example of slow cyclic inner join.

Example of faster acyclic inner join where column INVOICE_AMT is moved from table INVOICE to table
INVOICE_ITEMS.

© 2011 SAP AG. All rights reserved. 63

SQL Query tuning for Column Engine – EXISTS predicate

Left outer join processing is generally more expensive than inner join processing.

When an EXISTS or NOT EXISTS predicate is connected with other predicates through OR, it is internally mapped
to a left outer join.

In the example below a LEFT OUTER join is performed on INVOICE_ID between tables INVOICE and PAID.

© 2011 SAP AG. All rights reserved. 64

SQL Query tuning for Column Engine – Avoid disjoint
EXISTS predicate
Below is an example of how to avoid such disjoint EXISTS predicates and force an INNER join.

© 2011 SAP AG. All rights reserved. 65

SQL Query tuning for Column Engine – INTERSECT

INTERSECT is not natively supported by the column engine.

© 2011 SAP AG. All rights reserved. 66

SQL Query tuning for Column Engine – Replace
INTERSECT
Replace INTERSECT with SELECT DISTINCT and JOIN.

© 2011 SAP AG. All rights reserved. 67

SQL Query tuning for Column Engine – EXCEPT

EXCEPT is not natively supported by the column engine.

You can use EXCEPT to deduct the results from one results table from those of another.

© 2011 SAP AG. All rights reserved. 68

SQL Query tuning for Column Engine – Replace EXCEPT

Replace EXCEPT with SELECT DISTINCT, WHERE NOT EXISTS.

© 2011 SAP AG. All rights reserved. 69

SQLScript Recommended Practices - Reduce complexity
of SQL statements
Variables in SQLScript enable you to break up a complex SQL statement into many simpler ones.

This makes an SQLScript procedure easier to comprehend.

The two statements below break the complex query into two simpler SQL statements that are linked via table
variables otherwise a sub-query would need to be repeated multiple times.

© 2011 SAP AG. All rights reserved. 70

SQLScript Recommended Practices - Multi-level
aggregation – GROUPING SETS (1)
GROUPING SETS allow multiple levels of grouping to be computed in a single SQL statement.

Query 1 below with GROUPING SETS is equivalent to query 2 with UNION ALL.

© 2011 SAP AG. All rights reserved. 71

SQLScript Recommended Practices - Multi-level
aggregation – GROUPING SETS (2)
To retrieve the different levels of aggregation the client typically has to examine the result repeatedly, e.g. by
filtering by NULL on the grouping attributes.

In the example below the results for the 1st level of aggregation O_ORDERDATE are displayed up to row 2406 then
the results for the 2nd level of aggregation O_CUSTKEY, O_ORDERDATE are displayed.

© 2011 SAP AG. All rights reserved. 72

SQLScript Recommended Practices - Multi-level
aggregation – GROUPING SETS (3)
Also adding GROUPING SETS MULTIPLE RESULTSETS returns the different groupings on different result tabs.

© 2011 SAP AG. All rights reserved. 73

SQLScript Recommended Practices - Multi-level
aggregation – GROUPING SETS (4)
SQLScript can exploit results at a finer grouping.

This could save the client the effort of re-examining the query result by returning the different granularities of
groups in distinct table variables therefore saving the client the effort of re-examining the query result.

Consider the multi-level aggregation before expressed as SQLScript below.

© 2011 SAP AG. All rights reserved. 74

SQLScript Recommended Practices - Understand the
costs of statements (1)
Calculating a UNION ALL of two result sets is cheaper than calculating a UNION.

The reason for this is the additional duplicate elimination performed by the UNION operation.

© 2011 SAP AG. All rights reserved. 75

SQLScript Recommended Practices - Understand the
costs of statements (2)
The explain plan shows the costs of a SQL statement.

In the example below UNION results in an additional DISTINCT operation cost to eliminate duplicates.

© 2011 SAP AG. All rights reserved. 76

SQLScript Recommended Practices - Reduce
dependencies
One of the most important methods for speeding up processing in the HANA DB is a massive parallelization of
executing queries.

Parallelization is exploited at multiple levels of granularity.

The requests of different users can be processed in parallel.

Also single relational operators within a query are executed on multiple cores in parallel.

It is also possible to execute different statements of a single SQLScript in parallel if these statements are
independent of each other.

SQLScript is translated into a dataflow graph and independent paths in this graph can be executed in parallel.

From an SQLScript developer perspective, we can support the database engine in its attempt to parallelize
execution by avoiding unnecessary dependencies between separate SQL statements.

E.g. avoiding variable references between two statements.

E.g. use declarative logic (CE functions) instead of imperative logic (Cursors).

© 2011 SAP AG. All rights reserved. 77

SQLScript Recommended Practices - Simulating function
calls in SQL Statements
Whenever a procedure is called on data that results in a transformation of input tables the intermediate result has
to be bound to a table variable.

In the special case of a read-only procedure that returns a single table a procedure can be created using WITH
RESULT VIEW.

It is then possible to embed a call to the procedure in a SQL SELECT statement as follows.

© 2011 SAP AG. All rights reserved. 78

SQLScript Recommended Practices - Avoid using cursors

Cursors involve a row-at-a-time processing and therefore opportunities for parallel optimizations by the SQL engine
are missed.

For read-only access to a cursor consider using simple selects or joins.

Query 1 and 2 below produce the same output but query 2 is faster.

© 2011 SAP AG. All rights reserved. 79

SQLScript Recommended Practices - Updates and deletes

Cursors involve a row-at-a-time processing and therefore opportunities for parallel optimizations by the SQL engine
are missed.

Query 1 and 2 below produce the same output but query 2 is faster.

© 2011 SAP AG. All rights reserved. 80

SQLScript Recommended Practices - Avoid using
dynamic SQL
Allows for constructing SQL statements at execution time of a procedure.

Executing dynamic SQL is slow because compile time checks and query optimization must be performed for every
invocation of the procedure.

Without proper checks of the variables used, this might create a security vulnerability.

© 2011 SAP AG. All rights reserved. 81

Calculation View – Passing input tables

Currently input parameters for a calculation view are of scalar type, i.e. no input tables allowed.

Multiple values can only be passed as a string.

For example, a calculation view might pass such an input parameter as a search condition on a
table.

The calculation view expects the string (example string of vendors) like
WITH PARAMETERS ('PLACEHOLDER' = ('$$IN_VENDORNAME$$',
‘(AC INDUSTRIES LTD,
GREAT STAR TOOLS CO. LTD.,
CARLTON TRADING CO.,LTD)’ ) with only one set of quotation marks and brackets.

Two options then exist in HANA to interpret this string and use as search condition on a table;
1) Strip the string into substrings and place each vendor entry in a table. Pass this table in WHERE clause.
2) Use LOCATE string function in WHERE clause to check if a substring of the string exists in the table.

© 2011 SAP AG. All rights reserved. 82

Calculation View – procedure to strip string (1)

The example procedure (see notes) strips a string into a table using comma , as the separator
field.

The code trims the leading and trailing brackets from the string. TRIM LEADING/TRIM TRAILING.

The length of the string is taken. LENGTH.

The position of first separator is taken. LOCATE.

The string is searched from the first position 0 to the position of the separator (less 1) to remove
the separator. SELECT SUBSTRING.

This substring is moved to the table. CE_UNION_ALL.

The remaining length is calculated by subtracting the position of the separator from the length.

The new string starts at the position of the separator + 1 plus the remaining length.

The output from the procedure is a table of entries stripped down from the string.

© 2011 SAP AG. All rights reserved. 83

Calculation View – procedure to strip string (2)

There are a couple of options for using the table in the WHERE clause.

SELECT….
WHERE
VENDOR_NAME IN (SELECT string_list FROM :out_tab).

This operation is slow and invokes the ROW ENGINGE.

SELECT….
WHERE
((EXISTS (SELECT string_list FROM :out_tab WHERE vendor_name = :out_tab.string_list))

This option caused a performance issue when trying to activate the calculation view. An optimization here is
planned for a future revision.

© 2011 SAP AG. All rights reserved. 84

Calculation View – use LOCATE string function

Use LOCATE string function to search the string (iv_vendor_name) for a matching entry in the
table field (vendor_name).

Since the string can contain a brackets and commas before or after the vendor name, all
combinations have to be checked.

For example`, the first check below concatenates the table value with a comma before and after
and checks if this is located in the string.

WHERE
(locate(:iv_vendor_name, ','||"vendor_name"||',') != 0) OR
(locate(:iv_vendor_name, '('||"vendor_name"||',') != 0) OR
(locate(:iv_vendor_name, ','||"vendor_name"||')') != 0) OR
(locate(:iv_vendor_name, '('||"vendor_name"||')') != 0)
)

This option was fast and caused no performance issues when activating the calculation view.

© 2011 SAP AG. All rights reserved. 85

Calculation View – pass WHERE condition to Calc View (1)

It is possible to pass a WHERE clause to a calculation view.

This will automatically be applied as a filter to subsequent selects on analytic view or tables inside
the calculation view.

With latest HANA DB revision the filter is pushed down to Analytic View with better performance.

© 2011 SAP AG. All rights reserved. 86

Thank You!

SAP AGS
© 2011 SAP AG. Alle Rechte vorbehalten.

Weitergabe und Vervielfältigung dieser Publikation oder von Teilen daraus sind, HTML, XML, XHTML und W3C sind Marken oder eingetragene Marken des
zu welchem Zweck und in welcher Form auch immer, ohne die ausdrückliche W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.
schriftliche Genehmigung durch SAP AG nicht gestattet. In dieser Publikation SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects
enthaltene Informationen können ohne vorherige Ankündigung geändert werden. Explorer, StreamWork und weitere im Text erwähnte SAP-Produkte und -
Die von SAP AG oder deren Vertriebsfirmen angebotenen Softwareprodukte Dienstleistungen sowie die entsprechenden Logos sind Marken oder eingetragene
können Softwarekomponenten auch anderer Softwarehersteller enthalten. Marken der SAP AG in Deutschland und anderen Ländern.
Microsoft, Windows, Excel, Outlook, und PowerPoint sind eingetragene Marken Business Objects und das Business-Objects-Logo, BusinessObjects, Crystal
der Microsoft Corporation. Reports, Crystal Decisions, Web Intelligence, Xcelsius und andere im Text
IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, erwähnte Business-Objects-Produkte und Dienstleistungen sowie die
System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, entsprechenden Logos sind Marken oder eingetragene Marken der Business
zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Objects Software Ltd. Business Objects ist ein Unternehmen der SAP AG.
Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6, Sybase und Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere und
POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes, weitere im Text erwähnte Sybase-Produkte und -Dienstleistungen sowie die
BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, entsprechenden Logos sind Marken oder eingetragene Marken der Sybase Inc.
Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere, Sybase ist ein Unternehmen der SAP AG.
Netfinity, Tivoli und Informix sind Marken oder eingetragene Marken der IBM Alle anderen Namen von Produkten und Dienstleistungen sind Marken der
Corporation. jeweiligen Firmen. Die Angaben im Text sind unverbindlich und dienen lediglich zu
Linux ist eine eingetragene Marke von Linus Torvalds in den USA und anderen Informationszwecken. Produkte können länderspezifische Unterschiede
Ländern. aufweisen.
Adobe, das Adobe-Logo, Acrobat, PostScript und Reader sind Marken oder Die in dieser Publikation enthaltene Information ist Eigentum der SAP. Weitergabe
eingetragene Marken von Adobe Systems Incorporated in den USA und/oder und Vervielfältigung dieser Publikation oder von Teilen daraus sind, zu welchem
anderen Ländern. Zweck und in welcher Form auch immer, nur mit ausdrücklicher schriftlicher
Oracle und Java sind eingetragene Marken von Oracle und/oder ihrer Genehmigung durch SAP AG gestattet.
Tochtergesellschaften.
UNIX, X/Open, OSF/1 und Motif sind eingetragene Marken der Open Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame und
MultiWin sind Marken oder eingetragene Marken von Citrix Systems, Inc.

© 2011 SAP AG. All rights reserved. 88