primary key - SAP HANA

sunfloweremryologistData Management

Oct 31, 2013 (4 years and 8 days ago)

144 views

Analytic Views

What are they and how do we use them?

Internal


©
2011 SAP AG. All rights reserved.

2

Internal

400


700


86


What is an Analytic View?

An Analytic View

is an
OLAP Cube
,

an n
-
dimensional

array of data.


OLAP Cubes in business
applications usually
organize economic
numbers along

business categories.

Ship
-
to

Party

Material

Sales Org

SAP

IBM

RWE

DB

DE

AU

CH

US

4711

4712

4713

4714

Sales Org „DE“ shipped
Material „4713“ to Ship
-
to
Party „IBM“ at value „400
EUR“.

400


250


76


©
2011 SAP AG. All rights reserved.

3

Internal

Dimension ...

Dimension Key

Attribute 1

Attribute 2

Attribute ...

Classical Data Warehouse

Classical data warehouses
copy data into OLAP
Cubes in a 3
-
step process
called
Extract
-
Transform
-
Load (ETL)
.


Copies are
materialized
,
i.e. the data is saved to
physical tables in its
transformed state.



Table 1

Primary Key

Field 1

Field 2

Field ...

Table 2

Primary Key

Field 1

Field 2

Field ...

Table ...

Primary Key

Field 1

Field 2

Field ...

ERP

Table 1

010010110
110101110
10101110...

Table 2

010010110
110101110
10101110...

Table ...

010010110
110101110
10101110...

Facts

010010110
110101110
100101011
10101110...

Dim 1

010010110
110101110
100101011
10101110...

Fact Table

Primary Key

Fact 1

Fact 2

Fact ...

Dimension 1

Dimension Key

Attribute 1

Attribute 2

Attribute ...

Data Warehouse,
e.g. SAP Business Warehouse

Dimension 2

Dimension Key

Attribute 1

Attribute 2

Attribute ...

Dim 2

010010110
110101110
100101011
10101110...

Dim ...

010010110
110101110
100101011
10101110...

OLAP Cube,
also: Info Cube

1. Extract

2. Transform

3. Load

Report

©
2011 SAP AG. All rights reserved.

4

Internal

On
-
the
-
Fly Cubes

HANA reports directly from
physical tables.


Data is transformed on
-
the
-
fly, i.e. there is no
materialization.


An Analytic View is not a
transformed copy of data,

it is
the plan
how to
transform data on
-
the
-
fly.

Table 1

Primary Key

Field 1

Field 2

Field ...

Table 2

Primary Key

Field 1

Field 2

Field ...

Table ...

Primary Key

Field 1

Field 2

Field ...

ERP

Report







Transform

Analytic
View

©
2011 SAP AG. All rights reserved.

5

Internal

Why OLAP?

OLAP Cubes come along
with a set of efficient
transformations that
simplify analytics.

Ship
-
to

Party

Material

Sales Org

SAP

IBM

4712

4713

4714

DE

4711

AU

CH

US

RWE

DB

Slice

Ship
-
to

Party

Sales Org

SAP

IBM

4712

4713

4714

DE

4711

AU

CH

US

RWE

DB

Dice

Year

Material

Sales Org

2011

2010

4712

4713

4714

DE

4711

AU

CH

US

2009

2008

Rotate

Month

Material

Sales Org

Apr

Mar

4712

4713

4714

DE

4711

AU

CH

US

Feb

Jan

Drill Down

©
2011 SAP AG. All rights reserved.

6

Internal

N
-
Dimensionality

In contrast to a physical
table, an Analytic View is
not „flat“.


Data must be „flattened
out“ during extraction by
aggregating along the
dimensions of the data
cube.

Ship
-
to

Party

Material

Sales Org

SAP

IBM

RWE

DB

DE

AU

CH

US

4711

4712

4713

4714

Delivery

Sales
Org

Material

Net
Value


1

DE

4713

400 EUR

2

DE

4711

500 EUR

3

CH

4712

750 EUR

4

US

4712

900 EUR

©
2011 SAP AG. All rights reserved.

7

Internal

Extracting Tables from Cubes

In terms of SQL,

„flattening out“ means
reading with a GROUP BY
clause and aggregation
functions (SUM, AVG,
MIN, MAX).


Ship
-
to

Party

Material

Sales Org

SAP

IBM

RWE

DB

DE

AU

CH

US

4711

4712

4713

4714

Delivery

Sales
Org

Material

Net
Value


1

DE

4713

400 EUR

2

DE

4711

500 EUR

3

CH

4712

750 EUR

4

US

4712

900 EUR

SELECT


Delivery,


SalesOrg,


Material,


SUM
(NetValue)


FROM OLAPCube


GROUP BY Delivery, SalesOrg, Material
;

©
2011 SAP AG. All rights reserved.

8

Internal

Joining Cubes

As Joins are not specified
for n
-
dimensional data,
Analytic Views cannot be
joined to flat tables.


Data must be extracted
and flattened out before
joining.


Ship
-
to

Party

Material

Sales Org

SAP

IBM

RWE

DB

DE

AU

CH

US

4711

4712

4713

4714

Delivery

Sales
Org

Material

Net
Value


1

DE

4713

400 EUR

2

DE

4711

500 EUR

3

CH

4712

750 EUR

4

US

4712

900 EUR

©
2011 SAP AG. All rights reserved.

9

Internal

Technical View Schema

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Technically, an Analytic
View is a set of physical

tables, interconnected

in a
star schema
.


©
2011 SAP AG. All rights reserved.

10

Internal

Star Schema

Fact Table

Primary

Key

Foreign Key A

Foreign Key B

Foreign Key C

Foreign Key

D

Fact 1

Fact 2

Fact



Dimension B

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

A classical star schema
arranges a set of
dimension tables

around a
fact table

in a
star
-
shaped

form.

Dimension C

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension A

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension D

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

©
2011 SAP AG. All rights reserved.

11

Internal

Fact Table

Fact Table

Primary

Key

Foreign Key A

Foreign Key B

Foreign Key C

Foreign Key

D

Fact 1

Fact 2

Fact



Dimension B

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Only the fact table
provides the information
the user is actually
interested in:


The
facts
, also referred to
as
metrics
,
measures
, or
key figures
.


Facts generally are
numbers,

such as

„Sales Net Revenue“.

Dimension C

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension A

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension D

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

©
2011 SAP AG. All rights reserved.

12

Internal

Primary Key of a Star Schema

Fact Table

Primary

Key

Foreign Key A

Foreign Key B

Foreign Key C

Foreign Key

D

Fact 1

Fact 2

Fact



Dimension B

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

The fact table also
determines the

primary key
of the

star schema as a whole.


Each result line produced
from the star schema
at
the maximum possible
level of detail*

is uniquely
identified by this key.










* The maximum possible level of
detail is achieved by numbering
all

dimension attributes in the GROUP
BY clause of the SELECT, thus
preventing aggregation.

Dimension C

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension A

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension D

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

©
2011 SAP AG. All rights reserved.

13

Internal

Dimension Tables

Fact Table

Primary

Key

Foreign Key A

Foreign Key B

Foreign Key C

Foreign Key

D

Fact 1

Fact 2

Fact



Dimension B

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

The dimension tables
provide the
categories
,
classes
, or

attributes,
by
which the facts can be
grouped.

Dimension C

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension A

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension D

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

©
2011 SAP AG. All rights reserved.

14

Internal

Star Schema to OLAP Cube

Fact Table

Primary

Key

Foreign Key A

Foreign Key B

Foreign Key C

Foreign Key

D

Fact 1

Fact 2

Fact



Dimension B

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

The fact table provides the
content of the OLAP Cube
(the „what“ to group).


Dimension tables specify
the Cube‘s dimensions
(the „by what“ to group).

Dimension C

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension A

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension D

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Ship
-
to

Party

Material

Sales Org

SAP

IBM

RWE

DB

DE

AU

CH

US

4711

4712

4713

4714

©
2011 SAP AG. All rights reserved.

15

Internal

Inner Table

In the Analytic View,

the fact table is

called
inner table,

or
central table
.


It is one of the tables in

the
Data Foundation
.

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

©
2011 SAP AG. All rights reserved.

16

Internal

Outer Tables

The other tables in the
Data Foundation are
dimension tables, and
called
outer tables
.

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

©
2011 SAP AG. All rights reserved.

17

Internal

Primary Key of an Analytic View

The inner table determines
the primary key of the
Analytic View.


Each result line produced
from the Analytic View
at
the maximum possible
level of detail*
is uniquely
identified by this key.














* Again, the maximum possible level
of detail is achieved by numbering
all

dimension attributes in the GROUP
BY clause of the SELECT, thus
preventing aggregation.






Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

©
2011 SAP AG. All rights reserved.

18

Internal

Measures

Only the inner table
provides facts,

here called
Measures
.


Providing Measures from
outer tables would violate
the star schema.

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

©
2011 SAP AG. All rights reserved.

19

Internal

Aggregation Type

Each measure must be
provided with an
aggregation type
.


The aggregation type
specifies how two lines of
the fact table will be
merged.


Valid aggregation types
are
SUM, AVG, MIN
and
MAX
.

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

©
2011 SAP AG. All rights reserved.

20

Internal

Distributed Dimensions

Fact Table

Primary

Key

Foreign Key A

Foreign Key B

Foreign Key C

Foreign Key

D

Fact 1

Fact 2

Fact



Dimension B

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

In non
-
star schemas,
information on a single
dimension can be
distributed over multiple
sub
-
tables.

Dimension C

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension A

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension D

Dimension
Key

Foreign Key

Attribute 1

Attribute 2

Attribute ...

Dim D Sub 1

Dimension
Key

Attribute 4

Attribute 5

Attribute ...

Dim D Sub 2

Dimension
Key

Attribute 6

Attribute 7

Attribute ...

©
2011 SAP AG. All rights reserved.

21

Internal

Distributed Dimensions in the Data Foundation

Distributed dimensions can
be modelled as outer
tables in the Data
Foundation.


However, it is preferrable
to model them as Attribute
Views, see below.

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

©
2011 SAP AG. All rights reserved.

22

Internal

Attribute Views

Dimensions are modelled
best as
Attribute Views

to enable reuse in multiple
Analytic Views.

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Attribute View

Attribute View

Attribute View

Attribute
View

©
2011 SAP AG. All rights reserved.

23

Internal

Duplicate Attribute Views

Data cannot be arranged
along the same dimension
multiple times.


As one Attribute View
models one dimension, it
therefore does not make
sense to include the same
Attribute View in an
Analytic View twice.

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Attribute View

Attribute View

Attribute
View

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Attribute View

©
2011 SAP AG. All rights reserved.

24

Internal

Join Types in Star Schemas

Fact Table

Primary

Key

Foreign Key A

Foreign Key B

Foreign Key C

Foreign Key

D

Fact 1

Fact 2

Fact



Dimension B

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Joins in star schemas are
Left Outer Joins

resp.

Right Outer Joins

by definition
.


Definition of the fact table
enforces this:


The fact table provides the
valuable information


its lines
must be kept,

and
must not be made up
.


Because of this, explicit
documentation of the

join type can be omitted.

Dimension C

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension A

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension D

Dimension
Key

Attribute 1

Attribute 2

Attribute ...



Left

Outer

Join



Right

Outer

Join

This diagram distinguishes between Left and Right Outer Join because of the
visual arrangement of the tables. As the two are fully symmetrical, actual code
or models will usually only use one of them.

©
2011 SAP AG. All rights reserved.

25

Internal

Wrong Join Types in Star Schemas

Fact Table

Primary

Key

Foreign Key A

Foreign Key B

Foreign Key C

Foreign Key

D

Fact 1

Fact 2

Fact



Dimension B

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Other join types in a star
schema are
wrong
:


Inner Joins
lose facts with
fragmented dimensions.


Reversed and
Full

Outer
Joins
make up facts for
unused dimension entries.

Dimension C

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension A

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension D

Dimension
Key

Attribute 1

Attribute 2

Attribute ...



Full

Outer

Join



Inner

Join

Loses deliveries whose
shipping terms have not
yet been entered

Fakes deliveries for plants,
orgs and parties which
actually have none



Right

Outer

Join



Left

Outer

Join

©
2011 SAP AG. All rights reserved.

26

Internal

Cardinality in Star Schemas

Fact Table

Primary

Key

Foreign Key A

Foreign Key B

Foreign Key C

Foreign Key

D

Fact 1

Fact 2

Fact



Dimension B

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Cardinality in star schemas
is
N:1 fact to dimension

by definition
.


Cardinalities N and M on
dimensions are
wrong
:
they violate uniqueness

of the primary key.


Cardinality 1:1 is possible
but contradicts
dimension
independency:

such tables
should be merged with the
fact table instead of being
dimensions of their own.


Because of this, explicit
documentation of
cardinality can be omitted.



Dimension C

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension A

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

Dimension D

Dimension
Key

Attribute 1

Attribute 2

Attribute ...

N

1

1

N

1

M

1

N

©
2011 SAP AG. All rights reserved.

27

Internal

Joins to Attribute Views

Due to the star schema,
joins to Attribute Views

in Analytic Views are

Left or Right Outer Joins
with cardinality N:1

by nature
.

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Attribute View

Attribute View

Attribute View

Attribute
View







This diagram distinguishes between Left and Right Outer Join because of the visual arrangement of the tables. As the two are
ful
ly
symmetrical, actual code or models will usually only use one of them.

1

N

N

1

1

1

N

N



©
2011 SAP AG. All rights reserved.

28

Internal

Material

Material ID

Material Class

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Attribute View

Restrictions on Attribute Views

Attribute Views are natural
extensions to the Data
Foundation. Their joins and
cardinalities must follow
the star schema.


This gives the Attribute
View star shape, with one
central table connecting to
the Data Foundation.


Joins in Attribute Views
must have cardinality 1

on
the „more outer“ table.

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Status
Descriptions

Status Type

Status Value

Language

Description

N

N

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Attribute View

Attribute View

Attribute
View

©
2011 SAP AG. All rights reserved.

29

Internal

Omit Joins

Analytic Views improve
performance by omitting
joins which are not needed
to serve a request.



SELECT


DeliveryID,


ItemID,


PickingStatus,


MaterialClassName,


SUM
( Value)


FROM

AnalyticView


GROUP BY


DeliveryID,


ItemID,


PickingStatus,


MaterialClassName;

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Attribute View

©
2011 SAP AG. All rights reserved.

30

Internal

Invariant Joins

A join from a left table A to
a right table B can be
omitted, if:


a) no fields are requested
from B or tables beyond,


and


b) the join is
invariant
,

i.e. it
does not have the
potential to increase or
decrease the size of the
result set.


A

Primary

Key

Foreign

Key

1

A

2

A

3

D

B

Primary

Key

A

B

C

Result

A.Primary

Key

B.Primary

Key

1

A

2

A

3

null





A

Primary

Key

Foreign

Key

1

A

2

A

3

D

B

Primary

Key

A

B

C

Result

A.Primary

Key

B.Primary

Key

1

A

2

A

null

B

null

C

=

=

Right

Outer Join

between A and B

with cardinality

N:1

Left

Outer Join

between A and B

with cardinality

N:1

=

©
2011 SAP AG. All rights reserved.

31

Internal

Omittable Join Types

Due to the invariancy,
whether a join can be
omitted depends on the
join type
and
cardinality
.

Starting from a left table A, can a join to a right table B be
omitted if no fields from B (or tables beyond) are requested?

Join Type


Cardinality

Inner

Left

Outer

Right
Outer

Full

Outer

1:1

No

Yes

No

No

1:N

No

No

No

No

N:1

No

Yes

No

No

N:M

No

No

No

No

©
2011 SAP AG. All rights reserved.

32

Internal

Joins in the Data Foundation

Joins in the Data
Foundation generally
follow the same ideas as
joins to Attribute Views,

i.e. by default they are

Left or Right Outer Joins
with cardinality N:1 from
fact to dimension
.

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Attribute View

Attribute View

Attribute View

Attribute
View



N

1

©
2011 SAP AG. All rights reserved.

33

Internal

Inner Joins in Data Foundation

In practice, fact tables can
be joined from multiple
physical tables.


HANA does not offer a
separate abstraction layer
for this


it must be done
in the Data Foundation.


Use

Inner Joins in the
Data Foundation if
business semantics
require so
.


Do this with care, it
prevents join omittance
and thus has negative
performance impact.

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Attribute View

Attribute View

Attribute View

Attribute
View



Inner

Join

N

1

Business semantics: Do not
show items without a header

©
2011 SAP AG. All rights reserved.

34

Internal

Acyclicness

Any possible chain of joins
in the Analytic View must
be
acyclic
.

Business Partner

Business Partner ID

Name

Address

Contact

Address

Address

ID

Street

City

Postal

Code

Country

Org

Unit

Org

Unit ID

Name

Manager

Employee

Employee

ID

Name

Material

Material ID

Material Class

Country

Descriptions

Country Code

Language

Description

Person

Person ID

Last Name

First Name

Material
Descriptions

Material ID

Language

Description

Material

Class

Material Class ID

Name

Status
Descriptions

Status Type

Status Value

Language

Description

Data Foundation

Delivery

Header

Delivery

ID

Delivery

Date

Ship
-
to

Party

Sold
-
to

Party

Sales
Org

Delivery

Item

Delivery

ID

Item ID

Material

Value

Delivery

Item Status

Delivery

ID

Item ID

Picking

Status

Attribute View

Attribute View

Attribute View

Attribute
View

©
2011 SAP AG. All rights reserved.

35

Internal

Essence


Analytic Views are not flat,


but multi
-
dimensional arrays of data



Use N:1 Left Outer Joins


from Data Foundation


to Attribute Views



Use N:1 Left Outer Joins


from fact to dimension


within Data Foundation



Use Inner Joins in the Data Foundation


only if business semantics require so

©
2011 SAP AG. All rights reserved.

36

Internal

No part of this publication may be reproduced or transmitted in any form or for any purpose
without the express permission of SAP AG. The information contained herein may be
changed without prior notice.

Some software products marketed by SAP AG and its distributors contain proprietary
software components of other software vendors.

Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft
Corporation.

IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x,
System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries, eServer,
z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server,
PowerVM, Power Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER,
OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP,
RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX,
Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered
trademarks of IBM Corporation.

Linux is the registered trademark of Linus Torvalds in the U.S. and other countries.

Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or
registered trademarks of Adobe Systems Incorporated in the United States and/or other
countries.

Oracle is a registered trademark of Oracle Corporation.

UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.

Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are
trademarks or registered trademarks of Citrix Systems, Inc.

HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World
Wide Web Consortium, Massachusetts Institute of Technology.

Java is a registered trademark of Sun Microsystems, Inc.

JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for
technology invented and implemented by Netscape.

SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer,
StreamWork, and other SAP products and services mentioned herein as well as their
respective logos are trademarks or registered trademarks of SAP AG in Germany and other
countries.

©
2011 SAP AG. All rights reserved

Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal
Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services
mentioned herein as well as their respective logos are trademarks or registered trademarks
of Business Objects Software Ltd. Business Objects is an SAP company.

Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere, and other Sybase
products and services mentioned herein as well as their respective logos are trademarks or
registered trademarks of Sybase, Inc. Sybase is an SAP company.

All other product and service names mentioned are the trademarks of their respective
companies. Data contained in this document serves informational purposes only. National
product specifications may vary.

The information in this document is proprietary to SAP. No part of this document may be
reproduced, copied, or transmitted in any form or for any purpose without the express prior
written permission of SAP AG.

This document is a preliminary version and not subject to your license agreement or any
other agreement with SAP. This document contains only intended strategies, developments,
and functionalities of the SAP® product and is not intended to be binding upon SAP to any
particular course of business, product strategy, and/or development. Please note that this
document is subject to change and may be changed by SAP at any time without notice.

SAP assumes no responsibility for errors or omissions in this document. SAP does not
warrant the accuracy or completeness of the information, text, graphics, links, or other items
contained within this material. This document is provided without a warranty of any kind,
either express or implied, including but not limited to the implied warranties of
merchantability, fitness for a particular purpose, or non
-
infringement.

SAP shall have no liability for damages of any kind including without limitation direct,
special, indirect, or consequential damages that may result from the use of these materials.
This limitation shall not apply in cases of intent or gross negligence.

The statutory liability for personal injury and defective products is not affected. SAP has no
control over the information that you may access through the use of hot links contained in
these materials and does not endorse your use of third
-
party Web pages nor provide any
warranty whatsoever relating to third
-
party Web pages.