Ha300 en Col97 Fv Show[1]

HA300 - SAP HANA Implementation and Modeling Collection 97 SPS4

DISCLAIMER This presentation and SAP's strategy and possible future developments are subject to change and may be changed by SAP at any time for any reason without notice. 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 assumes no responsibility for errors or omissions in this document, except if such damages were caused by SAP intentionally or grossly negligent. © SAP 2012

© 2012 SAP AG. All rights reserved.

2

Copyright © 2012 SAP AG. All rights reserved.

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, PowerPoint, Silverlight, and Visual Studio 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, z10, z/VM, z/OS, OS/390, zEnterprise, PowerVM, Power Architecture, Power Systems, POWER7, POWER6+, POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System Storage, Storwize, XIV, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Informix, and Smarter Planet are trademarks or registered trademarks of IBM Corporation. Linux is the registered trademark of Linus Torvalds in the United States and other countries. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are trademarks or registered trademarks of Adobe Systems Incorporated in the United States and other countries. Oracle and Java are registered trademarks of Oracle and its affiliates. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.

Google App Engine, Google Apps, Google Checkout, Google Data API, Google Maps, Google Mobile Ads, Google Mobile Updater, Google Mobile, Google Store, Google Sync, Google Updater, Google Voice, Google Mail, Gmail, YouTube, Dalvik and Android are trademarks or registered trademarks of Google Inc. INTERMEC is a registered trademark of Intermec Technologies Corporation. Wi-Fi is a registered trademark of Wi-Fi Alliance. Bluetooth is a registered trademark of Bluetooth SIG Inc. Motorola is a registered trademark of Motorola Trademark Holdings LLC. Computop is a registered trademark of Computop Wirtschaftsinformatik GmbH. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, StreamWork, SAP HANA, 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. 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.

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

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.

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

Crossgate, m@gic EDDY, B2B 360°, and B2B 360° Services are registered trademarks of Crossgate AG in Germany and other countries. Crossgate is an SAP company.

Apple, App Store, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, Multi-Touch, Objective-C, Retina, Safari, Siri, and Xcode are trademarks or registered trademarks of Apple Inc.

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.

IOS is a registered trademark of Cisco Systems Inc. RIM, BlackBerry, BBM, BlackBerry Curve, BlackBerry Bold, BlackBerry Pearl, BlackBerry Torch, BlackBerry Storm, BlackBerry Storm2, BlackBerry PlayBook, and BlackBerry App World are trademarks or registered trademarks of Research in Motion Limited.


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.

3

Agenda SAP HANA Implementation and Modeling Unit 1: Approaching SAP HANA Modeling Unit 2: Connecting Tables Unit 3: Advanced Modeling Unit 4: Full Text Search Unit 5: Processing Information Models Unit 6: Managing Modeling Content Unit 7: Security and Authorizations Unit 8: Data Provisioning using SLT Unit 9: Data Provisioning using SAP Data Services Unit 10: Data Provisioning using Flat File Upload Unit 11: Data Provisioning using Direct Extractor Connection © 2012 SAP AG. All rights reserved.

4


1

Unit 1: Approaching SAP HANA Modeling Lesson1: Best practice guidelines

Objectives Approaching SAP HANA Modeling

At the end of this Lesson you will be able to:

Take into account Persistency Considerations

Explain the different engine types in the SAP HANA Architecture

To choose the best views for the Information Model

To discuss some General Recommendations


3

Overview Approaching SAP HANA Modeling This module covers the following topics:

Persistency Considerations

SAP HANA Engine Overview

Choosing Views for the Information Model


4

Client

Persistency Considerations I Approaching SAP HANA Modeling

Queries

Column Table

Select a, b, c FROM persistence_model

Column View

NewDB

Analytic View Attribute View Data Foundation

1


Attribute View Attribute View

RowTable

Calculation View

Calculation View

… …

… …

Via SQL Statements

Out = SELECT a, b, c FROM table:

2

Via CE Build-In function

Out = CE_PROJECTION (: data, [ a, b, c ] ;

3

4

5

5

Persistency Considerations II Approaching SAP HANA Modeling 2: Analytical View

3: Calculation View (SQL)

Usage

Good for quick start with HANA. Shall be used for simple applications and showcases.

Most recommended for analytical purpose, where read operations on mass data is required.

Good for quick build of scenarios with complex calculations. The model is usually simple and contains only a few fields.

Recommended for analytical purpose using complex calculation, which can not be expressed in an analytic view.

Pros

No additional modeling required. For most clients easy to consume.

Very high performance on SELECT. Supported by modeling. Well optimized.

Building calculation views via SQL syntax is easy.

Client queries can be well optimized and parallelized. Usually better performance results than SQL.

Limitations in regards to functions.

Client queries can be less optimized and could significantly be slower compared to other models.

Syntax is different compared to well-known SQL Language.

Cons

No support for analytical privileges, multi language and client handling. Complex calculation and logic shifted to client side. In general low performance.

1: Column Table


4: Calculation View (CE Functions)

6

SAP HANA Engine Overview I Approaching SAP HANA Modeling

SQL Optimizer Calculation Engine OLAP Engine

Join Engine

Column Store


Row Store Engine

Row Store

7

SAP HANA Engine Overview II Approaching SAP HANA Modeling

Calculation Views

Calculation Engine Analytic Views


OLAP Engine

Join Engine

Attribute Views

8

General Modeling Principles Avoid transfer data of large resultsets between the HANA DB and client application

Client / Application A

G

Y

Z

- Do calculation after aggregation. - Avoid Complex expressions (IF, CASE, ... )

Calculation Views

Reduce data transfer between views

Attribute Views

Analytical Views

A

G

Y

A

B

C

Aggregate data records (e.g using GROUP BY, reducing Coulmns) D

G

Y

Join on Key Columns or Indexed Columns Avoid calculations before aggregation on line item level

A

B

C

D

Column Store


Filter data amount as early as possible in the lower layers (CONSTRAINTS, WHERE Clause, Analytical Privileges..) 9

Choosing Views for the Information Model Approaching SAP HANA Modeling Analyze Data in HANA DB

Yes Use Analytic View

Use Starschema or Aggregation?

Yes Use Attribute View

No Only Joins and Calculated Expressions?

OK Use Graphical Calculation View

No Try Graphical Calculation View

OK Use CalcScenario or using CE-Functions


Not enough Use CalcScenario or Scripted CalcView with CE-Functions

Not enough Use Scripted Calculation View or Procedures

10

Summary Approaching SAP HANA Modeling

You should now be able to:

Take into account Persistency Considerations

Explain the different engine types in the SAP HANA Architecture

To choose the best views for the Information Model

To discuss some General Recommendations


11


1

Objectives Connecting Tables


Explain differences between Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, Text Join and referential Join when connecting tables.

Explain how using Standard Union and Union with constant values.


2

Business Example Connecting Tables Sales Order

We want to connect the Sales Order table to the Customer table linked to the State table.


Customer

State

3

Overview Connecting Tables This module covers the following topics: How to connect tables using Inner Left

Outer Join

Right Full

Join

Outer Join

Outer Join

Text

Join

Referential

Join

Union


4

Join Types – Definitions and Referential Integrity Join Type

Use when you need to report on…

Be aware that…

INNER

facts with matching dimensions only

facts without any dimension will be excluded dimensions without any fact will be excluded JOIN is always performed

LEFT OUTER

all posted facts whether there is a matching dimension or not

dimensions without any fact will be excluded best for performance because JOIN is omissible

RIGHT OUTER

all dimensions whether there are matching facts or not

facts without any dimension will be excluded JOIN is always performed

REFERENTIAL

facts for the requested dimensions AND referential integrity is ensured

it is the default join type acts as an INNER for Attributes Views join on attribute views is “optional”, which facts are

an SAP dimension table joined to a text table for translation purpose

only available for Attribute Views with SAP ERP tables (SPRAS field) or equivalent design acts as an INNER

TEXT


returned will depend on which attributes are queried for

5

Inner Join – Attribute View Connecting Tables Inner Join returns rows when there is at least one match in both sides of the join. Inner is used even if it’s not added.

Ä

Attribute View Ä Customer (3 & 4) is not returned due to no corresponding entry (TX) in the state table.


6

Inner Join – Analytical View Connecting Tables Analytical View

Ä


Ä Be aware that Inner Joins

lose facts with fragmented dimensions. Order (4 & 77) lost due to no corresponding customer and state record

7

Inner Join and Design Time Filters Connecting Tables

Design time filter applied (AGE < 13) of left/central table

Design time filter applied to (STATE = MI) on right table

Both design time filters are applied first before the join is executed


8

Left Outer Join – Attribute View Connecting Tables

Left Outer Join returns all rows from the left table even if there are no matches in the right table.

This join is popular in Analytical Views whereby the Attribute view is joined to the fact table.

Attribute View

Ä Ä Ä No matches for TX in the right table. © 2012 SAP AG. All rights reserved.

9

Left Outer Join – Analytical View Connecting Tables Analytical View

Ä


Ä Customer (TOM) is not returned due to no corresponding sale item record in sales order table.

10

Left Outer Join and Design Time Filters Connecting Tables

Design time filter applied (AGE < 13) of left/central table

Design time filter applied to (STATE = MI) on right table

Filters are applied to both tables and then afterwards the join is executed. Due to the left outer join TOM will be included in the result set even though he resides in TX


11

Right Outer Join – Attribute View Connecting Tables

Right Outer Join returns all the rows from the right table, even if there are no matches in the left table.

Attribute View

Ä Alabama is included in the result set, though there is no match in the left table.


12

Right Outer Join – Analytical View Connecting Tables Analytical View

Ä Ä Right Outer Join results in NULL measure.


13

Full Outer Join Connecting Tables

Full Outer Join is neither left nor right - it's both. It includes all the rows from both of the tables or result sets participating in the Join.

When no matching rows exist for rows on the left side or right side of the Join, you see NULL values.

? ? ? ? 1 WERNER 10 MI 2 MARK 11 MI 3 TOM 12 TX 4 BOB 13 TX C_ID CNAME AGE STATE © 2012 SAP AG. All rights reserved.

STATE SNAME AL ALABAMA MI MICHIGAN MI MICHIGAN ? ? ? ?

14

Text Join Connecting Tables

Text Join are used to join a text table to a master data table.

Text Joins acts as a Left Outer join and can be used with SAP tables where the language column (SPRAS) is present.

For each attribute it is possible to define a description mapping that will be specific to the end users language.


15

Join Types – Text Join for multilingual reporting

Text Join is used when translation for a dimension is available

Designed for ERP table (and typically SPRAS field)

User language is used as a filter at runtime to find the right translation for that attribute


16

Referential Join Connecting Tables Relies on Referential Integrity

Each entry in the left table MUST have a corresponding entry in the right table

Optimized for performance

Join is only performed if at least one field from the right table is requested.

Like an Inner Join when join is executed

When field from both tables are requested an inner Join is performed.

Only available in OLAP engine

Referential join is a feature available only in OLAP engine, when testing Attribute Views outside the context of a Analytical view then the Join Engine will perform a Inner Join.


17

Referential Join – Attribute View Connecting Tables

*** Referential join is a feature available only in OLAP engine, when testing Attribute Views outside the context of a Analytical view then the Join Engine will perform a Inner Join. As a result TOM and BOB will not be returned.


18

Referential Join – Analytical View Connecting Tables

*** Customer 77, TOM and BOB are not returned since C_ID is a Joined key field resulting in an Inner Join. BOB has no corresponding Texas description and TOM has no corresponding facts.

*** TOM is not returned due to no corresponding facts in the sales table

*** The Amount includes all facts including Customer 77 and BOBs order even through master records do not exist reason is when only non-key fields are selected from the left table, all joins to other tables will be omitted © 2012 SAP AG. All rights reserved.

19

Referential Join – Using MDX Connecting Tables

Like Inner Join

No referential integrity of data in both tables No Join processed


20

Calculation View- Join vs. Union Connecting Tables Caution!! Do not JOIN Analytical Views, this could lead to performance implications. Instead use Union with constant values when working with multiple fact tables


21

Unions Connecting Tables

Unions are used to combine the result-set of two or more SELECT statements.

The Union operation is popular for combining plan and actual values in CO-PA.

Note that Unions are not supported in modeled artifacts (Attribute Views or Analytical Views) and can only be realized in Calculation Views.

Refrain from Joining Analytical views; rather use Unions with Constant values.

Unions with Constant values are supported within Graphical Calculation Views and the UNION operator can accept 1..N input sources.

Whereas a Script Based calculation view’s comparable CE_UNION_ALL function can only accept 2 input sources at a given time.


22

Standard Union Connecting Tables


23

Union with Constant Values Connecting Tables


24

Summary Connecting Tables


Explain differences between Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, Text Join and Referential Join when connecting tables.

Explain how using Standard Union and Union with constant values.


25


1

Unit 3: Advanced Modeling Creating Attribute Views Using Hierarchies Creating Restricted & Calculated Measures Using Filter Operations Using Variables Creating Calculation Views SAP HANA SQL - Introduction SQLScript and Procedures Using Currency Conversion © 2012 SAP AG. All rights reserved.

2

Objectives Creating Attribute Views

At the end of this Lesson you will be able to: Explain how to create derived attribute views, Explain how to create shared attribute views, Explain how to create calculated attributes, Explain how to create time characteristics based attribute views, Explain how to create stand alone text tables. Explain how to use base table aliases, Explain how to include hidden attributes in an attribute view


3

Overview Creating Attribute Views

This module covers the following topics: Derived Attribute Views, Shared Attribute Views Calculated Attributes Time Characteristics Based Attribute Views Stand Alone Text Tables Using Base Table Aliases Hidden Attributes


4

Concept Attribute views Creating Attribute Views

Calculation Views

Calculation Engine

Analytic Views


OLAP Engine

Join Engine

Attribute Views

5

Derived Attribute Views Creating Attribute Views


6

Shared Attribute Views Creating Attribute Views


7

Calculated Attributes Creating Attribute Views


8

Time Characteristics Attribute View Creating Attribute Views


9

Creating Stand Alone Text Tables Creating Attribute Views


10

Using Multiple Base Tables Using Aliases Creating Attribute Views You will be prompted for an alias name when adding multiple instances of the same base table


11

Hidden Attributes Creating Attribute Views There may be instances where you want to include Attributes in your Analytical View (such as for granularity purposes) but for reporting you want these to be hidden. This can be achieved by setting the “Hidden” property of the attribute to “True”. In the context of an Analytical View this attribute will then not be visible.


12

Summary Creating Attribute Views

You should now be able to: Explain how to create derived attribute views, Explain how to create shared attribute views, Explain how to create calculated attributes, Explain how to create time characteristics based attribute views, Explain how to create stand alone text tables, Explain how to use base table aliases, Explain how to include hidden attributes in an attribute view


13

Unit 3: Advanced Modeling Creating Attribute Views Using Hierarchies Creating Restricted & Calculated Measures Using Filter Operations Using Variables Creating Calculation Views SAP HANA SQL - Introduction SQLScript and Procedures Using Currency Conversion

Objectives Using Hierarchies

At the end of this Lesson you will be able to: Explain how to implement leveled hierarchies, Explain how to leverage parent / child hierarchies, Explain how to create attribute based hierarchies.


15

Overview Using Hierarchies

This module covers the following topics: Implement Leveled Hierarchies Leverage Parent Child Hierarchies Create Attribute Based Hierarchies


16

Concept Using Hierarchies Using Hierarchies For example, consider the TIME attribute view with YEAR, QUARTER, and MONTH attributes. You can use these YEAR, QUARTER, and MONTH attributes to define a hierarchy for the TIME attribute view as follows: Hierarchies in HANA could only be used by reporting tools using MDX connectivity

Year

Quarter 1


Quarter 2

Quarter 3

Jan Feb Mar

Jul

Apr

May Jun

Quarter 4

Aug Sep

Oct

Nov Dec 17

Using Hierarchies Using Hierarchies Let's have a look at the following hierarchy, a minimal example :

Hierarchies in HANA could only be used by reporting tools using MDX connectivity


18

Implement Leveled Hierarchies Using Hierarchies Level Hierarchies are hierarchies that are rigid in nature, where the root and the child nodes can be accessed only in the defined order. For example, organizational structures, and so on. This page describes step-by-step how to create a SAP HANA Database hierarchy view by means of the SAP Hana Modeler. Create a table providing some hierarchy source data :


19

Implement Leveled Hierarchies Using Hierarchies If necessary, create a package in the Modeler. Create an attribute view in the Modeler :


20

Implement Leveled Hierarchies Using Hierarchies Select the source table as basis for the view :


21

Implement Leveled Hierarchies Using Hierarchies Select the attributes that should be part of the source view :


22

Implement Leveled Hierarchies Using Hierarchies Right click on the Hierarchies folder, create a New Level Hierarchy:


23

Implement Leveled Hierarchies Using Hierarchies For a leveled hierarchy, add the attributes to the hierarchy in the correct level order from top to bottom, with the lowest granularity at the lowest level of the hierarchy:


24

Leverage Parent Child Hierarchies Using Hierarchies Parent and Child Hierarchies can be illustrated as for example an Employee Master (Employee and Manager). The hierarchy can be explored based on a selected parent, and there are cases where the child can be a parent. If you want to create a parent-child hierarchy, make sure the child/successor attribute's property "Principal Key" to True :


25

Leverage Parent Child Hierarchies Using Hierarchies Right click on the Hierarchies tab, and select a New Parent Child Hierarchy:


26

Leverage Parent Child Hierarchies Using Hierarchies For a Parent Child Hierarchy there is no need to define the Child Attribute, as you have already done so when you have specified the Principal Key of the Table. What you will however need to specify is the Parent Attribute of the Child:


27

Hierarchy Active Property Using Hierarchies MDX per default only shows key Attributes. If “Hierarchy Active” = “false” for non-key Attribute, then the Attribute will not show up in the MDX reporting tool.


28

Hierarchy Active Property Using Hierarchies By enabling “Hierarchy Active” for non-key Attributes, you can make sure the Attribute can be used for reporting, even though it is not a key field.


29

Summary Using Hierarchies

You should now be able to: Explain how to implement leveled hierarchies, Explain how to leverage parent / child hierarchies, Explain how to create attribute based hierarchies.


30


Objectives Creating Restricted & Calculated Measures

At the end of this Lesson you will be able to: Understand the benefits of Restricted Measures Use Restricted Measures Understand when to use Calculated Measures Create Calculated Measures


32

Overview Using Filter Operations

This module covers the following topics: Concepts for Restricted & Calculated Measures Using Restricted Measures Creating Calculated Measures


33

The benefits of Restricted Measures Creating Restricted & Calculated Measures What is a Restricted Measure?

As the name implies it is a measure that does not give the complete picture of a measure, it is restricted to a subset of the original measure. The benefit of a Restricted Measure is, that it expands the modeling options in a view, giving the modeler the possibilities of creating objects that can be easily reported on or reused.


Country DE DE DE DE DE DE DE DE DE DE DE DE US US US US US US US US US US US US

Month 2010-01 2010-02 2010-03 2010-04 2010-05 2010-06 2010-07 2010-08 2010-09 2010-10 2010-11 2010-12 2010-01 2010-02 2010-03 2010-04 2010-05 2010-06 2010-07 2010-08 2010-09 2010-10 2010-11 2010-12

Amount 12.345,00 15.678,00 25.814,00 21.586,00 21.861,00 11.258,00 12.387,00 13.589,00 12.345,00 15.678,00 25.814,00 21.586,00 21.861,00 11.258,00 12.387,00 13.589,00 12.345,00 15.678,00 25.814,00 21.586,00 21.861,00 11.258,00 12.387,00 25.814,00

Restricted by Country: DE 209.941,00

US 205.838,00

Difference 4.103,00

Restricted by Months: Country DE US

Q1 53.837,00 45.506,00

Q2 54.705,00 41.612,00

34

Using Restricted Measures Creating Restricted & Calculated Measures Picture the example in the right table. You have a transactional table with cost data items, with each cost type split on a different line. If you want to find out the shipping cost you could create an Analytic View with Cost Type as an Attribute, and Amount as a Measure. You could then restrict your report by reporting on Cost Type, setting the Attribute filtered on Cost Type = “Shipping Cost”.


35

Using Restricted Measures Creating Restricted & Calculated Measures In order to utilize a Restricted Measure, you can instead create one which already limits the results to Shipping Costs only within the measure itself.


36

Using Restricted Measures Creating Restricted & Calculated Measures The Attribute you filter the Restricted Measure does not have be limited to one single Attribute. You can set it restricted to multiple attributes depending on your reporting requirements. There are also multiple operators to choose from.


37

Using Restricted Measures Creating Restricted & Calculated Measures

Further, the Restricted Measure does not have to be a straight sum of the Measure that it is based on. The Aggregation Types that you have available are:


SUM

MIN

MAX

38

When to use Calculated Measures Creating Restricted & Calculated Measures

In a data model sometimes not all Measures available in the base data will give your users sufficient information for reporting if you just provide the base Measures in your views. SAP HANA has a type of Measure available called Calculation Measures where the modeler is able to include calculations already within the view in order to help reporting or further modeling.


4 V= 3

πr

3

39

When to use Calculated Measures Creating Restricted & Calculated Measures

When you include calculations in your views using Calculated Measures you take advantage of the speed of SAP HANA letting the database engine perform the calculations, instead of doing these calculations in your end client reporting tool. Having ready defined calculations in views can also help simplifying reporting by unifying calculations having them calculated in the same way for all users instead of having users or developers create their own versions of the calculations.


Client Application

A

G

Y

Z

Calculation Engine Do calculation after aggregation

OLAP Engine

Column Store

Join Engine

Row Store

A

B

C

D

G

Y

Avoid calculation before aggregation on line item level

40

Creating Calculated Measures Creating Restricted & Calculated Measures

A Calculated Measure is defined in the view and when you create one you can use the calculations, mathematical functions etc. available in the editor.


41


For certain measure it is not possible to perform the calculations when the measures are already aggregated. The aggregated granularity of for example Price does not mean anything.


42

Creating Calculated Measures Creating Restricted & Calculated Measures For these types of Measures you can predefine the Calculated Measure to calculate each individual item before aggregating. This is done by selecting the option “Calculate Before Aggregation”.


43


This way you can be sure that you end up with a correct sum as the calculation are performed on the correct granular level.


44

Objectives Using currency Conversion

You should now be able to: Understand the benefits of Restricted Measures Use Restricted Measures Understand when to use Calculated Measures Create Calculated Measures


45


Objectives Using Filter Operations

At the end of this Lesson you will be able to: Explain how to compare constraint filter and WHERE clause, Explain how to create client dependant views, Explain how to model domain fix values.


47

Overview Using Filter Operations

This module covers the following topics: Compare constraint filter and WHERE clause, Create client dependant views, Model domain fix values.


48

Filter Operations Using Filter Operations Client Application

Calculation Engine

OLAP Engine

Join Engine

Column Store

Row Store


Reduce data transfer between the engines by using Filter Operations like : Using a Constraint or WHERE clause Creating Client Dependant Views Using Model Domain Fix Values

49

Compare Constraint Filter & WHERE Clause Using Filter Operations Constraint filter :

Is defined on design time on a table,

The filter applies on the table before the query starts to execute,

Normally faster than WHERE clause, as the results set is reduced before proceeding with the query execution plan, e.g. constraints applied before a table join is executed.


WHERE clause : Vs

Is defined on runtime in the SQL query,

The filters applies on the results set of a query.

50

Create Client Dependant Views Using Filter Operations Define « Default Client » as « dynamic » in properties of your views (Attribute Views and Analytic Views)

Define the value of the « Session Client » in the definition of the User ID. The variable constraint is substituted at runtime by the client that is set for the current user running a query on the model: © 2012 SAP AG. All rights reserved.

51

Model Fix Values Using Filter Operations


52

Model Fix Values Using Filter Operations


53

Summary Using Filter Operations

You should now be able to: Explain how to compare constraint filter and WHERE clause, Explain how to create client dependant views, Explain how to model domain fix values.


54


Objectives Creating Variables

At the end of this Lesson you will be able to: Explain the difference between Variables and Input Parameters Create Variables Create Input Parameters


56

Overview Creating Variables

This module covers the following topics: Understanding the difference between Variables and Input Parameters How to create Variables and apply them as Filters How to make use of different types of Input Parameters


57

Variables and Input Parameters Concepts Creating Variables In an Attribute or Calculation view you can create two types of objects to get data from reporting users:

Variables

These are bound to attributes and are used for filtering. As such, they can only contain the values available in the Attribute they relate to.

Input Parameters

Can contain any value the reporting user want to enter. Therefore, a data type for the Input Parameter must be specified.


58

Variables and Input Parameters Concepts Creating Variables Client Application

Calculation Engine

You

use variables to filter data at runtime. You assign values to these variables by entering the value manually, or by selecting it from the drop-down list.

Using

variables means that you do not need to decide the restriction on the value of attributes at the design time.

OLAP Engine

Join Engine

You

can apply variables in the analytic and calculation views.

If

Column Store


Row Store

a calculation view is created using an analytic view with variables, those variables are also available in the calculation view but cannot be edited.

59

Variables Types Creating Variables The following types of Variables are supported: Type

Description

Single Value

Use this to apply a filter to a Single Value

Interval

Use this where you want the user to specify a set start and end to a selected Interval.

Range

Use this when you want the end user to be able to use operators such as “Greater Than” or “Less Than”.

Range Variable Example:


60

Creating Variables for Filtering Creating Variables A variable restricts the results in the view for the selected Attribute. You select the Attribute in the view that you want to filter on, and you also define the: Selection Type: Whether selections should be based on intervals, ranges or single values. Multiple Entries: Whether multiple values of the selection types should be allowed. You can also define whether the Variable is Mandatory or if it should have a Default Value. © 2012 SAP AG. All rights reserved.

61

Creating Variables for Filtering Creating Variables

Just creating a Variable will however not enable the users to filter the result set. After a variable has been created it also needs to be assigned and applied to an Attribute as a Filter. © 2012 SAP AG. All rights reserved.

62

Creating Variables for Filtering Creating Variables By right clicking on the appropriate Attribute and applying a filter, you can then select a Variable as an operator for the filter, and then specify the created Variable. The correct Data Type will then automatically be assigned to the filter.


63

Creating Variables for Filtering Creating Variables

When displaying the data of a View with a Variable or Input Parameter included, the Value Help Dialog you can help you or the reporting user to find the selections you are looking for. © 2012 SAP AG. All rights reserved.

64

Input Parameters Concept Creating Variables

You might not want a variable to just restrict the data of a view. You might want to take input from the user and process it, returning dynamic data based on the user selection. Input Parameters makes this possible.


65

Input Parameter Types Creating Input Variables The following types of Input variables are supported: Type

Description

Currency

Use this during currency conversion where the end user should specify a source or target currency.

Date

Use this to retrieve a date from the end user using a calendar type input box.

Static List

Use this when the end user should have a set list of values to choose from.

Attribute Value

When an Input Variable has this type, it serves the same purpose as a normal Variable.

(none)

If none of the above applies you do not have to specify an Input Variable type. The Type can be left blank


66

Creating Input Parameters Creating Variables If we want the end user to decide whether Gross or Net amount should be shown in a View, what we first need to do is to create an Input Parameter to be used in a calculation. The Input Parameter can be of any suitable type, for example a StaticList type. In this example to the left the user can choose either ”Gross” or ”Net” as the selection.


67

Creating Input Parameters Creating Variables We also need somewhere to call the Input Parameter from. In for example a Calculated Measure, we can reference the result of the user selected Input Parameter. This is done by calling it within double dollar signs, see example:

if('$$INP_GROSS_OR_NET$$'='Gross',"GROSS_AMOUNT","NET_AMOUNT")


68

Creating Input Parameters Creating Variables The Input Parameter type ”Date” can be useful when you for example want an input date from the reporting user in order to create further calculations on. Whatever input is selected in the variable can be used as a basis for extended calculations.


69

Creating Input Parameters Creating Variables

When using the type ”Date” you are making it easier for the end user to select a date by utilizing a calendar dialog for selecting the appropriate date.


70

Objectives Creating Variables

You should now be able to: Explain the difference between Variables and Input Parameters Create Variables Create Input Parameters


71


Objectives Creating Calculation Views

At the end of this Lesson you will be able to: Explain how to create calculated attributes in a calculation view Explain how to create “simple” calculation views (non aggregate) Explain how to use the aggregation node Explain how to define unmapped columns in a union node


73

Overview Creating Calculation Views

This module covers the following topics: Calculated Attributes in Calculation Views “Simple” Calculation Views Aggregation Node Unmapped Columns in a Union Node


74

Concept Calculation Views Creating Calculation Views

Calculation Views

Calculation Engine

Analytic Views


OLAP Engine

Join Engine

Attribute Views

75

Calculated Attributes in Calculation Views Creating Calculation Views Apart from being able to hold standard Attributes, a Calculation View can also contain Calculated Attributes. This means that with the columns generated by the Calculation View, you can also create new Calculated Attributes specific to the Calculation View.


76

Simple Calculation Views Creating Calculation Views A standard Calculation View will group the measures by dimension, thereby producing an aggregated result. If this behavior is not wanted, it is possible to create a Simple Calculation View by setting the “Multidimensional Reporting” of the view to “disabled”. This can be useful when it is required to necessary to create a list based view, in essence creating a complex Attribute View. For this functionality to be utilised the Calculation View can however only contain attributes, no measures.


77

Simple Calculation Views Creating Calculation Views It is necessary to understand the impact of using a Simple Calculation View when dealing with values.

Source Data: Country Germany Germany Italy Italy

Calculation View: Country Germany Italy

Value 20 10


Value 3 17 5 5

A Simple Calculation View is not meant to aggregate measures, so a careful approach has to be taken when including values.

Simple Calculation View:

It behaves like an Attribute View in that it also:

Country Germany Germany Italy

Provides a list of the distinct values without aggregation

Value 3 17 5

Does not allow the usage of measures 78

Aggregation Node Creating Calculation Views In order to have further control of how the aggregation is done, it is beneficial to use the aggregation node in Graphical Calculation Views. When using the aggregation node you can specify which columns should be aggregated and also the aggregation type (sum, min or max). You can also add Calculated Columns to the node. These calculations will be performed after aggregation.


79

Unmapped Columns in a Union Node Creating Calculation Views When you have several result sets that you want to union together, placing them on top of each other. This can be done both in Graphical Calculation Views and SQL Script Calculation Views. In SQL Script this is done using CE functions. In Graphical Calculation views this is done using the Union Node.


In order for the columns from the different sources to go into the correct target, a mapping will need to be provided. This can be done via a drag and drop interface.

80

Unmapped Columns in a Union Node Creating Calculation Views There could be instances when a union needs to be performed where the sources have a different number of columns. You can then set a Constant Value for the source columns that do not have the target column. The Constant Value can be set by right-clicking on the Target column, selecting “Manage Mappings”.


81


82

Objectives SAP HANA SQL

Explain the language elements used in SAP HANA SQL statements.


83

Overview SAP HANA SQL


Overview SQL Language Elements Identifiers SQL Data Types Predicates and Operators Functions and Expressions SQL Statements - Examples

84

SQL - Definition SAP HANA SQL

Structured Query Language Standardized language for communication with a relational database. Used to retrieve, store or manipulate information in the database.


Class

Description

Example

DDL

Data Definition Language

CREATE, ALTER, DROP TABLE

DML

Data Manipulation Language

SELECT, DELETE, INSERT, UPDATE

DCL

Data Control Language

GRANT, REVOKE

85

SQL language elements SAP HANA SQL Identifiers

Used to represent names used in SQL statement

Data types

Specify the characteristics of a data value

Expressions

Clause that can be evaluated to return values

Operators

Used for calculation, value comparison or to assign values

Specified by combining one or more expressions or logical operators and returns one of the following logical or truth values: TRUE, FALSE, or UNKNOWN

Used in expressions to return information from the database

Predicates

Functions


86

Comment and Code page SAP HANA SQL Comments -double hyphens /* <……> */ Codepage The SAP HANA database supports Unicode to allow use of all languages in the Unicode Standard and 7 Bit ASCII code page without restriction.


87

Identifiers SAP HANA SQL


88

SQL Data types SAP HANA SQL Classification

Data Type

Datetime types

DATE, TIME, SECONDTIME, TIMESTAMP

Numeric types

TINYINT, SMALLINT, INTEGER, BIGINT, SMALLDECIMAL, DECIMAL, REAL, DOUBLE, FLOAT

Character string types

VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT

Binary types

VARBINARY

Large Object types

BLOB, CLOB, NCLOB, TEXT


89

Predicates SAP HANA SQL Comparison Predicates

{ = | != | <> | > | < | >= | <= } [ ANY | SOME| ALL ] { | }

Range Predicate

[NOT] BETWEEN AND

In Predicate

[NOT] IN { | }

Exists Predicate

[NT] EXISTS ( )

LIKE Predicate

[NOT] LIKE [ESCAPE ]

NULL Predicate

IS [NOT] NULL


90

Operators SAP HANA SQL Unary

Binary

operator operand

operand1 operator operand2

unary plus operator(+) unary negation operator(-) logical negation(NOT) multiplicative ( *, / ), additive ( +,- ) comparison operators ( =,!=,<,>,<=,>=) logical operators ( AND, OR )

Arithmetic Operators

-< expression > < expression > operator < expression >

Negation, Addition, Subtraction Multiplication, Division

String Operator

< expression > || < expression >

String concatenation

Comparison Operators

operator

>= Greater or equal to <= Less than or equal to !=, <> Not equal

Logical Operators

Search conditions can be combined using AND or OR operators. You can also negate them using the NOT operator.

AND, OR NOT

Set Operators

Set operators perform operations on the results of two or more queries.

UNION, UNION, ALL, INTERSECT, EXCEPT


91

Functions SAP HANA SQL Classification

Examples

Data type conversion Functions

CAST, TO_ALPHANUM, TO_BIGINT, …

DateTime Functions

ADD_DAYS, ADD_MONTHS, ADD_YEARS, DAYS_BETWEEN, DAYNAME, CURRENT_DATE, …

Number Functions

ABS, ACOS, ASIN, ATAN, COS …

String Functions

CONCAT, LEFT, LENGTH, TRIM, …

Miscellaneous Functions

IFNULL, CURRENT_SCHEMA, …


92

Expressions SAP HANA SQL

Case Expressions

IF ... THEN ... ELSE logic without using procedures in SQL statements.

Function Expressions

SQL built-in functions can be used as an expression.

Aggregate Expressions

Uses an aggregate function to calculate a single value from the values of multiple rows in a column.

Subqueries in expressions

SELECT statement enclosed in parentheses.


93

Expressions: Examples SAP HANA SQL

Case expression You can use IF ... THEN ... ELSE logic without using procedures in SQL statements.

::= CASE WHEN THEN , ... [ ELSE ] { END | END CASE }

Aggregate Expressions ::= COUNT(*) | ( [ ALL | DISTINCT ] ) ::= COUNT | MIN | MAX | SUM | AVG | STDDEV | VAR


94

SQL statement: Create Table SAP HANA SQL CREATE [] TABLE ; table_type ::= TEMPORARY

COLUMN | ROW | HISTORY COLUMN | GLOBAL TEMPORARY | LOCAL

table_contents_source ::= ( , … )|[ (column_name, ...) ] | [ | ] [ WITH [NO] DATA ] ] table_element ::= column_definition column_constraint | table_constraint ( column_name, ... ) like_table_clause ::= LIKE like_table_name as_table_subquery ::= AS () column_definition ::= column_name data type [] [] [DEFAULT default_value] [GENERATED ALWAYS AS ]


95

Create Table - Table Types SAP HANA SQL

COLUMN ROW

HISTORY COLUMN

COLUMN-based storage should be used, if the majority of access is through a large number of tuples but with only a few selected attributes. ROW-based storage is preferable, if the majority of access involves selecting a few records with all attributes selected. Creates a table with a particular transaction session type called HISTORY. Tables with session type HISTORY support time travel; the execution of queries against historic states of the database is possible.

GLOBAL TEMPORARY

Table definition is globally available while data is visible only to the current session. The table is truncated at the end of the session.

LOCAL TEMPORARY

The table definition and data is visible only to the current session. The table is truncated at the end of the session.


96

Create Table - Example SAP HANA SQL

Table Type

Column Constraint Table Elements Column Definition

Data Type


97

SQL statement: Insert SAP HANA SQL INSERT INTO [ ( column_name, ... ) ] { VALUES (expr, ... ) | } ;


98

SQL statement: Select SAP HANA SQL

SELECT [TOP number ] [ ALL | DISTINCT ] [] [] [] [] [] [] [] ;


99


Explain the language elements used in SAP HANA SQL statements.


100


101

Objectives SQLScript and Procedures


Explain SQLScript and SQLScript extensions SQLScript implementation logic Create and call a Procedure Explain calculation engine and calculation model Explain functionality of Calculation engine operators

102

Overview SQLScript and Procedure


Introduction to SQLScript Overview of SQLScript Extensions SQLScript implementation logic Procedures Introduction to calculation engine and calculation model Introduction to Calculation engine operators

103

SQLScript: Concept SQLScript and Procedures

SQLScript is a collection of extensions to Structured Query Language (SQL). Data Extension

Allows the definition of table types without corresponding tables.

Functional Extension

Allows definitions of (side-effect free) functions which can be used to express and encapsulate complex data flows

Procedural Extension

Provides imperative constructs executed in the context of the database process.

SQLScript allows developer to push data intensive logic into the database.

SQLScript encourages developer to implement algorithms using a setoriented paradigm instead of one tuple at a time paradigm.

SQLScript allows usage of imperative as well as declarative statements.


104

SQLScript: Implementation Logic SQLScript and Procedures SQL Script Client

x Orchestration Logic Imperative Extension

x

x Declarative Logic Functional Extension


105

SQLScript: Data Type Extensions SQLScript and Procedures Scalar Data Type : The SQLScript type system is based on the SQL-92 type system and supports following primitive data type: TINYINT, SMALLINT, INTEGER, BIGINT DECIMAL(p, s), REAL, FLOAT, DOUBLE VARCHAR, NVARCHAR, CLOB, NCLOB VARBINARY, BLOB DATE, TIME, TIMESTAMP


106

SQLScript: Data Type Extensions SQLScript and Procedures Table Type : SQLScript’s datatype extension also allows the definition of table types. These table types are used to define parameters for a procedure. A table type is created using the CREATE TYPE and delete using DROP TYPE statement.

Syntax: CREATE TYPE [schema.]name AS TABLE (name1 type1 [, name2 type2,...]) DROP TYPE [schema.]name [CASCADE]


107

SQLScript: Functional Extensions SQLScript and Procedures Functional

extension allow to describe complex dataflow logic using side-effect free procedures.

Procedures

can have multiple input parameters and output parameters(which can be of scalar or table types).

Procedures

describe a sequence of data transformations on data passed as input and database tables.

Data

transformations can be implemented as queries that follow the SAP HANA database SQL syntax by calling other procedures.

Read-only

procedures can only call other read-only procedures.


108

SQLScript: Procedure SQLScript and Procedures Procedure

is a reusable processing block. It is implemented using

SQLScript. and CREATE statement is used to modify the definition of a procedure.

DROP

A

procedure can be created as read only(without side-effect) or readwrite.

Procedure

can be implement using SQLScript, L or R language.


109

SQLScript: Procedure Creation using SQL editor SQLScript and Procedures Syntax: CREATE PROCEDURE {schema.}name {({IN|OUT|INOUT} param_name data_type {,...})} {LANGUAGE } {SQL SECURITY } {READS SQL DATA {WITH RESULT VIEW }} AS BEGIN ... END READS SQL DATA defines a procedure as read-only. Implementation LANGUAGE can be specified . Default is SQLScript. WITH RESULT VIEW is used to create a column view for output parameter of type table which can be used in SQL query. © 2012 SAP AG. All rights reserved.

110

SQLScript: Procedure Creation using Wizard SQLScript and Procedure

Start Procedure creation wizard from context menu of package.

Provide creation parameter.

Set “Access Mode” for read-only or read-write classification.

Security mode can be select by setting the value of attribute “Run With”


111 111

SQLScript: Procedure Creation using Wizard SQLScript and Procedure

Define output and input parameter of procedure.

Write application logic in Script view using SQLScript.


112

SQLScript: Calling a Procedure SQLScript and Procedure CALL - Procedure Called From Client A procedure (or table function) can be called by a client on the outer-most level, using any of the supported client interfaces. Syntax CALL [schema.]name (param1 [, ...])

For table output parameters it is possible to either pass a table or ‘?’. ‘?’ can be used to represent an empty parameter binding.


113

SQLScript: Calling a Procedure SQLScript and Procedure CALL...WITH OVERVIEW From Client This CALL statement returns one result set that holds the information of which table contains the result of a particular table’s output variable. This is used to populate an existing table by passing it as parameter. When passing ‘?’ to the output parameters, temporary tables holding the result sets will be generated.

Syntax CALL [schema.]name (param1 [, ...]) WITH OVERVIEW


114

SQLScript: Calling a Procedure SQLScript and Procedure CALL - Internal Procedure Call For internal calls, i.e. calls of one procedure by another procedure, IN variables are bound by literals or variable references, new OUT variables are bound to the result of the call. Syntax CALL [schema.]name (:in_param1, out_param [, ...])


115

Calculation Engine SQLScript and Procedures

SQL Script

Calculation engine is the execution engine for SQLScript.

SQL Script Complier

SQLScript statement are parsed into calculation model as much as possible. The calculation engine instantiates a calculation model at time of query execution.

Calculation Model (Data Flow Graph) Calc Engine

Model Optimizer (rule based) R

Intermediate Results

Model Executor R

Calc Engine Operators Logical Execution Plan

R

Database Optimizer


R

Script Execution Runtime

116

Calculation Model SQLScript and Procedures Union

R-OP

R-OP

R-OP

Filter

Filter

Filter

Join Input2


Input1

117

Calculation Model Example SQLScript and Procedures

Query 5

Query 4

Query 3

Query 1

EPM_PROCUREMENT1


Query 2

M_TIME_DIMENSION

118

SQLScript: Calculation Engine Plan Operators SQLScript and Procedure

Calculation engine plan operators encapsulate datatransformation functionality.

It is an alternative to using SQL statements as their logic is directly implemented in the calculation engine, i.e. the execution environment of SQLScript.

Operator has been categorized as Data Source Access and Relational.


119

SQLScript: Data Source Access Operators SQLScript and Procedure

CE_COLUMN_TABLE ("table_name"{, ["attrib_name", ...]}) Example: ot_books1 = CE_COLUMN_TABLE("BOOKS"); ot_books2 = CE_COLUMN_TABLE("BOOKS",["TITLE","PRICE","CRCY"]); This example only works on a column table and does not invoke the SQL processor. It is semantically equivalent to the following: ot_books3 = SELECT * FROM books; ot_books4 = SELECT title, price, crcy FROM books;


120

SQLScript: Data Source Access Operators SQLScript and Procedure CE_JOIN_VIEW("join_view_name"{, ["attrib_name", ...]}) Example: out = CE_JOIN_VIEW("PRODUCT_SALES", ["PRODUCT_KEY", "PRODUCT_TEXT", "SALES"]); Retrieves the attributes PRODUCT_KEY, PRODUCT_TEXT, and SALES from the join view PRODUCT_SALES. It is equivalent to the following SQL: out = SELECT product_key, product_text, sales FROM product_sales;


121

SQLScript: Data Source Access Operators SQLScript and Procedure CE_OLAP_VIEW("OLAP_view_name"{, ["DIM", "KEY_FIG", ... ]}) Example: out = CE_OLAP_VIEW("OLAP_view", ["DIM1", "KF"]); Is equivalent to the following SQL: out = select dim1, SUM(kf) FROM OLAP_view GROUP BY dim1;


122

SQLScript: Data Source Access Operators SQLScript and Procedure CE_CALC_VIEW ("CALC_VIEW_NAME"{, ["attrib_name", ...]}) Example: out = CE_CALC_VIEW("_SYS_SS_CE_TESTCECTABLE_RET", ["CID", "CNAME"]); Semantically equivalent to the following SQL: out = SELECT cid, cname FROM "_SYS_SS_CE_TESTCECTABLE_RET";


123

SQLScript: Relational Operators SQLScript and Procedure CE_JOIN (:var1_table, :var2_table, [join_attr, ...]{, [attrib_name , ...]}) Example: ot_pubs_books1 = CE_JOIN (:lt_pubs, :it_books,["PUBLISHER"]); ot_pubs_books2 = CE_JOIN (:lt_pubs, :it_books,["PUBLISHER"], ["TITLE","NAME","PUBLISHER","YEAR"]); This example is semantically equivalent to the following SQL but does not invoke the SQL processor. ot_pubs_books3 = SELECT P.publisher AS publisher, name, street, post_code, city, country, isbn, title, edition, year, price, crcy FROM :lt_pubs AS P, :it_books AS B WHERE P.publisher = B.publisher; ot_pubs_books4 = SELECT title, name, P.publisher AS publisher, year FROM :lt_pubs AS P, :it_books AS B WHERE P.publisher = B.publisher; © 2012 SAP AG. All rights reserved.

124

SQLScript: Relational Operators SQLScript and Procedure

CE_PROJECTION (:var_table, [param_name [AS new_param_name],...]{,[Filter]}) Example: ot_books1 = CE_PROJECTION (:it_books,["TITLE","PRICE", "CRCY" AS "CURRENCY"], '"PRICE" > 50'); Semantically equivalent to the following SQL:. ot_books2= SELECT title, price, crcy AS currency FROM :it_books WHERE price > 50;


125


CE_CALC ('', ) Example: with_tax = CE_PROJECTION(:product, ["CID", "CNAME", "OID", "SALES", CE_CALC('"SALES" * :vat_rate', decimal(10,2)) AS "SALES_VAT"], '"CNAME" = :cname'); Notice, that all columns used in the CE_CALC have to be included in the projection list.


126

SQLScript: Relational Operators SQLScript and Procedure CE_AGGREGATION (:var_table, [aggregate ("column") {AS "renamed_col"}] {,["column", ...]}); Example: ot_books1 = CE_AGGREGATION (:it_books, [COUNT ("PUBLISHER") AS "CNT"], ["YEAR"]); Semantically equivalent to the following SQL: ot_books2 = SELECT COUNT (publisher) AS cnt, year FROM :it_books GROUP BY year;


127


CE_UNION_ALL (:var_table1, :var_table2) Example: ot_all_books1 = CE_UNION_ALL (:lt_books, :it_audiobooks); Semantically equivalent to the following SQL: ot_all_books2 = SELECT * FROM :lt_books UNION ALL SELECT * FROM :it_audiobooks;


128

SQLScript: Control statement SQLScript and Procedure IF THEN {then-stmts1} {ELSEIF THEN {then-stmts2}} {ELSE {else-stmts3}} END IF


Example: SELECT count(*) INTO found FROM books WHERE isbn = :v_isbn; IF :found IS NULL THEN CALL ins_msg_proc ('result of count(*) cannot be NULL'); ELSE CALL ins_msg_proc ('result of count(*) not NULL - as expected'); END IF;

129

SQLScript: Control statement SQLScript and Procedure WHILE DO {stmts} END WHILE

FOR IN {REVERSE} .. DO {stmts} END FOR


130

SQLScript: Dynamic SQL SQLScript and Procedure EXEC ''

Dynamic SQL statement is used to construct SQL statements at runtime in a procedure.

Dynamic SQL allows to use variables where they might not be supported in SQLScript. It provides more flexibility in creating SQL statements.

Optimization of dynamic SQL statement is limited.

It is not possible to bind result of a dynamic SQL statement to a SQLScript variable.

Dynamic SQL is prone to SQL injection.

EXEC '‘ is used to construct dynamic SQL.


131


Explain SQLScript and SQLScript extensions

SQLScript implementation logic

Create and call Procedure

Explain calculation engine and calculation model

Explain functionality of Calculation engine operators


132


133

Objectives Using Currency Conversion

Understand Currency Conversion in SAP HANA Apply Currency Conversion in Analytic Views Leverage Fixed Currencies Leverage Source Currency from Attributes Create Target Currency Variables Use Currency conversion in Calculation Views


134

Overview Using Currency Conversion

Currency Conversion in SAP HANA Currency Conversion in Analytic Views Using Fixed Currencies Using Source Currency from Attributes Creating Target Currency Variables Currency Conversion in Calculation Views


135

Currency Conversion Using Currency Conversion

As most frontend tools do not allow defining or switching reporting currency in the UI, and as there might not be such information in master data, we have to convert the possibly many monetary document currencies into just a few. SAP HANA has the necessary functions needed to achieve currency conversion during data modeling.


136

Currency Conversion Using Currency Conversion As currency exchange rates fluctuate constantly in the global markets, when converting it is necessary not only to define the source and target currencies when converting, but also to define the time when currency conversion should take place. Examples could be: Billing Date Posting Date Financial Year End Today’s Date


137

Currency Conversion in SAP HANA Using Currency Conversion The preferred way to define currency conversion for measures is to model in an Analytic View.


138

Currency Conversion in SAP HANA Using Currency Conversion But still, sometimes due to the constraints in the master data, or because of the complexity of the reporting requirements, it might be necessary to model the conversion within a Calculation View.


139

Currency Conversion in SAP HANA Using Currency Conversion The following standard SAP tables need to be included in the SYSTEM schema or other specified schema, as they are used for currency conversion. What we need are some of the TCUR* tables. They must be replicated so that the conversions are working correctly. Table Name

Description

TCURR

Exchange Rates

TCURV

Exchange rate types for currency translation

TCURF

Conversion Factors

TCURN

Quotations

TCURX

Currency Decimals


140

Currency Conversion in Analytic Views Using Currency Conversion Process Flow - Currency Conversion in Analytic Views

Create a Measure

Select Target Currency

Define it as Measure Type: “Amount with Currency”

This is the currency we want to convert to.

Enable for Conversion This is option gives us the possibility to convert the currency.


Select Date of Conversion and Exchange Type Date of Conversion will use the exchange rate of the defined date. Exchange Type is the type of exchange rate we want to use.

Define Source Currency This is the currency we want to convert from. It is the currency type that our measure is stored in.

141

Currency Conversion in Analytic Views Using Currency Conversion A measure in an Analytic View can be defined with the Measure Type:

Analytic View

“Amount with Currency” This is the necessary Measure Type for the method.

Create a Measure

Target Currency

Enable for Conversion

Date of Conversion and Exchange Type

Source Currency


142

Currency Conversion in Analytic Views Using Currency Conversion Enabling the Measure for conversion releases further options in the Measure definition in order to complete the conversion as required.

Create a Measure

Target Currency



Source Currency


143

Currency Conversion in Analytic Views Using Currency Conversion Selecting the Currency brings up the Currency Dialog where the Target Currency can be defined. A Fixed Currency will convert the Source Currency into a single currency.

Create a Measure

Target Currency



We also have the option of creating the Target Currency not based on a Fixed Currency, but rather based on an Attribute or Variable.

Source Currency


144

Currency Conversion in Analytic Views Using Currency Conversion Clicking on the Source Currency, once again brings up the Currency Dialog, this time to set the Source Currency.

Create a Measure

Target Currency



If we know the base Currency we can set it as a Fixed Type. If it varies we can set it as an Attribute type and it will change based on an Attribute.

Source Currency


145

Currency Conversion in Analytic Views Using Currency Conversion The Exchange Type is the type of Exchange Rate we want to use, as supplied from our SAP TCUR* base tables. The Date Mapping defines the date when we want the currency conversion to occur, based on either a Fixed date, an Attribute or a Variable.

Create a Measure

Target Currency



Source Currency


146

Leverage Fixed Currencies Using Currency Conversion When the source, target or both the source and target currencies are known, it is beneficial to set the currency of either or both types to “Fixed”. Using this method all lines for the measure in the Analytic View will be converted using the same currency.

Sales Order 001 002 003 004


Amount GBP £2 500 £10 000 £3 000 £1 650

Sales Order 001 002 003 004

Amount USD $3 250 $13 000 $3 900 $2 145

147

Leverage Fixed Currencies Using Currency Conversion To achieve a conversion using fixed currencies we set the Currency Type as “Fixed” and define the currencies to the appropriate source and target currencies. This way we achieve a oneto-one conversion.


148

Leverage Source Currency from Attribute Using Currency Conversion There can sometimes be instances of master data where the base currencies vary across the table. On one line you might find for example a sales order with a value in Euro, on another line you might find a different value expressed in Japanese Yen etc. Summing up these different currencies would give us a useless value.


Sales Order

Currency

Amount

001

EUR

€ 2 500

002

JPY

¥100 000

003

CHF

CHF 3 000

004

SEK

165 000 kr

149

Leverage Source Currency from Attribute Using Currency Conversion With multiple currencies in the master data we are not able to set the source currency to a single fixed type. Instead, in order to convert the currency, we will need to define the source currency based on an attribute in the master data

Source Currency Attribute Sales Order 001 002 003 004


Currency Amount EUR JPY CHF SEK

€ 2 500 ¥100 000 CHF 3 000 165 000 kr

Sales Order 001 002 003 004

Amount USD $3 250 $13 000 $3 900 $2 145

150

Leverage Source Currency from Attribute Using Currency Conversion When using an Attribute Type to define the Source Currency we set the attribute that contains the currency code to be used for the conversion to the target currency. This way SAP HANA knows which currency exchange rate to use for each individual line.


151

Use Currency Conversion in Analytic Views Using Currency Conversion SAP HANA includes some further functionality to ease currency conversion.

Enable for decimal shifts This option is to be used when you want to shift the decimal separator to the appropriate place according to the currency exchange rate data available in the master data tables. Upon Conversion Failure This selection gives you the opportunity to define how SAP HANA should deal with conversion failures. You can opt to either: Fail / Set to NULL / Ignore


152

Use Currency Conversion in Analytic Views Using Currency Conversion To summarize currency conversion in Analytic views, you can set: Decimal Shift – Yes/No Target Currency – Fixed or Attribute Based Source Currency – Fixed or Attribute Based Exchange Type – When you have multiple Exchange Rates, you specify the one to use Exchange Date – The date when conversion should be performed Upon Conversion Failure: Fail/Set to NULL/Ignore


153

Use Currency Conversion in Analytic Views Using Currency Conversion

There can however be circumstances where the Target Currency options “Fixed” or “Attribute”-based are not sufficient. You might instead want the option to let the reporting user choose the currency that the measure should be displayed in. This will be dealt with in the following topic, “Create Target Currency Variable”.


154

Create Target Currency Variable Using Currency Conversion To enable a prompt where the reporting user can specify the target currency of the measure, we first need to create a new Variable. The Variable should be defined as: Type: Currency Data Type: VARCHAR, Length: 5


155

Create Target Currency Variable Using Currency Conversion After the variable has been defined, you can then set the Target Currency to be expressed not as a Fixed or Attribute Type, but rather as a Variable. The variable created can then be selected as the Currency Type.


156

Create Target Currency Variable Using Currency Conversion When you later view the data in the Analytic View, you get the option of choosing the measure target currency based on the variable that you have created.


157

Use Currency conversion in Calculation Views Using Currency Conversion Calculation View It is also possible to perform currency conversions in a Calculation View. The method used to convert currencies differs from how it is done in Analytic Views. Rather than defining the conversion rules graphically as done in the Analytic Views, the definitions will need to be written by the modeler.


158

Use Currency conversion in Calculation Views Using Currency Conversion The function we want to use to perform currency conversion in Calculation Views is called CE_CONVERSION. When calling CE_CONVERSION you specify the input table (in most cases an Analytic view), and then define how you want the currency conversion done.


159

Use Currency conversion in Calculation Views Using Currency Conversion The different argument definitions necessary when using CE_CONVERSION are similar to what needs to be defined when performing currency conversions graphically in an Analytic View. Key

Values

Default

Meaning

'error_ handling'

'fail on error', 'set to null', 'keep unconverted'

'fail on error'

describe reaction if a rate could not be determined for a row

'client'

any

none

define the client number used for currency conversion

'family'

'currency', 'unit'

none

describe the family of the conversion to be used

'method'

'ERP'

none

describe the conversion method to be used

'conversion_type'

any

none

define the type of exchange rate used for currency conversion

'source_unit'

any

none

define the default source unit for any kind of conversion

'target_unit'

any

none

define the default target unit for any kind of conversion

'reference _date'

any

none

define the default reference date for any kind of conversion

'schema'

any

current schema

define the default schema in which the conversion tables should be looked up

'output'

combinations of 'input', 'unconverted', 'converted', 'passed_through', 'output_unit', 'source_unit', 'target_unit', 'reference_date'

'converted, passed define which attributes should be included in the output _through, output_unit'

Please note the above is not a complete list, there are further optional arguments if required. See SAP HANA reference manual. © 2012 SAP AG. All rights reserved.

160

Use Currency conversion in Calculation Views Using Currency Conversion A simple Calculation View with the sole purpose of converting a measurement into a different currency could be written in three steps: Define the input table Perform the Conversion using the CE_CONVERSION function

Project the results

Please note the above is pseudo-code and does not run unmodified.


161

Objectives Using Currency Conversion

Understand Currency Conversion in SAP HANA Apply Currency Conversion in Analytic Views Leverage Fixed Currencies Leverage Source Currency from Attributes Create Target Currency Variables Use Currency conversion in Calculation Views


162


1

Unit 4: Fulltext Search Full text Search Overview Data Types and Full text Indexes Using Full text Search

Objectives Fulltext Search Overview

At the end of this Lesson you will be able to: Understand the Fulltext Search capabilities of SAP HANA Understand the benefits of Fulltext Search Understand how the text searching processes are invoked Understand when to use Fuzzy Search


3

Overview Fulltext Search Overview

This module covers the following topics: Fulltext Search capabilities of SAP HANA Fulltext Search benefits Text Searching Engines Fuzzy Search Introduction


4

What is Fulltext Search Fulltext Search Overview The Fulltext Search capabilities of HANA helps speed up search capabilities within large amounts of text data significantly. Fuzzy Search functionality enables finding strings that match a pattern approximately (rather than exactly), both finding approximate substring matches inside a given string and finding dictionary strings that match the pattern approximately. Text Analysis scripts provides additional possibilities of analysing the strings or large text columns.


5

What is Fuzzy Search Fulltext Search Overview Fuzzy Search is a fast and fault-tolerant search feature for SAP HANA. The term ”fault-tolerant search” means that a database query returns records even if the search term (the user input) contains additional or missing characters or other types of spelling error. Its applications can be for example: Fault-tolerant search in text columns (for example, html or pdf): Search for documents on 'Driethanolamyn' and find all documents that contain the term 'Triethanolamine'. Fault-tolerant search in structured database content: Search for a product called 'coffe krisp biscuit' and find 'Toffee Crisp Biscuits'. Fault-tolerant check for duplicate records: Before creating a new customer record in a CRM system, search for similar customer records and verify that there are no duplicates already stored in the system. When, for example, creating a new record 'SAB Aktiengesellschaft & Co KG Deutschl.' in 'Wahldorf', the system shall bring up 'SAP Deutschland AG & Co. KG' in 'Walldorf' as a possible duplicate. © 2012 SAP AG. All rights reserved.

6

SAP HANA Fulltext Search Aids Fulltext Search Overview HANA provides the following to aid Fulltext Search: Fulltext and Fuzzy Search Studio Modeler and SQL Script Enhancement Python based Text Analysis script sets. Search User Interface sample application


7

Fulltext Search Benefits Fulltext Search Overview The benefits include: Exploit unstructered content without additional costs Less data duplication and movement – leverage one infrastructure for analytical and search workloads Easy-to-use modeling tools – use HANA Studio to create search models Build Search Applications qiuckly – UI building blocks provided


8

Fulltext Search Processes Fulltext Search Overview Fulltext Search functions are invoked by a dedidated process UI

UI

Interfaces, Services

Store

Models

Metadata Repository

Calc Engine

SAP HANA

Engine

Search Engine

In-Memory Column Store

Text Processor

ETL/rep Source


9

Fulltext Search Processes Fulltext Search Overview To enable Fulltext Search, Search Models are created in HANA Studio by the modeller.

Suggestions

UI

UI components

Search

Metadata

Information Access Services

Modeler

Search Model

HANA Studio Metadata


Fuzzy

Ranking

Snippets Search Engine

Tables

Linguistic Processing

Column Store

Text Processor

HANA

Search

10

Fulltext Search Processes Fulltext Search Overview During data access through Information Access Services, Fuzzy Search queries are routed to the Search Engine, whilst Linguistic Processing is handled by the Text Processor.

Suggestions

UI

UI components

Search

Metadata

Information Access Services

Modeler

Search Model

HANA Studio Metadata


Fuzzy

Ranking

Snippets Search Engine

Tables

Linguistic Processing

Column Store

Text Processor

HANA

Search

11

Fulltext Search UI toolkit Fulltext Search Overview The Fulltext Search UI toolkit is provided with HANA SP4, and provides User Interface building blocks for developing search-based applications on SAP HANA. The toolkit is based on HTML5 and Javascript.


12

Text Analysis Fulltext Search Overview The Text Analysis enablement of SAP HANA allows you to make use of these unique capabilities in the domain of unstructured data as well. The text analysis is a set of Python based scripts, that can be installed and it can then extract entities such as persons, products, places, and more from documents and thus enrich the set of structured information in SAP HANA. These additional attributes enable improved analytics and search. The text analysis provides a vast number of possible entity types and analysis rules for many industries in 20 languages, they provide a rich standard set of dictionaries and rules for identifying and extracting entities from any business text. The standard covers common entities such as organizations, persons, countries, dates, measures, and many more. The standard also contains specialized extraction content such as Marketing (“voice of the customer”) or Public Sector.


13

Objectives Fulltext Search Overview

You should now be able to: Understand the Fulltext Search capabilities of SAP HANA Understand the benefits of Fulltext Search Understand how the text searching processes are invoked Understand when to use Fuzzy Search


14

Unit 4: Fulltext Search Fulltext Search Overview Data Types and Fulltext Indexes Using Fulltext Search

Objectives Data Types and Fulltext Indexes

At the end of this Lesson you will be able to: Understand the Fulltext Search usage for different data types Understand how to create columns that enable Fulltext Search Understand how to enable HANA Studio for Fulltext Modeling


16

Overview Data Types and Fulltext Indexes

This module covers the following topics: Fulltext Search usage for different data types Enabling columns for Fulltext search Enabling Hana Studio for Fulltext modeling


17

Supported Data Types Data Types and Fulltext Indexes Fuzzy search works out-of-the-box on the following column store data types: TEXT SHORTTEXT VARCHAR NVARCHAR DATE All data types with a full-text index


18

Full Text Indexes Data Types and Fulltext Indexes

I N D E X © 2012 SAP AG. All rights reserved.

It is possible to speed up the fuzzy search by creating additional data structures, which are used for faster calculation of the fuzzy score. These data structures exist in the memory only, so no additional disk space is required. You should enable the fast fuzzy search structures for all database columns that have a high load of fuzzy searches, and for all database columns that are used in performancecritical queries, to get the best response times possible. The additional data structures increase the total memory footprint of the loaded table.

19



For data types TEXT or SHORT TEXT the index creation is done during table creation; for data types VARCHAR, NVARCHAR or CLOB an index has to be created manually post table creation. Note that full text indexes can not be created for DATE data type columns.

20



The syntax used to enable Full Text Indexes on TEXT or SHORTTEXT is the following:

CREATE COLUMN TABLE mytable ( id INTEGER PRIMARY KEY, col1 SHORTTEXT(100) FUZZY SEARCH INDEX ON ); It is currently not possible to change these settings at a later point in time by using the ALTER TABLE command.

21



The syntax used to enable Full Text Indexes on VARCHAR, NVARCHAR or CLOB is the following: CREATE COLUMN TABLE mytable ( col1 NVARCHAR(2000) ); CREATE FULLTEXT INDEX myindex ON mytable(col1) FUZZY SEARCH INDEX ON ; This can be changed at a later point in time by using the ALTER FULLTEXT INDEX command.

22

Fuzzy Search SQL Syntax Data Types and Fulltext Indexes In order to use the Studio for Search modeling, you first enable Search Options in the preferences of the Studio under: Modeler -> Search Options


23

Fuzzy Search SQL Syntax Data Types and Fulltext Indexes You will then have access to a new tab called ”Information Access” in the properties of created Attributes.


24


You should now be able to: Understand the Fulltext Search usage for different data types Understand how to create columns that enable Fulltext Search Understand how to enable HANA Studio for Fulltext Modeling


25

Unit 4: Fulltext Search Fulltext Search Overview Data Types and Fulltext Indexes Using Fulltext Search

Objectives Using Fulltext Search

At the end of this Lesson you will be able to: Know how to use Fulltext Search Know how to use Fuzzy Search Understand Fuzzy Search relevance scoring Know how to use Freestyle Search


27

Overview Using Fulltext Search

This module covers the following topics: Fulltext Search Fuzzy Search Fuzzy Search Relevance Scoring Freestyle Search


28

Fulltext Search SQL Syntax Using Fulltext Search

You call Fulltext Search by using the CONTAINS() function in the WHERE-clause of a SELECT statement. Without the Fuzzy option the search will only return results that contain the exact phrase searched for.

SELECT SCORE() AS score, * FROM documents WHERE CONTAINS(doc_content, 'Driethanolamyn') ORDER BY score DESC;


29

Fulltext Search SQL Syntax Using Fulltext Search

A fuzzy search is an alternative to a non-faulttolerant SQL statement like the example below, which would not return any results when there are spelling errors.

SELECT ... FROM documents WHERE doc_content LIKE '% Driethanolamyn %' ...


30

Fuzzy Search Relevance Score Using Fulltext Search The fuzzy search algorithm calculates a fuzzy score for each string comparison. The higher the score, the more similar the strings are. A score of 1.0 means the strings are identical. A score of 0.0 means the strings have nothing in common. You can request the score in the SELECT statement by using the SCORE() function. You can sort the results of a query by score in descending order to get the best records first (the best record is the record that is most similar to the user input). When a fuzzy search of multiple columns is used in a SELECT statement, the score is returned as an average of the scores of all columns used. © 2012 SAP AG. All rights reserved.

0.0

1.0

31

String Type Fuzzy Search Using Fulltext Search String Type Search

SAP

~

SAP Deutschaland AG & Co


String types support a basic fuzzy string search. The values of a column are compared with the user input, using the fault-tolerant fuzzy string comparison. When working with string types, the fuzzy string comparison always compares the full strings. If searching with 'SAP', for example, a record like 'SAP Deutschland AG & Co. KG' gets a very low score, because only a very small part of the string is equal (3 of 27 characters match).

32

Text Type Fuzzy Search Using Fulltext Search Text Type Search

SAP

~ SAPPHIRE NOW Orlando


Text types support a more sophisticated kind of fuzzy search. Texts are tokenized (split into terms) and the fuzzy comparison is done term by term. For example, when searching with 'SAP', a record like 'SAP Deutschland AG & Co. KG' gets a high score, because the term 'SAP' exists in both texts. A record like 'SAPPHIRE NOW Orlando' gets a lower score, because 'SAP' is only a part of the longer term 'SAPPHIRE' (3 of 8 characters match).

33

Fuzzy Search SQL Syntax Using Fulltext Search You can call the fuzzy search by using the CONTAINS() function with the FUZZY() option in the WHERE-clause of a SELECT statement.

SELECT SCORE() AS score, * FROM documents WHERE CONTAINS(doc_content, 'Driethanolamyn', FUZZY(0.6)) ORDER BY score DESC; Optionally, the fuzziness threshold can manually be set when making the FUZZY() call. If set to for example 0.6, no matches lower than 0.6 will be returned. Default is 0.8.


34

Freestyle Search SQL Syntax Using Fulltext Search

If you want HANA to search for occurrences of your search word or phrase in multiple columns, you can perform a “Freestyle Search”. This type of search will go through any columns in the table that has “Freestyle Search” enabled.

SELECT SCORE() AS score, * Replacing the column FROM name with a star documents WHERE CONTAINS(*, 'Driethanolamyn', FUZZY) ORDER BY score DESC;


35


You should now be able to: Know how to use Fulltext Search Know how to use Fuzzy Search Understand Fuzzy Search relevance scoring Know how to use Freestyle Search


36


1

Objectives Unit 5 – Processing Information Objects

At the end of this Lesson you will be able to: Explain how to validate models Explain how to compare versions of information objects Explain how to check model references Explain how to generate auto documentation


2

Model Validation Processing Information Objects Set preferences for validation rules

Create Data Model Attribute Views Analytical Views

Validate Data Model

Activate Data Model

Calculation Views


3

Set Preferences for Validation Rules Processing Information Objects In the SAP HANA STUDIO, go to “Windows” -> “Preferences” And then select “Modeler” -> “Validation Rules”


4

Set Preferences for Validation Rules Processing Information Objects At any moment, you can restore default settings.

You can select precisely which rules are applied during the validation of the Information Objects


5

Execute Validation Rules Processing Information Objects To validate Information Objects, you can use the button “Validate” or right click directly on the object. You can also select several Information Objects.


6

Execute Validation Rules Processing Information Objects In Job Log, in menu “Current”, you can see the job detail by double clicking on it. In menu “History”, all job logs appear for a period set in “Preferences”.


7

Object Versions Processing Information Objects

View Inactive

View Active

Validation, Save and Activation Creation of the view, the view is inactive


View Inactive

Modification and Save

Activation of the view, the view is active

View Active

Validation, Save and Activation

Modification of the view, the view is inactive

Activation of the view, the view is active

8

Object Versions – Comparing versions Processing Information Objects View Active

View Inactive

Modification of the View Active by Adding fields © 2012 SAP AG. All rights reserved.

9

Object Versions – View Version History Processing Information Objects

Only Active Versions are displayed. Name of the user activating the view, Activation Date and Period from the last activation are available in Version History


10

References- Checking Model References Processing Information Objects In the modeler, it is possible to check where are used different Information objects in the schema. This function could be very helpful to study the impacts of changes in the data model. Select an object, do a right click and select « Where Used » function.


11

References- Checking Model References Processing Information Objects In the « Where-Used List », the number of usages of the object is available. Thereby, « Type », « Name » and « Package » of each object which are currently used for the selected object are displayed.


12

Auto Documentation Processing Information Objects It is possible to generate automatically documentation about the data model in HANA. These documents could provide a list of all objects containing in a package or details on previous selected objects. You can generate Auto Documentation with a right click on an Information Objects or directly with the button below.


13

Auto Documentation – Select Document Type Processing Information Objects Two document types are available : Model Details (display each particularity of an Information Objects) Model List (display a list of each component of the package)


14

Auto Documentation – Add Objects to Target List Processing Information Objects All Information Objects in the Content are available. You have to select one or several objects and use the “Add” button. Use the button “Remove” to cancel the selection. You can add objects from different packages in the same generated document.


15

Auto Documentation – Select Export Type & Save to Location

Processing Information Objects Unfortunately, it is not possible yet to change the export file type. Only .pdf type is supported for the moment. Then, choose an target emplacement to save the generated documents.


16

Summary Processing Information Objects

You should now be able to: Explain how to validate models Explain how to compare versions of information objects Explain how to check model references Explain how to generate auto documentation


17


1

Objectives Managing Modeling Content

At the end of this Lesson you will be able to: Explain how to manage schemas Explain how to import and export data models Explain Translating metadata texts


2

Schemas – Creating Schemas Managing Modeling Content You create schemas to group the tables. For import you need to create the schema where all the tables are imported. Schemas are created with a SQL Script statement. SQL Syntax : CREATE SCHEMA schema_name [OWNED BY name] Parameters : OWNED BY Specifies the name of the schema owner. Description : The CREATE SCHEMA statement creates a schema in the current database.


3

Schemas – Managing Schemas Mapping Managing Modeling Content You use this procedure to map the logical schemas with the physical schemas while transferring information objects from a source system to target in case of SAP shipped content. You can define different schema mappings in the same time.


4

Schemas – Managing Schemas Mapping Managing Modeling Content Export TABLE1 from SYSTEM S1 with SCHEMA_S1

Import TABLE1 into SYSTEM S2 and modify SCHEMA_S1 into SCHEMA_S2

SYSTEM S2

SYSTEM S1 SCHEMA_S1.TABLE1


SCHEMA_S1.TABLE1

SCHEMA_S2.TABLE1

5

Delivery units, packages and models in perspective

Package 1

Models and objects

Transport to another HANA system Delivery Unit Package 2


Models and objects

6

Import & Export Managing Modeling Content You can import models from your local system or from a server. Procedure :

SYSTEM S1


Creating Delivery Unit

Exporting Models (Client or Server)

Importing Models (Client or Server)

SYSTEM S2

7

HANA Content Transport Capabilities In context of Data Marts: 2-step integration into CTS+ (“lose coupling”):

Manual preparation (server-side export) Automated transport and deployment in target system via CTS+

SAP Solution Manager CTS+ CTS+

Export DIR

Export DIR

Potential for ABAP-based new applications (HPAs)

TLOGO-based transport Encapsulating SAP HANA content in ABAP objects (allows transport SAP HANA content with application code though standard CTS mechanisms)

Manual

Source

CTS+

Target

In context of SAP NW 7.3 BW, powered by SAP HANA Leverage existing transport functionality © 2012 SAP AG. All rights reserved.

8

Import & Export – Create Delivery Unit Managing Modeling Content You use a Delivery Unit to create a group of transportable objects for content delivery and to export information models from source system to target server. From the Quick Launch tab page, choose « Delivery Units.. » and follow the steps given below.


9

Import & Export – Create Delivery Unit Managing Modeling Content

From the Delivery Units dialog box, choose create.

You need to associate packages with delivery units. This is required when you export models.


10

Import & Export – Create Delivery Unit Managing Modeling Content Enter the delivery unit name. Enter the responsible user. In the Version field, enter the delivery unit version. Enter the support package version of the delivery unit. Enter the patch version of the delivery unit.


11

Import & Export – Export Model to Server Managing Modeling Content You use this procedure to export models. Prerequisites : You have created a delivery unit.


12

Import & Export – Export Model to Client or Server Managing Modeling Content Select the delivery unit. Then choose whether to export on the server or the client and click on “Next” button.


13

Import & Export – Import Model from Server Managing Modeling Content You use this procedure to import models from the server.


14

Import & Export – Import Models from Server Managing Modeling Content

Select the system. Select the file repository on the server where models have been exported. Then select models you want to import. Define parameters as “Overwritten inactive versions” and “Activate objects” and then click on “Finish” button


15

Import & Export – SAP Support Mode Managing Modeling Content In order to ease communication when working together with SAP to gain support for Information Models, the export tool provides a method to export Information Objects to the server in a mode named “SAP Support Mode”. Only active objects can be exported in this mode. These will be exported to the server and the file(s) can then be sent to SAP support for troubleshooting purposes.


16

Copying Information Objects Managing Modeling Content

Select the system and click on “Next” button. Then define the folder location and select the package or models you want to export. Then select a target repository and click on “Finish” button.


17

Copying Content Delivered by SAP Managing Modeling Content You use this procedure to copy standard content shipped by SAP or an SAP partner to your local repository (see note 1608552 for implementing RDS content). Prerequisite: To copy the contents of objects, the administrator needs to create a mapping in the _SYS_BI.M_CONTENT_MAPPING table. Procedure: 1. From the Quick Launch tab page, choose Mass Copy. 2. Select the required object(s). 3. Choose Add. 4. Choose Next. Copy checkbox. 5. Choose Finish. The status of content copy can be viewed in Job log. © 2012 SAP AG. All rights reserved.

18

Translating Metadata Texts


19

Repository Translation Tool (RTT) The Repository Translation Tool (RTT) is a Java-based command line tool that exports language files in a standard format for customer or partner usage. RTT implements this process in four steps: Export: Exports the texts in the original language (written by the developer) from the SAP HANA Repository text tables to the file system. Upload : Uploads the texts from the file system to the SAP translation system. After this step, the translators can translate the texts from the original language into the required target languages. Download: Downloads the translated texts from the SAP translation to the file system. Import: Imports the translated texts from the file system to the SAP HANA Repository text tables.


20

RTT Usage examples Export the texts from those packages matching "pack*" from the database and upload the texts into the translation system, using the default configuration file ("rtt.properties"): rtt -e -p pack* Download the translated texts from those packages matching "pack*" from the translation system and import the texts into the database, using the default configuration file ("rtt.properties"): rtt -i -p pack* Export the texts from the database into the directory "exports": rtt --export -p pack* -x exports Upload the texts in the directory "exports" to the translation system: rtt --upload -p pack* -x exports Download the translated texts into the directory "imports": rtt --download -p pack* -x imports Import the translated texts from the directory “ imports": rtt --import -p pack* -x imports


21

Summary Managing Modeling Content

You should now be able to: Explain how to manage schemas Explain how to import and export data model Explain Translating metadata texts


22


1

Unit 7: Security and Authorizations User Management & Security Types of Privileges Template Roles Administrative

Objectives User Management & Security



Explain How To Handle User Management And User Provisioning, Explain The Authentication Methods, Explain User and Role Concept in SAP HANA, Explain How To Maintain User’s Roles, Explain How To Maintain SAP HANA Privileges.

3

Overview User Management & Security

This module covers the following topics:


User and Role Concept, User and Role Creation, Manage User or Role, Grant and Revoke User or Role, Assign Privilege to User or Role.

4

User Management and Security in SAP HANA User Management & Security

Create Users Assign Initial Passwords Important User Parameters

Assign Security

Manage Users Lock Users

Control Access to Objects

Reset Passwords

Row-Level Security

Check User Privileges

Restrict allowed actions

Integration with BI


5

Relationships Between Entities User Management & Security The relevant entities mentioned below relate to each other in the following way: Privilege

n Granted to n n

Principal

Granted to

A principal is either a role or a user.

A known user can log on to the database. A user can be the owner of database objects.

A role is a collection of privileges and can be granted to either a user or another role (nesting).

A privilege is used to impose restrictions on operations carried out on certain objects

n Role

User 1 owns n Object

User management is configured using SAP HANA studio. © 2012 SAP AG. All rights reserved.

6

User Provisioning - Get Users into the System User Management & Security Roles allow grouping privileges Create roles for specific tasks, e.g. Create data models (on a given subset of the data) Activate data models Consume models All types of privileges can be granted to a role Individual privileges Roles (Æ create a hierarchy of roles)

Roles / privileges can be assigned to users User / Role management are closely related Reflected in almost identical editor


User Role: edit + activate Role: edit model

Package: Package: create create // edit edit models models

SQL: SQL: select select

Role: activate model

Package: Package: activate activate

SQL: SQL: write write runtime runtime object object

7

User Provisioning - User and Role Concept User Management & Security

Creating Named Users in SAP HANA

Actual Database Users Create via SAP HANA Studio Or using standard SQL statements

Authentication Methods

User / Password

Set up and manage passwords using SAP HANA Studio or SQL

Kerberos Authentication Certificate-based Requires Named User in SAP HANA DB


8

Authentication concept User Management & Security

SAP HANA database provides the following options for authentication: Direct logon to the SAP HANA database with user name and password Kerberos (third-party authentication provider) for SSO Environment

For more administrative operations : Such as database recovery, the credentials of the SAP operating system user (adm) are also required.


9

Additional Authentication Method, SAML User Management & Security Security Assertion Markup Language, SAML: - For users not directly connected to SAP HANA.. - Used for authentication only (not authorization). Middleware / Application Server Scenario: Application Server needs to connect to SAP HANA Database on behalf of a user. SAML assertion is requested from the client. SAML assertion is issued by the identity provider after the client was successfully authenticated there. SAML assertion is then sent to SAP HANA Database. Access is granted based on the established trust to the identity provider.


R

Browser R

Application Server

R HTTP R

Identity Provider

R Connect with SAML ASSERTION

HANA

10

SAML In SAP HANA Studio User Management & Security SAML may be selected as a user authentication method when creating user in the SAP HANA studio.


11

Managing Users and Roles User Management & Security

Create Users

Define and Create Roles

Assign Privileges to Roles


Grant Role to User

12



Create Users



Grant Role to User

13

Creating Role using SAP HANA Studio User Management & Security

Graphical UI for Creating / managing roles In SAP HANA Studio Æ Navigator Tree Path:

() Æ Catalog Æ Authorizations Right-Click “Roles” folder Select “New” Æ “Role” from context menu

Using SQL Syntax Run the following statement: CREATE

ROLE ;


Create Users



Grant Role to User

14


Create Users




Grant Role to User

15

Assign Privileges to Roles User Management & Security

On the appropriate privilege tab: Click on the green

icon

In the search box, start typing For System / Object Privileges : the object name For direct privilege assignment: the privilege name

Select the desired object or privilege Click OK


Create Users

Assign Privileges to Roles © 2012 SAP AG. All rights reserved.

Grant Role to User

16


On the appropriate privilege tab: Click on the green

icon

In the search box, start typing For System / Object Privileges : the object name For direct privilege assignment: the privilege name

Select the desired object or privilege Click OK


Create Users


Grant Role to User

17

Assign Privileges to Roles and save User Management & Security

Using the “save” button Using the “deploy” button (green arrow) Errors during save Typically: missing privilege for editing user (USER ADMIN) Or missing grant option: For Object/Privilege combinations: on object For direct privilege assignment: on privilege


Create Users


Grant Role to User

18


Create Users




Grant Role to User

19

Create Users or Roles User Management & Security Graphical UI for Creating / managing roles In SAP HANA Studio Æ Navigator Tree Path: () Æ Catalog Æ Authorizations Right-Click “Users” folder Select “New” Æ “User” from context menu

Choose authentication methods Define the initial password (user/password) Or define the external User ID (e.g. Kerberos to set up SSO)

Other user settings

To save the user:

Define default client This is used as an implicit filter value when reading from SAP HANA data models Define and Create Roles

Create Users


Grant Role to User


20


Create Users




Grant Role to User

21

Grant Roles to User User Management & Security Using Studio: Switch to tab “Granted Roles” in User Editor Open search dialog ( ) Start typing the role name Add the role Allow/disallow granting the role (note: System Privilege “ROLE ADMIN” supersedes this GRANT OPTION)

Using SQL Syntax Run the following statement:

GRANT TO ; Define and Create Roles


Create Users

Grant Role to User


22

Grant Role to User User Management & Security Using the “save” button Using the “deploy” button (green arrow)

Errors during save Typically: missing privilege for editing user E.g.: System privilege ROLE ADMIN missing Or (without ROLE ADMIN): GRANT OPTION for role missing




Create Users

Grant Role to User

23

Revoke Roles from User User Management & Security Using Studio: Switch to tab “Granted Roles” in User Editor Select the role from list of granted roles Click the icon Save the user (this also revokes a GRANT OPTION)

Using SQL Syntax Run the following statement:

REVOKE FROM ;

Note on Cascaded Dropping of Privileges If the user had granted the role to other users, revoking the role (and the grant option) also revokes the role from these grantees © 2012 SAP AG. All rights reserved.

24

Summary User Management & Security

You should now be able to: Explain

How To Handle User Management and User Provisioning, Explain The Authentication Methods, Explain User and Role Concept in SAP HANA, Explain How To Maintain User’s Roles, Explain How To Maintain SAP HANA Privileges.


25


Objectives Types of privileges

At the end of this Lesson you will be able to: Explain

The Authorization Concept, Explain What is SQL Privilege, Explain What is SYSTEM Privilege, Explain What is Package Privilege, Explain What is Analytic Privilege, Explain What is a Template Role.


27

Overview Types of privileges

This module covers the following topics: Authorization Concept, SQL Privilege, SYSTEM Privilege, Package Privilege, Analytic Privilege.


28

Authorization concept Types of privileges When accessing the SAP HANA database using a client interface (such as ODBC, JDBC, MDX), any access to data must be backed by corresponding privileges. Different schemes are implemented SQL Privilege Privilege SQL statement type (for example, SELECT, UPDATE, and CALL…)

System Privilege SQL privilege

SYSTEM privilege

System Privileges are assigned to users and roles.

Authorization Concept

Package Privilege Restrict the access to and the use of packages Package in the repository privilege


Used for administrative tasks.

Analytic Privilege Analytic privilege

Analytic Privileges are used to provide row-level authorization Views.

29

SQL Privilege Types of privileges In the SAP HANA database, a number of privileges are available to control the authorization of SQL commands. Following the principle of least privilege, users should only be given the smallest set of privileges required for their role. Two groups of SQL Privileges are available: System Privileges These are system-wide privileges that control some general system activities mainly for administrative purposes, such as creating schema, creating and changing users and roles.

Object Privileges These privileges are bound to an object, for example, to a database table, and enable object-specific control activities, such as SELECT, UPDATE, or DELETE to be performed.


30

System Privilege Types of privileges 6 types of system privilege are available on SAP HANA Database:

Analytics

Auditing

Catalog and Schema Management

Users and Roles


System Management

System privilege

Data Import and Export

31

System Privilege Types of privileges 6 types of system privilege are available on SAP HANA Database:

Analytics

Auditing

Catalog and Schema Management

Users and Roles


System Management

System privilege

Data Import and Export

32

Package Privilege – Assign to users/roles Types of privileges

In the SAP HANA studio, you can manage the package privileges on the Package Privileges tab

The SAP HANA database repository is structured hierarchically with packages assigned to other packages as subpackages. If you grant privileges to a user for a package, the user is automatically also authorized for all corresponding subpackages.


33

Package Privilege – Create package & subpackage Types of privileges The SAP HANA database repository is structured hierarchically with packages assigned to other packages as subpackages. If you grant privileges to a user for a package, the user is automatically also authorized for all corresponding subpackages.

Create a package (right click under « Content »)


Create a subpackage (right click under a package)

34

Package Privilege – Native & Imported Types of privileges In the SAP HANA database repository a distinction is made between native and imported packages. Native packages are packages that were created in the current system and should therefore, be edited in the current system. Imported packages from another system should not be edited, except by newly imported updates. An imported package should only be manually edited in exceptional cases. Hence different privileges are required to manage Native or Imported privileges

Exported packages SAP HANA 1

Exported packages

Exported packages

SAP HANA 2

Package can be edited Package cannot be edited, only import changes on the package © 2012 SAP AG. All rights reserved.

35

Analytic Privilege - The concept Types of privileges Analytic Privileges are used to control access to SAP HANA data models

Without Analytic Privilege, no data can be retrieved from

Attribute Views Analytic Views Calculation Views

Implement row-level security with Analytic Privileges

Restrict access to a given data container to selected Attribute Values

Field from Attribute View Field from Attribute View used in Analytic View Private Dimension of Analytic View Attribute field in Calculation View Combinations of the above Single value, range, IN-list


36

Analytic Privilege - The concept Types of privileges Analytic Privileges are used to control access to SAP HANA data models

Without Analytic Privilege, no data can be retrieved from

Attribute Views Analytic Views Calculation Views

Implement row-level security with Analytic Privileges

Restrict access to a given data container to selected Attribute Values

Field from Attribute View Field from Attribute View used in Analytic View Private Dimension of Analytic View Attribute field in Calculation View Combinations of the above Single value, range, IN-list


37

Analytic Privilege - Start creation wizard Types of privileges Analytic Privileges are repository objects Create and manage via SAP HANA Studio Create in any package Does not need to be the

same package as views

Call creation wizard: Right-click folder “Analytic Privileges” in package Enter name Click Next

and description


38

Analytic Privilege – Select Information Models Types of privileges

Select applicable Information Models Views have two functions in privilege

Views you want to grant access to Views from which you want to select fields for restrictions

You can add further views to the privilege later


39

Analytic Privilege - Editor Overview Types of privileges

Restrictions apply to all views in list of “Reference Models” Choose “Add” in “Reference Models” section

Pick any appropriate view From any package

Do not use the “Applicable to All Content Models”-option Reason:

Can have surprising side-effects You give away control over model access


40

Analytic Privilege - Select field for attribute restriction Types of privileges You may implement value restrictions for all selected fields If no value restriction implemented Æ no restriction (wildcard) Otherwise: user will only be allowed to see listed values UI offers single value or range condition Can add several conditions per field (combined via “AND”)


41

Analytic Privilege Types of privileges Like views: activation required to create run-time object Only run-time object is grantable to users / roles

Name of run-time object: “/”

OR


42

Analytic Privilege Check Types of privileges

The Analytic Privilege Check evaluates Analytical Privileges:

Granted to the User

With Cube restriction covering the view

With currently valid Validity restrictions

With Activity restrictions (READ)

With Dimension restrictions covering attributes of the view

If no Analytic Privilege for the user can be found, user queries are rejected with a “…not authorized” error message.


Trace file in SAP HANA Studio Administration – Diagnosis Files

43

Summary Types of privileges


The Authorization Concept, Explain What is SQL Privilege, Explain What is SYSTEM Privilege, Explain What is Package Privilege, Explain What is Analytic Privilege, Explain What is a Template Role.


44


Objectives Template Roles


The Purpose Of the Pre-Delivered Roles, Explain Which role is Required for Information Composer.


46

Overview Template Roles

This module covers the following topics: Pre

Delivered Role, Role for Information Composer.


47

Pre-delivered Roles Template Roles SAP HANA comes with several pre-defined / standard roles : Roles that should (must) be used unchanged PUBLIC – minimal privileges for a user to work with the database at all Is implicitly granted whenever a user is created

Role templates

CONTENT_ADMIN – the only role in the system with vital privileges, e.g.:

MODELING – a very richly privileged role that enables

SQL Privileges on Schema _SYS_BIC – with GRANT OPTION SQL Privileges on Schema _SYS_BI – with GRANT OPTION Creation and activation of Information Models Creation and activation of Analytic Privileges

MONITORING – role with full read-only access to all meta data, monitoring and statistics

Regard these roles as “templates” Æ name change coming soon

Do not use these roles – build your own roles instead


48

Information Composer Role Template Roles The SAP HANA Information Composer is a Web application that allows you to upload and manipulate data on the SAP HANA database. The SAP HANA Information Composer uses the SAP NetWeaver Core Engine for Partners 1.0 (LJS 1.0), which interacts with the SAP HANA database. The roles required to access to the SAP HANA Information Composer client: IC_MODELER role: This role allows users to upload new content into the SAP HANA database and to create physical tables and calculation views. IC_PUBLIC role : This role allows users to see the shared physical tables and calculation views

As long as the SAP HANA Information Composer is in use, the SAP_IC user must not be deleted. Otherwise, the IC_MODELER and IC_PUBLIC roles will also be deleted.


49

Summary Template Roles


the purpose of the pre-delivered Roles, Explain which role is required for Information Composer.


50


Objectives Administrative


How To Deactivate a User, Explain How To Reactivate a User, Explain How To Reset a Locked User, Explain How To Manage User Password.


52

Overview Administrative

This module covers the following topics: Deactivate

/ Reactivate User, Manage Connection Attempt, Set Initial Password to User, Force User To Change Password.


53

Deactivate / Reactivate a user Administrative Deactivation of Users The administrator can deactivate a user account with the following SQL command: ALTER USER DEACTIVATE USER NOW; After the user account is deactivated, the user cannot log on to the SAP HANA database until the administrator resets the user’s password. Reactivation of Users The administrator can reactivate a user account. A user account can be locked because of the following reasons:

The user’s password has expired.

The user has made too many invalid logon attempts.

If the user’s password has expired, the user has to change the password to a new value. If the user has made too many invalid logon attempts, the administrator can use an SQL command to unlock the user account.


54

Deactivate / Reactivate a user Administrative Deactivation of Users The administrator can deactivate a user account with the following SQL command: ALTER USER DEACTIVATE USER NOW; After the user account is deactivated, the user cannot log on to the SAP HANA database until the administrator resets the user’s password. Reactivation of Users The administrator can reactivate a user account. A user account can be locked because of the following reasons:

The user’s password has expired.

The user has made too many invalid logon attempts.

If the user’s password has expired, the user has to change the password to a new value. If the user has made too many invalid logon attempts, the administrator can use an SQL command to unlock the user account.


55

Deactivate / Reactivate a user in SAP HANA Studio Administrative Prerequisite: System Privilege USER ADMIN

Deactivate

Reactivate


56

Manage connection attempts Administrative The number of invalid logon attempts allowed is set to 6 by default). Which means after too many wrong attempts, the user will be locked. The Administrators can reset the number of invalid logon attempts with the following SQL command: ALTER USER RESET CONNECT ATTEMPTS ; With the first successful logon after an invalid logon attempt, an entry is made into the INVALID_CONNECT_ATTEMPTS view showing:

The number of invalid logon attempts since the last successful logon

The time of the last successful logon

Administrators and users can delete the information of invalid logon attempts with the following SQL command: ALTER USER DROP CONNECT ATTEMPTS;


57

Manage user password Administrative Defines whether users have to change their initial passwords at first logon. Logging on with the initial password is still possible but only ALTER USER PASSWORD ; the command can be executed. All other statements give the error message "user is forced to change password". Administrators can force a user to change the password at any time with the following SQL command: ALTER USER FORCE PASSWORD CHANGE


58

System Tables and Monitoring Views Administrative System tables and monitoring views query information about the system using SQL commands. The results appear as tables in SYS Schema. Some of the tables and views support User Management. For Example: Tables: P_Users

All users

P_User_

Kerberos Kerberose users

P_User_SAML

SAML users

P_Password

Password change time

Views: Invalid_ Connection_Attempts

Number of invalid connection attempts for a user

Granted_Privileges

Privileges granted to Users and Roles

Granted_Roles SAML_Providers


Roles granted to Users or other Roles SAML Providers

59

Summary Administrative


How To Deactivate a User, Explain How To Reactivate a User, Explain How To Reset a Locked User, Explain How To Manage User Password.


60


1

Unit 8: Data Provisioning using SLT Positioning and Key Concepts Overview on Configuration Aspects Data Replication at a Glance Administration and Monitoring at a Glance SLT based transformation concepts and Advanced Replication settings Extension of (target) table structure and Partitioning Transformation of Data Filtering and Selective Data Replication Specific Considerations Appendix

Objectives Positioning and Key Concepts

At the end of this Lesson you will be able to: Explain the positioning of SAP LT Replication Server Describe the key concepts and features List the prerequisites and how to set up the SAP LT Replication Server Name the benefits of the trigger-based replication approach


3

Overview Positioning and Key Concepts

This module covers the following topics: Product name, positioning and key benefits Commercial aspects and software shipment Overview on key concepts, features and user interfaces Overview on key installation and configuration steps


4

Product Name

SAP Landscape Transformation Replication Server for SAP HANA


5

SAP LT Replication Server for SAP HANA Leverages Proven SLO Technologies

SLO* technologies have been used since more than 10 years in hundred of projects per year

Key offerings foster SAP‘s Application Lifecycle Management concept

SAP LT Replication Server - as a new use case - leverages several SLO technologies

Application Lifecycle Management

*) System Landscape Optimization © 2012 SAP AG. All rights reserved.

6

Positioning and Key Benefits of SAP LT Replication Server for SAP HANA

Key Benefits of the Trigger-Based Approach: Allows real-time (and scheduled) data replication from SAP and NON-SAP sources, replicating only relevant data into HANA Ability to migrate data into HANA format while replicating data in real-time „Unlimited“ release coverage (from SAP R/3 4.6C onwards) sourcing data from SAP ERP (and other ABAP based SAP applications) Leverages proven SLO technology (Near Zero Downtime, TDMS, SAP LT) Simple and fast set-up of LT replicator (initial installation and configuration in less than 1day) and fully integrated with HANA modeler UI

SAP LT Replication Server is the ideal solution for all HANA customers who need real-time or scheduled data replication sourcing from SAP and NON-SAP sources © 2012 SAP AG. All rights reserved.

7

Commercial Aspects and Software Shipment

Commercial Aspects SAP LT Replication Server for SAP HANA will be part of SAP HANA software license model

Software Shipment SAP LT Replication Server for SAP HANA will be part of SAP HANA software shipment and fully integrated into the SAP HANA modeler UI


8

Overview - Trigger-Based Approach Positioning and Key Concepts SAP LT Replication Server does not have to be a separate SAP system and can run on any SAP system with SAP NetWeaver 7.02 ABAP stack (Kernel 7.20EXT)

HANA Studio

Application Table

Trigger Based Delta Recording

Replication Configuration

RFC Connection

Replication Engine

DB Connection Application Table

SAP source system

SAP LT Replication Server

Connection(s) between source system and SAP HANA system are defined as “Configuration” on the SAP LT Replication Server


SAP HANA system Data load and replication are triggered via SAP HANA Studio

9

Architecture and Key Building Blocks Positioning and Key Concepts

Read module

RFC Connection

Structure mapping & Transformation

Write module Application table

Logging table

DB Connection Application table

DB trigger

SAP source system

Efficient initialization of data replication based on DB trigger and delta logging concept (as with NearZero downtime approach) © 2012 SAP AG. All rights reserved.


Flexible and reliable replication process, incl. data migration (as used for TDMS and SAP LT)

SAP HANA system

Fast data replication via DB connect LT replication functionality is fully integrated with HANA Modeler UI

10

Architecture for Non-SAP Source Replication Positioning and Key Concepts

DB Connection

Read module Structure mapping & Transformation

Application table

Logging table

Write module DB trigger

Non SAP source system

DB Connection Application table


SAP HANA system

In a first step, SAP LT Replication Server transfers all metadata table definitions from the non-SAP source system to the HANA system. From the HANA Studio perspective, non-SAP source replication works as for SAP sources. When a table replication is started, SAP LT Replication Server creates logging tables within the source system. As a difference, the read modules are created in the SAP LT Replication Server. The connection the non-SAP source system is established as a database connection. © 2012 SAP AG. All rights reserved.

11

Multi System Support 1/2 Positioning and Key Concepts

System A

Schema 1

System B

Schema 2

Source systems

SAP HANA system

System A

Schema 1

System B

Schema 1

Source systems

SAP HANA system


Source systems are connected to separate HANA schema on the same HANA System

Source systems are connected to separate HANA systems. Schema name can be equal or different

12

Multi-System Support 1/2 Positioning and Key Concepts N:1 Replication System A Schema 1

System B Source systems

SAP HANA system

Source systems are connected to same HANA system and also the same schema

1:N Replication

Schema 1

System A SAP Source system

Schema 2

SAP HANA systems

SAP source system is connected to separate HANA systems or to the same system with different schema name.

If one source system is connected to several target schemas (currently up to 1:4 supported), the relevant target schema can be selected in the data provisioning UI.


13

Set-up of LT Replication Server Positioning and Key Concepts Installation aspects Source system(s): use respective DMIS add-on LT replication server: use add-on DMIS_2010_1_700 with SP5-7; other system requirements (NW 7.02; SAP Kernel 7.20EXT) apply Apply SPS04 for SAP HANA 1.0

Configuration steps for SAP LT Replication Server Define a schema for each source system Define connection to source system Define DB connection into SAP HANA Define replication frequency (real-time; frequency for scheduled replication) Define maximum number of background jobs for data replication

Set-up of data replication in SAP HANA Select relevant source system Start (initial load only and / or continuous replication)


14

Key Configuration Steps Positioning and Key Concepts

Call SAP LT Replication Server Configuration (Transaction: LTR)

Define configuration data


15

Starting the Data Replication Positioning and Key Concepts Choose data provisioning to launch SAP HANA Modeler UI

1. Select source system and target schema as defined in SAP LT Replication Server; related system information and schema will be displayed 2. Use button Load and / or Replicate to set up the data replication 3. Use button Stop Replication to finish replication 4. Use button Suspend to pause replication 5. Use button Resume to continue replication


16

DB Supportability Matrix (HANA 1.0 SPS04): Loading Data via SAP LT Replication Server for SAP HANA Database

Technical availability SAP Sources

Non SAP Sources (*)

MSFT SQL Server Enterprise Edition

OK

OK

Oracle Enterprise Edition

OK

OK

IBM DB2 LUW/ UDB (DB6)

OK

OK

IBM DB/2 zSeries

OK

OK

IBM DB2 iSeries (former AS/400)

OK

Planned

IBM Informix

OK

Planned

SAP MaxDB

OK

OK

Sybase ASE

OK (with DB-Version 15.7.0.11)

OK (with DB-Version 15.7.0.11)

For non-SAP source systems, the customer database license needs to cover a permanent database connection with 3rd party products like LT replication server. (*) Since a DB connection from LT replication server to a non-SAP system is required, the OS/DB restrictions of NetWeaver 7.02 apply (see at http://service.sap.com/pam)


17

SAP LT Replication Server - Technical Enabler for Multiple Data Provisioning Use Cases Table-base Replication integrated into HANA Studio Real-time for SAP and NON-SAP sources

Replication engine for existing RDS Solutions and ERP Accelerators

Replication engine for new SAP HANA Application Accelerators

+ serving HANA in the Cloud


18

Summary Positioning and Key Concepts

You should now be able to: Explain the positioning of SAP LT Replication Server Describe the key concepts and features List the prerequisites and how to set up the SAP LT Replication Server Name the benefits of the trigger-based replication approach


19


Objectives Overview on configuration aspects

At the end of this Lesson you will be able to: Describe the set-up of a configuration on the SAP LT Replication Server Explain the impact of the configuration set-up on the data replication


21

Concept: Define Configuration / Schema Overview on configuration aspects

Schema 1 Replication Configuration

Trigger-based delta recording

SAP source system

RFC Connection

Replication engine

DB Connection


SAP HANA system

A new configuration can be created in the LT Configuration and Monitoring Dashboard. In that step, the connection between the source and the HANA system is established and the target schema will be created (if it doesn’t exist already). Also replication control tables are created and table lists are replicated from the source system. In addition, the required roles and GRANT / REVOKE procedures are generated. © 2012 SAP AG. All rights reserved.

22

Creating a New Configuration for SAP Sources Overview on configuration aspects General Data Define the replication target Schema Name in the HANA system (if schema does not exist, it will be created automatically) Define the Number of Replay Jobs used for data load and replication

Connection to the source system SAP Source System: Use previously defined RFC destination to source system Non SAP Source System: Select the source database system and set the required fields (see also next slides)

Connection to HANA system Define the User Name and Password which can be used to connect to the HANA system (see also next slide) Define the Host Name and Instance Number of the target HANA system

Allow Multiple Usage to allow usage of source system in different configurations (1:N replication)

Read from Single client Flag for client specific load and replication. Read will be only from the client which is specified in RFC connection

Table space assignment Optional: define table space for logging tables. If no table space is defined, logging table will be created in the same table space as the original table. Own table space is recommended for easier monitoring of the table sizes of the logging tables

Replication Mode Replication can be executed in Real-time mode or in Scheduled mode © 2012 SAP AG. All rights reserved.

23

Creating a New Configuration for Non-SAP Sources Overview on configuration aspects

To replicate from non-SAP source system select Legacy and the affected database system.

Depending on the database system, additional required information needs to be specified (e.g. for DB2 specify the DB connection and the table space name). Start with transaction LTR © 2012 SAP AG. All rights reserved.

24

Results of Creating a New Configuration Overview on configuration aspects When the popup to create a new configuration is closed by pressing the OK button, the following actions are performed automatically: Configuration settings are saved on the LT Replication Server New user and schema are created on the HANA system with the defined target schema name (not performed if an existing schema is reused) Replication control tables (RS_* tables) are created in target schema User roles for the target schema are created: _DATA_PROV -> Role to manage data provisioning _POWER_USER -> Contains all SQL privileges of the target schema _USER_ADMIN -> Role to execute authority procedures (see below) A procedure to grant (RS_GRANT_ACCESS) or revoke (RS_REVOKE_ACCESS) are created in the target schema Replication of tables DD02L (stores the table list), DD02T (stores the table short descriptions) and DD08L (R/3 DD: relationship definitions) is started automatically. Once those tables are replicated , the HANA studio knows which tables are available in the source system SYS_REPL and table RS_REPLICATION_COMPONENTS are created (if they don’t exist already based on a previous configuration) Replication is registered in table RS_REPLICATION_COMPONENTS


25

Summary Overview on configuration aspects

You should now be able to: Describe the set-up of a configuration on the SAP LT Replication Server Explain the impact of the configuration set-up on the data replication


26


Objectives Data Replication at a Glance



Explain the different option for data replication and the related implications

28

Overview Data provisioning at a glance


Load and replicate data

Suspend and resume data replication of certain tables

Stop and Restart the master job of a configuration in SLT


29

Launch Data Provisioning UI Data provisioning at a glance All further data provisioning steps are executed from the HANA Studio. Therefore, switch to the HANA Studio, choose the perspective Information Modeler and start the quick launch. Select your system and start the Data Provisioning via the link in section DATA.

If you cannot enter this screen or do not see any data, verify if a data provisioning role was assigned to your user.


30

Start Load / Replication Data provisioning at a glance Press the respective button to Load the current data of a table from the source system

Replication includes load and delta replication

Replicate a table includes the load of the current data and the replication of all changes in the source system

Choose a table from the list or enter a search string to search for a specific table. Use the button Add to select the table. Once all relevant tables are selected, load / replication is triggered when popup is closed via button Finish.


31

Start Load/Replication - Executed Activities Data provisioning at a glance

Read module

RFC Connection

HANA Studio

Structure mapping & transformation

Write module Application Table

Logging Table


DB Trigger

SAP source system


SAP HANA system

Data provisioning can be managed via the HANA Studio modeler view using the data provisioning. If a table is started for load or replication, the LT runtime objects (reader, mapping and transformation and writer modules) are generated in the respective systems. If tables are selected for replication, also the delta recording (logging table and DB trigger) is activated on the source system. © 2012 SAP AG. All rights reserved.

32

Stop / Suspend Replication Data provisioning at a glance Press the respective button to Stop the replication and also to stop delta recording for that table (deletes DB trigger!) Suspend data replication but keep delta recording active Resume a previously suspended data replication The table selection popup will look similar as for Load and Replication. In case of Stop Replication or Suspend, only those tables can be selected which are already in replication mode. In case of Resume only those tables can be selected which are in suspend mode.

Please be aware that in case a replication is stopped and started again, the corresponding table will be dropped. The initial load must be repeated as delta recording was deactivated for a certain time and changes might not be recorded. So in case you only want to pause the delta replication, use the mode Suspend and Resume as delta recording is not deactivated and the replication can be continued without a need of a new initial data load.


33

Stop/Suspend Replication - Executed Activities Data provisioning at a glance

Stop data replication Application Table

Logging Table

Delete trigger & Log. tab. Application table

DB Trigger

SAP source system


SAP HANA system

Stop replication

Application table Logging table

Stop data replication Application table

DB Trigger

SAP source system Suspend replication

Application table Logging table


SAP HANA system

Continue data replication Application table

DB Trigger

SAP source system Resume replication © 2012 SAP AG. All rights reserved.


SAP HANA system

34

Configuration and Monitoring Dashboard Master Job settings – Stop and Restart 1 Choose configuration and change

to Edit mode

2 The master job can now easily be

stopped and restarted for the relevant configuration on SLT Replication Server. The whole replication for a configuration will be stopped.

Triggers in source systems are still active and working so the logging tables will be filled continuously indepndent of stopping the master job on SLT Replication Server! Stopping option is usefull to pause the replication of a configuration temporary only. © 2012 SAP AG. All rights reserved.

35

Summary Data provisioning at a glance



Explain the different option for data replication and the related implications

36


Objectives Administration and Monitoring at a Glance



Explain the different option for monitoring the replication process

38

Status Monitoring in HANA Studio Data provisioning at a glance The load / replication status can be monitored in the Data Load Management screen within the data provisioning tool. In this section, the current status of all relevant tables of the selected source system / target schema is displayed.


39

Configuration and Monitoring Dashboard Enhanced Statistics

Additional statistical data can be displayed in the “Statistics” tab page. You can view the number of records that have been replicated, and the relevant operation (insert, update, delete).


40

SAP Replication Manager - Mobile Application 1/2 Benefits and Requirements Monitor

Monitor the data replication process and system parameters.

Execution

Trigger execution of important data replication functions.

Higher Flexibility

Application can be run anytime and anywhere from a mobile which is connected to the internet.

Analytical View

Provide an analytical perspective of real-time data replication in terms of latency. Infrastructure Requirements


SUP2.1 Gateway (NW 7.02) (Minimal gateway) Backend IW_BEP 200 ( SP2.0) IW_BEP ,GW add-on DMIS (DMIS_2010) SLT system should be a NW 700 EHP2 with SAP Kernel 7.20 EXT 41

SAP Replication Manager - Mobile Application 2/2 Screenshots

Analytical View Execute

Monitor

Higher Flexibility © 2012 SAP AG. All rights reserved.

42

Summary Administration and Monitoring at a Glance



Explain the different option for monitoring the replication process

43

Unit 8: Data Provisioning using SLT Positioning and Key Concepts Overview on Configuration Aspects Data Replication at a Glance Administration and Monitoring at a Glance SLT based transformation concepts and Advanced Replication settings

Extension of (target) table structure and Partitioning Transformation of Data Filtering and Selective Data Replication Specific Considerations Appendix

Objectives SLT based transformation concepts


Explain the basics of the SLT based transformation concepts

Leverage the data processing and transformation process

Explain how to change advanced replication settings for certain configurations of SLT Replicaion Server


45

Overview SLT based transformation concepts


Details on the basic concept of SLT based transformation

Trigger conditions in detail


46

Concept SLT based transformation concepts The main purpose of SAP HANA is reporting data of particular ERP content. Generally this will be done with an one-to-one replication of ERP table into the new database. Depending on the customer-specific requirement, it’s sometimes necessary to filter, to change, or to extend the original data within the load process into HANA. Key Use Cases:

Conversion of data Î Change data within data replication/loading

Filtering Î Reduce number of records to be replicated

Structural changes of target table in HANA Î Add, remove and/or change type of fields in target table

Partitioning of target table in HANA Î Partitioning within data replication/loading


47

Concept SLT based transformation concepts Currently, transformation & filtering topics are only provided by project base. (Only for SAP lead projects)


48

Concept - Data Processing SLT based transformation concepts

1 Start of Replication Cycle 2 RFC Connection

…

Get next portion from source

3

Loop through portion

4

Map fields of a record to receiver structure

Data Portions

… Data Portions

5 Source system

DB Connection

Add record to sender portion


SAP HANA system

The

data for transformation will be split into portions (default: load: 10,000 lines, replication: 5,000 lines)

The

portions will be processed successively, mapped, and transferred to sender

Extension

of functionality (for example data conversion) is possible at several points of the process using ABAP includes Î Transformation rules

Implementation

of rules, structure changes of tables, partitioning of tables etc. will specified within Advanced Replication Settings (Transaction IUUC_REPL_CONTENT)


49

Specify Advanced Replication Settings Combined Transaction for all Table Settings (1) General features of Advanced Replication Settings:

The UI is designed maintain customizing tables of SLT Replication server. This customizing is used to set-up structure changes of tables partitioning of tables in SAP HANA change and/or filter of data performance optimization to use parallel read withtin data replication. Define tables settings

Select Configuration

Select Table(s) © 2012 SAP AG. All rights reserved.

Save 50

UI to Specify Advanced Replication Settings Combined Transaction for all Table Settings (2) Transaction IUUC_REPL_CONTENT 2 Select table

1

Select Configuration

Transport settings / transformation content via upload / download to other SLT system. D ef se ine tti ta ng bl s e

3


51

Specify Advanced Replication Settings Define Table Settings – Process Flow

Add Table

Enter Tablename

Choose Appropriate Tabfolder


Save

Specify Advanced Replication Setting for Table

52

Specify Advanced Replication Settings Define Table Settings

1 Enter table name

2 Choose appropiate tab page: IUUC_REPL_TABSTG ÎDefine table deviation and partitioning IUUC_ASS_RUL_MAP ÎTransformation rules to change and/or filter data IUUC_SPC_PROCOPT ÎTrigger specific adaption IUUC_PERF_OPTION ÎPerformance optimization to use parallel read

Ability to import or export settings from / to file (only SLT table settings from new UI)


53

Advanced Replication Settings Export/Import Settings– Process Flow

Choose Import Option

Save Settings from Dev-System

ImportSettings into Target SLT Server


Set Parameter if Option ‚Load Selected Settings‘ was chosen

54

UI to Specify Advanced Replication Content Export/Import Settings Screenshots 3

2

Choose Import Option

Possibility to compare and load settings selectively for each table

to in r gs rve n i tt Se e T S rt t SL o p e Im arg T

For the export / import options, only the settings of Customizing tables will be uploaded and downloaded. Î No objects (for example ABAP includes of rules) are exported or imported. If you import settings, the mass transfer ID of the relevant SLT configuration must be entered.

1 Save Settings from Dev. System © 2012 SAP AG. All rights reserved.

55

Summary SLT based transformation concepts


Explain the basics of the SLT based transformation concepts

Leverage the data processing and transformation process

Explain how to change advanced replication settings for certain configurations of SLT Replicaion Server


56


Objectives Change of (target) table structure and Partitioning


Explain the concept of extending a table

Specify your own extended structures

Explain how to implement partitioning


58

Business Example Change of (target) table structure Change of Table Structures within Transformation RFC Connection

Source system


Scenarios

add table fields like Source System ID Î recommended for N:1 Replication into same table

get rid of certain table fields in HANA

change type of table fields (cast of values has to be defined within transformation rules if needed)


DB Connection

SAP HANA system

Options of Implementation 1.

By using a template table Define template tables of target structure in Sender system or SLT Server change table settings in Replication Settings

2.

By defining Table Deviation in Advanced Replication Settings

59

Specify Structure Changes Setup changes in Advanced Replication Settings name of table in SAP HANA (optional)

Structural change by using a template table name of table with structure to be used as template for table

Structural change by defining Table Deviation Click on Edit Table Structure

Î ‘X’: table of target type was created in LTReplication Server Î ‚ ‚ : table of target type was created in sender system


60

Specify Structure Changes Define Table Deviation A different target structure can be defined - without having to create the structure in the DDIC. The table deviation can be defined in table IUUC_REPL_TAB_DV. In this case, only the deviation must be defined (ignore, change, or add certain fields). All other fields are derived from the source system. The table deviation can be defined in the replication content UI. There is no extra option to set the key flag of a new table field. If the new field should become a key field, you have to set the position of the new field so that the field in the following line is also a key field.


61

Specify Structure Changes Example - Extend table with Key Field SOURCE_SYS_ID 1

2

Edit Table Structure

Define AdditionalField on HANA

3 Start Load/Replication in HANA


62

Specify Table Settings Enhanced Table Settings - Partition There is an additional field (PARTITION_CMD) in table IUUC_REPL_TABSTG where a partitioning command can be defined for certain tables. The partition command will be added to the create statement of the table on the HANA System.

The partition command has to be entered in the same way as it would be defined in the SQL editor of the HANA studio, for example: PARTITION BY HASH (a, b) PARTITIONS 4 SLT will add the partition command when generating the SQL command to create the table. For example: CREATE COLUMN TABLE mytab (a INT, b INT, c INT, PRIMARY KEY (a,b)) PARTITION BY HASH (a, b) PARTITIONS 4

HANA WIKI - https://wiki.wdf.sap.corp/wiki/display/ngdb/Partitioning Note that instead of the partitioning command, each SQL parameter (e.g. for localization) is possible © 2012 SAP AG. All rights reserved.

63

Specify Table Settings Further settings

NO_DROP ROW_STORE RD_PORTION_SIZE


Î data in HANA will not be deleted before the replication Î target table in HANA will be created as row table instead of column table Î no of records per portion; empty means default value

64

Summary SLT based transformation concepts

You should now be able to: Explain the concept of extending a table Specify your own extended structures Explain how to implement partitioning


65


Objectives SLT based transformation concepts


Explain the different types of rules to change data within replication

Specify the parameter in advanced replication settings


67

Business Example Transformation data Change of Data within Replication by using Transformation Rules RFC Connection

Source System

DB Connection

Transformation of Data

LT Replication Server

SAP HANA System

Scenarios

Options of Implementation

To make certain fields anonymous Î HR reporting

1.

Applying Parameter Rules

To fill initial fields e.g. created by change of table stucture

2.

Applying Event-Based Rues

To convert units or currency and recalculate amounts and

values © 2012 SAP AG. All rights reserved.

68

SLT based Transformation Concepts Types of Transformation Rules Generally, there are two types of transformations rules, which can be used within transformation both has to be created as ABAP include, which can be embedded into replication

Types of Transformation Rules Parameter-based rules Less flexible than event-based rules Easy to create by using parameters Event-based rules More flexible than parameter-based rules Knowledge of data processing within the SAP LT Replication Server needed to select the right event for the specific business scenario has access to all fields of a record © 2012 SAP AG. All rights reserved.

69

Specify Data Transformation Setup changes in Advanced Replication Settings

Data Transformation by using a Parameter Rule

Data Transformation by using a Event-Based Rule

no event = Parameter Rule

event of the rule

Parameters of the rule, which will be used in the ABAP Include

not in use for event-Based Rules

name of ABAP Include containing the rule

name of ABAP Include containing the rule


70

SLT based Transformation Concepts Details - Parameter Rules Parameter Rules will be performed after the record is mapped to the receiver structure and before it will be added to the sender portion

Import Parameters are used within ABAP-Include to apply values, which were determined in Advanced Replication Settings if no parameter has to be used within the rule, IMP_PARAM_1 has to be set to ‘DUMMY’! values of fields of the sender record will be addressed in the include using the following name:

IMPORT PARAMETER 1: IMPORT PARAMETER 2: IMPORT PARAMETER 3:

i__1 (for example, i_mandt_1) i__2 i__3

if you have defined a literal the technical name will be:

IMPORT PARAMETER 1: IMPORT PARAMETER 2: IMPORT PARAMETER 3:

i_p1 i_p2 i_p3

Export Parameter specifies the fieldname of the receiver structure, which has to be filled wihtin the parameter rule Addressed in the include using the following name:

EXPORT PARAMETER: © 2012 SAP AG. All rights reserved.

e_ (for example, e_mandt). 71

SLT based Transformation Concepts Example - Fill Source_SYS_ID within Parameter Rule 1

2

Create ABAP include

Specify Advanced Tables Settings for the table

3

Start Load/Replication in HANA


72

SLT based Transformation Concepts Details – Event-Based Rules Event based Rules and Parameter Rules can be performed at several events of the transformation process the event has to be determined in Advanced Replication Settings Fields can be directly addressed in ABAP-Includes using field symbols (event BOL, BOR, EOR and EOL only) Fields of Sender Structure - (for example, -mandt)

Fields of Receiver Structure - (for example, -mandt) Typical use-case for an event-based rule if you have to apply a mapping to figure out the value of the field to be changed with the rule, a select statement could be executed for each record wihtin a parameter rule. This could decrease the overall performance of the replication dramatically. By creating an event based rule at the beginning of the replication process (BOP or BOT) an internal table containing the mapping information could be initialized once. In a second event-based rule where the records will be processed, the mapping information can be taken from internal table (in memory) instead of a select to database. So the overall performance can be optimzed by applying the eventbased rules. © 2012 SAP AG. All rights reserved.

73

SLT based Transformation Concepts Events of Event-Based Rules BOP (Begin of Processing):

Start Load / Replication BOP – Begin of Processing

Processed only once, before the data transfer really starts.

DO

Can be used to initialize certain global fields that might be used in subsequent events (for example, fill internal mapping tables)

Get next portion from source system EOP (End of Processing): BOT – Begin of Block

LOOP AT source

BOT (Begin of Block):

BOL – Begin of Loop

MOVE-CORRESPONDING source To target

Processed only once, after the data transfer is completed. Access to all data records of a portion read from the sender system

EOT (End of Block):

Access to all data records immediately before they are passed to the receiver system

BOR – Begin of Record

BOL (Begin of Loop):

individual field mapping

EOR – End of Record EOL – End of Loop

EOL (End of Loop):

ENDLOOP Write to target system EOT – End of Block

Like BOT if only one table is included in the conversion object; in case of objects with multiple tables, it can be applied to each specific table Like EOT if only one table is included in the conversion object; in case of objects with multiple tables, it can be applied to each specific table

BOR (Begin of Record):

This event is processed before the field mapping of the individual fields is started.

ENDDO

EOR (End of Record):

EOP – End of processing


This event is processed after the field mapping of the individual fields of a certain data record has finished. 74

SLT based Transformation Concepts Example - Fill Source_SYS_ID within Event-Based Rule 1

2

Create ABAP include

Specify Advanced Tables Settings for the table


75

SLT based Transformation Concepts Transformation Rules - Insert Line of Coding

Instead of an ABAP routine, which contains the transformation rules, it is possible to enter one line of coding (max 72 characters) directly.

If the line of coding is defined, no ABAP include will be performed.

This option is suitable for implementing simple parameter rules to fill table fields, such as the source system ID.


76

Summary Extension of (target) table structure


Explain the different types of rules to change data within replication

Specify the parameter in advanced replication settings


77


Objectives Selective data replication


Explain the benefit of filtering

Explain the concept of filtering within SLT

Specify your own filtering rule

Define trigger adjustments


79

Business Example Selective data replication / filtering Reduce Number of Records to Be Replicated by Filter RFC Connection

Source system


Scenarios

Replicating certain data only Î Only data of specific years should be used in HANA, for instance ÎReplication of data active tables as they are in DDIC tables DD02l, DD03l etc.


DB Connection

SAP HANA system

Implementation

Either by trigger adjustment in source system or by rules (event-based or parameter-based)

80

Concept - Filtering Selective data replication Conditional filter (in SLT) by parameter-based or event-based rules Î To skip a record from load and replication macro SKIP_RECORD can be used in the code of the include. In fact, all data (means not filtered data) will be transferred from the source system to SAP LT Replication Server, but not forwarded to HANA Î means SLT system will read all data from the source and write only relevant data into HANA. No database-specific knowledge needed Valid for both initial load and replication

Selected delta replication (“Trigger Adjustments”) ÎDone by trigger adjustment directly of data base of the source system Î Will be implemented using tabfolder in advanced Replication Settings IUUC_SPC_PROCOPT. For experts only Database-specific Will be implemented in source system Decreases the total amount of data to be extracted from source system Performance advantages because of reduction of triggered and transferred data Only valid for replication data; it is not working for initial load. © 2012 SAP AG. All rights reserved.

81

Example: Filtering by Company Code Selective data replication

Realized with a parameter rule

RESULT: Only records where MJAHR(FiscalYear ) = ‚1998‘ were transferred to HANA:


82

Define the Trigger Condition SLT based transformation concepts Trigger condition has to be defined in folder IUUC_SPC_PROCOPT of Advanced Replication Settings. Based on the database of the source system, different syntaxes are adapted. Field DBSYS LINE_NO LINE

DBSYS ADABAS D DB2 DB6 MSSQL ORACLE

Action Enter the database type of the source system. You can specify multiple lines if the condition is too complex to be filled in only one line. Enter the trigger condition here. Only when the data change fulfills the condition , it will be recorded into the logging table for the SLT replication. Syntax field1 = 'value0' AND field2 IN ( 'value1', 'value2' ) ___."field1" = 'value0' AND ___."field2" IN ( 'value1', 'value2' ) ___.field1 = 'value0' AND ___.field2 IN ( 'value1', 'value2' ) field1 = 'value0' AND field2 IN ( 'value1', 'value2' ) :___.field1 = 'value0' AND :___.field2 IN ( 'value1', 'value2' )


Sample AS4LOCAL = 'N' ___."AS4LOCAL" = 'N' ___.AS4LOCAL = 'N' AS4LOCAL = 'N' :___. AS4LOCAL = 'N'

83

Define the Trigger Condition SLT based transformation concepts In this example, we want to customize the trigger directly in the source system in a way that only changes to the data with AS4LOCAL = ‘N’ will be recorded.

Please note: trigger filtering will only work for replication phase. If it is required to filter the table during the initial load phase, an additional event (or parameter) filter is also necessary!


84

Summary Selective data replication / filtering


Explain the benefit of filtering

Explain the concept of filtering within SLT

Specify your own filtering rule

Define trigger adjustments


85


Specific considerations Transformation Rules for Cluster Tables Apply SAP note 1662438 at first. After implementing the note, you should follow the steps (table BSEG as an example): For initial load, you should create one entry in IUUC_ASS_RUL_MAP for BSEG and include program for BSEG table For replication, you should create an addition entry in IUUC_ASS_RUL_MAP for RFBLG and include program for RFBLG table


87

Specific considerations Performance Optimization

Default setting for initial load: Reading Type 3 The default setting is 3 jobs for each table for parallel read If more parallel jobs are defined for parallel read than are available for loading, the tables will be processed successively To determine the order of table processing the sequence number (Seq. No.) can be used.


88

Specific considerations Accelerated Load Procedures – Reading types Reading Type

Advantages

Disadvantages

1 – Access Plan Calculation

• Fast data load if index exists

• Additional index may be required

• Parallel data load possible

• Requires a key field which is sufficiently selective • Calculation required before load

3 – DB_SETGET (default)

• No separate index required

4 & 5 – Index Cluster

• Very fast data load after data is extracted to table DMC_INDXCL

• Multi-threading possible with DMIS_2010 SP07

• Additional consumption of database buffer

• Additional table space temporarily required in the source system

• Minimal usage of DB buffer


89


Appendix – Load from SAP Archive – Integration of SLT with SAP Solution Manager

Load from SAP Archive 1/2 Architecture and Key Building Blocks The respective ILM API must be available in the source system. It can be installed by means of SAP Note 1652039 (46C – 731).

RFC Connection

Read Module

Structure Mapping & Transformation

Write Module

ADK Archive Access API


ADK Archive SAP Source system


SAP HANA system

Archived data can be selected by the date of the archiving session.


This presentation and SAP‘s strategy and possible future developments are subject to change and may be changed by SAP at any time for any reason without notice. 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

92

Load from SAP Archive 2/2 Define Load Object Use report IUUC_CREATE_ARCHIVE_OBJECT on SLT Replication Server setup and start the loading process for archive objects.

Select replication configuration Select archive object Define selection criteria

Select the relevant tables within the current archive object


93

Integration of SLT with SAP Solution Manager - Monitoring Capabilities


SAP Solution Manager is set to be the single source of truth for monitoring and incident management of the entire HANA stack.

Ensures proactive information about replication status through alerting and notification capabilities

94

Overview - Monitoring with SAP Solution Manager 7.1 SP5 Replication notifications and alerts are now visible in SAP Solution Manager 7.1 SP5.

SLT monitoring summarizes the following information per configuration: Connectivity to source and target system Status of latency time last 24h replication Status of master and load jobs Trigger status © 2012 SAP AG. All rights reserved.

95

Set-Up of System Monitoring for SLT SLT System monitoring is defined on ABAP technical system level If DMIS Add-on is detected, SAP template “SAP SLT ABAP Addon“ is assigned by default


96

SLT System Monitoring - Connectivity Status Availabile Monitoring Capabilities for SLT Per schema the connectivity status from SLT to the source and to the target system is monitored


97

SLT System Monitoring – Status of Latency Times Performance Monitoring for SLT Per schema the worst rating of average latency of the past 24 hours is reported


98

SLT System Monitoring – Status of Master and Load Jobs Exception Monitoring for SLT Per schema the job status for master and load jobs is monitored


99

SLT System Monitoring – Trigger Status Exception Monitoring for SLT Per schema the trigger status is monitored (worst case for all table triggers)


100


1

Unit 9: Data Acquisition using SAP Data Services Introduction to SAP Data Services Loading data into SAP HANA

Objectives Data Acquisition using SAP Data Services

At the end of this Lesson you will be able to: Explain Data Services capabilities with SAP HANA Load data from SAP ECC source table into SAP HANA using an ABAP dataflow.


3

Solution: One-Stop Solution for Information Management

DATA SERVICES ETL

Data Quality

Data Profiling

Text Analytics

Metadata Management

Data Sources: Structured and Unstructured


4

SAP Data Services – Hadoop Connector Connect unstructured & structured data for greater insight Log Files

SAP HANA - y I n or em

Multi-structured Data Sources

Enterprise Portals

M

Hadoop

Data Warehouses

1

3

Media and Other Files

2

SAP BW

Sybase IQ

Structured Data Disk

Map-Reduce, is a parallel processing paradigm where code is sent to data for instant processing

1

Collect & Store Files are stored in their native

format incurring no transformational costs. Built in fault-tolerance. Commodity hardware and software solution makes Hadoop scale cost effectively. © 2012 SAP AG. All rights reserved.

2

Mobile

Analyze & Process Prepare the data to enable the

class of problems to be solved. Problems like searching, counting, pattern detection lend themselves well to Map Reduce paradigm

Dashboard/ Report

On Demand Services

3

Integrate & Consume

Read from / load into Hadoop Familiar, easy-to-use Data Services UI Enterprise support and Integration

into enterprise infrastructure

5

SAP Data Services – SAP HANA Extract, transform, and load data quickly

Metadata

Modeler

Repository Server Open Hub BW

Any Source


Designer and Management Console

SAP Data Services

Data Load

In-Memory Computing Engine

SAP HANA

6

SAP Data Services 4.0 SP2 with SAP HANA Database SAP Data Services 4.0 SP2 is the minimum release to work with HANA 1.0 SP4

Performance improvements for loading HANA Option in the HANA target for commit size will allow for better tuning. Option to choose column or row type of table in Template tables.

Support for Bulk Updates in SAP HANA (through an intermediate staging table) Direct UPDATE and DELETE statements via ODBC are slow. Option to load all data into a temporary table in HANA, together with an operation code to indicate this row is an INSERT/UPDATE/DELETE. Once all data is in the temporary table a SQL statement get issued to HANA to apply the INSERTS/UPDATES/DELETES to the actual target. Overall this gives a big performance gain because Data Services is only generating (bulk) INSERTs, and the actual UPDATEs/DELETEs are all executed in memory in the HANA engine. Improved ABAP integration to ERP ODP – Operational Data Provider Framework New SAP delivered API implemented on the ERP side


© SAP AG 2012

7

Full extractor support through ODP Full extractor support through ODP data replication API: Data Services can use this API to get initial and delta loads, the data can be streamed to Data Services. Main points: Only “released” extractors are shown to Data Services. Business Suite team releases standard extractors as they are certified for ODP Customer can release generic extractors using transaction RODPS_OS_EXPOSE Delta support through the delta queues (same mechanism as used by BW today) Data is streamed from SAP to Data Services. Overall DS-specific subset of overall ODP functionality released with ECC 6.0 EhP6. Standard extractors need to be “released” by the Business Suite team


8

Unit 9: Data Acquisition using SAP Data Services Introduction to SAP Data Services Loading data into SAP HANA

Process Flow: SAP HANA and SAP Data Services 4

Create a connection to a SAP source system

Create a connection to SAP HANA

Import metadata from SAP BW Extractor to Data Services Repository


Execute a Data Services job to populate SAP HANA

Design a Data Services job to populate SAP HANA

Preview uploaded data

10

Standard vs ABAP Dataflows Standard Dataflow can be used if you have the following requirements: reading a single table small number of columns (data load buffer resticted to 512 Bytes per row. ABAP Dataflow can be used if you have the following requirements: reading multiple ECC tables. push down any join operations to the SAP Application. better performance


11

What is an ABAP Dataflow?

SAP Data Services

SAP Application SAP databases ABAP data flow Data flow

Send program

Read

Job Server

Transport File BAPI calls

SAP database

Read Load

IDOCs Access Server


12

What is an ABAP Dataflow?

Data Flow R/3 data flow Source(s)

SNWD_SO_I(ECC.)


Data Transport

Query

Query

Target

DataTransport976

13

Using Data Services Template Tables If the structures of the tables are similar or identical to the tables in the source system, then it’s not necessary import the meta data prior to executing the Data Services Job. Data Services provides a Template Tables functionality which executes a SQL statement in the target database prior to the data load which generates the meta data.

1 Inside the HANA Data Store select Template Tables, drop it into the Data flow

2 Enter Table name and Owner name (Owner name = schema name)


3 Map the source structure to the template table

14

Steps to load data into SAP HANA using an ABAP Dataflow Step 1: Create ECC and SAP HANA Datastores in the repository.


15

Steps to load data into SAP HANA using an ABAP Dataflow Step 2: Import the ECC table or extractor metadata into the repository.


16

Steps to load data into SAP HANA using an ABAP Dataflow Step 3: Create a Batch Job and add an ABAP dataflow


17

Steps to load data into SAP HANA using an ABAP Dataflow Step 4: Add the ECC source, Query transform and Data Transport in the workspace of the ABAP dataflow.

Double click on ABAP data flow to drill down


18

Steps to load data into SAP HANA using an ABAP Dataflow Step 5: Do the mappings in the Query transforms


19

Steps to load data into SAP HANA using an ABAP Dataflow Step 6: Execute the job and monitor


20

Steps to load data into SAP HANA using an ABAP Dataflow Step 7: Preview data in SAP HANA


21

Summary Data Acquisition using SAP Data Services

You should now be able to: Explain Data Services capabilities with SAP HANA Load data from SAP ECC source table into SAP HANA using an ABAP dataflow.


22


1

Unit 10: Uploading Data from Flat Files Introduction to Flat File Upload Loading flat file data into SAP HANA

Objectives Data Acquisition using Flat File Data Load

At the end of this Lesson you will be able to: Understand the capabilities and positioning of the Flat file data load functionality Load data from Flat Files into the SAP HANA Database


3

New feature in SPS4: Uploading data from flat files

With SPS4, it’s possible to upload data from flat files, available at client file system, to SAP HANA database If the required table for loading the data does not exist in SAP HANA database, it’s necessary to create a table structure that is based on the flat files The application suggests the column names and data types for the new tables and it’s possible to edit them The new table always has a 1:1 mapping between the file and table columns When loading new data in the table, it gets appended to the existing data The application does not allow to overwrite any column or change the data type of existing data The supported file types are: .csv, .xls, and .xlsx Especially suited for Proof of Concepts or projects where only an one-time data load is required

+ Quick and easy data load - No delta logic available; no transformation capabilities Æ 1:1 mapping only © 2012 SAP AG. All rights reserved.

4

Unit 10: Uploading Data from Flat Files Introduction to Flat File Upload Loading flat file data into SAP HANA

Process Flow: Uploading data from flat files

Select Import Source

Select File for Upload

Select Target System


Manage Table Definition and Data Mapping

Select Target Table

Check Target Table

6


In the File Menu, choose import

Expand the SAP HANA Content directory

Select Data From Local File and choose Next


Select Target System © 2012 SAP AG. All rights reserved.



Select Target Table

Check Target Table 7

Select Target System In the Target System section, select the Target System where the data should be imported Choose Next





Select Target Table


Select File for Upload In the Flat File Upload screen, browse for the file which should be uploaded into SAP HANA database If a .xls or .xlsx file has been selected, choose the corresponding worksheet If a .csv File has been selected, select a delimiter If a header row exists in the flat file, select Header row exists and enter row number If only a specific row range should be relevant for the import, remove check for Import all data and enter the start / end line Note: A delimiter is used to determine columns and pick correct data against them. In a .csv file, the accepted delimiters are: , ;





Select Target Table


Select Target Table

For the Target Table, two options are available: New: When selecting New, a new table with the name entered will be generated within the schema chosen. Existing: When selecting Existing, data will be appended to an existing table. Choose Next





Select Target Table



In the Manage Table Definition and Data Mapping screen it’s possible to map the source and the target columns

The application proposes a mapping structure automatically based on the naming

Additionally it’s required to select a Key




Note: Only 1:1 column mapping is supported. Additionally, it’s possible to edit the table definition by changing the store type, data types, renaming, adding or deleting columns


Select Target Table



It’s possible to preview the data based on the flat file chosen

Select Finish to finalize the creation process





Select Target Table


Check Target Table

The table should be available within the schema defined during process step Select Target Table Double click table to see table definition





Select Target Table


Check Target Table Right click table and select Data Preview





Select Target Table


Summary Data Acquisition using Flat File Data Load

You should now be able to: Understand when to use Flat File data load functionality How to load data from Flat Files into the SAP HANA Database


15


1

Unit 11: SAP HANA Direct Extractor Connection Overview Rationale SAP HANA Direct Extractor Connection (DXC) SAP HANA Direct Extractor Connection Details SAP Business Content DataSource Extractors SAP HANA Direct Extractor Connection Setup & Configuration Comparison with other SAP HANA Data Acquisition Techniques Appendix: DXC Sidecar Variation

Objectives Direct Extractor Connection


Explain an additional data acquisition technique for working with data from SAP Business Suite systems that has been added to the existing techniques for HANA data acquisition.


3

Overview Direct Extractor Connection

This module covers the following topics: Overview


4

Overview SAP HANA Direct Extractor Connection An additional data acquisition technique for working with data from SAP Business Suite systems has been added to the existing techniques for HANA data acquisition: SLT Replication Data Services …and now SAP HANA Direct Extractor Connection (DXC)


5

Overview SAP HANA DXC Concept: Illustration SAP ERP

SAP HANA

Embedded BW InfoCubes Data Store Objects InfoObjects generic data transfer

DataSource

Data flow redirected from embedded BW: transferred over http connection

Data models in HANA are built using active data table of In-Memory DataStore object

In memory DSO

Activation Queue

PSA

Active Version

Generic data transfer

Activation Processing

DataSource (flat structure) Extractor

Status

ERP data

Data

load into HANA activation queue

Separate

activation step

Scheduled © 2012 SAP AG. All rights reserved.

batch jobs 6




Explain an additional data acquisition technique for working with data from SAP Business Suite systems that has been added to the existing techniques for HANA data acquisition.

7



At the end of this Lesson you will be able to: Explain the rationale SAP HANA Direct Extractor Connection (DXC)


9


This module covers the following topics: Rationale SAP HANA Direct Extractor Connection (DXC)


10

Pre-Existing Foundational Data Models of SAP Entities for use in SAP HANA Challenges Data stored in many various tables, with high complexity in many modules of SAP Business Suite systems LT “real-time” approach Æ Uses base tables in the SAP Business Suite as a basis for data modeling of SAP Business Suite entities Project solution: Model SAP entities from scratch In some cases Æ Big challenges b/c of high complexity in the SAP Business Suite system

DXC Benefits: Leverage SAP Delivered Business Content DataSources Æ Which are existing foundational data models for key entities in SAP Business Suite systems Significantly reduces complexity of data modeling tasks in SAP HANA Speeds up timelines for customer’s implementation projects


11

Provides Semantically Rich Data from SAP Business Suite to SAP HANA Challenges In many modules of SAP Business Suite systems Æ Application logic needed to have semantically rich data (data appropriately reflecting the state of business documents) LT “real-time” approach Æ Uses base tables in the SAP Business Suite as a basis for data modeling Æ Semantically rich data not provided “out of the box” Project solution: Implement business logic from scratch to properly represent SAP Business Suite data It can be extremely challenging to determine proper application logic to implement on a project basis in SAP HANA (depending on use case)

DXC Benefits DXC Æ uses SAP DataSource Extractors Æ provides semantically rich data “out of the box”

Ensures the data appropriately represents the state of business documents from ERP Application logic to “make sense of the data” already built into many extractors Avoid potentially difficult work of “reinventing the wheel” on a project basis in HANA -> reimplement application logic in HANA which is already provided in DataSource extractors


12

Simplicity / Low TCO Challenges Some use cases require straightforward use of SAP ERP data Æ Simple interface desired System landscape impact of other data acquisition techniques LT “real-time” Æ Separate NetWeaver instance in system landscape DataServices Æ ETL tool, Separate BOE-instance in system landscape

DataServices Æ Requires an SP from March 2011 on the ERP system to use SAP DataSource Extractors (1522554 /Note:1558737)

DXC Benefits DXC provides a very simple, straightforward interface, re-uses existing extractors in SAP ERP HTTP-Connection directly from SAP Business Suite to HANA

TCO advantages (Due to simplicity, minimal system landscape impact) DXC is available simply by applying a note (In most cases) Re-use widely available skill-sets BW extraction and load is well-known in the industry


13

Activation Mechanism for Handling Delta Processing Challenges Many SAP Business Content DataSources offer delta processing, aka change data capture

Extraction only sends data created, changed, or deleted since the last extraction run Æ Efficiency Some DataSource extractor types deliver data with special properties Æ It should not be simply loaded directly into a table in HANA Æ This would cause incorrect results in reports

Delta processing types AIM, AIE, AIED, AIMD, ADD, ADDD and CUBE require data to be loaded into a DataStore Object (DSO) Æ Activation processing is important for data correctness Standard DSOs in BW include an activation mechanism, which handles the special properties of this data appropriately (e.g. after image only, overwrite, deletion flag, etc.) Without DXC, HANA standalone cannot properly handle data from the aforementioned types DXC Benefits DXC provides a special In-Memory DataStore Object (IMDSO) for use in HANA standalone

IMDSO Includes the same activation mechanism features as BW DSOs IMDSO properly handles special requirements of extractor types such as AIM and AIMD, which need DSO activation Æ ensures correct data in reports IMDSO ensures proper sequencing, overwrite, deletion of data, etc. © 2012 SAP AG. All rights reserved.

14


You should now be able to: Explain the rationale SAP HANA Direct Extractor Connection (DXC)


15



At the end of this Lesson you will be able to: Explain the SAP HANA Direct Extractor Connection in details


17


This module covers the following topics: SAP HANA Direct Extractor Connection Details


18

SAP HANA Direct Extractor Connection Details In typical business suite systems, the embedded BW is not utilized Customers typically have separate BW systems

DXC uses the embedded BW system to enable extraction and monitoring only Data flow is redirected Æ It gets sent to HANA Note: Modeling in the embedded BW is not part of the DXC solution

Note: An architectural variation available, which uses a “sidecar” BW instead of the embedded one. See appendix for details.


19

SAP HANA Direct Extractor Connection Details The extraction from the SAP Business Suite system -> controlled from the Data Warehousing workbench inside the embedded BW When data is extracted from, the SAP Business Suite system, it is not loaded into the PSA of the embedded BW instead it is redirected and sent to HANA It gets loaded into in-memory DSO’s activation queue Then activated into the active table of the in-memory

However, in the data load monitor of the embedded BW, the data load into the activation queue in the DSO in HANA appears like data is loading into the PSA in the embedded BW


20

SAP HANA Direct Extractor Connection Details Delta processing (aka “change data capture”) Works the same for DXC as it would if BW were the receiving system If the DataSource is delta enabled, then delta-enabled data is available with SAP HANA Direct Extractor Connection

DXC internally in HANA uses the ICM (Internet Connectivity Manager) – receives XML packages over the http(s) connection

Mechanism written on the XS Engine (special component for HANA) Receives data packets from ICM, converts format Inserts the records into the activation queue of the in-memory DSO Activation processing Æ records are go into the active table in proper sequence

Both the ICM and XS Engine components must be installed in SAP HANA to utilize DXC


21

SAP HANA Direct Extractor Connection Details Limitations for DXC Business Suite System based on NetWeaver 7.0 or higher (e.g. ECC) with at least the following SP level: Release 700 Release 701 Release 702 Release 730

SAPKW70021 SAPKW70104 SAPKW70201 SAPKW73001

(SP stack 19, from Nov 2008)

DataSource must have a key field defined Procedure exists to define a key if one is not already defined Certain DataSources may have specific limitations Inventory types, e.g. 2LIS_03_BF – data requires special features only available in BW Certain Utilities DataSources – can work with one and only one receiving system Some DataSources are not delta enabled – not a specific issue for DXC or HANA, but something to take into account © 2012 SAP AG. All rights reserved.

22

SAP HANA Direct Extractor Connection SAP Business Suite – DataSource Extractors Example from Sales Order Item Content HANA Data Models - virtual In-Memory Data Store Object Tables: Active table: /BIC/A 2LIS_11_VAITM00 Activation Processing Activation Queue table: /BIC/A 2LIS_11_VAITM40

SAP HANA Data flow redirected from embedded BW Transfer Structure Extract StruktureMC11VA0ITM

Delta Queue

ARFCSDATA

ARFCSSTATE

Delta

DataSource for transactional data 2LIS_11_VAITM

SAP ERP

Init/Full

Update Mode

Setup Table

TRFCQOUT

Update Methods Application logic MCVBAK

Sales Order © 2012 SAP AG. All rights reserved.

MCVBAP

Communication Structure

Application Tables 23


You should now be able to: Explain the SAP HANA Direct Extractor Connection in details


24



At the end of this Lesson you will be able to: Explain the SAP Business Content DataSource Extractors


26


This module covers the following topics: SAP Business Content DataSource Extractors


27

SAP Business Content DataSource Extractors Overview Proprietary Extraction Technology Application based change data capture (aka delta capabilities)

SAP Business Suite

Extractors are application based and take data from the context of the application itself Extraction is event/process driven and (in some cases) is accomplished with publishing new or changed data in ERP based Delta Queues for receiving systems (e.g. BW or HANA) Extract structures can easily be enhanced using append structures Transformations can be implemented at the time of extraction using Business Add Ins (BADIs) Extract Structures based on entities in the Business Suite Asynchronous, mass data capable extraction


28

SAP Business Content DataSource Extractors Thousands of SAP Business Content DataSources Exist

Transactional data

Master data

Texts

Hierarchies

Totals

ERP & R/3

900

700

2300

100

4000

CRM

160

280

700

40

1180

SRM

30

30

60

10

130

Others

360

210

330

30

930

GRC

20

30

120

10

180

1470

1250

3510

190

6420

transactional data master data attributes master data text master data hierarchy

business documents


master data

29


You should now be able to: Explain the SAP Business Content DataSource Extractors


30



At the end of this Lesson you will be able to: Explain SAP HANA Direct Extractor Connection Setup & Configuration


32


This module covers the following topics: SAP HANA Direct Extractor Connection Setup & Configuration


33

Setup & configuration Relevant notes All relevant information related to setup & configuration is provided by Note 1665602 - Setup & Config: SAP HANA Direct Extractor Connection (DXC) Note 1583403 - Direct extractor connection to SAP HANA


34

Setup & configuration Relevant notes All relevant information related to setup & configuration is provided by Note 1665602 - Setup & Config: SAP HANA Direct Extractor Connection (DXC) Note 1583403 - Direct extractor connection to SAP HANA


35

Step 1: Enabling XSEngine and ICM Service


36

Step 2: Delivery Unit Import

Result


37

Step 3: Application Server Configuration


38

Step 4: Creating a DXC User in SAP HANA

Create a user who has the privileges to execute the DXC extraction and load. Add the roles PUBLIC and MONITORING


39

Step 5: Creating a DXC Schema in SAP HANA


40

Step 6: Create an HTTP Connection to the SAP HANA System


41

Step 7: Configure DXC HTTP Interface Destination and maintain DataSource parameters


42



43



44



45

Step 8: Configuration Steps Specific to SAP Business Warehouse Replicate DataSources

Result in HANA (IMDSO) Æ


46

Step 8: Configuration Steps Specific to SAP Business Warehouse Replicate DataSources

Result in HANA (IMDSO) Æ


47

Step 9: Create InfoPackages


48

Step 10: Scheduling and Monitoring

Monitor Data Load in the Source SAP Business Suite System Verifying Data Transfer in the SAP HANA Database Create a Process Chain for Regular Data Transfer


49

Step 10: Scheduling and Monitoring

Monitor Data Load in the Source SAP Business Suite System Verifying Data Transfer in the SAP HANA Database Create a Process Chain for Regular Data Transfer


50

Step 11: Monitoring the Activation Process of IMDSO


51

Step 11: Monitoring the Activation Process of IMDSO


52

Step 12: Setup Email Alerting for the Activation Process


53

Step 12: Setup Email Alerting for the Activation Process


54


You should now be able to: Explain SAP HANA Direct Extractor Connection Setup & Configuration


55



At the end of this Lesson you will be able to: Explain the Comparison with other SAP HANA Data Acquisition Techniques


57


This module covers the following topics: Comparison with other SAP HANA Data Acquisition Techniques


58

Comparison with other SAP HANA Data Acquisition Techniques Contrast with Data Services Direct Extractor Connection ETL type: Simple and straightforward ETL approach; no “premium” features SAP DataSources: available for all SAP Business Content DataSources (Extractors) and Generic DataSources with a defined key; key can be defined if missing Support Package Requirement: SP required in the source SAP Business Suite system that came out in March 2008; DXC is implemented by applying a special SAP note Delta handling (change data capture): Yes, for all SAP Business Content DataSources and all delta processing types; uses an In-Memory DSO with activation processing Software: Uses existing components in SAP HANA (XS Engine, ICM); configuration file imported into SAP HANA Transformations: very limited - BADI (ABAP) in extraction exit available. When extensive transformations are required, it’s recommended to use DataServices


SAP Data Services ETL type: Sophisticated ETL tool with extensive valuable features (data quality, metadata mgmt, transformations, etc.) SAP DataSources: available for SAP Business Content DataSources (Extractors), limited to the subset of DataSources released to Operational Data Provider Support Package Requirement: SP must be applied to the source SAP Business Suite system that came out in March 2011 – see SAP note 1522554 Delta handling (change data capture): Yes, except for SAP Business Content DataSources with delta processing types AIM, AIE, AIED, AIMD, ADD, ADDD, CUBE Software Requirement: BusinessObjects Enterprise and DataServices required Transformations: extensive transformation capabilities available in the DataServices ETL tool

59

Comparison with other SAP HANA Data Acquisition Techniques Contrast with SLT Direct Extractor Connection

SAP Landscape Transformation

Type: Batch-driven ETL. Data comes from SAP delivered Business Content DataSource extractors

Type: Trigger-based table replication. Data comes from base tables of SAP Business Suite systems

Real-Time: No. Once every 15 minutes approx. theoretical maximum (depends on DataSource)

Real-Time: Yes. Expected lag time typically less than 3 seconds

Delivered Foundational Data Models for SAP Entities: Yes

Delivered Foundational Data Models for SAP Entities: Generally no; some RDS content available

Semantically Rich Data: Yes, via SAP delivered Business Content DataSource extractors

Semantically Rich Data: No. Semantics must be implemented on a project basis in SAP HANA

System Landscape: Nothing added, uses existing components in SAP HANA

System Landscape: SAP NetWeaver 7.01 instance (SLT) required

Project Acceleration for Data Marts in HANA: Significant benefit

Project Acceleration Building Data Marts in HANA: Available with some RDS packages

Transformations: Limited, BADI in extraction exit available

Transformations: Some available as of SP3


60

Comparison with other SAP HANA Data Acquisition Techniques Supported Capability Matrix – Part 1 – Data from Tables

* See SAP Note 1513496 for official release limitations © 2012 SAP AG. All rights reserved.

61

Comparison with other SAP HANA Data Acquisition Techniques Supported Capability Matrix – Part 1 – Data from Tables


62

Comparison with other SAP HANA Data Acquisition Techniques Supported Capability Matrix – Part 2 - Extractors


63


You should now be able to: Explain the Comparison with other SAP HANA Data Acquisition Techniques


64



At the end of this Lesson you will be able to: Explain the DXC Sidecar Variation


66


This module covers the following topics: DXC Sidecar Variation


67

Appendix: DXC Sidecar Variation SAP HANA Direct Extractor Connection: Sidecar Rationale Customers with older Business Suite systems (lower than ones based on NetWeaver 7.0, e.g. ERP 4.7 or lower) or customers who do not want to use the embedded BW now have an alternative: The Sidecar variation Customers with a BW system connected can use that BW as the “bridge” between ERP and HANA (via DXC) – the same mechanism as with the embedded BW, but it’s external from the source ERP system.

DXC utilizing a sidecar BW 7.x or ORANGE (BW on HANA) Data flow is redirected from within BW 7.x sidecar or Orange (BW on HANA) - Data is not loaded into BW – gets redirected to HANA No concurrent consumption (Means – a DataSource can be used either in the connected BW or with DXC – cannot be used for both!) Apply SAP note 1583403 in BW Apply SAP note in ERP (minor enhancement, minimum risk)


68

Appendix: DXC Sidecar Variation SAP HANA DXC Concept: Illustration Embedded BW Data models in HANA are built using SAPofHANA active data table In-Memory DataStore object

SAP ERP Embedded BW

In memory DSO

InfoCubes Data Store Objects InfoObjects

generic data transfer

DataSource

Data flow redirected from embedded BW: transferred over http connection

PSA

Activation Queue

Active Version




Status

ERP data


Data load into HANA activation queue

Separate activation step

Scheduled batch jobs 69

Appendix: DXC Sidecar Variation SAP HANA DXC Concept: Illustration Standalone BW BW 7.x

SAP HANA

InfoCubes

In memory DSO

Data Store Objects InfoObjects generic data transfer DataSource

PSA

Data flow redirected to Hana: transferred over http connection

Activation Queue




Status

ERP data

SAP ERP

Data

load into HANA activation queue

Separate

activation step

Scheduled © 2012 SAP AG. All rights reserved.

Active Version

batch jobs 70

Summary Direct Extractor Connection

You should now be able to: Overview Explain the rationale SAP HANA Direct Extractor Connection (DXC) Explain the SAP Business Content DataSource Extractors Explain SAP HANA Direct Extractor Connection Setup & Configuration Explain the Comparison with other SAP HANA Data Acquisition Techniques


71

Copyright © 2012 SAP AG. All rights reserved.

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, PowerPoint, Silverlight, and Visual Studio 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, z10, z/VM, z/OS, OS/390, zEnterprise, PowerVM, Power Architecture, Power Systems, POWER7, POWER6+, POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System Storage, Storwize, XIV, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Informix, and Smarter Planet are trademarks or registered trademarks of IBM Corporation. Linux is the registered trademark of Linus Torvalds in the United States and other countries. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are trademarks or registered trademarks of Adobe Systems Incorporated in the United States and other countries. Oracle and Java are registered trademarks of Oracle and its affiliates. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.

Google App Engine, Google Apps, Google Checkout, Google Data API, Google Maps, Google Mobile Ads, Google Mobile Updater, Google Mobile, Google Store, Google Sync, Google Updater, Google Voice, Google Mail, Gmail, YouTube, Dalvik and Android are trademarks or registered trademarks of Google Inc. INTERMEC is a registered trademark of Intermec Technologies Corporation. Wi-Fi is a registered trademark of Wi-Fi Alliance. Bluetooth is a registered trademark of Bluetooth SIG Inc. Motorola is a registered trademark of Motorola Trademark Holdings LLC. Computop is a registered trademark of Computop Wirtschaftsinformatik GmbH. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, StreamWork, SAP HANA, 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. 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.

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

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.

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

Crossgate, m@gic EDDY, B2B 360°, and B2B 360° Services are registered trademarks of Crossgate AG in Germany and other countries. Crossgate is an SAP company.

Apple, App Store, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, Multi-Touch, Objective-C, Retina, Safari, Siri, and Xcode are trademarks or registered trademarks of Apple Inc.

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.

IOS is a registered trademark of Cisco Systems Inc. RIM, BlackBerry, BBM, BlackBerry Curve, BlackBerry Bold, BlackBerry Pearl, BlackBerry Torch, BlackBerry Storm, BlackBerry Storm2, BlackBerry PlayBook, and BlackBerry App World are trademarks or registered trademarks of Research in Motion Limited.


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.

72

Ha300 en Col97 Fv Show[1]

Recommend Documents