HA100_EN_Col09

HA100 SAP HANA Introduction SAP HANA

Date Training Center

Instructors Education Website

Participant Handbook Course Version: 09 Course Duration: 2 Day(s) Material Number: 50128319

An SAP course use it to learn, reference it for work

Copyright Copyright © SAP SE or an SAP affiliate company. 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 SE. The information contained herein may be changed without prior notice.

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

Trademarks Adobe, the Adobe logo, Acrobat, PostScript, and Reader are trademarks or registered trademarks of Adobe Systems Incorporated in the United States and other countries. Apple, App Store, FaceTime, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, MultiTouch, ObjectiveC,

Retina, Safari, Siri, and Xcode are trademarks or registered trademarks of Apple Inc. Bluetooth is a registered trademark of Bluetooth SIG Inc. Citrix, ICA, Program Neighborhood, MetaFrame now XenApp, WinFrame, VideoFrame, and MultiWin

are trademarks or registered trademarks of Citrix Systems Inc. Computop is a registered trademark of Computop Wirtschaftsinformatik GmbH. Edgar Online is a registered trademark of EDGAR Online Inc., an R.R. Donnelley & Sons Company. Facebook, the Facebook and F logo, FB, Face, Poke, Wall, and 32665 are trademarks of Facebook.

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. HP is a registered trademark of the HewlettPackard Development Company L.P. HTML, XML, XHTML, and W3C are trademarks, registered trademarks, or claimed as generic terms by the Massachusetts Institute of Technology (MIT), European Research Consortium for Informatics

and Mathematics (ERCIM), or Keio University. 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. Microsoft, Windows, Excel, Outlook, PowerPoint, Silverlight, and Visual Studio are registered trademarks of Microsoft Corporation. INTERMEC is a registered trademark of Intermec Technologies Corporation. IOS is a registered trademark of Cisco Systems Inc. The Klout name and logos are trademarks of Klout Inc. Linux is the registered trademark of Linus Torvalds in the United States and other countries.

Motorola is a registered trademark of Motorola Trademark Holdings LLC. Mozilla and Firefox and their logos are registered trademarks of the Mozilla Foundation.

g201516105457

Novell and SUSE Linux Enterprise Server are registered trademarks of Novell Inc. OpenText is a registered trademark of OpenText Corporation.

Oracle and Java are registered trademarks of Oracle and its affiliates. QR Code is a registered trademark of Denso Wave Incorporated. RIM, BlackBerry, BBM, BlackBerry Curve, BlackBerry Bold, BlackBerry Pearl, BlackBerry Torch, BlackBerry Storm, BlackBerry Storm2, BlackBerry PlayBook, and BlackBerry AppWorld are trademarks

or registered trademarks of Research in Motion Limited. SAVO is a registered trademark of The Savo Group Ltd. The Skype name is a trademark of Skype or related entities. Twitter and Tweet are trademarks or registered trademarks of Twitter. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. WiFi is a registered trademark of WiFi Alliance. SAP, R/3, ABAP, BAPI, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer,

StreamWork, SAP HANA, the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, Sybase, Adaptive Server, Adaptive Server Enterprise, iAnywhere, Sybase 365, SQL Anywhere, Crossgate, B2B 360° and B2B 360° Services, m@gic EDDY, Ariba, the Ariba logo, Quadrem, bprocess, Ariba Discovery, SuccessFactors, Execution is the Difference, BizX Mobile Touchbase, It's time to love work again, SuccessFactors Jam and BadAss SaaS, and other SAP

products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE in Germany or an SAP affiliate company.

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

Disclaimer These materials are subject to change without notice. These materials are provided by SAP SE and its affiliated companies (“SAP Group”) for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty

statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.

g201516105457

g201516105457

About This Handbook This handbook is intended to complement the instructorled presentation of this course, and serve as a source of reference. It is not suitable for selfstudy.

Typographic Conventions American English is the standard used in this handbook. The following typographic conventions are also used. Type Style

Description

Example text

Words or characters that appear on the screen. These include field names, screen titles, pushbuttons as well as

menu names, paths, and options. Also used for crossreferences to other documentation both internal and external. Example text

Emphasized words or phrases in body text, titles of graphics, and tables

EXAMPLE TEXT

Names of elements in the system. These include report names, program names, transaction codes, table names, and individual key words of a programming language, when surrounded by body text, for example SELECT and INCLUDE.

Example text

Screen output. This includes file and directory names and their paths, messages, names of variables and parameters, and passages of the source text of a program.

Example text

Exact user entry. These are words and characters that you enter in the system exactly as they appear in the documentation.

Variable user entry. Pointed brackets indicate that you replace these words and characters with appropriate entries.

2015

© SAP SE or an SAP affiliate company. All rights reserved.

v

About This Handbook

HA100

Icons in Body Text The following icons are used in this handbook. Icon

Meaning For more information, tips, or background

Note or further explanation of previous point Exception or caution Procedures

Indicates that the item is displayed in the instructor's presentation.

vi


2015

Contents Course Overview .............................................................................. ix Course Goals ................................................................................. ix Course Objectives ............................................................................ x

Unit 1: Introduction and Positioning. ......................................................1 Introduction to SAP HANA. .................................................................. 2 SAP HANA Scenarios. ..................................................................... 35

Unit 2: SAP HANA Studio. SAP HANA Studio

.................................................................. 4 5 .......................................................................... 46

Unit 3: Architecture.......................................................................... 7 7 Architecture.

.................................................................................

78

Persistence Layer .......................................................................... 86

Unit 4: Modeling.............................................................................1 01 Introduction to the COPA Scenario ..................................................... 1 02 Introduction to the SAP HANA Modeler Perspective. ................................. 1 06 Levels of Modeling. ........................................................................ 1 13

Unit 5: Data Provisioning ..................................................................2 05

........................................................... 2 07 SAP BusinessObjects Data Services ................................................... 2 22 SAP Landscape Transformation Replication Server .................................. 2 33 SAP Direct Extractor Connection ........................................................ 2 40 SAP Replication Server ................................................................... 2 45 Smart Data Access ........................................................................ 2 51 Smart Data Integration and Smart Data Quality ....................................... 264 Smart Data Streaming .................................................................... 2 72 Uploading Data from Flat Files

Unit 6: Reporting............................................................................2 87 SAP HANA Database Connectivity Options ........................................... 2 88 SAP BI...................................................................................... 2 92 SAP BusinessObjects Analysis for Office .............................................. 3 01 SAP BusinessObjects Design Studio .................................................. 3 10

2015


vii

Contents

HA100

SAP Lumira.

................................................................................ 3

19

Appendix 1: Useful Resources about SAP HANA . ................................3 37

viii


2015

Course Overview SAP HANA enables business departments to analyze business as it happens. Individuals can create flexible analytical models based on realtime data originating from business applications. ERP operational analytics is further enhanced with SAP HANA interfaces to BI client reporting tools, such as SAP BusinessObjects Explorer, SAP Crystal Reports, SAP BusinessObjects Dashboards, and SAP BusinessObjects

Web Intelligence. This twoday introductory course is for consultants, project team members and modelers who want to learn about this new technology. The course explains how to perform basic modeling and administrative tasks in the SAP HANA Studio. These tasks include data provisioning using several tools such as SAP Data Services or SAP

Replication Server, and creating flexible analytic models based on realtime data originating from SAP ERP applications. In order to cover an end to end scenario, the participants will also learn how to connect to SAP HANA from SAP BusinessObjects BI 4 tools such as SAP BusinessObjects Explorer, SAP BusinessObjects Analysis, and SAP BusinessObjects Web Intelligence.

Target Audience This course is intended for the following audiences: • •

Application consultants Project team members

Course Prerequisites Required Knowledge •

None

Recommended Knowledge • • •

2015

SAP ERP reporting and analytics General business modeling experience Basic understanding of business system landscapes


ix

Course Overview

HA100

Course Goals This course will prepare you to: •

Get a good understanding and overview of SAP HANA

Course Objectives After completing this course, you will be able to: • • • • •

x

Explain SAP HANA concepts Use SAP HANA Studio Create simple information models Understand how data can be loaded into HANA Get an overview of how to report on HANA using client tools


2015

Unit 1 Introduction and Positioning Unit Overview This unit gives an overview of SAP HANA benefits, positioning, and deployment options.

Unit Objectives After completing this unit, you will be able to: • • • •

Explain the common pain points in a system using a classic database

Explain how SAP HANA can handle the pain points and help to improve performance Explain benefits of application development, spatial and text analysis Explain the architecture of the main SAP HANA implementation scenarios

Unit Contents Lesson: Introduction to SAP HANA ................................................. 2

Lesson: SAP HANA Scenarios .................................................... 35

2015


1

Unit 1: Introduction and Positioning

HA100

Lesson: Introduction to SAP HANA Lesson Overview This lesson explains why SAP HANA has been developed and how this new technology can help increasing business opportunities.

Lesson Objectives After completing this lesson, you will be able to: • • •

Explain the common pain points in a system using a classic database Explain how SAP HANA can handle the pain points and help to improve performance Explain benefits of application development, spatial and text analysis

Business Example Today, a lot of companies need to deal with an amazing amount of data and are not able to report on them efficiently due to data volume. The purpose of SAP HANA is to enable easy storage and efficient processing of these data. In particular, SAP HANA combines inmemory data storage and columnar data storage, two modern and extremely powerful features.

Information Explosion

Figure 1: Reality #1: Information Explosion

2


2015

Lesson: Introduction to SAP HANA

HA100

The first challenge is the information explosion. Massive amounts of data is being

created every year, and how fast your business reacts to it determines whether you succeed or fail. IDC, in its 2013 Top 10 predictions (released in November 2012) estimates that the “digital universe” will reach 4 ZB (1 zettabyte = 1 trillion gigabytes). On the horizon of 2020, IDC projects that the digital universe will reach 40 zettabytes

of data, which is also 40 trillion gigabytes. It represents 50 times the volume of the digital universe in 2010. And a stack of DVD boxes containing all the digital universe data would be twice as high as the distance between Earth and Mars. kilobyte (kB) > megabyte (MB) > gigabyte (GB) > terabyte (TB) > petabyte (PB) > exabyte (EB) > zettabyte (ZB) > yottabyte (YB)

To learn more about the IDC forecast on the digital universe in 2020, see: http://www.emc.com/collateral/analystreports/idcthedigitaluniversein2020.pdf In a Sloan Management survey in 2010, 60% of executives said their companies have more data than they know how to use effectively. With data doubling every 18 months, that percentage is going to keep growing.

Figure 2: Reality #2: Consumerization of IT

At the same time, the consumerization trend is driving up expectations as to what enterprise IT can help the business to do. People want instant access to information – in the moment – whether that is a moment of risk or a moment of opportunity. If the moment has passed and your business has not taken the right action, it has failed. People want instant answers. They want them to be right. They want them anywhere, any time.

2015


3


HA100

Figure 3: Reality #3: IT Cannot Deliver

This puts IT in a tough place. IT cannot deliver what the business needs. Why? Because the cost of managing that data explosion is too high. Because there is no practical way to instantly analyze everything that is going on relative to the business. IT can deliver some of the information. The most critical slice of information can be delivered in near real time. But it is not enough. Data is growing. Demand is increasing. We must find a way to deal with this – a way to process and analyze massive amounts

of data in real time.

Figure 4: The Daily Challenges of Data Volume and Complexity...

4


2015


HA100

Figure 5: ... and the Main Drawbacks

To resolve the difficult equation of data volume, speed, and flexibility, here comes

SAP HANA. Using groundbreaking inmemory hardware and software, you can manage data at massive scale, analyze it at amazing speed, and give the business not only instant access to real time transactional information and analysis, but also more flexibility. Flexibility to analyze new types of data in different ways, without creating custom data warehouses and data marts. Even the flexibility to build new applications which were not possible before.

SAP HANA Value Proposition We keep throwing around words like massive amounts of data and amazing speed. What kinds of scale, speed and improvement are customers seeing?

Figure 6: SAP HANA Proof Points

2015


5


HA100

Here are some proof points: •

Amazing speed. One of our pilot customers reduced the time it took to run a report from one hour

to one second. That is 3600 times faster. Let's put that in perspective. SAP talks about helping you to “run better”, so lets use that as an example. When an average person runs, they move at about 7 miles per hour. 3600 times faster

would be about 25,000 miles per hour. That is the fastest any human being has ever travelled, and it was only done once by the astronauts on Apollo 10, on their return from the moon in 1969. •

Amazing amounts of data. During testing for HANA we executed queries against 460 billion rows of data in less than one second. That is like being able to analyze every repair and service visit for every car on earth in the last 12 months, in one second. Or to process every address that everyone alive today has ever lived at, in one second. Or to calculate the amount of taxes paid by everyone on the planet, since 1950, in one second.

•

Amazing value. Having the ability to create new realtime processes and simplify your IT landscape has a big impact. According to a study by Oxford Economics, companies that implement realtime systems see an average 21% revenue growth, and a 19% reduction in IT cost.

6


2015


HA100

Figure 7: Query Acceleration Example – Large Bank – 1 Month of Customer Information

Why wait for data? •

All customers want to see their current business data in realtime. Nobody wants

to wait until data is uploaded into a data warehouse. Why wait for new systems? •

2015

Latest hardware and latest database technology already support realtime reporting on massive amount of data.


7


HA100

Figure 8: SAP Naming Update: SAP HANA

SAP HANA •

•

•

8

SAP HANA is a flexible, datasourceagnostic appliance, that enables customers to analyze large volumes of data from SAP and nonSAP systems in realtime, avoiding the need to materialize transformations. SAP HANA appliance software is a hardware and software combination that integrates a number of SAP components, including the SAP HANA database and several data replication systems: SAP Landscape Transformation, SAP HANA Direct Extractor Connections (DXC), SAP Data Services or SAP Sybase Replication Server. The SAP HANA database is a hybrid inmemory database that combines rowbased, columnbased, and objectbased database technology. It is optimized to exploit the parallel processing capabilities of modern multicore CPU architectures. With this architecture, SAP applications can benefit from current hardware technologies.


2015


HA100

Figure 9: Improvements in Technology

Historically, database systems were designed to perform well on computer systems

with limited RAM. As a consequence, slow disk I/O was the main bottleneck in data throughput. The architecture of those systems was designed with a focus on optimizing disk access, for example by minimizing the number of disk blocks (or pages) to be read into main memory when processing a query.

2015


9


HA100

Figure 10: SAP's Path to SAP HANA

SAP HANA is the result of inmemory experiences within SAP applications, further

software innovations in regards of database management systems as well as general hardware innovation in the market.

10


2015


HA100

SAP HANA Architecture

Figure 11: SAP HANA Architecture

So what is SAP HANA exactly? First of all, SAP HANA is a database. But it is not just a database like many others in

the market: SAP HANA provides a unique combination of hardware and software innovations which have a huge potential to optimize business applications running on SAP HANA. In the following, both dimensions –hardware and software optimizations– will be explained in more detail. 1. HARDWARE OPTIMIZATION

2015


11


HA100

Figure 12: Computer Architecture is Changing

Computer architecture has changed in recent years. Now, multicore CPUs (multiple CPUs on one chip or in one package) are standard, and extremely fast communication between processor cores enables parallel processing. Main memory is no longer a limited resource: modern servers can have 2 TB of system memory, and this allows complete databases to be held in RAM. Currently, server processors have up to 64

cores... and 128core processors will soon be available. With the increasing number of cores, CPUs are able to process much more data per time interval. This shifts the

performance bottleneck from disk I/O to the data transfer between CPU cache and main memory. In the next slides, you will understand the four main concepts of the SAP HANA database: • • • •

12

Column Store Compression Partitioning Insert only on Delta


2015


HA100

Row and Column Store In addition to a classical rowbased data store, SAP HANA is able to store tables in

its columnbased data store. It is important to understand the differences between these two methods, and why columnbased storage can highly increase certain types of data processing.

The concept of column data storage has been used for quite some time. For example, the first version of SAP Sybase IQ, a columnbased relational database, was released

in 1999. Historically, columnbased storage was mainly used for analytics and data warehousing, where aggregate functions play an important role. On the other hand, using column stores in Online Transaction Processing (OLTP)

applications requires a balanced approach to insertion and indexing of column data, in order to minimize cache misses. The SAP HANA database allows the developer to specify whether a table is to be stored columnwise or rowwise. It is also possible to alter an existing columnbased

table to rowbased, and vice versa.

Figure 13: Software Optimization for Data Processing

2015


13


HA100

Conceptually, a database table is a twodimensional data structure with cells organized in rows and columns. Computer memory, however, is organized as a linear structure. To store a table in linear memory, two options exist:

• •

A rowbased approach stores a table as a sequence of records, each of which contain the fields of one row. In a columnbased table, the entries of a column are stored in contiguous memory locations.

Figure 14: Key Facts: When to Use Row Store or Column Store?

•

Row Store If you want to report on all the columns of a table, then the row store is more suitable because reconstructing the complete row is one of the most expensive operation for a columnbased table.

•

Column Store If you to want to store in a table huge amounts of data that should be aggregated

and analyzed, then a columnbased storage is more suitable.

14


2015


HA100

Figure 15: Column and Row Store Tables in SAP

With columnbased storage, data is only partially blocked. Therefore, individual columns can be processed at the same time by different cores.

Compression

Figure 16: Column Data Storage

2015


15


HA100

Apart from performance reasons, column store offers much more potential leverage stateoftheart data compression concepts. For example, SAP HANA works with bit encoded values and compresses repeated values, which results in much less memory requirements than for a classical row store table.

Partitioning

Figure 17: Partitioning Data for Faster Processing of Data in parallel

16


2015


HA100

Insert Only on Delta

Figure 18: Management of inmemory columnstore tables

Updating and inserting data into a sorted column store table is a very costly activity, as the sort order has to be regenerated and thus the whole table is reorganized each time. For this reason SAP has tackled this challenge by separating these tables into a Main Store (readoptimized, sorted columns) and Delta Stores (writeoptimized, non sorted

2015


17


HA100

columns or rows). There is a regular database activity which merges the delta stores into the main store. This activity is called “Delta Merge”. This diagram shows the different levels of data storage, and distinguishes the main store from the delta stores.

•

MAIN Storage After a merge (=reorganization), SAP HANA stores all data in a main storage organized by columns.

•

DELTA 1 Storage New or changed entries are initially stored in a delta storage, which is organized by columns. However, for speed purposes, the delta storage is not sorted (contrary to the main storage).

Note: If the delta reaches a certain size, it merged back into the main storage completely sorted. •

DELTA 2 Storage For recording of high speed event like formula one sensor recording or mass RFID reading, data entry can go to a second delta storage, which is organized as a row store. This storage works as a very short term input buffer in order not

to loose any sensor signal. This second delta storage is frequently merged into the first delta storage and thus into the main storage. Queries run against all storages simultaneously. The main storage being the largest, but because of the sorted data also is the fastest. DELTA 1 is slightly slower for read queries, but much faster for inserts. DELTA 2 is very fast for insert, but much slower

for read queries, and therefore kept relatively small. To learn more about the Main and Delta storage of SAP HANA, you can consult the How to Guide How to Delta Merge for SAP HANA and SAP NetWeaver BW POWERED BY SAP HANA , available athttp://scn.sap.com/docs/DOC27558.

18


2015


HA100

Software Optimization

Figure 19: SAP HANA Software Optimization (1)


The fact that SAP HANA comes with different engines to process calculation logic and execute programming code is a great opportunity to push dataintensive calculations from the ABAP application layer into the SAP HANA database. For this reason, SAP ABAP has been enhanced with NetWeaver 7.30 and 7.40 to exploit the advanced inmemory features of SAP HANA.

2015


19


HA100

This results in less data transfer between application layer and database layer, and a much better usage of resources. The application layer focuses more on orchestration and triggering the processing within the database. In the end, complex logic can be processed in very little time which results in great performance improvements. Thus the main concepts of software innovations can be summed up as follows:

1. 2.

Bring the logic to where the data is Calculate first, then only move results


SAP applications are, of course, required to support not only SAP HANA but all the

database management systems that are certified for ABAP. For this reason, there is an enhancement in those ABAP programs which are SAP HANA optimized. In a Business Addin (BAdI), those programs first check for the database in place. In

case of SAP HANA, the optimized version is triggered; in the other case the classical ABAP flow is executed. Thus there is two versions of certain processes on the application layer

• •

20

Standard ABAP code working on every supported database Optimized ABAP code working on SAP HANA only


2015


HA100

Figure 22: SAP HANA Software optimization: Example of SAP BW

SAP BW provides a good example of how certain dataintensive and performancecritical processes have been optimized for SAP HANA in the recent versions. This concept has been followed very strictly in the current version of SAP NetWeaver BW 7.40.

Geospatial Data Silos of information create an incomplete picture. Data has no shape, form or geographic context that can be visually understood. Data is incomplete and not in sync with business systems, as it is not linked to the business process. By combining business data with geographical data you can identify new opportunities

and root causes by leveraging spatiallyenabled business applications on the SAP HANA platform.

2015


21


HA100

Figure 23: The Challenge of Information Silos

SAP HANA includes a multilayered spatial engine and supports spatial columns, spatial access methods, and spatial reference systems. Spatial data is data that describes the position, shape, and orientation of objects in a defined space. Spatial data is represented as 2D geometries in the form of points, line strings, and polygons.

For example, a map of a state, representing the union of polygons representing zip code regions.

22


2015


HA100

Figure 24: SAP HANA a Spatial Enabled Database

The shape of the geographical data you want to store determines which datatype you want to use. A geographical column can be defined at table creation or added to an already existing table. Geospatial data is typically entered using the WKT (Well Known Text) format. This format is maintained by the Open Geospatial Consortium

(OGC) as part of the Simple Features defined for the OpenGIS Implementation Specification for Geographic Information. Additionally, there are other ways of entering geographical data, for example WKB (Well Known Binary) and ESRI shapefiles (a popular geospatial vector data format).

Two common operations performed on spatial data are calculating the distance between geometries, and determining the union or intersection of multiple objects. These calculations are performed using predicates such as intersects, contains, and crosses. The software provides storage and data management features for spatial data, allowing you to store information such as geographic locations, routing information, and shape

data. These information pieces are stored as points and various forms of polygons and lines in columns defined with a corresponding spatial data type (such as ST_Point

and ST_Polygon). You use methods and constructors to access and manipulate the spatial data. The software also provides a set of SQL spatial functions designed for compatibility with other products.

2015


23


HA100

Figure 25: Geographical Data Types

Calculation Views now support spatial operations like calculating the distance between

geometries and determining the union or intersection of multiple objects. These calculations are performed using predicates such as intersects, contains, and crosses.

A predicate is a conditional expression that, combined with the logical operators AND and OR, makes up the set of conditions in a WHERE, HAVING, or ON clause, or in an IF or CASE expression, or in a CHECK constraint. In SQL, a predicate may

evaluate to TRUE, FALSE. Spatial predicates are implemented as member functions that return 0 or 1. Spatial operations are modeled as “Spatial Joins” to query data from database tables that contain spatial data.

24


2015


HA100

Figure 26: Calculation View Spatial Join Operations

The relate predicate has the greatest flexibility and specifies the spatial relationship between two spatial types, using a small two dimensional table. You can specify multiple spatial conditions at once, like wether the interiors of two columns relate or not.

2015


25


HA100

Figure 27: Calculation View Spatial Operations Examples (1/2)

The following examples illustrate the usage of the predicates

Touches, Within Distance

and Contains . The value entered for the distance in the Within Distancepredicate, represents the values corresponding to the geometry used in the projection, like 12 meter.

26


2015

HA100


Figure 28: Calculation View Spatial Operations Examples (2/2)

SAP HANA Native Application Development In the traditional 3tier applications (especially from SAP), the database is largely used as a data store mechanism only. Massive queries bring large amounts of data back to the application server for processing. Lots of application execution time is spent in the

application server looping over records and performing exclusions, calculations, etc. This leads to the need for database buffers at the application server level. This also creates a situation where most of the computing resources are allocated to several, large application servers since the bulk of all the logic is performed at this level. With HANA, the key to the best application performance is pushing as much of the logic execution into the database as possible. We now “trust” the database. Keep all the data intensive logic down in the database as SQL, SQLScript, and HANA Views. Lightweight imperative logic, flow logic, and service enablement can be down at the Extended Application Services level (XS). XS is not like the traditional application service – no database buffering, stateless only – it should be treated as a light, passthrough layer only. Finally, the complete UI rendering and processing should be done in the client device. Any client side UI development model can be used, although SAP provides SAPUI5 (HTML5 libraries and development tools) as well as UI Integration Services (Open Social based, lightweight portal). HANA

2015


27


HA100

should serve out static HTML content and JavaScript libraries as well as expose data

and logic via pure REST data services. The UI creation, presentation logic and data injection should all happen on the client side.

Figure 29: 3tier Application (Java, ABAP) vs. Native SAP HANA

Figure 30: Code to Data

28


2015

HA100


Figure 31: SAP HANA Extended Application Services Overview

2015


29


HA100

Using the SAP HANA Platform has the following advantages: •

Simplicity at runtime A major advantage of a 2tier architecture is simplicity, leading to higher efficiency. Native HANA applications have reduced cost of ownership, as no additional application server needs to be installed and administrated. As stated before, HANAbased applications often have no need for a traditional application server, because the presentation logic moves to the client and the dataintensive processing is pushed to the database layer.

•

Simplicity at design time The development landscape has a single development environment and a single repository. This eliminates the risk of inconsistencies that can arise when parts

of the application need to be stored in different repositories. •

Strategic and futureproof Other important aspects are strategic alignment, the chance to participate in innovation and to be prepared for the future. With the decision to build a native HANA application, you support SAP’s inmemory strategy. As HANA is the strategic new SAP platform, you ensure that your architecture is sustainable. You also benefit from continuous technology innovations, as the HANA platform

is enhanced and constantly improved at high speed. •

Speed of Development With HANA XS, you can develop webbased access to data in HANA very fast. HANA views and tables can be exposed via OData services in declarative way without coding. As the OData service is generated from metadata, you don’t have to change any code, for example after you add new fields to a view.

•

Access to HANA APIs The XS JavaScript API contains interfaces that are otherwise not available, or in

a less convenient way, for example the repository API. •

Performance Further performance optimizations can be expected, because the XS engine is an integrated part of HANA, developed by the HANA core team.

30


2015


HA100

In a development model for SAP HANA based native applications, the extended application services offer: • •

Easy access to the SAP HANA database via HTTPbased consumption. Attractive, dynamic HTML5 UI applications via OData services or by writing native applicationspecific code that runs in SAP HANA context.

• •

Powerful search services. Builtin web server to access static content stored in SAP HANA repository.

The application development is following a layered approach: • • •

UI rendering completely in the client (browser, mobile apps). Serverside procedural logic in JavaScript. All artifacts stored in the SAP HANA repository.

Full Text Search and Text Analysis Overview A typical challenge in today's business is to search for information among huge amounts of data, constantly collected in structured and unstructured form in different

areas in or outside your company. You may need then to extract meaningful information out of your data, to be able to analyze it properly.

Figure 32: Text Search, Text Analysis, and Analytics

For example you may need to perform “faulttolerant” searches, e.g when looking for duplicate master data where mistyped names are common; or you may need to perform

so called “linguistic” searches, e.g you search for the string “computed” and you would like to see also “compute”, “computing”, “computes”, ... in your search results.

2015


31


HA100

You may also have additional search requirements, like ranking your search results or search one string at once over all the fields of a table. SAP HANA offers native search capabilities to support these and other business needs with the “Full Text Search”.

Figure 33: SAP HANA Text Searching

Among the search capabilities in SAP HANA, the “Fuzzy Search” offers the

possibility to search for approximate matches to a given string. For example you could

search for the string Walldorf and find all exact and approximate (to a degree that you can specify in your search) matches, like Walldorf , Wadlorf , Valldorf, Wahldorff, ... . In general a Fuzzy Search will look for all strings matching the given one exactly or differing by missing, added or mistyped characters. The behavior

is similar to what you experience when you search for a web page in internet on a search engine: you do not want to see only exact matches of your search string. The “Text Analysis” allows the extraction of structured information from unstructured

information. For example it allows linguistic markup, like for example identifying the part of a speech (verbs, nouns, adjectives and so on). It allows also to identify entities (locations, persons, dates, ...) in an unstructured text.

32


2015


HA100

Figure 34: SAP HANA Text Analysis

The results of a Text Analysis are stored in a table and can therefore be consumed through all supported HANA Scenario, for example in information models, in R language scripts, via the Predictive Analytic Library functions or via the HANA Information Access toolkit for HTML5 by building a search UI.

Figure 35: Text Search and Analysis Use Cases / Scenarios

2015


33


HA100

Lesson Summary You should now be able to: • Explain the common pain points in a system using a classic database • Explain how SAP HANA can handle the pain points and help to improve performance • Explain benefits of application development, spatial and text analysis

34


2015

Lesson: SAP HANA Scenarios

HA100

Lesson: SAP HANA Scenarios Lesson Overview This lesson discusses the different system configuration and possible SAP HANA

scenarios.

Lesson Objectives After completing this lesson, you will be able to: •

Explain the architecture of the main SAP HANA implementation scenarios

Business Example A sidecar scenario can be a first step to meeting urgent and important business needs. The COPA (Controlling – Profitability Analysis) accelerator is an example

of a sidecar scenario. SAP HANA is used as a secondary database where data is replicated in realtime. Information and results can be consumed on an SAP Business Objects platform or directly in SAP ECC.

Then, once the SAP Business Suite or BW migration is effective, the sidecar perimeter can be integrated to a schema of the SAP HANA Database used with Business Suite or BW. The third step corresponds to the situation, when SAP HANA is the unique platform

for all SAP applications in an organization.

2015


35


HA100

Technical Architecture Patterns and Use Cases

Figure 36: Technical Architecture Patterns

Figure 37: SAP HANA Use Cases

36


2015


HA100

This diagram provides all the use cases of how SAP HANA can play a role in the Enterprise Information System.

•

HANA Platform (Datamart) Build all kind off applications like OLAP, predictive or text mining.

•

HANA apps for Suite Apps bound to the Suite, that run on HANA, based on data replicated from the Suite Any DB to HANA DB.

•

HANA accelerators Accelerate the Suite by replicating the data from any DB to HANA DB. In case HANA data is available a ABAP case statements decides, to run processing on the HANA Server instead of the ABAP stack. In addition BI clients can leverage

the data in SAP HANA and accelerate the reporting. •

Business Warehouse, Business One, Business Suite on HANA Take advantage of SAP HANA without disruption for smarter innovations, faster business processes and simpler interactions.

•

Cloud on HANA A public Service platform to PlatformAsAService offering that allows SAP partners and SAP customers to build, deploy and operate nonABAP applications

in an open and standardsbased cloud environment. •

New Apps New SAP applications based on SAP HANA.

This HA100 course uses the second scenario “HANA platform” in the training system: There are some replicated source tables from an ERPsystem in a standalone

SAP HANA environment.

SAP Cloud powered by SAP HANA SAP is the only company, which can integrate SAP public cloud solutions including Ariba & Successfactors to SAP HANA solutions, including Suite on HANA (SoH) & Business Warehouse (BW) running either on HANA Enterprise Cloud (HEC) or on premise.

2015


37


HA100

Figure 38: Cloud Portfolio SAP’s cloud portfolio is the most comprehensive on the market today. It is designed

to unlock all of the new possibilities of cloud computing, as well as help speed adoption of HANA innovations. At its center is a set of public cloud applications and cloud suites. These provide cloud line of business applications that give the ability to manage core business with increased agility, while bringing them SAP’s best practices and 40+ years of business

process management experience. To bring the benefits of cloud economics and HANA for existing SAP landscapes, the SAP Cloud Portfolio includes managed cloud services. SAP’s managed cloud offering

has full integration with SAP Apps (Ariba, Successfactors). In fact, SAP can truly integrate SuccessFactors, Ariba, Suite and other SAP Applications. You can migrate your SAP Business Suite and SAP BW licenses onto the SAP HANA Enterprise Cloud and take advantage of HANA optimized infrastructure as well as tailored professional services delivered by SAP or certified partners. The key advantage of the SAP HANA Enterprise Cloud is, it is the fastest and easiest way to access HANA

based innovations for mission critical applications. You can take advantage of cloud economics and subscription pricing to access HANA through the SAP HANA infrastructure subscription offering. These can be purchased and provisioned via the SAP HANA Marketplace. The SAP HANA Marketplace is also the store through which you can purchase ecosystem innovations that run stand alone or extend SAP applications.

38


2015


HA100

Spanning across the portfolio is set of business network applications for business to business collaboration as well as social applications for people to people collaboration. All of the portfolio is built on a common platform, the SAP HANA platform. Through

this approach, SAP makes available the entire portfolio, the application, development, and integration services, as well as the HANA database and analytics and foundational

capabilities of the SAP HANA Cloud Platform to speed the development and delivery of innovations, both from SAP and from partners.

Figure 39: Overview Cloud Offerings

2015


39


HA100

Lesson Summary You should now be able to: • Explain the architecture of the main SAP HANA implementation scenarios

40


2015

Unit Summary

HA100

Unit Summary You should now be able to: Explain the common pain points in a system using a classic database • • Explain how SAP HANA can handle the pain points and help to improve performance • Explain benefits of application development, spatial and text analysis • Explain the architecture of the main SAP HANA implementation scenarios

2015


41

Unit Summary

42

HA100


2015

Unit Summary

43

HA100


2015

Unit Summary

44

HA100


2015

Unit 2 SAP HANA Studio Unit Overview This unit is an introduction to SAP HANA Studio.

Unit Objectives After completing this unit, you will be able to: • • •

Understand the structure of SAP HANA Studio Configure Perspectives

Create a Package

Unit Contents Lesson: SAP HANA Studio ......................................................... 46 Procedure: Exercise 1: Getting Started with the SAP HANA Studio ..... 62

2015


45

Unit 2: SAP HANA Studio

HA100

Lesson: SAP HANA Studio Lesson Overview This lesson is an introduction to the SAP HANA graphical user interface. The SAP HANA Studio is the main entry point to SAP HANA for system administrators, modelers and developers.


Understand the structure of SAP HANA Studio Configure Perspectives Create a Package

Business Example You are a consultant at a client site, and it is the first time you launch SAP HANA Studio.

You need to add the customer's SAP HANA instance to your SAP HANA Studio installation, so that you can work on the server. You also have to create a new Delivery

Unit and Package in which you will store your Information Models. Information Models in SAP HANA are a combination of attributes, dimensions, and measures, created by different types of modelling views.

46


2015

Lesson: SAP HANA Studio

HA100

SAP HANA Studio

Figure 40: The SAP HANA Studio

The SAP HANA Studio is delivered as part of the SAP HANA installation package, and provides an environment for administration, modeling, development and data provisioning. It can be installed on any client PC that has a connection to the SAP HANA system. The SAP HANA studio is a Javabased application that runs on the Eclipse platform.

For more information on the Eclipse IDE (Integrated Development Environment): http://www.eclipse.org) .

Perspectives in the SAP HANA Studio When you start the SAP HANA Studio for the first time, you must choose a perspective. Perspectives are predefined User Interface (UI) layouts with several views. One or several perspectives address the needs of a particular SAP HANA user role. For example, a System Administrator would typically use the SAP HANA Administration Console perspective. Each view can be moved around via drag & drop. You can also customize a perspective by adding or removing views.

2015


47


HA100

Figure 41: Perspectives are Based on Views

Here is an example of some perspectives that are available in the SAP HANA Studio. •

SAP HANA Modeler The SAP HANA Modeler perspective is used by Data Architects to create Information Models, as a combination of attributes, dimensions, and measures,

included in different types of modelling views. •

Administration Console The Administration Console perspective is used by SAP HANA Administrators to administrate and monitor the whole SAP HANA system.

•

Resources The Resources perspective is used to organize files, such as text files, sql scripts, and so on, by project.

•

Other perspectives Some perspectives in the SAP HANA Studio are designed for HANA applications development, Java development and Lifecycle Management.

48


2015


HA100

Figure 42: Opening a Perspective

To open a perspective, choose Windows → Open Perspective , then select a perspective from the list, or choose Other... .

It is possible to have several perspective open at the same time, and to switch from one perspective to another. To do so, in the perspective switcher in the upperright corner of the screen, choose the perspective you want to open.

Figure 43: Adding a View to a Perspective

2015


49


HA100

To add a view to a perspective, choose Windows → Show View, then select a view from the list, or choose Other... .

Note: The SAP HANA Studio is base on Eclipse. For this reason, you will see a lot of views in the Show View dialog box. The most relevant views to use when you start working with SAP HANA Studio are located in the following folders: • • • •

SAP HANA SAP HANA Modeler Help (Cheat Sheets and Help views) General (Project Explorer and Properties views)

Figure 44: Customizing the Systems View

Some views can be customized. To do so, choose the View Menu button and choose Customize View....

50


2015


HA100

Figure 45: Resetting a Perspective

Any perspective can be reset to its default views and default layout. To do so, choose Window → Reset Perspective... .

Adding a System to the SAP HANA Studio When you start the SAP HANA Studio for the first time, there is no connection to any SAP HANA system available. You must create a first connection to an SAP HANA system. To add a system to SAP HANA, you must open a perspective in which the Systems view is included. For example: • •

2015

The SAP HANA Administration Console perspective. The SAP HANA Modeler perspective.


51


HA100

To add an SAP HANA system, you need the following information: • •

The name of the server that runs the SAP HANA Engine. The instance number. This is a twodigit number, that determines the communication port to the SAP HANA system. For example, if you connect to an SAP HANA system with instance 00, the port used to communicate with the server will be 3 0015

•

The credentials of a user that is defined in the SAP HANA system. This user must be active.

Note: •

It is possible to connect to several SAP HANA systems within one single

•

instance of the SAP HANA Studio. You can connect to the same SAP HANA system with two different users.

There are two main methods to add a system to the SAP HANA Studio: •

Manually With this method, you add one SAP HANA system at a time.

– In the Systems view, rightclick any blank area and choose Add system... .

•

– Fill in the server name, instance number and system description, and choose Next . – Fill in the user name and password, and choose Finish. By importing a Landscape This method allows you to connect to several SAP HANA systems at the same time, by importing an xml file generated previously by a landscape export from the SAP HANA Studio installed on your computer or another one.

– Choose File → Import and choose SAP HANA → Landscape . – Specify the landscape file location, the destination folder for the import, and choose Finish.

Note: The landscape xml file does not contain any password. You will have to specify the user and password for any system added to the Systems view.

52


2015


HA100

The Systems View

Figure 46: The Systems View

The Systems view lists all the systems that have been registered (manually, or by a landscape import). For each system, the content is organized as follows:

2015


53


HA100

In the SAP HANA Studio, one central point of access is the Systems View, which is usually placed on the left side of the screen. •

Catalog As in many database structures, the catalog contains tables, views, indexes, and so on. All these objects are organized into schemas. Schemas are used to categorize a database content according to customer defined groupings that have a particular meaning for users. Schemas also help to define access rights to the database objects.

Note: From a modeling standpoint, schemas can help to identify which tables to use when defining information models. But a model can incorporate tables from multiple schemas. Schemas do not limit your modeling capabilities. The column views that you create are always located in schema _SYS_BIC, their metadata in schema _SYS_BI . If you run an SAP Application directly on SAP HANA, then the schema will always be SAP . For example, if your productive CRM System is called CRP , the corresponding schema will be SAPCRP . •

Content The Content folder of the catalog is where you store all the HANAspecific modeling objects.

Note: The physical tables are the only storage area for data within

SAP HANA. All the information models that will be created in the modeler will result in database views. As such, SAP HANA does not persist redundant data for each model, and does not create materialized aggregates. •

Provisioning The Provisioning folder is essentially related to Smart Data Access, a data provisioning approach in which you can combine data from remote sources (Hadoop, SAP ASE, SAP IQ) with data of your SAP HANA physical tables, by

exposing them as virtual tables. •

Security In the Security folder, the System Administrators define users and roles.

54


2015


HA100

Workspace SAP HANA Studio stores your projects in a folder called a workspace.

A workspace is an environment that maps a local directory to a package hierarchy in the SAP HANA repository. This folder is used by the SAP HANA Studio to store designtime objects, such as information views, when you edit them. There can be several workspaces defined in the configuration of the SAP HANA Studio installed on your computer. This is relevant when you work on different projects.

Note: During this training, you will only use one workspace, which is stored

by default in C:\Users\train##\hdbstudio (where ## is your student number).

System Administration and Monitoring To administer and monitor one or several SAP HANA systems, you can use the following views: • •

The System Monitor view The Administration view

Figure 47: The System Monitor View

The System Monitor view gives you a summarized view of the system landscape. By default, all the systems that are listed in the Systems view appear in the System Monitor view. You get the most important information about system status, alerts, as

well as disk space, memory and CPU usage. You can customize this view by adding or removing columns. To do so, choose the Configure Viewer button. Alternatively, you can rightclick in the System Monitor view and choose Configure Table . If you want to filter the list of systems that are shown in the view. rightclick in the System Monitor view and choose System Filter .

2015


55


HA100

Figure 48: The SAP HANA Administration Console Perspective

The SAP HANA Administration Console is a perspective that is predelivered by SAP. This is the main view to administrate HANA systems. For example, you can do the following tasks: • • • • •

Start and stop a system Configure a system Monitor a system Backup and restore a system Perform a problem analysis

To open the Administration view, you can: • •

Doubleclick the system in the System Monitor view Doubleclick the system in the Systems view

The SAP HANA Modeler Perspective When you want to create Information Models in the SAP HANA Studio, you need to open (or switch to) the SAP HANA Modeler perspective. To do so, choose Open Perspective → SAP HANA Modeler .

56


Window →

2015


HA100

The Quick View is a practical entry point, dedicated to the modeler perspective. From this view, you can create, manage and transport information models (packages, views), define or execute data provisioning, define schema mapping, and so on.

Note: You can define your favorite actions (for example,

Export, Import, and

Validate, and display only a custom list of these favorites.

Figure 49: The SAP HANA Modeler perspective

When you want to perform an action from the Quick View , you must first select the SAP HANA system on which the action will be executed.

Note: You actually select both a System and a User logged on to the System. If you are logged on to the same SAP HANA system with two (or more) different users, the action will be authorized based on the privileges of the user you have selected. The SAP HANA Studio offers other perspectives for debugging, resource, team synchronizing and more.

2015


57


HA100

If you have closed the Quick View and want to reopen it, you can do one of the following actions: • •

Choose Help → Quick View . Reset the Modeler perspective.

Note: The Quick View can only be displayed within the Modeler perspective.

Figure 50: The Systems View – Modeling Content

The Content node of the Systems view displays the data from a data modeling perspective. The information views, along with other modeling objects such as analytic privileges or procedures, are organized in packages. Each package is a repository that you can assign to a delivery unit in order to transport the objects it contains.

The SAP HANA Development Perspective The SAP HANA Studio includes a development perspective with debugging functionality.

58


2015


HA100

Figure 51: The SAP HANA Development Perspective

From the development perspective it is possible to check in and out development objects, connecting to a repository

2015


59


HA100

Figure 52: Development Repository Integration

SAP HANA Extended Application Services (also known as XS Server) is a key aspect

of SAP HANA as a platform. XS is an application server, web server, and basis for an application development platform, that resides inside SAP HANA. XS is not a completely separate technology that happens to be installed on the same hardware server as SAP HANA; XS is actually an extension of, and tightly integrated into, the SAP HANA database. Since the release of SAP HANA SPS05, XS is available for customers and partners who want to develop their own SAP HANAbased applications. Examples of applications developed with SAP HANA XS are the SAP HANA Live Browser or SAP Operational Intelligence .

60


2015


HA100

Getting Help within the SAP HANA Studio There are several ways to get help when you use the SAP HANA Studio. •

The SAP HANA Studio Help This is a classic online help, with structured content and search capabilities. The

content corresponds to the SAP HANA documentation. To open the Help view within the SAP HANA Studio, choose Help → Help Contents , Help → Search or choose F1 .

Note: You can also display the Help content in an external window. To do so, choose the Show in an external window button in the Help view. •

The SAP Help Portal You can access directly all the SAP HANA documentation available on the SAP Help Portal from the SAP HANA Studio. In addition to the documentation, you can find under Additional Information several links to other information sources, such as the SAP Community Network, Central SAP Notes, and so on.

2015


61


HA100

Exercise 1: Getting Started with the SAP HANA Studio Use The purpose of this exercise is to discover and customize the SAP HANA Studio user interface. After logging on to a Windows Remote Desktop session, you will perform

the following tasks: • • • • •

Start SAP HANA Studio Add an SAP HANA System to the Systems view Define SAP HANA Studio preferences Work with Views and Perspectives Create a delivery unit, and a package to store the modeling content that you will create during the course.

Note: As a preliminary step, fill out the missing details below, as given by the instructor, and use this information for all exercises during this course:

Remote Desktop Alias Remote Desktop User name Remote Desktop Password

SAP HANA System Host SAP HANA System Instance SAP HANA System User name SAP HANA System Password SAP BusinessObjects Server SAP BusinessObjects User name SAP BusinessObjects Password

Procedure 1.

Log on to the WTS training landscape, details will be provided by the instructor.

Note: Logging in via WTS is not required for all training locations.

Continued on next page

62


2015


HA100

2.

Connect to the SAP HANA training landscape. •

From the Windows desktop of the WTS training landscape, choose All Programs → Accessories → Remote Desktop Connection .

•

Enter the Remote Desktop alias and choose Connect .

Start →

Figure 53: Remote Desktop Connection

• •

In the target system logon page, choose Use another account . Enter your Remote Desktop user name and password, and choose

OK.

Figure 54: Logon Screen

You are now in the right desktop to start working with SAP HANA.


2015


63


3.

Start the SAP HANA Studio.

• • • 4.

HA100

Choose Start → All Programs → SAP HANA Studio

. If prompted to select a workspace, keep the default workspace C:\Users\train##\hdbstudio (## is your group number), select the checkbox Use this as the default and do not ask again , and click OK. If a Secure Storage dialog box opens to provide additional information for password recovery, click No .

Open the SAP HANA Administration Console perspective.

Figure 55: SAP HANA Studio – Overview Screen

• •

Choose Open SAP HANA Administration Console . In case you are asked for generation of a master password, choose NO.


64


2015


HA100

5.

Register a new system.

•

In the Systems view, rightclick in the blank area and choose

Add System.

Hint: Alternatively, you can click the Add Systems icon on the top of the Systems view and choose Add System. • • • •

Enter the Host Name and Instance Number as given by the instructor. In the Description field, enter SAP HANA . In the Locale dropdown list, choose English (United States) . Choose Next .

Figure 56: Registering a New System


2015


65


6.

HA100

Enter your credentials as given by your instructor.

• •

Select the Authentication by database user checkbox. Enter your user name and password as given by your instructor.

Note: Optionally, to store your password in the Windows secure

storage, select the Store user name and password in secure storage checkbox. This will avoid entering your password each time you start the SAP HANA Studio.

• • • •

Do not use SSL or HTTPS. Choose Next . Select the AutoReconnect checkbox. Choose Finish.

Figure 57: Connection Properties


66


2015


HA100

7.

Check the Default Client parameter in the SAP HANA Studio preferences. Note: SAP HANA supports modeling using multiple clients in SAP source data. You will learn more about the concept of client later on in this course.

• • •

Choose Window → Preferences Expand the preference tree to select the SAP HANA → Modeler → Default Model Parameter item. Check that the Default client setting in the SAP HANA Studio preferences is set to Session Client .

Figure 58: Default Model Parameters

8.

Open the SAP HANA Modeler perspective. •

Choose Window → Open Perspective → Other to display all the available perspectives. This includes the predelivered perspectives and the ones that you may have created.

•

Choose the SAP HANA Modeler perspective.


2015


67


HA100

Figure 59: Opening a Perspective

9.

Switch to the SAP HANA Administration Console perspective. In the perspective switcher, located in the upperright corner of the screen, choose SAP HANA Administration Console .

Note: The perspective switcher shows a list of all the open perspectives.

Figure 60: The Perspective Switcher


68


2015


HA100

10. Navigate the Systems view to display the main sections of the SAP HANA system tree. • • •

The Catalog node The Content node The Security node

Figure 61: The Systems View

Note: In the Catalog node, the System Administrator has created a schema for you. The schema name is the same as your user name.

11. Open the Administration view for your SAP HANA system. • •

In the Systems view, select the H00 (STUDENT##) system. Choose the Administration button.


2015


69


HA100

Figure 62: The PreDelivered Administration Perspective

12. Switch back to the SAP HANA Modeler perspective and create a new package.


70


2015


HA100

Figure 63: New Package (1)

•

In the Systems view, rightclick the Content folder and choose New → Package .

•

Enter the following information:

– Name:

STUDENT##

– Description:

STUDENT##

– Delivery unit: [leave blank] •

Choose OK .


•

Check that the new package is now listed in the Content folder.


2015


71


HA100


72


2015


HA100

Lesson Summary You should now be able to: • Understand the structure of SAP HANA Studio Configure Perspectives • • Create a Package

2015


73

Unit Summary

HA100

Unit Summary You should now be able to: • Understand the structure of SAP HANA Studio • Configure Perspectives • Create a Package

74


2015

Unit Summary

75

HA100


2015

Unit Summary

76

HA100


2015

Unit 3 Architecture Unit Overview This unit provides an overview to the architecture behind SAP HANA.

Unit Objectives After completing this unit, you will be able to: • • • • •

Understand the architecture of SAP HANA Explain the necessity of a persistence layer Understand the difference between data and log volumes Describe the reboot process after a power failure Explain the principles of shadow paging

Unit Contents Lesson: Architecture................................................................. 78 Lesson: Persistence Layer ......................................................... 86 Procedure: Exercise 2: Working with Catalog Objects ..................... 90

2015


77

Unit 3: Architecture

HA100

Lesson: Architecture Lesson Overview This lesson introduces a description of the SAP HANA Database architecture and components.


Understand the architecture of SAP HANA

Business Example A customer needs to transform his landscape to improve his global performance and lessen his IT administration tasks. He needs to turn to the SAP HANA technology and

learn the new architecture of the SAP HANA Appliance.

HANA System Architecture

Figure 66: Index Server Architecture

78


2015

Lesson: Architecture

HA100

The Index Server of the SAP HANA database is a core component that orchestrates the database's operations. The Connection and Session Management

which creates and manages sessions and

connections for the database clients such as SAP BusinessObjects Reporting tools or

applications. The Transaction Manager coordinates transactions, controls transactional isolation, and keeps track of running and closed transactions. The client requests are analyzed and executed by a set of specialized engines and processors that handle Request Processing and Execution Control . Once a session is established, the database client typically uses SQL statements to communicate with this module. For analytical applications, the multidimensional query language MDX is also supported. Incoming SQL requests are received by the SQL Processor . This component executes the Data Manipulation Language (DML) statements, such as INSERT, SELECT, UPDATE or DELETE. Other types of requests are delegated to other components.

For example, Data Definition Language (DDL) statements, such as the definition of relational tables, columns, views, indexes and procedures, are dispatched to the Metadata Manager. Planning commands are routed to the Planning Engine that allows financial planning applications to execute basic planning operations in the database layer. The SAP HANA database offers programming capabilities to execute applicationspecific calculations inside the database system. The SAP HANA database

has its own programming languages. SQL Script is used to write database stored procedures. Procedure calls are forwarded to the Stored Procedure processor. Incoming MDX requests are processed by the MDX engine and also forwarded to the Calculation Engine , which is a common infrastructure that also supports SQL Script, MDX and Planning operations. The Persistence Layer component manages the communication between the Index Server and the file system that store the Data volume and Transaction Log volume.

2015


79


HA100

High Availability and Disaster Recovery

Figure 67: SAP HANA High Availability – High Availability – Disaster Tolerance

It is crucial for all business applications to make sure they can support daily business

without disruption. SAP HANA offers features to make sure that the system is available even in the event of a disaster. There are features to guarantee this within a datacenter and also between physically different datacenters.

Figure 68: High Availability Per Datacenter

80


2015


HA100

Figure 69: Scale Out – SAP HANA Database Landscape

HighAvailability enables the failover of a node within one distributed SAP HANA appliance. Failover uses a cold standby node and gets triggered automatically. Landscape Up to 3 master nameservers can be defined. During startup one server gets

elected as active master. The active master assigns a volume to each starting index server or no volume in case of standby servers.

Master nameserver failure In case of a master nameserver failure, another of the remaining nameservers will become active master. Indexserver failure The master nameserver detects an indexserver failure and executes the failover. During the failover the master nameserver assigns the volume of the failed indexserver to the standby server.

2015


81


HA100

Figure 70: High Availability Between Different Data Centers

The mirroring is offered on the storage system level. It will be offered together with

the appliance by an HAcertified partner. The hardware partner will define how this concept is finally realized with his operation possibilities.

Performance impact is to be expected on data changing operations as soon as the synchronous mirroring is activated. The impact depends strongly on a lot of external factors like distance, connection between data centers, etc. The synchronous writing of the log with the concluding COMMITs is the crucial part here.

In case of emergency, the primary data center is not available any more and a process for the takeover must be initiated. So far a lot of customers wished to have a manual process here, but an automated process is also able to be implemented. This takeover process then would end the mirroring officially, will mount the disks to the already

installed HANA software and instances, and start up the secondary database side of the cluster. If the host names and instance names on both sides of the cluster are identical, no further steps with hdbrename are necessary. It would be possible to run a development and/or QA instance of the three tier installation on this secondary cluster hardware, simply to utilize it until the takeover

is executed. The takeover then would stop these dev. and/or QA instances and mount the production disks to the hosts. It would require an additional set of disks for the dev. and QA instance.

82


2015


HA100

So far no hot standby via log shipping is available or even log shipping by recovering of log backups on a standby host. This needs some changes in the engines of HANA

database which needs time to be realized. Both solutions are on the agenda of HANA's future development.

SAP HANA Components The following tables show which components will be installed in an SAP HANA landscape, depending on the actual scenario. These technologies are reflected in the different editions of SAP HANA, and correspond to a different range of requirements from the customer.

Figure 71: Bill of Material / SAP HANA Appliance Software Components

2015


83


HA100

Figure 72: Bill of Material / SAP HANA Peripheral Components

84


2015


HA100

Lesson Summary You should now be able to: • Understand the architecture of SAP HANA

2015


85


HA100

Lesson: Persistence Layer Lesson Overview This lesson explains how the data persistence works in SAP HANA and present the role of each component of the persistence storage layer.

Lesson Objectives After completing this lesson, you will be able to: • • • •

Explain the necessity of a persistence layer Understand the difference between data and log volumes Describe the reboot process after a power failure Explain the principles of shadow paging

Business Example As an SAP HANA Database Administrator, you need to understand how the data persistence works, to be able to know how the system will recover in the event of a power failure.

Persistence Layer Components The persistence layer of SAP HANA relies on Data and Log Volumes. The inmemory

data is regularly saved to these volumes.

86


2015

Lesson: Persistence Layer

HA100

Figure 73: SAP HANA Persistence – Data and Log Volumes

Data and log volumes are used as follows: • •

On a regular basis, data pages and before images (undo log pages) are written into the data volumes. This process is called a Savepoint. Between two savepoints, after images (redo log pages) are written in the log volumes. This is done each time a transaction is committed.

The savepoint process relies to a concept called Shadow Memory.

2015


87


HA100

Figure 74: SAP HANA Persistence Shadow Memory Concept

Shadow paging is used to undo changes that were persisted since the last savepoint.

With the shadow page concept, physical disk pages written by the last savepoint are not overwritten until the next savepoint is successfully completed. Instead, new physical pages are used to persist changed logical pages. Until the next savepoint is complete, two physical pages may exist for one logical page: • •

The shadow page, which still contains the version of the last savepoint.

The current physical page which contains the changes written to disk after the last savepoint.

System Restart Procedure After a restart, the system is restored from the savepoint versions of the data pages. This way, all data changes written since the last savepoint are not restored. After the savepoint is restored, the log is replayed to restore the most recent committed state.

88


2015


HA100

Figure 75: Persistence Layer in SAP HANA Database – System Restart

The system restart includes the following actions: •

Restore data

– Reload the last savepoint

• •

– Seach the undo log for uncommitted transactions saved with last savepoint (stored on the data volume) and roll them back – Search the redo log for committed transactions since last savepoint (stored on the log volume) and reexecute them Load all the tables of the row store into memory Load the tables of the column store that are marked for preload into memory

Note: Only tables marked for preload are loaded into memory during startup. Tables marked for loading on demand will only be loaded into memory at first access.

2015


89


HA100

Exercise 2: Working with Catalog Objects Use After completing this exercise, you will be able to: • • • •

See whether the content of a table is stored in rows or in columns in SAP HANA

Display the structure of a table Identify what indexes are defined for a table Preview the content of a table

During this exercise, you will use two tables from the

• •

TRAINING schema:

Table MARA . This is the table that contains master data of products in an SAP ERP database. Table HANA_SEARCH_DATA .

Procedure 1.

Start the SAP HANA Studio and log on to the SAP HANA system. For this exercise you can use either the SAP HANA Administration Console perspective or the SAP HANA Modeler perspective.

2.

Display table MARA in the catalog of the SAP HANA system.

Hint: To find the MARA table easily, you will apply a filter to the list of tables.

• •

In the Systems view, expand the catalog folder to find the TRAINING schema, which includes the Tables node. Rightclick the Tables node and choose Filters... .


90


2015


HA100

Figure 76: Filtering Catalog Objects

• •

Enter the filter pattern

MA and choose OK . Doubleclick the Tables node.

The table list now filtered and displays the table

MARA among others.


2015


91


HA100

Figure 77: List of Tables

3.

Open the definition of table MARA and identify the storage type.

•

Rightclick table MARA and choose Open Definition .

Figure 78: Opening a Table Definition


92


2015


HA100

Figure 79: Table Definition

• • • 4.

This screen shows the table structure with all columns, their data types, length, etc. The table type (column store or row store) is displayed in the topright corner of this screen. Table MARA is using the columnbased storage type.

Identify the key columns of table MARA . The key fields of the table are marked in the Key column. •

For table MARA , the key columns are MANDT (client) and MATNR (material number).


2015


93


HA100

Figure 80: Key Fields

5.

Preview the data of table MARA . In the context menu of the table, you can use one of the following options:

• •

Open Content, to simply display the table content. Open Data Preview , to explore the table contents as raw data (flat table view), build analyze the distinct values, or you can do a quick analysis using a chart or a table.

Rightclick the MARA table and choose Open Data Preview . 6.

Open the definition of table HANA_SEARCH_DATA and identify the tables storage type and indexes.

•

Rightclick the Tables node and choose Filters... .

• • •

Enter the filter pattern HANA and choose OK. If needed, doubleclick the Tables node to view the filtered table list. Rightclick table HANA_SEARCH_DATA and choose Open Definition .


94


2015


HA100

Figure 81: Table HANA_SEARCH_DATA

• •

Identify the storage type of the HANA_SEARCH_DATA table. To display the list of indexes created on the table, select the Indexes tab.

Your observations should be as follows:

• •

2015

The table is a rowstore table. There are no indexes defined.


95


HA100

Lesson Summary You should now be able to: • Explain the necessity of a persistence layer • Understand the difference between data and log volumes • Describe the reboot process after a power failure • Explain the principles of shadow paging

96


2015

Unit Summary

HA100

Unit Summary You should now be able to: • Understand the architecture of SAP HANA • Explain the necessity of a persistence layer • Understand the difference between data and log volumes • Describe the reboot process after a power failure • Explain the principles of shadow paging

2015


97

Unit Summary

98

HA100


2015

Unit Summary

99

HA100


2015

Unit Summary

100

HA100


2015

Unit 4 Modeling Unit Overview In order to report on SAP HANA you need to create Information Models. This unit will guide you on how to model your Information Models.

Unit Objectives After completing this unit, you will be able to: • • • • • • • •

Explain the COPA HANA case study Understand the features of SAP HANA Studio Modeler perspective Describe the levels of modeling in SAP HANA and understand how the different

type of models support each other Create an Attribute View Create an Analytic View Create a Calculation View Display the Data from Information Views Introduce SAP HANA Live and its Virtual Data Model

Unit Contents Lesson: Introduction to the COPA Scenario.................................... 102 Lesson: Introduction to the SAP HANA Modeler Perspective ................ 106 Lesson: Levels of Modeling ....................................................... 113 Procedure: Exercise 3: Create the Attribute Views ........................ 135 Procedure: Exercise 4: Create the Analytic View for Actuals ............ 158 Procedure: Exercise 5: Create the Analytic View for the Plan ........... 170 Procedure: Exercise 6: Create the Calculation View ...................... 186

2015


101

Unit 4: Modeling

HA100

Lesson: Introduction to the COPA Scenario Lesson Overview This lesson looks at introducing the case study for exercises, and a high level overview

of the different types of models in SAP HANA.


Explain the COPA HANA case study

Business Example You want to create Information Models to report on COPA data using SAP HANA.

Introduction to the COPA Scenario In this unit we will create a complex data model that combines financial data for both budgeted (planned) and actuals. The raw data has already been imported for us into the SAP HANA system. The data comes from an SAP ERP system, from a module called COPA. The PA stands for Profitability Analysis. The data used by Profitability Analysis is contained in just a few tables, but these tables can be very large, and reports on these large tables can run

a long time. This is an ideal case to showcase the ease and power of SAP HANA. The data model will help us to create a actual vs. planned financial report. We do this in the last unit on reporting, where we will then use the data models that we have built here. The actual vs. planned financial report will be built in two'cubes', one for actuals

and one for planned (budgeted) data. We will combine this with master data about the customers, where the customers are, and which products they bought. .

102


2015

Lesson: Introduction to the COPA Scenario

HA100

Figure 82: COPA Storage Architecture

The actuals data is stored in a table starting with CE1, in this case CE1IDEA. The planned data is stored in a table starting with CE2, in this case CE2IDEA.

We want to report sales vs budget per product and per customer location, e.g. at the country or province/state or city level.

Using data from COPA in HANA Using the knowledge about the Data Model, you will be able to create Information Models based on COPA data.

2015


103

Unit 4: Modeling

HA100

Figure 83: Exercise: Enable Plan/Actual Comparison in SAP HANA, Using Data From COPA

104


2015

Lesson: Introduction to the COPA Scenario

HA100

Lesson Summary You should now be able to: • Explain the COPA HANA case study

2015


105

Unit 4: Modeling

HA100

Lesson: Introduction to the SAP HANA Modeler Perspective Lesson Overview This lesson will cover an overview of SAP HANA Studio Modeler perspective.


Understand the features of SAP HANA Studio Modeler perspective

Business Example You want to understand the features of SAP HANA Studio Modeler perspective so that you can build Information Models

The SAP HANA Modeler Perspective

Figure 84: SAP HANA Overview

106


2015

Lesson: Introduction to the SAP HANA Modeler Perspective

HA100

Figure 85: The Modeler Perspective in the SAP HANA studio

SAP HANA Studio is intended for persons with extensive technical knowledge and can therefore be regarded as the more powerful tool. It offers more extensive functions as shown below: • • • • •

2015

Creating information objects such as Attribute Views, Analytic Views, Calculation Views, Stored Procedures and Analytic Privileges Processing information models Managing modeling content by performing multiple administration activities Importing table definitions from the source ERP system into SAP HANA Studio

Loading data into these table definitions


107

Unit 4: Modeling

HA100

Figure 86: The Modeler Quick View

The Modeler Quick View provides easy access to frequently performed modeling tasks in the SAP HANA studio. This replace the Quick Launch tab from the previous versions of the studio.

108


2015

HA100


Figure 87: The Webbased Development Workbench

Except for the SAP HANA studio, we also have a webbased Development Workbench

that you can use from any HTMLcompatible browser for modeling and development in SAP HANA. This uses the builtin HTML5 framework inside SAP HANA called SAPUI5. The obvious place where this will really help is for cloudbased solutions.

Figure 88: SAP HANA Studio Preferences

2015


109

Unit 4: Modeling

HA100

For data previews you need to add a client filter. There are several ways: • • •

Set a default client on the model itself Set the default client in the Modeler preferences Set a default client in the user session properties. (You will need the USER ADMIN privilege to change your user session properties)

Figure 89: Terminology

110


2015

HA100


Figure 90: SAP HANA Studio Features

2015


111

Unit 4: Modeling

HA100

Lesson Summary You should now be able to: • Understand the features of SAP HANA Studio Modeler perspective

112


2015

Lesson: Levels of Modeling

HA100

Lesson: Levels of Modeling Lesson Overview This lesson deals with the different levels of information models that you can create in

SAP HANA. You will learn how these models car be created and connected together in order to build a data model that can be consumed by client applications.


Describe the levels of modeling in SAP HANA and understand how the different

• • • • •

type of models support each other Create an Attribute View Create an Analytic View Create a Calculation View Display the Data from Information Views Introduce SAP HANA Live and its Virtual Data Model

Business Example You have a good understanding about the data on which you want to create reports, and you are ready to create new Information Models. Before you start, you want to know what types of information models you have to create in SAP HANA.

Overview of Information Models

Figure 91: Modeling Process Flow

2015


113

Unit 4: Modeling

HA100

The diagram depicts the process flow for modeling within SAP HANA. This example focuses on the process of creating Information Models and also demonstrates how to export these models for easy portability.

Figure 92: Overview of Information Models (1)

114


2015


HA100



2015


115

Unit 4: Modeling

HA100

Figure 95: Exercise Case Study

During the course, you will work on different exercises in order to create Attribute Views, Analytic Views and one Calculation View. These views are based on data from the COPA (Controlling Profitability Analysis) module of the SAP ERP.

The Calculation View will then be used by several reporting tools such as SAP BusinessObjects BI tools and Excel, to analyze the data.

116


2015


HA100

Attribute Views

Figure 96: Attribute Views Overview

The main steps to create an Attribute View are as follows.

Figure 97: Attribute View

1. Set Attribute View parameters

2015


117

Unit 4: Modeling

118

HA100


2015


HA100

Figure 98: Creating a New Attribute View

•

Assign a unique name to the model. The allowed characters are: capital letters (AZ), numbers (09), and underscore

_ •

Enter a short description for the view There is no multilanguage support for this kind of metadata.

•

Select the type of Attribute View Beyond the Standard Attribute View type, a Time view creates a master data view for time characteristics (calender year, quarter, month, week, day, fiscal

year/period, and so on). A Derived Attribute View is a linked copy that allows you to use two attribute views with exactly the same definition in the same Analytic or Calculation view.

Note: An Attribute View cannot be used more than once in another view. If needed, you can create one or several Derived Attribute Views.

•

You can use an existing Attribute View as a template for the new one. Use the Copy From feature. After the copy, the two views are independent. Note: When using this option, models can be selected from any package. You are not limited to the models within the selected package. Packages simply organize Information Models a bit like schemas organize physical tables.

•

You must decide which package will contain the Attribute View.

2. Select tables

2015


119

Unit 4: Modeling

HA100

Figure 99: Attribute View – Select Table(s)

Note: Tables can be selected from multiple schemas, and are not limited to one schema per Attribute View.

120


2015


HA100

Figure 100: Attribute View – Add Additional Table

To find a table in a schema containing many tables, you can filter the list of tables:

• • •

Right click the Tables folder within the schema. Enter a filter pattern.

Choose OK The list of tables in the schema is now filtered.

•

Expand the Tables folder to display the filtered list of tables.

This is a useful option if you want to display the table definition first, to make sure

you add the correct table.

2015


121

Unit 4: Modeling

HA100

Figure 101: Attribute View – Define Join Properties

3. Define join conditions

Figure 102: Join Types

122


2015


HA100

The SAP HANA database supports the following types of joins:

•

Inner Join The Inner Join combines records from the left and right table exactly when the specified criteria are met. If the criteria are not met, no record is created in the result set.

•

Left Outer Join A record from the left table is always stored in the result set. If a record from the right table meets the criteria, it is combined to the record from the left table,

otherwise the columns are empty (null). •

Right Outer Join A record from the right table is always stored in the result set. If a record from the left table meets the criteria, it is combined to the record from the right table,

otherwise the columns are empty (null).

Figure 103: Examples of SQL Join Operations

•

Text Join Text Join is used to join a description table to a master data table. This is a requirement for description mapping. The text join always filters on one language and thus you need to specify a language column in the text table. The language is filtered based on the view settings (which are per default taken from

the user profile of the modeler)

2015


123

Unit 4: Modeling

HA100

Figure 104: Text Join

•

Referential Join The Referential Join Is semantically an inner join that assumes that referential integrity is given, which means that the left table always has a matching entry in the right table. It can be seen as an optimized inner join where the right table is not checked if no field from the right table is requested. It can be used, for example, in the following scenarios: – In an Attribute View, to join the master data tables – In an Analytic view, to join the Data Foundation with the relevant Attribute Views (in the Logical Join node) Referential Joins are only executed when columns from both tables are requested. Therefore, if a field is selected from the right table it will act similar to inner join, and if no fields from the right table is selected it will act similar to

a left outer join.

124


2015


HA100

Caution: Referential joins must be used carefully: you have to check that the database model enforces the referential integrity between the joined tables. •

Star Join A star join is only available in calculation views. It is used for data organized in

the multidimensional model of a socalled star schema. The fact table for a star join can be any type of input node; however, only Calculation Views of Data Category Dimension are allowed as input nodes for dimensions. The type of joins between the fact and dimension tables within the star schema can be defined in the star join node. The available options are: Inner join, Left outer join, Right outer join, and Text join.

Note: 4. Select key attributes, attributes, and set filters as needed.

Figure 105: Attribute View – Output Field Selection and Filters

5. Define Description Mappings

2015


125

Unit 4: Modeling

HA100

Figure 106: Attribute View – Set Description Mapping

126


2015


HA100

Figure 107: Map Texts

•

2015

Description mapping is a technique to consistently connect the description (text) to the semantic key of an attribute value.


127

Unit 4: Modeling

HA100

Figure 108: Attribute Properties – Property Hierarchy Active – See NonKey Fields via MDX

If views are displayed via Excel, each Attribute View is represented in Excel as a Dimension. For example, you have modeled the Product dimension with attributes Product Number and Product Category. • •

Product Number is the key Product Category is a nonkey attribute

Each attribute in the list of output fields has among its properties a setting Hierarchy Active . This setting is False by default. If it is set to False , the attribute will only show up in Excel (via MDX) if it is a key field of the attribute view. If the property is set to key field.

True , the attribute will show up in Excel although it is not a

6. Create Hierarchies (level hierarchies)

128


2015


HA100

Figure 109: Attribute View – Define a Level Hierarchy

Note: Currently, hierarchies defined in the modeler are only accessible via MDX.

7. Create Hierarchies (parent child hierarchies)

2015


129

Unit 4: Modeling

HA100

Figure 110: Attribute View – Define a ParentChild Hierarchy

Note: Currently, hierarchies defined in the modeler are only accessible via MDX.

8. Create Calculated Attributes

130


2015


HA100

Figure 111: Attribute View – Calculated Attributes

9. Save and Activate the Attribute View

2015


131

Unit 4: Modeling

HA100

Figure 112: Attribute View – Save and Activate the View

Figure 113: Save and Activate

Before a view is visible for reporting, it needs to be activated (deployed). The activation process translates the metadata defined in the Information Model (the designtime object) into a database object (the runtime object, which is a socalled column view). All the runtime column views are normally managed within the standard schema _SYS_BIC in the Catalog node of your system.

132


2015


HA100

A modified version of a view cannot be activated. You always need to save the view first.

Note:

•

There is no implicit Save action performed when a user activates a modified view from the Systems view: this action activates the last saved version of the view

•

However, if you use the icon on top of the Model Editor window, this icons triggers a Save and Activate action (as indicated bythe icon's tool tip).

•

It is not necessary to close the modeling editor before activating an information view.

Once the view activation is finished, you can see the log of this activation process (button in topright corner of the Log view). This activation log contains, among other things, the SQL call that has been executed in order to create the views in the database. This SQL call can be a good starting point to analyze technical issues with views. The Deployment Log is also useful if there is an error during view activation.

At this point, you have deployed an Attribute View. You are now able to report fact data on this attribute, especially by including this view in an Analytic Views.

10. Preview the Data

2015


133

Unit 4: Modeling

HA100

Figure 114: Attribute View – Preview the Data

The data preview functionality is useful for confirming that one has modeled the data the way he/she intended. It can be done at the Attribute view, or at the physical tables at the foundation level. There are three main views one can select from when previewing data: • • •

Raw Data – table format of data Distinct Values – graphical and text format identifying unique values Analysis – select fields (attributes and measures) to display in graphical format

of in a table Reporting at this level will not include measures as measures are in Analytic Views not

Attribute Views. To conduct analysis on measures according to the attributes in this Attribute View, you must include this Attribute View in an Analytic View definition.

134


2015


HA100

Exercise 3: Create the Attribute Views Use After completing this exercise, you will be able to: • • • •

Add tables to an Attribute View Join tables with a relevant join type Specify the output columns Preview the data of an attribute view

You are at a customer site and have been asked to build Information Models in SAP

HANA in order to display COPA data. To be able to display plan vs. actual comparisons by product and country, you will need to create two Attribute Views: • •

An Attribute View for the locations An Attribute View for the products

These Attribute Views, based on several elementary tables from the source SAP ERP,

will be joined later on to the fact tables of Plan and Actual data.

Prerequisites Before you start, review the following diagram to identify which tables and columns you will use during this exercise, and how the tables must be joined.


2015


135

Unit 4: Modeling

HA100

Figure 115: Creating the Attribute View Overview

Procedure 1.

Start the SAP HANA Studio and open the SAP HANA Modeler perspective.

2.

In the STUDENT## package that you created earlier, create a new Attribute View for the Customers with the following properties.

• • •

Field

Value

Name

LOCATION_## (where ## is your

Label

Customer Location ##

View Type

Attribute View

Subtype

Standard

student number)

In the Content (rightclick) menu of the Systems view, rightclick the STUDENT## package and choose New → Attribute View . Enter the required data. Choose Finish. Continued on next page

136


2015


HA100

Figure 116: Create an Attribute View

3.

Add the following tables from the TRAINING schema to the data foundation.

Table name

Content

KNA1

Customers

T005U

Regions

T005T

Countries


2015


137

Unit 4: Modeling

HA100

Figure 117: Adding the KNA1 Table

•

In the Scenario pane, click the + sign on the right of the Data Foundation

• • • • • •

node. In the Search field , enter KNA1 . Select the KNA1 (TRAINING) table and choose OK. Click the + sign on the right of the Data Foundation node. In the Search field , enter T005 . While pressing the Ctrl key, select tables T005U and T005T . Choose OK.

Hint: You can also choose Add Objects from the context menu of the Data Foundation node, or simply drag the relevant tables from the TRAINING Schema of the catalog to the Data Foundation node. 4.

Join the tables as follows, using for each join a Text Join type based on the SPRAS column. KNA1 is considered as the left table, others as right table. Join field LAND1 of the left table to field LAND1 of table T005U (country code).

138


2015


HA100

Join field LAND1 of the left table to field LAND1 of table T005T (country

code). Join field REGIO of the left table to field BLAND of table T005U (region code). Join field MANDT (SAP Client) of the left table KNA1 to field MANDT

of tables T005U and T005T • • •

In the Details pane, draw a connector from the KNA1.LAND1 column to the T005U.LAND1 column. To edit the join properties, doubleclick the new connector. In the Join Type dropdown list, choose Text Join.

Note: The cardinality is automatically set to 1..1 . •

In the Language column dropdown list, choose the SPRAS column. (If you do not find the SPRAS column in the list, you might have used one of the

• • 5.

T005 tables as the left table instead of KNA1) Chose OK. Repeat the previous steps for the remaining joins and MANDT.

Add the following columns to the output. The KUNNR and MANDT columns from table KNA1 must be defined as a Key Attribute . Source Table KNA1

2015

Columns • • • •

MANDT [Key attribute] (SAP client) KUNNR [Key attribute] (customer number) LAND1 (country) ORT01 (country)


139

Unit 4: Modeling

HA100

•

REGIO (region)

Figure 118: Adding Columns to the Output

•

In the TRAINING.KNA1 table, rightclick the KUNNR column and choose Add To Output .

Note: Alternatively, you can either doubleclick the column name or click the rounded selector at the left of the column name.

• •

140

Repeat the previous step for all the columns to include in the output.

To define the key attribute, in the Output pane, select the KUNNR and MANDT column and, in the Properties pane, set the Key Attribute property to True .


2015


HA100

Figure 119: Adding KUNNR as a Key Attribute

6.

Add the following columns (text columns) to the output. These columns will be used as Label columns for other columns, so they must be hidden.

Source Table KNA1

Column NAME1

Content

T005U

BEZEI

Region name

T005T

LANDX

Country name

Customer name

•

In the TRAINING.KNA1 table, rightclick the Add To Output.

•

In the Output pane, select the NAME1 column and, in the Properties pane, set the Hidden property to True .

NAME1 column and choose

Note: Hiding these columns can also be done in the following step, in the Semantics node details.

Repeat the previous steps for the other columns.

2015


141

Unit 4: Modeling

HA100

Figure 120: Adding Text Fields as Hidden Fields

7.

In the Semantics, define the Label and Label columns as follows.

Note: Defining a label column in an attribute view allows SAP HANA to map codes to descriptions (for example, “US” and “United States”) in a meaningful manner. This mapping can be interpreted by some BI clients, such as Information Design Tool.

142

Column name

Label

MANDT

SAP client

KUNNR

Customer Number

NAME1

LAND1

Country

LANDX

ORT01

City

REGIO

Region


Label Column

BEZEI

2015


HA100

• • • 8.

NAME1

Customer Name

BEZEI

Region Name

LANDX

Coutry Name

In the Scenario pane, select the Semantics node. In the Columns pane, for the KUNNR row, click the Label Column field and choose NAME1 in the dropdown list. Repeat the last step for the two other columns.

Save, validate, and activate the Attribute View. • • • •

Rightclick the LOCATION_## Attribute View and choose Validate. In the Job Log view, on the Current tab, check the status of the last Model validation job. Rightclick the LOCATION_## Attribute View and choose Activate. In the Job Log view, on the Current tab, check the status of the last Activation job.

Figure 121: Activate the Attribute View.

2015


143

Unit 4: Modeling

HA100

9.

Preview the data of the LOCATION_## Attribute View.

• •

In the Systems view, rightclick the LOCATION_## Attribute View and choose Data Preview . Select the Raw Data tab.

Figure 122: Data Preview LOCATION_## Attribute View

Note: The KUNNR , LAND1 , and REGIO columns have a label column displayed in the Data Preview. 10. Close the Data Preview tab and the Definition tab of the Attribute View LOCATION_##. 11. For the sake of time, we will just copy the next attribute view from an already created example. The steps to create it from the start would be similar to the previous example. In the STUDENT## package, create a new Attribute View for the Products with the following properties.

144


2015


HA100

Field

Value

Name

PRODUCT_## (where ## is your

Label

Products Description ##

View Type

Attribute View

Copy From

STUDENT00.PRODUCT_00

student number)

Figure 123: Copy the PRODUCTS_00 attribute view from STUDENT00

• • •

In the Content (rightclick) menu of the Systems view, rightclick the STUDENT## package and choose New → Attribute View . Enter the required data. Choose Finish.

The attribute view was now created for you. You still have to save and activate your new attribute view.

2015


145

Unit 4: Modeling

HA100

12. Save, validate, and activate the Attribute View.

• • • •

Rightclick the PRODUCT_## Attribute View and choose Validate. In the Job Log view, on the Current tab, check the status of the last Model validation job. Rightclick the PRODUCT_## Attribute View and choose Activate. In the Job Log view, on the Current tab, check the status of the last Activation job.

13. Preview the data of the PRODUCT_## Attribute View.

• •

In the Systems view, rightclick the PRODUCT_## Attribute View and choose Data Preview . Select the Raw Data tab.

Figure 124: Data Preview PRODUCT_## Attribute View

14. Close the Data Preview tab and the Definition tab of the Attribute View PRODUCT_## .

146


2015


HA100

Analytic Views

Figure 125: Analytical Views The multidimensional Model

Figure 126: Creating an Analytic View

1. Set Analytic View parameters

2015


147

Unit 4: Modeling

HA100

Figure 127: Analytic View – View Creation Wizard

2. Select tables

Figure 128: Analytic View – Select Table(s)

148


2015


HA100

The Analytic View editor helps you to create a fact table by adding and joining the tables in the Data Foundation node. In addition, you can join attribute views to these tables in the Logical Join node. You use the Output pane to model various view elements: Columns, Calculated Columns, Restricted columns and Input Parameters. In the Semantics node, you can classify these Columns and Calculated Columns as attributes and measures. You can also create variables/input parameters, and assign variables to the columns in the Semantics node. 3. Select Attribute Views (optional)

Figure 129: Analytic View – Select Attribute View(s)

•

2015

Attribute Views can be added to the Logical Join


149

Unit 4: Modeling

HA100

Figure 130: Analytic View – Analytic View Editor

4. Join Attribute Views •

The Data Foundation view shows the physical table with all fields that can be incorporated in the final model.

•

The Logical Join view displays only the fields that you have chosen to include in

the output of the Data Foundation node, as well as the restricted and calculated measures that have been defined.

150


2015


HA100

Figure 131: Analytic View – Define the Data Foundation

5. Select the columns and set filters.

Figure 132: Analytic View – Join Attribute Views to Data Foundation

2015


151

Unit 4: Modeling

HA100

Define join conditions and cardinality. The default settings for joins are

Referential.

Click on the properties tab on the lower section of the screen to change these settings

if necessary.

Figure 133: Analytic View – Defining Calculated Columns

6. Create Calculated Columns

152


2015


HA100

Figure 134: Analytic View – Defining Restricted Columns

7. Create Restricted Measures

2015


153

Unit 4: Modeling

HA100

Figure 135: Analytic View – Save and Activate the View

Figure 136: Semantic View

8. Save and Activate The Analytic View can be validated, saved and activated as shown in the slide. When saving, just the metadata of the model is saved but this cannot be viewed from the reporting tools like SAP BusinessObjects.

154


2015


HA100

Once activated it creates a column view (database view) in schema _SYS_BIC

Figure 137: Analytic View – Preview Data of Analytic View

The data preview functionality can be used for verification of the designed model. It has three tabs: • • •

Raw Data – table format of data Distinct Values – graphical and text format identifying unique values Analysis – select fields (attributes and measures) to display in graphical format

or in a table.

2015


155

Unit 4: Modeling

HA100

Figure 138: Analytic View

Figure 139: Analytic View You can display the data in a table format or a graph. There are multiple graphs types

available.

156


2015

HA100


Figure 140: EXERCISE – Creating Analytic Views for the COPA Scenario

2015


157

Unit 4: Modeling

HA100

Exercise 4: Create the Analytic View for Actuals Use After completing this exercise, you will be able to: • • • • •

Use fact tables and Attribute Views in an Analytic View Join tables and views Filter the rows of a fact table Rename columns Create calculated columns

Now you have created the Attribute Views for Products and Customer locations, you need to create the Analytic View based on Actual data from the COPA model. The corresponding fact table is CE1IDEA . The Analytic View will display some columns directly extracted from the fact table, as well as calculated columns to determine the net revenue and the contribution margin.

Prerequisites Before you start, review the following diagram to identify which tables, views and columns you will use during this exercise, and how they must be joined.

Figure 141: Creating the Analytic View for Actuals Overview


158


2015


HA100

Procedure 1.

In the STUDENT## package, create a new Analytic View with the following properties.

• • •

Field

Value

Name

CEA1_##

Label

Contribution Margin for Actuals ##

View Type

Analytic View

In the Content node of the Systems view, rightclick the STUDENT## package and choose New → Analytic View . Enter the required data. Choose Finish.

Figure 142: Create Analytic View CEA1_##

2.

Add the following table from the TRAINING schema to the data foundation.

• • • 3.

Table name

Content

CE1IDEA

Actual data from the COPA model

In the Scenario pane, click the + sign on the right of the Data Foundation node. In the Search field , enter CE1. Select the CE1IDEA (TRAINING) table and choose OK .

Add the following columns to the output. Continued on next page

2015


159

Unit 4: Modeling

HA100

Source Table

Columns

CE1IDEA

•

• • • • • •

MANDT (SAP Client) KNDNR (Customer number) ARTNR (Material number) PERIO (Period)

VKORG (Sales organization) PLIKZ (Actuals/Plan flag)

In the TRAINING.CE1IDEA table, rightclick the choose Add To Output .

KNDNR column and

Hint: To find easily the columns, you can use the

Find Column

button in the upperright corner of the main pane.

• 4.

Repeat the previous step for all the columns to include in the output.

To retrieve relevant information, you must filter the CE1IDEA table on the following columns: Column

Filter condition Objective

PALEDGER = 01

Use the correct currency type

VRGAR

Use Billing Data

= F

Note: Except from Billing Data, the CE1IDEA table also contains data from Incoming Sales Order (A), Direct Posting from FI (B), etc.

•

In the TRAINING.CE1IDEA table, rightclick the

• • •

choose Apply Filter. In the Operator dropdown list, choose Equal . In the Value field, enter 01. Repeat these steps for the VRGAR column and enter value F.

PALEDGER column and


160


2015


HA100

Figure 143: Data Foundation Tab

5.

Add the PRODUCT_## and LOCATION_## attribute views to the Logical Join node. • • • •

6.

In the Scenario pane, click the + sign on the right of the Logical node. In the Search field , enter PRODUCT . Select the PRODUCT_## (STUDENT##) attribute view and choose OK . Repeat these steps to add the LOCATION_##

Join the data foundation and Attribute Views as follows, using for each join a Referential join type , and a 1..1 cardinality Data Foundation is considered as the left table, others as right tables. Join field KNDNR of the left table to field KUNNR of Attribute View LOCATION_## (Customer number). Join field ARTNR of the left table to field MATNR of Attribute View

PRODUCT_## (Material number).

Join field MANDT_1 of the left table to field MANDT of each Attribute View.

•

In the Details pane, draw a connector from the KNDNR column of the data foundation to the KUNNR column of the LOCATION_## Attribute View.

• • • • •

To edit the join properties, doubleclick the new connector. In the Join Type dropdown list, choose Referential. In the Cardinality dropdown list, choose 1..1 column. Choose OK . Repeat the previous steps for the other three joins.


2015


161

Unit 4: Modeling

HA100

Figure 144: KNDNR and ARTNR as Attributes

7.

Add the following columns (measures) from the data foundation to the output. Source Table CE1IDEA

• •

Columns

• • • •

VV010 (Gross Revenue) VV070 (Sales Deduction) VV290 (Production Variance) VV960 (Other Expenses)

Select the Data Foundation node. In the TRAINING.CE1IDEA table, rightclick the choose Add To Output .

VV010 column and

Hint: To find easily the columns, you can use the

Find Column

button in the upperright corner of the main pane.


162


2015


HA100

Figure 145: Using the Find Column feature

• 8.

Repeat the previous step for the other columns to include in the output.

Rename the VVxxx and other columns to give them a meaningful name and add a label.

Hint: To rename several columns at the same time, in the Output pane of the Logical Join, click the Pencil icon. Name

New Name

Label

PERIO

PERIOD

Period

VKORG

SALESORG

Sales Organization

PLIKZ

PLANACTUAL

Plan/Actual Indicator

VV010

GROSSREVENUE Gross Revenue


2015


163

Unit 4: Modeling

HA100

VV070

SALESDEDUCTION Sales Deduction

VV290

PRODVARIANCE

VV960

OTHEREXPENSES Other Expenses

Production Variance

• • •

Select the Logical Join node. In the Output area (on the right of the screen), select the VV010 column. In the Properties area (on the right of the screen), in the Name field, enter GROSSREVENUE and, in the Label field,, enter Gross Revenue.

•

Repeat the previous steps for the other columns to rename.

Hint: Alternatively, you can select the Logical Join node, choose the Rename button (pencil) in the top of the Output area, and enter the new names and labels for all the columns at the same time.

Figure 146: Rename the Measures


164


2015


HA100

9.

In the Semantics, assign the correct column type to each column in the output: the four VVxxx columns are measures, other columns are attributes. • • • •

Select the Semantics node. In the topright corner of the

Column area, choose the Auto Assign button.

Check that the Type column displays the correct type for each row. If needed, modify the autoassigned column types.

Figure 147: Object Types

Note: In the Details of the Semantics node, the columns are presented on two different tabs: • •

On the Local tab, you find columns that come from the data foundation. On the Shared tab, you find columns that come from attribute views.

10. Create three calculated columns as follows. Each new column is a and its data type is DECIMAL (15,2) .

measure ,


2015


165

Unit 4: Modeling

HA100

Column name

Label

Formula (measures from Elements)

NETREVENUE

Net Revenue

CMARGIN1

Contribution Margin 1 “NETREVENUE”

“GROSSREVENUE” “SALESDEDUCTION” “PRODVARIANCE”

CMARGIN2

Contribution Margin 2 “CMARGIN1” “OTHEREXPENSES”

• • • •

Select the Logical Join node. To create the NETREVENUE column, in the

Output area, rightclick the Calculated Columns folder and choose New Calculated Column. Enter the Name and Label as specified in the table above. Specify the remaining properties:, Data Type = Decimal , Length = 15, Scale = 2 , and Column Type = Measure.. Enter the specified formula, or create it graphically based on the available Elements and Operators.

Hint: If you enter the formula members manually, each member appears in bold when it has been validated against an available element.

• • •

Choose OK. Repeat the previous steps for the other calculated columns. In the Semantics node details, check the type of each column.


166


2015


HA100

Figure 148: Creating Calculated Columns

11. In the Semantics, change the order of the measures so that they logically represent the calculation order. That is, NETREVENUE after SALESDEDUCTION, and

so on. The columns should be order as follows: PERIOD SALESORGANIZATION

PLANACTUAL GROSSREVENUE SALESDEDUCTION NETREVENUE PRODVARIANCE CMARGIN1 OTHEREXPENSES


2015


167

Unit 4: Modeling

HA100

CMARGIN2

•

In the Semantics node details, select the NETREVENUE item and click the Move Up button to put the it just after the SALESDEDUCTION column.

•

Repeat this step for the other columns.

12. In the Semantics, hide the MANDT column from the PRODUCT_## attribute view.

• •

In the Semantics node details, display the Columns tab.

In the Shared area, check the Hide checkbox for the MANDT(PROD UCT_00_MANDT) column.

13. Save, validate, and activate the Analytic View.

• • • •

Rightclick the CEA1_## Analytic View and choose Validate. In the Job Log view, on the Current tab, check the status of the last Model validation job. Rightclick the CEA1_## Analytic View and choose Activate. In the Job Log view, on the Current tab, check the status of the last Activation job.

14. Preview the data of the

• • •

CEA1_## Analytic View.

In the Systems view, rightclick the CEA1_## Analytic View and choose Data Preview . Select the Raw Data tab. Optionally, create a pie chart with attribute LANDX in Label axis and measure NETREVENUE (sum) in Values axis: go to the Chart tab, drag the LANDX attribute to the Label axis and the NETREVENUE measure to the Value axis. Then choose the chart type.

Hint: To split a portion of a pie chart, you must rightclick it and choose Explode .


168


2015


HA100

Figure 149: Preview the Data

2015


169

Unit 4: Modeling

HA100

Exercise 5: Create the Analytic View for the Plan Use After completing this exercise, you will be able to: • • • • •

Use fact tables and Attribute Views in an Analytic View Join tables and views Filter the rows of a fact table Rename columns Create calculated columns

After creating the Analytic View for Actuals, you must create the Analytic View based on Planning data. The corresponding fact table is CE2IDEA . The structure of this Analytic View is very similar to the one for the Actuals, except a few slight differences.

Prerequisites Before you start, review the following diagram to identify which tables, views and columns you will use during this exercise, and how they must be joined.

Figure 150:

Procedure Continued on next page

170


2015


HA100

1.

In the STUDENT## package, create a new Analytic View with the following properties.

Hint: For the sake of time, we again make a copy from an existing data model. If you wish to create the planned analytic view from start to finish, follow basically the same steps, but using table CE2IDEA instead of CE1IDEA. The PLIKZ field might also not be available in

this analytic view.

• • •

Field

Value

Name

CEP1_##

Label

Contribution Margin for Plan ##

Copy From

STUDENT00.CEP1_00

In the Content node of the Systems view, rightclick the STUDENT## package and choose New → Analytic View . Enter the required data. Choose Finish.

Your Planned analytic view was now created for you. You still have to save and

activate your new data model. 2.

Save, validate, and activate the Analytic View. • • • •

3.

Rightclick the CEP1_## Analytic View and choose Validate . In the Job Log view, on the Current tab, check the status of the last Model validation job. Rightclick the CEP1_## Analytic View and choose Activate . In the Job Log view, on the Current tab, check the status of the last Activation job.

Preview the data of the CEP1_## Analytic View. • •

In the Systems view, rightclick the CEA1_## Analytic View and choose Data Preview . Select the Raw Data tab.


2015


171

Unit 4: Modeling

HA100

Figure 151: Preview the Data

Calculation Views

Figure 152: Calculation View

172


2015


HA100

Figure 153: Two Types of Calculation Views

Figure 154: Calculation View (Graphical)

2015


173

Unit 4: Modeling

HA100

Figure 155: Calculation View (Graphical) – View Creation Wizard

Since SAP HANA version 1.0 SPS07, there are three types of a graphical Calculation Views.

•

Dimension This type of view is used to build the equivalent of an Attribute View. No multidimensional reporting is enabled, and the last node is a Projection node.

•

Cube In the calculation view of type Cube , the final node is an Aggregation node. In this case, multidimensional reporting is possible.

•

Cube with Star Join This new type of Calculation View (from SAP HANA 1.0 SPS07 onwards) allows the modeler to join one or several calculated views of type Dimension in the very last stage of the Model calculation.

Note: With the introduction of the third type of Calculation View, Cube with Star Join , a slightly different modeling approach is possible, where multidimensional reporting would be based on complex transformation/calculations done only on the fact tables (and no the Analytic views), and the attributes would be joined at the very end of the calculation process, in a star join.

174


2015


HA100

Figure 156: Calculation View (Graphical) – Adding Nodes

This editor helps you to build view logic graphically using the tools available in the Designer view for join, union, projection and aggregation operations. You can perform these operations on data sources and/or on top of other operations. In the Details view, you define the output of the selected nodes. You use the Output view to

model various view elements: attributes, calculated attributes, measures, calculated measures, hierarchies, counters, variables, and input parameters. There are different five different types of nodes.

View Description

Description

Union

Used to combine the result set of two or more data sources.

For example, to know the names of all the employees of a store with different branches maintaining their own employee records table. Join

Used to query data from two or more data sources, based

on some condition. For example, to retrieve the sales of two stores maintaining individual tables for sales based on

the customer id.

2015


175

Unit 4: Modeling

HA100

Projection

Used to filter or create a subset with only the columns of a table or view that are required for your model. For example, selecting the employee name and sales quantity from a table that includes many more columns.

Aggregation

Used to summarize the data of a group of rows by calculating values in a column. For example, to retrieve total sales of some product in a month. The supported aggregation types are sum, min, and max.

Star Join

Used only in Calculation Views of type Cube with Star Join , and only as the last calculation step (just before the Semantics node).

Note: These node types can be used at any stage of the calculation view, except the Star Join node that is only available as the final node of a calculation view of type Cube with Star Join .

Figure 157: Calculation View (Graphical) – Output Node

176


2015


HA100


It is possible to create complex expressions to calculate columns. Example Expression: midstr(string(“ERDAT”),strlen(string(“ERDAT”))9,4


2015


177

Unit 4: Modeling

HA100


Figure 161: Calculation View (Scripting)

178


2015


HA100

Figure 162: Calculation Views – SQL Script Introduction

Most often, applications only offload very limited functionality into the database using SQL: most of the application logic is normally executed in an application server.

As a consequence, the data that you want to process needs to be copied from the database into the application server and vice versa. When executing data intensive logic, this data flow between the database server and the application server is very expensive in terms of processor usage, and extremely timeconsuming.

On the contrary, SQLScript offers another modeling approach, by pushing dataintensive application logic into the database. The SAP HANA database exposes a very sophisticated interface to the application consisting of many languages. The expressiveness of these languages far exceeds that attainable with OpenSQL.

Note: SQL Script is covered in more detail in course HA300.

2015


179

Unit 4: Modeling

HA100

Figure 163: Calculation View (Scripting) – Calculation View Wizard

180


2015


HA100

Figure 164: Calculation View (Scripting) – Calc View Table Type

Hint: In order to get the correct data types from existing database tables, you can do the following:

• •

2015

Rightclick the table in the Systems and choose Definition Rightclick into the editor that opens up (the tab displaying the list of table fields) and choose Export SQL


181

Unit 4: Modeling

HA100

Figure 165: Calculation View (Scripting) – Function Definition

Figure 166: Calculation View (Scripting) – Add the SQL Script Code

182


2015

HA100


Figure 167: Calculation View (Scripting) – Creating RunTime Objects

Figure 168: Calculation View (Scripting) – Calc View Output Structure

2015


183

Unit 4: Modeling

HA100

Figure 169: Calculation View (Scripting) – Save and Activate

Figure 170: Recommendations – How to Build Modeling Content

184


2015


HA100

Figure 171: How to Build Content

Figure 172: Calculation View & SQL Script – When to Use

2015


185

Unit 4: Modeling

HA100

Exercise 6: Create the Calculation View Use After completing this exercise, you will be able to: • • •

Create a Graphical Calculation View Add Analytic Views to a Calculation View Create Projections, Unions and Aggregations

Note: The SQLScriptbased Calculation View is covered in the HA300 course.

You are ready for the last step of your data modeling. You will use the Analytic views

for Actual and Planning, and group them into a single calculation view that will allow a direct data comparison. You will have to create a dedicated column in order to distinguish Actual and Plan data

Prerequisites Before you start, review the following diagram to identify the Analytic Views that you

will use and know what calculations you have to define on these views.

Figure 173: Design of the Calculation View


186


2015


HA100

Procedure 1.

In the STUDENT## package, create a new Calculation View with the following

properties: Field

Value

Name

CE_PLAN_ACTUAL_##

Label

Comparison for COPA ##

View Type

Calculation View

Subtype

Graphical

Data Category

Cube

With Star Join

[Deselected]

Note: The Calculation View must allow multidimensional analysis. That's why you must choose the Cube data category. • • •

In the Content node of the Systems view, rightclick the STUDENT## package and choose New → Calculation View . Enter the required data. Choose Finish.


2015


187

Unit 4: Modeling

HA100

Figure 174: New Calculation View

2.

Create two Projection nodes Projection_1 and Projection_2 based on (respectively) the CEA1_## and CEP1_## Analytic Views . All the columns of the source views must be included in the output.

• • • • • •

At the left of the Scenario pane, choose the Projection button.

Click in the empty area of the Scenario pane to create the Projection_1 node. Click the + sign on the right of the Projection_1 node. In the Search field , enter CEA . Select the CEA1_## (STUDENT##) view and choose OK. Select the Projection_1 node and, in the Details pane, rightclick the top bar of the STUDENT##.CEA1_## table and choose Add All To Output .

Note: Do not choose Add All to Output several times, because it would duplicate the columns in the output.

Note: You may have to remove the row.count field from the output list.

•

Repeat the previous steps for

Projection_2 and view CEP1_## . Continued on next page

188


2015


HA100

Figure 175: Build up Data Flow

3.

The Actuals/Plan data flag was not included in the COPA source data for the Plan. You must recreate it as a calculated column In Projection_2. This column must be defined as follows: Field

Value

Name

PLANACTUAL

Datatype

NVARCHAR

Length

1

Expression • • • • •

'1' (flag value for Plan data)

Select the Projection_2 node. In the Output pane, rightclick the Calculated Columns folder and choose New. Enter the required data. Choose Validate Syntax . Choose OK .


2015


189

Unit 4: Modeling

HA100

4.

Create a Union node Union_1 based on the two projections Projection_1 and Projection_2 . This node must also be defined as the source for the Aggregation node.

• • • • 5.

On the left of the Scenario pane, choose the Union button. Click in the empty area of the Scenario pane to create the Union_1 node.

Draw two connectors from each of the Projection nodes to the Union_1 node. Draw a connector from the Union_1 node to the Aggregation node.

To map the source columns of the

• •

Union_1 node, use the Auto Mapping feature.

Select the Union_1 node. Choose the Auto Map by Name button.

Figure 176: Union Node mapping

6.

In case the column names of the two projections are not identical, you will have

to map manually the unmapped columns. For example, if the columns names for Production Variance are not identical, you might have to do the following mapping:


190


2015


HA100

• •

• 7.

New Mapping

Projection_2.PRODUCTION

PRODVARIANCE (same target as

VARIANCE

Projection_1.PRODVARIANCE)

Draw a connector from the Projection_2.PRODUCTIONVARIANCE source

column to the PRODVARIANCE target column. To remove the PRODUCTIONVARIANCE target column, on the right of the Details pane, rightclick PRODUCTIONVARIANCE and choose Remove Target . Repeat these steps for any other nonidentical column names.

Add all the columns from the Aggregation node to the output. • •

8.

Source column

In the Scenario pane, select the Aggregation node. In the Details pane, rightclick the top bar of the Union_1 table and choose Add All To Output .

Assign the relevant Attribute and Measure type to all the columns in the semantics.

Hint: In the Semantics node, use the Auto Assign feature. • •

Select the Semantics node. In the Column pane, choose the Auto Assign button.


2015


191

Unit 4: Modeling

HA100

Figure 177: Output

9.

In the Semantics, modify the Label of the Columns and define Label Columns, as follows. The columns defined as Label Column must be hidden.

Column Name

Label

PERIOD

Period

SALESORG

Sales Organization

Label

Hidden

Column

GROSSREVENUE Gross Revenue SALESDEDUCTION Sales Deduction NETREVENUE

Net Revenue

PRODVARIANCE Production Variance CMARGIN1

Contribution Margin 1

OTHEREXPENSES Other Expenses CMARGIN2

Contribution Margin 2

MATNR

Product Number

MAKTX

Product Name

KUNNR

Client Number

MAKTX

x NAME1 Continued on next page

192


2015


HA100

LAND1

Country

ORT01

City

REGIO

Region

NAME1

Client Name

x

BEZEI

Region Name

LANDX

Country name

x x

LANDX BEZEI

Note: From SAP HANA 1.0 SPS09 onwards, it is possible to extract the semantics from a source information view.

• • •

Select the Columns tab of the Semantics node. Select any column (for example, PERIOD) and choose the Extract Semantics button (the leftmost button in the Local pane). In the Extract Semantics window, select all the columns.

Hint: To select all the columns at once, click the checkbox in the header row of the columns list. •

• •

For each column, define the Data Source (to extract the semantics from) as view STUDENT##.CEA1_## . So, the semantics for the Calculation View

will be extracted from the Analytic View for Actuals. Check that the checkbox Overwrite semantics already defined is selected. Choose OK .

Note: If you get an error message related to the last field of the list, select any other field from the list and choose

OK again.

10. Save, validate, and activate the Calculation View. • • • •

Rightclick the CE_PLAN_ACTUAL_## Calculation View and choose Validate. In the Job Log view, on the Current tab, check the status of the last Model validation job. Rightclick the CE_PLAN_ACTUAL_## Calculation View and choose Activate. In the Job Log view, on the Current tab, check the status of the last Activation job. Continued on next page

2015


193

Unit 4: Modeling

HA100

11. Preview the data of the Calculation View.

• •

In the Systems view, rightclick the CE_PLAN_ACTUAL_##

Calculation

View and choose Data Preview . Select the Raw Data tab.

An Illustration of SAP HANA Modeling Capabilities: SAP HANA Live As a conclusion for this introduction to levels of modelling, let's have a look at the SAP HANA Live offering, which is a set of SAPdelivered HANA content packages that connect directly to a Business Suite system (ERP, CRM, etc.) to neable immediate

reporting ou your SAP Business suite transactional data.

Explaining SAP HANA Live

Figure 178: The Rationale for SAP HANA Live

You are running your operational systems on HANA and enjoying extraordinary performance in the OLTP environment. But each day, you must extract your operational data and load this to SAP BW in order

to support the reporting requirements. This creates a delay between operations and analytics. One of the consequences of this delay is missed opportunities.

194


2015


HA100

You would like to remove this delay by allowing reporting directly on live operation tables.

The solution is to implement SAP HANA Live.

Figure 179: SAP HANA Live in a Nutshell

SAP HANA Live for SAP Business Suite comes with a comprehensive set of predefined models from across the SAP Business Suite. These models contain all the necessary joins and transformations to turn the data from

your Business Suite database tables into meaningful information.

2015


195

Unit 4: Modeling

HA100

Figure 180: SAP HANA Live – Comprehensive Suite coverage

The SAP HANA Live offering includes a number of packages covering a large number of SAP Business Suite applications and specific solution areas (FI, SD, MM, GRC, CRM, and so on).

You can choose which packages you will install from Service Marketplace. Here are a few examples of SAP HANA Live packages. Please have a look at the SAP Help Portal in order to find out the contents of each package. Goto http://help.sap.com/hba. • •

SAP HANA Live for SAP ERP 1.0 (SAP HANA ANALYTICS FOR ERP 1.0)

•

SAP HANA Live for Policy Management 1.0 (SAP HANA ANALY. FOR

SAP HANA Live for EHP 4 for SAP ERP 1.0 (SAP HANA ANALY. ERP EHP4 1.0) FSPM 1.0)

• • •

SAP HANA Live for SAP CRM 1.0 (SAP HANA ANALYTICS FOR CRM 1.0) SAP HANA Live for SAP SCM 1.0 (SAP HANA ANALYTICS FOR SCM 1.0) SAP HANA Live for SAP solutions for GRC 1.0 (SAP HANA ANALYTICS

FOR GRC 1.0) • •

196

SAP HANA Live for SAP PLM 1.0 (SAP HANA ANALYTICS FOR PLM 1.0) SAP HANA Live for Insurance 1.0 (SAP HANA ANALYTICS FOR INS 1.0)


2015


HA100

Figure 181: SAP HANA Live – Main Benefits

This slides lists the main benefits of SAP HANA Live.

The Virtual Data Model of SAP HANA Live

Figure 182: Detailed HANA Live architecture

This diagram shows a more detailed view of the architecture of SAP HANA Live.

SAP HANA Live relies on a Virtual Data Model (VDM).

2015


197

Unit 4: Modeling

HA100

The Virtual Data Model is a structured representation of HANA database views used in the SAP HANA Live for SAP Business Suite following consistent modelling rules. It provides direct access to SAP business data via standard SQL or OData requests.

Business data is exposed via welldefined database views, operating directly on the tables of the SAP Business Suite systems, and transforming them into consistent

and easily understandable views. These views can be consumed directly by rich client user interfaces, such as HTML5, SAP BusinessObjects, Excel, without any

additional software layer (for example, an ABAP application system) in between. (twotier architecture). The virtual data model consists of the following types of views:

•

Query Views Query views are designed for direct consumption by an analytical application (for example based on HTML5) or a generic analytical tool (for example SAP BusinessObjects tools).

They are always the top view in a hierarchy of views and are not designed for reuse in other views. However, the virtual data model also includes query views in order to support (showcase) applications based on the virtual data model.

•

Reuse Views Reuse views are the heart of the virtual data model. They expose the business data in a wellstructured, consistent, comprehensible way covering all relevant business data in SAP Business Suite systems.

They are designed for reuse by other views and must not be consumed directly by analytic tools. •

Private Views Private views encapsulate certain SQL transformations on one or several database tables or even other views. They are not classified as reuse views as they might not carry clear business semantics but are rather intended to be reused in other views. They are comparable to subroutines or (private) methods in programming languages. A private view may be based on database tables, other private views or on reuse views.

198


2015


HA100

Figure 183: Virtual Data Model – How it is Designed

The design of the Virtual Data Model has the following properties:

• • • • •

It relies entirely on SAP HANA Modeling techniques It is built exclusively with calculation views It uses joins to combine reuse views to a consumption query view It adds calculated attributes along the way, as well as input parameters and variables when needed Most of the calculation views are of subtype Graphical .

This diagram also gives an example of a view from the SAP HANA Live Virtual Data Model.

2015


199

Unit 4: Modeling

HA100

Lesson Summary You should now be able to: Describe the levels of modeling in SAP HANA and understand how the different • type of models support each other • Create an Attribute View • Create an Analytic View • Create a Calculation View • Display the Data from Information Views • Introduce SAP HANA Live and its Virtual Data Model

200


2015

Unit Summary

HA100

Unit Summary You should now be able to: • Explain the COPA HANA case study • Understand the features of SAP HANA Studio Modeler perspective Describe the levels of modeling in SAP HANA and understand how the different • type of models support each other • Create an Attribute View • Create an Analytic View • Create a Calculation View • Display the Data from Information Views • Introduce SAP HANA Live and its Virtual Data Model

2015


201

Unit Summary

202

HA100


2015

Unit Summary

203

HA100


2015

Unit Summary

204

HA100


2015

Unit 5 Data Provisioning Unit Overview There are several ways of loading data into SAP HANA. This unit describes the different methods.


Present the main features of the SAP Data Services ETL solution for SAP HANA Describe the process of loading data from ECC to SAP HANA using SAP Data

•

Services Understand the architectural foundation of SAP Landscape Transformation Replication Server and its technical prerequisites

•

Configure SAP Landscape Transformation Replication Server for connectivity to

• • • • • • • • • •

2015

Understand the capabilities of the flat file data load functionality Load data from Flat Files into the SAP HANA Database

the source SAP ERP system and the target SAP HANA Database Configure data provisioning in SAP HANA Studio and trigger an initial load and/or replication Understand how you can load data into SAP HANA from an SAP Business Suite source system. Explain the key benefits of SAP Replication Server List the supported primary and replicate RDBMS

Describe the architecture of SAP Replication Server Understand how to connect SAP Replication Server with an SAP HANA system Understand the Smart Data Access concepts Know when to use Smart Data Acces Understand SAP HANA smart data integration (SDI) Understand SAP HANA smart data quality (SDQ)


205

Unit 5: Data Provisioning

• • •

HA100

Understand SAP HANA smart data streaming Know where to use Smart Data Streaming Understand the value of Smart Data Streaming in certain use cases

Unit Contents Lesson: Uploading Data from Flat Files ......................................... 207 Procedure: Exercise 7: Flat File Upload .................................... 217 Lesson: SAP BusinessObjects Data Services .................................. 222 Lesson: SAP Landscape Transformation Replication Server ................. 233 Lesson: SAP Direct Extractor Connection....................................... 240 Lesson: SAP Replication Server.................................................. 245 Lesson: Smart Data Access ....................................................... 251 Lesson: Smart Data Integration and Smart Data Quality ...................... 264 Lesson: Smart Data Streaming ................................................... 272

206


2015

Lesson: Uploading Data from Flat Files

HA100

Lesson: Uploading Data from Flat Files Lesson Overview One of the most simple options for data provisioning is to upload data from flat files. This lesson will you the steps required to upload your own data from a csv file.

Lesson Objectives After completing this lesson, you will be able to: • •

Understand the capabilities of the flat file data load functionality Load data from Flat Files into the SAP HANA Database

Business Example You want to upload Sales Organization texts from a flat file to an SAP HANA table, so

you can use the data in modeling afterwards.

Overview of Data Provisioning for SAP HANA

Figure 184: Positioning of Data Provisioning

2015


207


HA100

Basically, data provisioning in SAP HANA is the process of loading, either once, on

a regular basis, or in realtime, data from an external source into the SAP HANA database.

Figure 185: Data Provisioning Methods Overview

There are different methods available to support organizations to provision data into a

SAP HANA database. The need for them arises when data is generated and managed within other databases, but data needs to be available in SAP HANA in order to leverage its modeling and processing features.

208


2015


HA100

Basically there are two ways to go forward: •

Replication is about transferring contents of database tables 1:1 from source to target. If a record is changed or posted into the source table, the same change will be triggered towards the target table. Transformations between source and target are possible to a light extent only. The target structure and its content is normally the same as the source (maybe with small changes only). The emphasis

is however on the speed to make data available in SAP HANA. Realtime replication can be setup based on SAP Landscape Transformation (SLT) as well

as SAP Replication Server (based on SAP Sybase technology). •

Extraction , on the other hand, covers the ExtractionTransformationLoading (ETL) process. The focus is less on speed, as these ETLprocesses normally are scheduled in hourly, daily or weekly schedules only. ETL tools like SAP BusinessObjects DataServices can connect to a vast amount to source systems with structured as well as unstructured data. These data can be extracted and transformed in various forms in order to cleanse data, improve its

quality and prepare it for posting it into the target system. Normally the target structure and its content is a result of complex transformations based on more

than one source table. For test purposes or adhoc scenarios, you also have the option to upload flat files into SAP HANA. This is a special case which cannot be really positioned in neither 1. nor 2.

Figure 186: Comparision of the Different Data Provisioning Methods

2015


209


HA100

Uploading Data from Flat Files To upload data from flat files, SAP HANA offers several features. In particular, in can create the metadata (tables and columns definition) based on the flat file structure, and you can allso insert the records in the target SAP HANA table at the same time.

• • • • • •

If the target table for loaded data does not exist in SAP HANA database, it’s necessary to create a table structure based on the flat file The application suggests the column name and data type for the new tables, and it is 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 for existing columns The supported file types for upload are: .csv, .xls, and .xlsx

The steps to import a file file into SAP HANA are as follows:

Figure 187: Process Flow: Uploading Data from Flat Files

1. Select the source type for the import

210


2015


HA100

Figure 188: Select Import Source

2. Select the target schema in which the target table must be created (or already exists)

Figure 189: Select Target System

2015


211


HA100

3. Select the file that contains the data

Figure 190: Select File for Upload

In the File Import Wizard , 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

Hint: In csv files, the delimiter is used to separate the data of each column. The supported delimiters are: , (coma) ; (semicolon) and : (colon)

4. Select the target table

212


2015


HA100

Figure 191: Select Target Table

For the Target Table, two options are available:

•

New A new table with the name you enter will be generated within the schema you choose.

•

Existing

Data will be appended to an existing table. 5. Map file structure and table definition

2015


213


HA100

Figure 192: Manage Table Definition and Data Mapping (1)

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. Depending on the source data structure and the target model requirement, you can, or must, 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 and column data types, renaming, adding or deleting columns. 6. Preview data and finalize data import It is possible to preview the actual data from the source file and check if they are correctly mapped to the columns of the target table.

214


2015


HA100

Figure 193: Manage Table Definition and Data Mapping (2)

To import the data, choose Finish. You can then check if the target table was correctly created (if it is a new table) and populated with the source data.

Figure 194: Check Target Table – Definition

2015


215


HA100

Figure 195: Check Target Table – Data Preview

216


2015


HA100

Exercise 7: Flat File Upload Use This Exercise gives you a handon example how easy it is to upload data from flat files (CSV, XLS, XLSX) into the SAP HANA database. You can create your own flat file or use a template file, shared folder.

VKORG_TEXTS.csv , in a

Procedure 1.

Create a flat file to be uploaded • •

From the Windows task bar, choose

Start → Documents .

Doubleclick folder HANA Student . If needed, enter the credentials provided by your instructor.

• •

Select the file VKORG_TEXTS.csv Copy this file to your desktop.

Figure 196: Create a template flat file

2.

In the SAP HANA Studio Modeler Perspective, select the source file and target

schema/table • •

Choose File → Import → SAP HANA Content → Data From Local File Choose Next Continued on next page

2015


217


HA100

Figure 197: Import flat file to SAP HANA Studio step 1

• •

If needed, choose the target system and/or logged on user, and click

Next

In the Source file field, select your flat file VKORG_TEXTS.csv from the Desktop

• • • •

As the Field Delimiter, choose Comma Select the Header row exist checkbox. For the Target Table, create a new one in your own

STUDENT## schema.

Table name = HA100_FF_IMPORT . Click Next


218


2015


HA100


3.

Define the Table Definition and Data Mappings • • •

Make sure that the Column store store type is select Click Finish. In the Job Log view, check whether the import has been completed successfully.


4.

Find the new imported table and validate its content •

In the Systems view , select your HANA system and expand the Catalog → STUDENT## → Tables folder.

•

Find your table HA100_FF_IMPORT and validate the definition and the content of this table. Continued on next page

2015


219


HA100


220


2015


HA100

Lesson Summary You should now be able to: • Understand the capabilities of the flat file data load functionality • Load data from Flat Files into the SAP HANA Database

2015


221


HA100

Lesson: SAP BusinessObjects Data Services Lesson Overview SAP Data Services 4 enables you to integrate disparate data sources to deliver more timely and accurate data that end users in an organization can trust. This lesson will look at the fundamentals of how to provision data from SAP Data Services into SAP HANA.


Present the main features of the SAP Data Services ETL solution for SAP HANA Describe the process of loading data from ECC to SAP HANA using SAP Data

Services

Business Example You are working in an organization where data is stored in various disparate databases

like Oracle, DB2 and other legacy systems. You have been asked to recommend the best application for consolidating and replicating data into SAP HANA from both SAP and nonSAP sources, using an ETL solution. Therefore, you need to understand the benefits of using SAP Data Services

over other methods.

222


2015

HA100

Lesson: SAP BusinessObjects Data Services

SAP Data Services Introduction

Figure 201: SAP BO DataServices Data Extraction

Figure 202: Data Management Challenges

2015


223


HA100

This is a summary of the main challenges that many organizations faced. If they are not addressed properly, these challenges can limit your ability to know your business.

•

Data is siloed With data scattered across your organization in different ERP systems, databases, or homegrown systems, you may find different versions of the truth, limiting

your ability to gain a complete view of the business. •

Data is inaccurate Have you ever received a promotion mail in your mailbox? Consider the wasted marketing costs (in printed material and postage) for a company that leverages customer promotions as a source of revenue. Data is inherently inaccurate because things change and your business requirements continue to evolve. As a result, you need ongoing maintenance of your data quality by both the business and IT stakeholders. Common issues

like incorrect customer names, addresses, and product names only add to the challenge that organizations have to face before they can leverage their corporate data as an enterprise asset. •

Data is inconsistent Definitions of common business entities like customers, products, supplier,

material names and codes vary from system to system, which creates inconsistencies that data access alone cannot address. You need a better way to reconcile this. •

Data is incomplete A customer record may be missing a postal code or country code, and would be unusable unless it is appended with the correct data.

•

Data is inaccessible Connectivity will allow you to get access to the data in your enterprise systems.

But this solves only part of the problem, because making inaccurate data accessible does not necessarily help you to leverage this information for a business goal. Sometimes, the data is in an unstructured format, such as a free form text coming

from a CRM call log.

224


2015


HA100

Figure 203: The Data Management Nightmare

Another big issue faced by organizations is the explosion of the number of tools they

use to manage data movement and data quality. In the above figure, you see a typical IT environment where multiple information management tools from multiple vendors increase the difficulty to manage information

consistently and effectively across the enterprise. Each tool has its own metadata repository, development environment, administrative environment, runtime architecture requirements that you need to support and maintain.

Which costs a lot of time and money. Many of these tools are commonly used in an endtoend solution supporting the requirements from data extraction, transformation, cleansing, matching, and metadata management. For example, data integration and data quality have a deep symbiosis that needs coordination.

2015


225


HA100

Figure 204: SAP Data Services Your OneStop Solution

SAP Data Services is the one and only allinone solution for data integration (ETL),

data quality management, information stewardship (data profiling and metadata management), and text analytics. With Data Services, you have a onestop shop solution for 5 core critical information

management capabilities: • • • • •

226

Extract, Transport and Load (ETL)

Data Quality Profiling

Metadata Management Text Analytics


2015


HA100

SAP Data Services helps you to: • •

•

•

Move and integrate enterprise data to and from almost any data source and target

with marketleading ETL capabilities. Improve data from any data domain, such as customer, product, supplier, material, with marketleading data quality management to cleanse, enrich, match, and consolidate data. Govern data with a new information stewardship solution providing a business user interface for understanding and measuring the quality of data using integrated data profiling, data quality scoring, and metadata management capabilities. Unlock insights from structured and unstructured sources including text data sources using text analytics.

Technically, SAP Data Services is underpinned by a development user interface, a metadata repository, a data connectivity layer, a runtime environment, and a management console, so IT groups can lower total cost of ownership (TCO) and accelerate time to value by using one, integrated, allencompassing tool for all of the above tasks.

SAP Data Services and SAP applications Data Services provides several methods for moving data into and out of SAP applications.

2015


227


HA100

Figure 205: Data Flows

•

Data exchange via RFC Reads data from SAP applications using regular data flows and supports tables

(for small data sets only) and extractors. •

ABAP programming language Reads data from SAP applications using ABAP data flows and supports tables,

hierarchies, extractors, and functions with scalar arguments. •

RFC/BAPI

Loads data to SAP applications and allows direct access to application tables outside of the data flows through RFC (Remote Function Calls).

•

IDoc interface Reads from and loads data to SAP applications. Data Services can send, receive, and create SAP IDocs including extended and custom IDocs and supports reduced input sets, parallel processing, realtime, and batch processing.

228


2015


HA100

Using SAP Data Services to connect SAP ECC and SAP HANA

Figure 206: Process Flow: Data Services

Figure 207: Import the Created Table Structure into Data Services

2015


229


HA100

Figure 208: Create and Execute a Data Services Job to Populate HANA

Figure 209: Execute the Job to Populate the HANA Target Table and Monitor the Load

230


2015

HA100


Figure 210: View the Data Uploaded by Data Services

2015


231


HA100

Lesson Summary You should now be able to: Present the main features of the SAP Data Services ETL solution for SAP HANA • Describe the process of loading data from ECC to SAP HANA using SAP Data • Services

232


2015

Lesson: SAP Landscape Transformation Replication Server

HA100

Lesson: SAP Landscape Transformation Replication Server Lesson Overview This lesson gives an overview to SAP Landscape Transformation Replication Server

in connection with SAP HANA.


Understand the architectural foundation of SAP Landscape Transformation Replication Server and its technical prerequisites

•

Configure SAP Landscape Transformation Replication Server for connectivity to

•

the source SAP ERP system and the target SAP HANA Database Configure data provisioning in SAP HANA Studio and trigger an initial load and/or replication

Business Example You are a business in a market with fast changes. Your SAP HANA Information Models need to use the most uptodate data in order to keep up with market changes.

You have decided to deploy SAP Landscape Transformation Replication Server to achieve this.

2015


233


HA100

Introduction to SAP Landscape Transformation Replication Server

Figure 211: SAP Landscape Transformation Replication Server

SAP Landscape Transformation Replication Server (also known as

SAP LT, or SLT) is for all SAP HANA customers who need realtime or scheduled data replication, from both SAP and nonSAP sources. It also provides the ability to perform complex

data transformations on the fly. SAP LT can perform the following types of data copy:

•

Load A onetime event data copy from the source system to the target system is performed

•

Replication SAP LT performs an initial load of all the data, and sets up a realtime replication

of data from the source to the target system. This replication is triggerbased, meaning that a DB trigger is defined in the source database on each table marked for replication. Each time a data modification is done to a source table, the trigger captures this event and SAP LT transports this change to the target database.

234


2015

HA100


Figure 212: SAP Landscape Transformation Replication Server for SAP HANA

Figure 213: SAP LT Data Transformation Use Cases

2015


235


HA100

SAP Landscape Transformation Replication Server

Architecture

Figure 214: Replication from SAP Sources

Figure 215: SAP LT Replication from nonSAP Sources

In case the source is not an SAP system, SAP LT Replication Server transfers in a first

step all the table definitions from the nonSAP source system to the HANA system (tables DD02L and DD02T). From the HANA perspective, everything looks like the source was an SAP source.

236


2015

HA100


When a table replication is started, SAP LT Replication Server creates logging tables

within the source system. Only the read modules are created in the SAP Landscape Transformation Replication Server. This contrasts with the SAPsource scenario, where the read modules are located in the source system. Another difference is that the connection from SAP LT Replication Server to a nonSAP source system is established by means of a database connection, not an RFC (Remote Function Call).

Configuration and Basic Utilization

Figure 216: Configuration and Monitoring Dashboard

With the Configuration and Monitoring Dashboard, the SAP LT replication server

provides the user with different status information (triggers active/inactive, job monitor, status load and replication with error alert, system connection) and statistical

information (for example lowest/highest/average speed rate of a replication).

2015


237


HA100

Figure 217: Setup of Data Replication in SAP HANA System

238


2015


HA100

Lesson Summary You should now be able to: • Understand the architectural foundation of SAP Landscape Transformation Replication Server and its technical prerequisites Configure SAP Landscape Transformation Replication Server for connectivity to • the source SAP ERP system and the target SAP HANA Database • Configure data provisioning in SAP HANA Studio and trigger an initial load and/or replication

2015


239


HA100

Lesson: SAP Direct Extractor Connection Lesson Overview This lesson gives a highlevel overview of SAP Direct Extractor Connection (DXC). This is a batchdriven data acquisition technique that relies on BW Extractors that are

installed in any SAP Business Suite system and allows to extract semantically rich content from the source system.


Understand how you can load data into SAP HANA from an SAP Business Suite source system.

Business Example You work for ABC organization, where BW is heavily utilized. You want to load BW

data into SAP HANA by reusing exisiting extractors which have already have the business logic applied.

SAP Direct Extractor Connection Overview SAP HANA Direct Extractor Connection (DXC) is a means for providing outofthebox foundational data models to SAP HANA, which are based on SAP Business Suite entities.

DXC is also a data acquisition method. The rationale for DXC is simple: low TCO data acquisition method for SAP HANA, leveraging existing delivered data models. Customer projects may face significant complexity in modeling entities in SAP Business Suite systems. In many cases, data from different areas in SAP Business Suite systems requires application logic to appropriately represent business documents.

SAP Business Content DataSource Extractors have been available for many years as a basis for data modeling and data acquisition for SAP Business Warehouse. They are used to load data in the “embeded” SAP Business Warehouse (BW) system that comes with any Business Suite system based on SAP NetWeaver 7.0 or higher. With DXC, these SAP Business Content DataSource Extractors are available to deliver data

directly to SAP HANA.

240


2015

Lesson: SAP Direct Extractor Connection

HA100

DXC is a batchdriven data acquisition technique; it can be considered as a form of extraction, transformation and load, where the transformation is done by the source system (SAP Business Suite) by ABAP extractors. The main benefits of DXC are as follows:

•

Leverages preexisting foundational data models of SAP Business Suite entities

for use in SAP HANA data mart scenarios: – Significantly reduces complexity of data modeling tasks in SAP HANA – Speeds up timelines for SAP HANA implementation projects •

Provides semantically rich data from SAP Business Suite

•

– Ensures that data appropriately represents the state of business documents from ERP – Application logic to give the data the appropriate contextual meaning is already built into many extractors Simplicity and Low TCO: – Reuses existing proprietary ETL mechanism built into SAP Business Suite systems, over a simple HTTP(s) connection to SAP HANA

•

– No additional server or application is needed in system landscape Change data capture (delta handling) – Efficient data acquisition: DXC only brings new or changed data into SAP HANA – DXC provides a mechanism to properly handle data from all delta processing types

Note: DXC does not require BW on SAP HANA.

Direct Extractor Connection Architecture

Figure 218: SAP DXC Technical Foundations

2015


241


HA100

An SAP Business Suite system is based on SAP NetWeaver. As of SAP NetWeaver version 7.0, SAP Business Warehouse (BW) is part of SAP NetWeaver itself, which means a BW system exists inside any SAP Business Suite system, such as the SAP

ERP (ECC 6.0 or higher). This BW system is referred to as an “embedded BW system”. Typically, this embedded BW system inside SAP Business Suite systems is actually not utilized, since most customers who run BW have it installed on a separate server, and they rely on that one. With the default DXC configuration, you use the scheduling and monitoring features of this embedded BW system, but not its other features such as storing data, data warehousing, or reporting and BI.

DXC extraction processing essentially bypasses the normal data flow, and instead sends data to SAP HANA. The following illustration depicts the default configuration of DXC.

Figure 219: SAP DXC with SAP HANA Architecture

An InMemory DataStore Object (IMDSO) is generated in SAP HANA, which directly corresponds to the structure of the Data Source you are working with. This IMDSO consists of several tables and an activation mechanism. The active data table of the IMDSO can be utilized as a basis for building data models in SAP HANA (attribute views, analytical views, and calculation views).

242


2015

HA100

Lesson: SAP Direct Extractor Connection

Data is transferred from the source SAP Business Suite system using an HTTP connection. Generally, the extraction and load process is virtually the same as when extracting and loading data into SAP Business Warehouse: you rely on InfoPackage scheduling, the data load monitor, process chains, and so on, which are all well known from operating SAP Business Warehouse.

Note: In the data load monitor of the embedded BW, the data load into the activation queue in the DSO in HANA appears as if data were loaded into the Persistent Staging Area (PSA) of the embedded BW. But actually it is not: Data is directly pushed further into the predefined schema of SAP HANA.

For more technical information about SAP Direct Extractor Connection, you can refer to the general SAP Note 1583403 Direct extractor connection to SAP HANA , available on the SAP Service Marketplace athttp://service.sap.com/notes/1583403.

2015


243


HA100

Lesson Summary You should now be able to: • Understand how you can load data into SAP HANA from an SAP Business Suite source system.

244


2015

Lesson: SAP Replication Server

HA100

Lesson: SAP Replication Server Lesson Overview This lesson gives an overview of SAP Replication Server, a logbased database replication tool, and explain how it can be used to provision data into an SAP HANA database.

Lesson Objectives After completing this lesson, you will be able to: • • • •

Explain the key benefits of SAP Replication Server List the supported primary and replicate RDBMS

Describe the architecture of SAP Replication Server Understand how to connect SAP Replication Server with an SAP HANA system

Business Example In the context of an SAP HANA implementation, you have decided to implement SAP Replication Server to serve as your data provisioning tool. You want to know more about this technology, and also discuss its future development within the SAP

product offering.

SAP Sybase Replication Server Introduction Replication Server is a sophisticated transactional data movement product that moves and synchronizes data across the enterprise, without geographical distance limitation, to meet demanding requirements in the enterprise such as guaranteed data delivery, realtime business intelligence and zero operational downtime. Replication Server facilitates this by nonintrusively handling data at the source and target, including Sybase, Oracle, Microsoft, and IBM, while ensuring high performance and transactional integrity.

2015


245


HA100

Figure 220: SAP Replication Server – Main Objectives

One key feature of SAP Replication Server is that it relies on a logbased replication technique: the Changed Data Capture (CDC) is not done against the data volumes of the source database, but instead by reading directly the database log. This logbased approach reduces the workload that the replication process brings to the source database, thus enhancing the availability of this system.

246


2015


HA100

Figure 221: SAP Replication Server – Example of Scenarios

Note: SAP Replication Server supports a lot of scenarios, such as

• • •

Bidirectional replication Support for Disaster Recovery OnetoMany / ManytoOne replication

However, in scenarios including SAP HANA, only the replication database can run on SAP HANA, not the source database.

2015


247


HA100

Supported Databases and Server Architecture

Figure 222: SAP Replication Server – Supported Databases

In the SAP Replication Server terminology, the source system is called Primary Database , and the target system Replicate Database . The above figure lists the types of databases that are supported by SAP Replication

Server, either as the Primary or the Replicate Database. You will notice the wide range of supported source database, meaning that SAP Replication Server is able to read and interpret the log's content of many database vendors.

Note: Depending on the Primary and Replicate database, some RDBMS

features (data types, and so on) may not be supported. Additionally, not all the configurations allow the replication of Data Definition Language (DDL) statements. We recommend that you consult the SAP Replication Server documentation available on the SAP Help Portal, athttp://help.sap.com/replicationserver .

248


2015

HA100


SAP Replication Server with SAP HANA

Figure 223: SAP Replication Server – SAP HANA as the Replicate Database

SAP HANA is only supported as the replicate database, and from SAP Replication Server version 15.7 SP100 onwards. Additionally, the replication of Data Definition Language (DDL) statements is not currently supported.

Summary of SAP HANA Provisioning methods

Figure 224: Summary of SAP HANA Data Provisioning

2015


249


HA100

Lesson Summary You should now be able to: • Explain the key benefits of SAP Replication Server • List the supported primary and replicate RDBMS • Describe the architecture of SAP Replication Server • Understand how to connect SAP Replication Server with an SAP HANA system

250


2015

Lesson: Smart Data Access

HA100

Lesson: Smart Data Access Lesson Overview On this lesson you will be introduced to the Smart Data Access functionality in SAP HANA. This is an important building block for understanding how SAP gets their various database products to work together, and how their Big Data solutions work


Understand the Smart Data Access concepts Know when to use Smart Data Acces

Business Example A retail company does not want to load all their retail data into SAP HANA as most of their reports are only using the last 6 weeks of data. But for some reports they would like e.g. to compare this year's sales to last year's sales. So they do not want to archive the data, but want it available in their reports. In the case of reports on older data they do not expect the normal fast HANA reporting times. You can store the older data in a database like SAP IQ, and transparently to the user fetch it from there in case a report

requires this older data. For all reports on newer data, the SAP HANA system still delivers fast reporting times. Smart Data Access provides such capabilities. And it is standard functionality built into SAP HANA.

Introduction to Smart Data Access Smarter Data Virtualization SAP HANA smart data access brings inmemory computing to data virtualization providing a nondisruptive intelligent architecture to provision enterprisewide data from heterogeneous systems such as Hadoop, Teradata, other SAP HANA systems,

SAP ASE and SAP IQ. Experience data like never before. In an “alwayson” and rapidly changing world Big Data is enabling new customer interactions and insights, new experiences, and new business processes. Businesses are rethinking how they work and imagining what was previously impossible. SAP understands customers current business practices and has the expertise and technologies such as inmemory and cloud to exploit Big Data.

2015


251


HA100

Think about an orchestra playing. Harmony and coordinated cooperation is needed to succeed. Each player has a piece and contributes to the whole. SAP HANA is the conductor and soloist. Here we look at how SAP HANA integrates with Hadoop and other databases providing smart data access and discusses how organizations can become more competitive by leverage insight from all their data to support realtime business.

Figure 225: A Comprehensive “EndtoEnd” Approach is Needed

You must INGEST, STORE, PROCESS, and PRESENT all the data to get a complete

and actionable picture. If you were to do a big data project on your own today you would have to do many things to put together an end to end solution. SAP solutions help you in all areas from cloud and mobile to SAP HANA that can help you achieve what you need, as well as synthesize data from other data sources like Hadoop. In this way you can optimizes storage and where the data is analyzed to best fit your needs.

Today you need to Transact, Analyze and Act simultaneously. Traditional approaches are dead! Today’s world is very different to one that existed when the traditional relational database was first architected. Back then the database dealt with recording and storing transactional data only. What reporting happened was only for decision support. It was high value, highly structured data – not the mounds of data that may or may not have value that we face today. And it was in an era when time was measured by the calendar, not the stopwatch. Today is a completely different world. We generate vast amounts of nontransactional data – whether documents, Facebook

252


2015


HA100

posts, tweets, or log information coming off our phones, web servers, and other connected devices. We no longer want to just report against operational activities, but we also want to analyze, explore, predict, visualize and inspect in ways never imagined in those early days.

Figure 226: The Value Proposition for SAP HANA smart data access

The Value Proposition for SAP HANA smart data access includes the following:

• • • • •

2015

Easily utilize enterprise wide data leveraging HANA capabilities High Performance Secure access to remote data Leverage Big Data processing Seamless archived data access


253


HA100

Figure 227: SAP HANA smart data access : The “glue” of the platform

SAP HANA smart data access starts becoming the “glue” of the platform. •

Provide a data platform for application developers to write new data intensive analytical applications, without regard to where the data resides, its type, quality,

• • •

254

or size Platform will provide the capability to extract the required information (not data), from a variety of data sources Platform will leverage data processing engines (databases, Hadoop, etc) to synthesize information needed for analysis This will enable the rapid development of these applications, with the highest degree of performance


2015


HA100

Some of the benefits of Smart Data Access are: • • • • • • • • • • • • •

Enables access to remote data access just like “local” table No special syntax to access heterogeneous data sources Smart query processing pushing as much processing as possible to target data

source Smart query processing including query decomposition with predicate pushdown, functional compensation Functional compensation allows customers to use the full power of the HANA Automatic data type translation enables remote data types to be mapped to HANA data types Supports data location agnostic development No special syntax to access heterogeneous data sources Provides SAP HANA to SAP HANA queries Nondisruptive evolution Support of Insert, Update and Delete (except Hadoop) Calculation View Support for Virtual Tables

Deliver Generic Adapter framework to extend additional Remote Sources

Figure 228: SAP HANA smart data access Increase the value of your data

2015


255


HA100

SAP HANA smart data access increases the value of your data. You can create a fast & flexible data warehouse without expensive ETL, massive storage, security and privacy risks. Gartner calls this a “logical data warehouse”, Forrester “data fabric” and IDC “data warehouse without ETL”. You can build big data applications with fast and secure query access to data while minimizing unnecessary data transfers and data redundancy. You can bring social media data and critical enterprise information together, giving comprehensive visibility into customer behavior and sentiment Features: • • •

Inmemory based virtualization technology Data virtualization for heterogeneous data sources Simplified data queries ensuring optimal response time

Benefits:

• • •

Nondisruptive smarter data intelligent architecture High performance, Realtime, enterprisewide data access Realtime insights across heterogeneous data sources

Figure 229: SAP HANA smart data access capability

Smarter Data Virtualization

256


2015


HA100

SAP HANA smart data access provides nondisruptive intelligent architecture to provision enterprisewide data from heterogeneous systems such as Hadoop, Teradata, SAP Sybase ASE and SAP Sybase IQ. As well it provides the capability for

SAP HANA to SAP HANA queries which can facilitate SAP NetWeaver Business Warehouse on SAP HANA to SAP Business Suite on SAP HANA query use cases among others. SAP brings inmemory computing to data federation and it will optimize where data is stored and acted upon. Now customers can easily build unified transactionalanalytical applications with secure access to data across their business networks without large data transfers. Inmemory computing lets then get results from the core process in realtime, providing a single version of truth on which business

can act. Heterogeneous data sources that you can acces are: • • • • • • • • • •

2015

SAP HANA to Hadoop (Hive) SAP HANA to Teradata SAP HANA to SAP HANA SAP HANA to SAP ASE SAP HANA to SAP IQ Microsoft SQL Server (from SPS07)

Oracle (from SPS07) IBM DB2 (from SPS08)

IBM Netezza (from SPS08) SAP MaxDB (from SPS09)


257


HA100

Figure 230: Used from the SAP HANA studio

You can use the SAP HANA studio when working with Smart Data Access. • • • • • •

258

Enables users to develop applications on SAP HANA Create remote sources, create virtual tables, set security policies Access remote sources and build virtual tables, using remote table schema and data types Test virtual tables Execute queries and analyze query plan Monitor queries


2015


HA100

Figure 231: Use cases for SAP HANA smart data access

SAP HANA smart data access can be used in various different ways. From integration with Hadoop to using SAP NetWeaver Business Warehouse data to data aging using

SAP IQ. Let us consider a business case. Problem Statement Customer is using HANA for real time offer management for their online store. One of elements of data needed for this analysis is average daily sales by product, which is kept in a Teradata point of sales system. As the point of sales data is massive, and

only summarized aggregates by product are needed for the analysis, options relating to how this can be loaded into HANA are being considered. Options Considered • • •

Option 1: Load the POS data into HANA and do the computation Option 2: Do the calculations in Teradata and load the results into HANA Option 3: Create virtual tables in HANA, which refer to the Teradata tables, and then use the virtual tables to compute summary statistics needed

Technical Architecture The customer elects to go with option 3, as the summary statistics can be computed in

real time, on demand and only the required data needs to be brought into HANA. To support the analysis, customer uses HANA smart data access to create virtual tables in

HANA which reference tables in Teradata. SQL statements are written for computing the summary statistics, based on the virtual tables in HANA, and this data is combined

with the other information needed for creating the offers.

2015


259


HA100

Benefits • • •

Quick application development, as not all data needs to be loaded into HANA, before the project can start Summary statistics based on POS data can be computed on demand, hence they are more current Cost effective, as ETL and other processing costs are saved

Figure 232: Use SAP IQ as a Nearline store for SAP HANA

Another scenario is using SAP HANA smart data access together with SAP IQ in a Nearline storage example. SAP IQ can be used as a cost effective archive for warm data with SAP HANA. SAP HANA contains the critical hot data and SAP IQ retains and process the archived warm data that is seamless accessible from SAP HANA.

Let us consider another business case. Problem Statement Customer has created a new point of sale system using HANA, and while they need 3 months of data to be online and in HANA, any data more than 3 months old is typically needed for reference purposes only.

260


2015


HA100

Options Considered •

Option 1: Keep data older than 3 months in a separate system. Any time information older than 3 months is needed, customer needs to log into a different

•

system to access this data . Option 2: Archive data more than 3 months old in SAP Sybase IQ. Create virtual tables in HANA, which refer to the data in IQ, and any time access to the

IQ data is needed, it can be done via HANA. Additionally, archived data (older than 3 months) can be combined with current data in HANA in the same query Technical Architecture The customer elects to go with option 2, as they can keep archived data in IQ, while accessing it seamlessly from HANA. Using SAP Data Services, data older than 3 months is selected and moved into IQ, and deleted in HANA. Virtual tables are created in HANA which reference tables in IQ. Any time access to archived data is needed, it can be queried from HANA, via the virtual tables Benefits • • •

Storage tiering can be done, with hot data in HANA, and warm in IQ Seamless access to archived data can be provided in HANA Customers can write applications in HANA which combine real time data with archived data as part of their query

Figure 233: SAP HANA Big Data Architecture

2015


261


HA100

The SAP HANA Big Data Architecture gives us a glimpse of what is possible with these exciting new technologies. We now have a comprehensive platform for Big Data and Analytics • • • • • • • •

Frequency accessed data inmemory in HANA Dramatic reduction in time to Analysis Access Big Data in Hadoop directly from SAP HANA Leverage Hadoop from SAP IQ using SDA and UDF’s Leverage SAP IQ from SAP HANA and SAP HANA from SAP IQ Store historical data in SAP IQ and Hadoop Leverage Column Stores query speeds Leverage compression for storage and processing efficiencies

Some of the expected improvements for you can achieve for Big Data are: • • • • • •

262

MultiTemperate Data Virtualization Less frequently accessed data on disk Process data without moving it Push processing into the remote source Reduce complexity and layers of data Become more agile with nonmaterialized views


2015


HA100

Lesson Summary You should now be able to: • Understand the Smart Data Access concepts • Know when to use Smart Data Acces

2015


263


HA100

Lesson: Smart Data Integration and Smart Data Quality Lesson Overview A brief introduction to the new features of Smart Data Integration and Smart Data Quality in SAP HANA.


Understand SAP HANA smart data integration (SDI) Understand SAP HANA smart data quality (SDQ)

Business Example Up to now we either imported data into SAP HANA using ETL processes in batch mode (not realtime) if we wanted clean data (data quality), or using SAP LT Replication Server in realtime (with no data cleaning). Using the new Smart Data Integration and Smart Data Quality in SAP HANA, we can leverage the power of SAP

HANA to give us both data quality and realtime replication.

Smart Data Integration and Smart Data Quality

Figure 234: A Traditional ETL, Replication and Smart Data Access Configuration

264


2015

HA100

Lesson: Smart Data Integration and Smart Data Quality

To accelerate the reporting performance with diskcentric computing, preaggregates were required, which led to the need for separate OLTP system and OLAP systems. In addition, an ETL system is then required to transform data from the OLTP system to the OLAP system overnight, making results available for analysis and reporting after

around 24 hours from the time data has been collected.

Figure 235: SAP HANA EIM Services Simplifying the Landscape and Lowering Data Latency

InMemory computing, readoptimized column based “mainstore” for analytical operations, writeoptimized insertonly “differential buffer” for transactional operations, and the ability to aggregate on the fly on any key figure with column store and parallel aggregation, OLTP system, OLAP system and ETL system can be

replaced with one SAP HANA system. This transforms how we construct business applications and our expectations in consuming them. Adopting this new technology will sharpen your competitive edge by dramatically accelerating not only data querying speed but also end to end business

processing speed. The value of the information becomes much higher. The ability to immediately detect an event and transaction as it happens will significantly helps to control the risk, open new opportunities. In business terms, this means that complex

analyses, plans and simulations can be done with realtime data and made available immediately. Previously, people had to decide between speed versus a more granular picture of their business. Now customers can look at information in any level of detail and in realtime. With realtime data replication capability, data from any source can be replicated into HANA allowing business users to get comprehensive insight into their business.

2015


265


HA100

Not only does fastevolving inmemory computing give a tremendous performance boost to analytics and reporting, but it also enables ′unthinkable′ applications combining event processing, data analysis and transactions.

Figure 236: SAP HANA EIM Services – New in SPS09

Realtime, batch and federation (SDA) data delivery styles are supported. This can be used for all styles of deployments, e.g. on premise and in the cloud. The new SAP HANA SDI has the following properties: • • • • •

It provides both data replication and data transformation services, It is open and extensible, It works on SAP and nonSAP data of any style, shape and size,

Its modeling environment is part of HANA Studio and HANA Webbased Development Workbench, It extends HANA’s transformation capability by integrating ETLtype transformations natively,

•

266

It accelerates performance through a native HANA implementation.


2015

HA100


Figure 237: SAP HANA EIM Services – Value Proposition

•

Cloud integration made simple and reliable – process and data integration, realtime, HANA optimized

•

Faster timetovalue with prebuilt integration solutions for onpremise and cloud

applications from SAP and others

2015

•

Expertise you can trust – established mass of SAP integration customers, vibrant

• •

SAP Community Network, partner network with product knowledge Realtime innovation – Extract and load data natively into SAP HANA Scale your integrations to match the most demanding enterprise needs – proven power of HANA ASE reliant messaging, Industry’s best maintenance and support offerings


267


HA100

Figure 238: Smart Data Integration Extending HANA by Integrating Real Time Delivery Mode

For more details on some of the SDI functionality, have a look at the SAP Replication

Server details earlier in this course.

Figure 239: Smart Data Integration Transformations

Smart Data Integration starts bringing the functionality of various technologies together to work seamlessly in provisioning quality data into your SAP HANA system.

268


2015

HA100


Figure 240: Smart Data Integration – Builtin Adapters for Common Sources

You can see the extensive list of realtime and batch adapters that are available.

2015


269


HA100

Figure 241: Smart Data Quality Transformations

Leveraging the power of SAP HANA, we can now deliver quality data faster than before.

270


2015

HA100


Lesson Summary You should now be able to: • Understand SAP HANA smart data integration (SDI) • Understand SAP HANA smart data quality (SDQ)

2015


271


HA100

Lesson: Smart Data Streaming Lesson Overview A quick introduction to new functionality in SAP HANA to capture, process, and react to realtime event (data) streams.


Understand SAP HANA smart data streaming Know where to use Smart Data Streaming Understand the value of Smart Data Streaming in certain use cases

Business Example We discuss a few business or use cases in this short chapter.

Introduction to Event Stream Processing

Figure 242: SAP HANA Smart Data Streaming A New Component with SPS09

272


2015

HA100

Lesson: Smart Data Streaming

Welcome to Event Stream Processing (ESP). This product has been a standalone product from SAP, and before that from Sybase, for a while. It is now part of SAP

HANA, and extends the capabilities of the SAP HANA platform with the addition of realtime event stream processing. We can capture data (millions of events per second

) arriving continuously from devices and application, act on this new information as soon as it arrives, and react using alerts, notifications and immediate response to changing conditions. With SAP HANA, it is called Smart Data Streaming.

Figure 243: SAP HANA Smart Data Streaming Streaming Data Sources Are Becoming Ubiquitous

Enterprises today are flooded with streams of messages as things happen. Individual

events may not be significant by themselves, but how do you extract insight from the noise? How do you know when something of significance has occurred? You might have thousands of sensors reporting status every few seconds – and most of

that information is uninteresting. But when something is starting to go wrong, you want to know as soon as possible, so that you can take action before a small trend becomes a big problem.

2015


273


HA100

Figure 244: SAP HANA Smart Data Streaming Database Queries vs. Continuous Queries

SAP HANA smart data streaming extends the capabilities of the SAP HANA Platform As we talk about how ESP applies continuous queries to streams of events, and how

you can collect events in windows so that you can examine new events in the context of past events, and this can be confusing because it starts to sound like a database. But it’s important to understand that ESP is not a database. With ESP you define CONTINUOUS queries. These are not queries in the traditional

sense that are run against a database. These queries are defined in advance and they are eventdriven, updating continuously as new information arrives. And while ESP can hold data temporarily in windows, it does not provide a way to store data permanently. that is obviously where SAP HANA comes in. And while there is some overlap in the types of analytics you can apply to the data, probably the most basic distinction is this: ESP is eventdriven – it publishes output

in response to the arrival of new event information. Whereas a database operates ondemand: it delivers results in response to queries that are run against it by a user,

by an application, or on a scheduled basis. Now it’s true, that most databases have triggers, that let you define an action to take in

response to new information being added to the database. But triggers are much more constrained in what they can do, and they act in response to a single event – whereas ESP can act based on combinations of events, such as trends or patterns. There’s also

the streaming architecture of the ESP engine, which is designed to stream new events

274


2015


HA100

through the continuous queries at very high rates – hundreds of thousands of incoming

events per second, streaming through the continuous queries to produce new output – something database triggers just weren’t designed for. And finally, there’s the aspect of streaming output. When you want to see what data

has changed in a database, you need to query it. To monitor a database for changes, you need to regularly poll it. ESP on the other hand, produces streaming output: it will tell you when something changes. So bottom line, is that ESP is distinctly different from – and complementary to – a database – event a highspeed realtime database like HANA. It can’t do what a database does, and where streaming data is concerned, it provides capabilities that a database can’t provide.

Figure 245: SAP HANA smart data streaming Extracts insight from events

So how does ESP work? ESP lets you define continuous queries that are applied to one or more streams of incoming messages to produce streams of output events. The output events are the “complex events” we’re talking about when we talk about complex event processing (CEP). These are higher level events that are produced by ESP on the basis of the analysis of the incoming events. This is what sets ESP (or CEP) apart from simple event processing. It’s the ability

to examine incoming events in the context of other events or other information, to understand what’s happening. In many cases, a single event may not contain much information or by itself be interesting, but when combined with other events, you may

be able to observe a trend or pattern that is very meaningful.

2015


275


HA100

Let’s take a simple example: let’s say you want to watch for some overheating equipment. You’ve got sensor data coming in that tells you the equipment temperature.

But that data alone might not be very interesting. If the equipment temperature is 90 degrees, but it’s outside and the outside air temperature is 85 degrees, that might be normal. But if the equipment is 90 degrees when the air temp is 30 degrees, that could indicate a problem. Or maybe it’s a trend you care about: so an individual data point doesn’t tell you much, but you want to look at how it has changed in the last hour – or the last 5 seconds. Or maybe you want to compute a moving average and compare it to historical norms for similar equipment, or users, or context. These are the types of things you can do with ESP.

Figure 246: SAP HANA Smart Data Streaming Use case: Active Equipment

Monitoring

The Internet of Things is not the future. It’s already here. Millions of devices are now

connected and delivering information. We even have lightbulbs with builtin WiFi. But how do you take advantage of all that data that’s available? How do you collect it an analyze it? And what if you want to be able to respond immediately to what’s going on? That’s where ESP comes in: it’s ideally suited to collecting this data from thousands

of devices – not after the fact but as fast as they send the data. But it doesn’t stop at data collection. The real value comes from the ability to actively monitor and understand this data. Take advantage of shortlived opportunities. Or act on emerging problems before they become big problems.

276


2015


HA100

Figure 247: SAP HANA Data Center Intelligence Use case: Realtime

Regarding the realtime use case, we talk about •

The scalable streaming of event. As an example let’s use the syslog events from the network and stream these events into the event store for further analysis.

•

Correlation of events and derive actions. An example here is the notification on alerts on defined error conditions by correlation of event from different sources

within a given time window.

2015


277


HA100

Figure 248: SAP HANA Smart Data Streaming Use Case: Capital Markets

Trading surveillance applications take many forms and go beyond market surveillance undertaken by exchanges. Many brokers rely on surveillance to ensure that orders going through their systems comply with all exchange and market regulations and dark pool operators use surveillance to prevent information leakage.

Figure 249: SAP HANA Smart Data Streaming Design Time

You can define streaming “projects”, that define the input streams, continuous queries,

and outputs. These projects are defined in CCL – the stream processing language used. A streaming plugin for the SAP HANA studio provides both a CCL editor and

visual editor, along with the testing tools.

278


2015

HA100


Figure 250: Streaming Projects Connecting to SAP HANA Tables

You can capture the high speed event stream's data into SAP HANA tables. Update of these tables is based on analysis of incoming event streams. The streaming projects you defined in the previous graphic can apply inserts, updates, and deletes to the SAP

HANA tables, and are not just limited to event logging. This allows HANA tables to “mirror” event windows in streaming projects.

Figure 251: SAP HANA System with One or More Streaming Nodes

You can build an SAP HANA system with a one or more streaming nodes.

2015


279


HA100

Figure 252: DesignTime Tools in SAP HANA Studio

Designtime tools for ESP are now built into the SAP HANA Studio. We’ve been working to more tightly integrate ESP with SAP HANA, and we now offer all of the ESP Studio capabilities as a plugin to SAP HANA Studio. This means that right from within SAP HANA Studio, users can now build ESP projects, run them and test them.

Figure 253: SAP HANA Smart Data Streaming Range of Connectivity Options

280


2015


HA100

This is the range of connectivity that is currently supported. We are constantly adding to the list. Also, this list only represents the offtheshelf connectivity. It’s also easy

to implement custom adapters.

2015


281


HA100

Lesson Summary You should now be able to: • Understand SAP HANA smart data streaming • Know where to use Smart Data Streaming • Understand the value of Smart Data Streaming in certain use cases

282


2015

Unit Summary

HA100

Unit Summary You should now be able to: • Understand the capabilities of the flat file data load functionality • Load data from Flat Files into the SAP HANA Database Present the main features of the SAP Data Services ETL solution for SAP HANA • Describe the process of loading data from ECC to SAP HANA using SAP Data • Services • Understand the architectural foundation of SAP Landscape Transformation Replication Server and its technical prerequisites Configure SAP Landscape Transformation Replication Server for connectivity to • the source SAP ERP system and the target SAP HANA Database • Configure data provisioning in SAP HANA Studio and trigger an initial load and/or replication • Understand how you can load data into SAP HANA from an SAP Business Suite source system. • Explain the key benefits of SAP Replication Server List the supported primary and replicate RDBMS • • Describe the architecture of SAP Replication Server • Understand how to connect SAP Replication Server with an SAP HANA system • Understand the Smart Data Access concepts • Know when to use Smart Data Acces • Understand SAP HANA smart data integration (SDI) • Understand SAP HANA smart data quality (SDQ) • Understand SAP HANA smart data streaming • Know where to use Smart Data Streaming • Understand the value of Smart Data Streaming in certain use cases

2015


283

Unit Summary

284

HA100


2015

Unit Summary

285

HA100


2015

Unit Summary

286

HA100


2015

Unit 6 Reporting Unit Overview This unit gives an overview of the different clients that can be used to create reports on top of SAP HANA.


Understand connectivity options for reporting on top of SAP HANA Understand the SAP Business Objects platform and reporting possibilities Understand how SAP Business Objects Analysis for Office can report on HANA.

Understand why an ODBC connection to HANA is needed when using SAP Business Objects Analysis for Office.

•

Create Dimensions and Measures from HANA with SAP Business Objects Analysis for Office.

• • •

Understand how SAP Business Objects DesignStudio can report on HANA. Understand the main topics about SAP Business Objects DesignStudio

Get acquainted with SAP Lumira

Unit Contents Lesson: SAP HANA Database Connectivity Options .......................... 288

Lesson: SAP BI ..................................................................... 292 Lesson: SAP BusinessObjects Analysis for Office ............................. Procedure: Exercise 8: Analysis for Office Using a Local ODBC

301

Connection ...................................................................... 305

Lesson: SAP BusinessObjects Design Studio ................................. 310 Lesson: SAP Lumira................................................................ 319

2015


287

Unit 6: Reporting

HA100

Lesson: SAP HANA Database Connectivity Options Lesson Overview This lesson gives an introduction to the different options available to connect client tools to the SAP HANA Database.


Understand connectivity options for reporting on top of SAP HANA

Business Example You have created Information Models in SAP HANA and are about to create reports on top of these. You need to discuss with your client what connectivity options the client tools offer.

SAP HANA Database Connectivity Options

Figure 254: Architecture Overview – SAP HANA Database and Surroundings

288


2015

HA100

Lesson: SAP HANA Database Connectivity Options

In the following slides, we take a look at several reporting tools connecting to SAP HANA.

Figure 255: Recommended BI Client connectivity to SAP HANA 1.0

2015


289

Unit 6: Reporting

HA100

SAP HANA provides various interface reporting options •

ODBO – OLE DB for OLAP – Microsoftdriven specification for multidimensional (crosstab style) reporting – Requests are sent to the database via MDX (MultiDimensional eXpression language)

•

ODBC – Open DataBase Connectivity – Microsoftdriven specification for relational reporting – Database requests are made via SQL (Structured Query Language)

– Heavily adopted in industry – No longer Microsoftcentric – Unix and Linux drivers exist for ODBC •

JDBC – Java DataBase Connectivity – Relational reporting drivers specified by the Java community. Popular on Unix platforms

•

BICS – BI Consumer Services

– SAP Proprietary interface that offers advantages for OLAP access over MDX on multidimensional reporting objects – Common driver technology used by SAP BusinessObjects Analysis, Office Edition and Semantic Layer based Business Objects clients for connectivity

to SAP NetWeaver BW

290


2015

Lesson: SAP HANA Database Connectivity Options

HA100

Lesson Summary You should now be able to: • Understand connectivity options for reporting on top of SAP HANA

2015


291

Unit 6: Reporting

HA100

Lesson: SAP BI Lesson Overview This lesson will walk you through the different types of SAP BI tools available for

you to use with SAP HANA.


Understand the SAP Business Objects platform and reporting possibilities

Business Example You have deployed SAP HANA at a client who is also a user of SAP Business Objects. You want to decide which SAP BI client tool will work best for the different reports

you are aiming to create on top of your Information Models.

SAP BI

Figure 256: BI Clients (I)

Rolespecific and configurable interfaces for all levels of user wherever they are

292


2015

Lesson: SAP BI

HA100

The platform covers the full spectrum of BI capabilities, including reporting, query

and analysis, discovery, dashboard, and predictive analysis. Everyone from the business analyst to the casual information consumer can get to the information they need with minimal dependence on IT resources and developers. Business Users can interact with highly visual tools and immediately answer business questions BI platforms must include interfaces for all types of users – including those that are

not comfortable with traditional reporting and query and analysis. SAP BI includes a number of intuitive, nextgeneration usability solutions such as Explorer, one that combines the speed and simplicity of search with the power and reliability of BI

Advanced Users can leverage the most complete BI toolset in the marketplace A successful BI platform has to support all the information access, interaction, and

analysis needs of the different profiles of users across your organization. Using advanced analytics such as SAP BusinessObjects Analysis, financial and business analysts can interrogate complex historical data, look for trends and patterns and build predictive models. Casual Users have easy to use selfservice access to explore information A key goal for BI platforms remains helping you extend the reach of factbased decisions to all users, not just the estimated 15% that traditionally use BI tools. Casual users must be able to have selfservice access information through the familiar interfaces and workflows that they already use in their daily work, whether it’s the browser, Microsoft Office suite or directly on their desktop using widgets.

2015


293

Unit 6: Reporting

HA100

Figure 257: Analytic solutions from SAP: BI focus

Reporting Get the reporting foundation you need to guide your daily actions as well as your overall business intelligence (BI) strategy – with SAP software. Drive fast and informed short and longterm decision making. And deliver insights that are meaningful, actionable, and of real value to employees, customers, and partners. • • • • •

Quickly author highly formatted reports that turn Big Data into deep insight

Optimize business processes by embedding reports into applications Help users answer their own business questions to minimize IT support costs

Share personalized, interactive reports with users on any device Securely distribute reports off any data source, both internally and externally

Dashboarding and Apps

294


2015

Lesson: SAP BI

HA100

Drive business intelligence (BI) adoption across your organization with compelling,

insightful dashboards and BI apps. Our solutions can help you create mobile, highly interactive, rolebased dashboards that are able to deliver trusted, aggregated numbers to your decision makers – anytime, anywhere. • • •

Design interactive, high impact dashboards

•

Drive high BI adoption and ROI through broadly published dashboards

Give users easy access to trusted KPIs – for improved business performance

Get the flexibility you need to create dashboards that comply with precise user specifications

Agile Vizualization Empower your business users with easy, selfservice access to data discovery functionality – for instant answers to pressing business questions. Provide an intuitive,

interactive user experience with immediate response time, and eliminate the need for training and IT assistance.

•

Increase user autonomy with selfservice discovery of relevant insights – anytime, anywhere

•

Help business users see the big picture while navigating the details – for fully

• • •

informed decisions Empower users to uncover connections and trends in new and unexpected areas Reduce IT backlog and free up time for more valueadded activities Benefit from accelerated ROI, and reduce costs by leveraging existing IT investments

Figure 258: The Convergence of the SAP BI Client Portfolio

2015


295

Unit 6: Reporting

HA100

Besides the BI requirements of the target audience (user groups) many other dimensions need to be taken into account in order to decide on the right BI Client strategy: • • • •

Source systems and data sources needed to be connected to

•

Deployment of reporting content (NetWeaver Portal, BO BI Plattform, etc.)

Reporting Client deployment (Desktop, Web, MS Office Integration) Reporting Client technology (HTML5, Flash, Mobile requirements)

Reporting Client features (hierarchies, variables, calculations, graphical visualization, Export to PDF/XLS etc.)

Thus, defining the right reporting strategy is not an easy task and requires thorough

evaluation. The SAP BI philosophy has always been to give each user persona the right best of breed tool for the way they work. Power users who want to analyze data need a different experience then an executive who wants to track and monitor ongoing strategic and tactical organizational initiatives. Over time we’ve broadened the capabilities within each persona experiences. That being said, not everyone in an organization is using BI today – typically about 15%. To address that fundamental challenge that we looked for models of even greater simplicity, like the world of search. Think of how easy it is to find basic information

with Google. It’s a great, simple way to begin an investigation. Shouldn’t BI be that easy? No doubt and enterprises large or small are seeking new simpler, faster ways of providing information access to their employees. We have recognized that need and as you will see later, we are offering an innovative BI solution. One that offers a highlyintuitive interface, so users can “walk” through the data, guided by the product, answer questions and gain insight. The name of this solution is SAP Lumira and we

will provide more details later in the presentation.

296


2015

Lesson: SAP BI

HA100

Figure 259: Pixel Perfect Reporting – SAP Crystal Reports for Enterprise

Figure 260: Dashboarding and Data Visualization SAP BusinessObjects

Design Studio

2015


297

Unit 6: Reporting

HA100

Figure 261: Ad Hoc Query and Reporting – SAP BusinessObjects Web

Intelligence

Figure 262: Advanced Analysis SAP BusinessObjects Analysis for MS Office

298


2015

Lesson: SAP BI

HA100

Figure 263: Data Search and Exploration SAP Lumira / SAP Fiori

Figure 264: HANA Client Tool Access Matrix

2015


299

Unit 6: Reporting

HA100

Lesson Summary You should now be able to: Understand the SAP Business Objects platform and reporting possibilities •

300


2015

Lesson: SAP BusinessObjects Analysis for Office

HA100

Lesson: SAP BusinessObjects Analysis for Office Lesson Overview This lesson explains how SAP Business Objects Analysis for Office works when

reporting on SAP HANA.


Understand how SAP Business Objects Analysis for Office can report on HANA.

Understand why an ODBC connection to HANA is needed when using SAP Business Objects Analysis for Office.

•


Business Example You are working in an organization where Microsoft Excel is heavily used. Business

Controllers have requested to connect to HANA using Office. As such, you have decided to deploy Business Objects Analysis for Office.

SAP BusinessObjects Analysis for Office SAP BusinessObjects Analysis, edition for Microsoft Office, is a Microsoft Office AddIn that allows multidimensional analysis of OLAP sources in Microsoft Excel, MS Excel workbook application design, and intuitive creation of BI presentations with MS PowerPoint. The AddIn is available for the following Microsoft Office versions:

• • • •

Microsoft Office 2013 (Excel and PowerPoint) Microsoft Office 2010 (Excel and PowerPoint) Microsoft Office 2007 (Excel and PowerPoint) Microsoft Office 2003 (Excel)

In the edition for Microsoft Office, you can use SAP BEx Queries, query views and

SAP Netweaver BW InfoProvider and SAP HANA views as data sources. The data is displayed in the workbook in crosstabs. You can insert multiple crosstabs in a workbook with data from different sources and systems. If the workbook will be used by different users, it is also helpful to add info fields with information on the data source and filter status.

2015


301

Unit 6: Reporting

HA100

Using the design panel, you can analyze the data and change the view on the displayed data. You can add and remove dimensions and measures to be displayed easily with drag and drop. To avoid single refreshes after each step, you can pause the refresh to

build a crosstab. After ending the pause, all changes are applied at once. You can refine your analysis using conditional formatting, filter, prompting, calculations and display hierarchies. You can also add charts to your analysis. If you want to keep a status of your navigation, you can save it as an analysis view. Other users can then reuse your analysis. For more sophisticated workbook design, the edition for Microsoft Office contains

a dedicated set of functions in Microsoft Excel to access data and meta data of connected systems. There are also a number of API functions available that you can use with the Visual Basic Editor, to filter data and set values for variables.

You can also plan business data based on the current data in your data source. You can enter the planning data manually and you can enter planning data automatically using planning functions and planning sequences of SAP NetWeaver BW Integrated Planning. Analysis, edition for Microsoft Office, must be installed on your local machine. You

can connect directly to a SAP NetWeaver BW system and SAP HANA or you can connect via a business intelligence platform (SAP BusinessObjects Enterprise or SAP BusinessObjects Business Intelligence platform) to include data sources.

Figure 265: Analytical Reporting on HANA 1.0 with Analysis for Office

302


2015


HA100

The main capabilities of SAP BusinessObjects Analysis for Office are as follows:

• • • • • • • • • • • •

Save/Opena workbook to/from the SAP BI Platform server Undo/Redo Define style sets for crosstabs

Swap axes Analyze data with the design panel Filter members Sort data by members and by measures Insert charts Insert filter components

Convert crosstab cells to formula Create Presentations Pause Refresh

Figure 266: SAP BusinessObjects Analysis, Office Edition (1)

2015


303

Unit 6: Reporting

HA100

Figure 267: SAP BusinessObjects Analysis, Office Edition (2)

Hint: Detail information on:http://scn.sap.com/community/businessobjects analysismsoffice

304


2015


HA100

Exercise 8: Analysis for Office Using a Local ODBC Connection Use Analysis for Office (AAO) has been installed in the training system. In this exercise

you will use AAO to connect to SAP HANA through a locally defined ODBC connection. The ODBC connection has already been created by the administrator.

Procedure 1.

Start Analysis for Office from the Start Menu: 1. 3.

Start Menu All Programs _SAP Business Intelligence

4.

SAP BusinessObjects Analysis

5.

Analysis for Microsoft Excel

2.

2.

Select Analysis → Insert → Select Data Source

Figure 268: Data Source

3.

In order to use a local ODBC connection (instead of authenticating against BOE Skip. and using a relational DB connection published to CMC), click


2015


305

Unit 6: Reporting

HA100

Figure 269: ODBC Connection

4.

Select the ODBC connection named HDB32, click Next

5.

Enter your SAP HANA user name and password, click OK

Figure 270: Logon

6.

Find and expand your package (studentXX), select one of the views you have created, click OK


306


2015


HA100

Figure 271: Package

7.

A report based on that view is shown.

Figure 272: Report

8.

Drag and drop dimensions and measures in the rows and columns to navigate in

that view and build your report.


2015


307

Unit 6: Reporting

HA100

Figure 273: Dimensions and Measures (1)

To filter members or measures, right click on the row or column item.

Figure 274: Dimensions and Measures (2)

308


2015


HA100

Lesson Summary You should now be able to: Understand how SAP Business Objects Analysis for Office can report on HANA. • • Understand why an ODBC connection to HANA is needed when using SAP Business Objects Analysis for Office.

•


2015


309

Unit 6: Reporting

HA100

Lesson: SAP BusinessObjects Design Studio Lesson Overview This lesson explains how SAP Business Objects DesignStudio works when reporting

on SAP HANA.


Understand how SAP Business Objects DesignStudio can report on HANA.

Understand the main topics about SAP Business Objects DesignStudio

Business Example Users have the ability to easily design analytic content that is centrally governable and that ranges from basic templates formed through guided analytics all the way to more advanced applications.

SAP BusinessObjects Design Studio SAP BusinessObjects Design Studio stands for the brand new long awaited successor to BEx Web and BEx Web Application Designer. SAP BusinessObjects Design Stuido

allows for intuitive design of centrally governable analytic content ranging from guided analytics to sophisticated OLAP applications and aggregated dashboards. The product features outofthe box iPad support, a stateofthe art HTML5 UI, seamless

application theming, a WYSIWYG Eclipsebased designer, full and native support of BW BEx queries, direct connectivity to HANA as well as an advanced scripting engine just to name a few.

310


2015

Lesson: SAP BusinessObjects Design Studio

HA100

Figure 275: SAP BusinessObjects Design Studio I

SAP BusinessObjects Design Studio enables application designers to create analysis applications and dashboards – based on SAP NetWeaver BW, SAP HANA and universe data sources – for browsers and mobile devices (iPads, for example). It is

the product of choice when full support for SAP NetWeaver BW and SAP HANA data models and engine capabilities is required. The product offers a design tool that allows you to create applications easily and intuitively without the need for native HTML and iPad UI programming skills. SAP BusinessObjects Design Studio can be

used locally and integrated in the following platforms: • • •

SAP BusinessObjects Business Intelligence (BI platform)

SAP NetWeaver SAP HANA

Besides analysis applications, application designers can also create planning applications that support both manual and automated data entry and changes to data. A RealTime package, also available with Design Studio, allows application designers to create visualizations with streaming data (push based), and to create visualizations, which have a near RealTime connection to SAP HANA or SAP BW (pull based).

In addition to the standard palette of components in Design Studio, which are used to visualize data and enable user interaction, you can develop 3rd party components with the Design Studio SDK and enhance your analysis applications with custom components. Besides SDK components that visualize data from a data source, you can also create SDK components that act as data sources for SDK components themselves.

This enables SDK components to access a broad range of data sources such as local

2015


311

Unit 6: Reporting

HA100

files, Web services, or new types of backend system. You can store and provide access to the analysis applications containing the 3rd party components on any of the supported platforms.

Hint: In local mode, you can create demo analysis applications for evaluating SAP BusinessObjects Design Studio with users at customer locations.

Figure 276: SAP BusinessObjects Design Studio II

Basic Concepts

Entities The design tool of SAP BusinessObjects Design Studio enables application designers

to create and edit applications. These applications are saved in an XML file format. Applications generally consist of user interface components (such as charts, crosstabs,

buttons) and data source aliases. Data source and data source alias A data source alias represents an instance of a data source (for example, a BW query,

or a SAP HANA data source) at runtime of the application. A single application can contain multiple instances of one data source. Every instance, for example, can apply its own filters on the same data source and thus represents its own subset of data. You

can see the list of components and data source aliases used in an application in the outline view of the design tool.

Data binding

312


2015


HA100

To visualize data from a data source alias in a crosstab or chart, define a data binding in the design tool for these components. The data binding is simply a reference to the data source alias which provides the data. When the data of a data source alias changes

(for example, a filter is applied at runtime), the system automatically updates all components that have a data binding to that data source alias. You can also see the data

binding relationships between components and data source aliases in the outline view User interaction Typically you create applications that not only visualize data, but which also provide possibilities for the application user to interact with the data; for example, changing filters, selecting drilldown dimensions or branching into a details view for a selected

set of aggregated data. To enable user interaction with the data, add the relevant UI components to the application. For example, you could provide a row of buttons above a crosstab and chart component to filter the visualized data by different criteria.

Eventing and script API UI components provide a set of specific events that the application user can execute on the component. For example, every button provides an on click event. You can see a list of all available events of the component in the properties view of the design

tool. Here, you can open the script editor and specify which actions should take place when the application user triggers the event. The script language is a subset of JavaScript and allows a sequence of script API method calls to be defined. The script

API provides access at runtime to the application itself as well as to its components and data source aliases. Therefore, the application designer has flexible control of the

application behavior by using event scripts. Setting properties in design time and run time Besides the events, components also offer a variety of other properties in the properties view of the design tool. Some properties are common for all or almost all components; the component name identifies the component within the application, for example, and the layout properties define the size and position of the component within the application. Other properties are specific and depend on the component type. In all cases, the property values displayed in the properties view define the initial

state of the components at application startup time. At application runtime, you can enable modification of properties by executing event scripts, as almost all component

properties are accessible through the script API.

2015


313

Unit 6: Reporting

HA100

Figure 277: Main Screen Elements

The Components view contains all components that you can use for creating analysis applications. Components are user interface elements that you can drag and drop into the layout editor and thus create the content of the application. Once you have inserted a component in your application, you can change its layout and behavior by editing its properties in the Properties view. Most of the components offer a set of specific

events that the application user can execute on the component. As components have different functions, they are grouped in different folders: Analytic components You use analytic components like crosstabs and charts to visualize your data. After you have dragged and dropped a crosstab or a chart into the layout editor, the component initially displays dummy data. As soon as you assign a data source to the crosstab or chart, it displays the data of the data source. This group contains the following components: • •

chart crosstab

The crosstab displays multidimensional data in a table with analytic functions. Basic components

314


2015


HA100

The Basic Components folder contains a variety of components with different functions. Components like Dropdown Box, Radio Button and Checkbox are used to select or filter data and thus enable user interaction at runtime. Components like Button and Image are used to enable navigation, whereas components like Text and Date Field are used to enhance the design and layout of the application. Container components Container components are used to group and structure the content of an application. They can also be used to optimize an application for a mobile device and thus enable specific motion gestures in mobile applications.

Figure 278: Initial View Editor to Define the View of the Data Source

Key Features

2015


315

Unit 6: Reporting

• • • • • • • • • •

HA100

Drag and Drop in NavigationPanel & Navigation Panel Usability Context Menu in Crosstab Cell Locking (for Planning Apps) Report to Report Interface Script API Extensions & Global Functions Multi Language Support Enhanced Standard Analysis Template Online Composition of Apps & Bookmarking Data Source SDK Lumira (CVOM) SDK (CVOM (Common Visualization Object Modeler)

• • •

Realtime Streaming Fiori / sFin Integration New Partner Extensions

Figure 279: SAP BusinessObjects Design Studio (SDK)

316


2015

HA100


Figure 280: SAP BusinessObjects Design Studio (Fiori)

Figure 281: Sources for a Data Source Object

2015


317

Unit 6: Reporting

HA100

Lesson Summary You should now be able to: Understand how SAP Business Objects DesignStudio can report on HANA. • • Understand the main topics about SAP Business Objects DesignStudio

318


2015

Lesson: SAP Lumira

HA100

Lesson: SAP Lumira Lesson Overview With the Lumira family SAP offers a new premium reporting alternative for data search and exploration. The core feature is its flexibility as it empowers users to analyze data in an agile way and use a great variety of visualizations. There are three deployment options for Lumira which differ in their functionality.


Get acquainted with SAP Lumira

Business Example This demo is meant to give an idea of how SAP Lumira works as a premium alternative to SAP BusinessObjects Explorer. We will again use the Calculation view

which was created in the COPA modeling chapter and create agile visualizations.

Deployment and Features SAP Lumira delivers beautiful analytics, allowing you to easily convey and share knowledge. •

Using beautiful and intuitive data visualization templates, Lumira lets you focus

on telling your story. •

The power of sharing visualizations and insights in the cloud with everyone and without having to sacrifice performance, security, or functionality.

•

Combine and mashup all your data with simpler, errorfree data prepping workflows. Cool charts and visualizations to illustrate hidden insights where numbers cannot.

SAP Lumira is a selfservice solution that allows analysts and decision makers to

access, transform, and visualize data. The SAP Lumira desktop experience is used to prepare data from multiple sources, visualize it, and then compose stories from those

visualizations that can be shared with other decision makers using the SAP Lumira server and cloud platforms which provide browser and mobile based experiences to further analyze data and collaborate with colleagues on datasets, stories, and other business intelligence artifacts.

2015


319

Unit 6: Reporting

HA100

Figure 282: Trusted data discovery with SAP Lumira

Required components Before installing SAP Lumira Server version 1.20 onto your SAP HANA server, ensure that your deployment environment meets the following requirements: • • •

SAP HANA Support Package 08 Revision 82 or 83, with the XS Engine, must be installed. Other revisions of SAP HANA may not be supported. The XS Engine must be installed and running. It is typically installed together with SAP HANA. The SAP HANA SelfService Analytics Application Function Library (SAL AFL) Revision 82 or 83 must be installed on your SAP HANA server. SAL AFL must be the same version as the SAP HANA server it is installed on.

• • •

320

SAP HANA Lifecycle Manager (HLM) 1.0 Support Package 07 Patch 12 or higher must be installed on your SAP HANA server. SAP HANA Studio revision 82 must be installed on the client machine, and must be able to connect to your SAP HANA system. If you plan to undeploy SAP Lumira Server, you must install the SAP HANA Client.


2015

Lesson: SAP Lumira

HA100

The SAP Fiori configuration application sets all required SQL connections between your SAP HANA repository and SAP Lumira Server. The application also enables the job scheduler, script service, and assigns the technical user the rights required for all features.

Hint: The SQL connection configuration (SQLCC) file on the SAP HANA XS server is modified automatically by the application. SAP Lumira Desktop SAP Lumira installation wizard The SAP Lumira installation wizard identifies your computer's operating system, checks for installation prerequisites, and updates files as required. The wizard is in a

selfextracting archive file called SAP LumiraSetup.exe. In a silent installation, the SAP Lumira installation wizard runs without displaying a user interface or prompting for user input; it reads required input from a text file.

A silent installation is typically used by network administrators to push multiple installations across a network or for custom installations (for example, to configure

automatic updates with an onpremise web application server). After creating a silentinstallation response file, you can add the silentinstallation command to your installation script. You can manually check for updates or set up a scheduled check for automatic SAP Lumira updates from the SAP Public Portal web site or the SAP Support Portal web

site. If you have configured an onpremise web application server for automatic updates, you can also select the frequency of updates (but the SAP Lumira installation

and version are derived from the server). If you do not have an onpremise web application server, you can choose whether updates are downloaded and installed via a manual or scheduled update.

2015


321

Unit 6: Reporting

HA100

Figure 283: Data Acquisition Overview

Data Access Extensions A Data Access Extension is an executable created by an application developer, that extends the ability of SAP Lumira Desktop (version 1.17 and later) to import additional data sources such as XML files, social data sources like Twitter or Facebook, or other big data sources such as Google Big Query. This SDK will help you to create extensions using the SAP Lumira data extension framework. Data extension applications are placed in the SAP Lumira > Desktop > daextensions folder. Currently, SAP Lumira supports data formats such as CSV, Text, Microsoft Excel,

data from your clipboard, and access to remote data such as universes, SAP HANA database, and SAP BW, to build visualizations and story boards.

Using data source extensions, you can perform the standard SAP Lumira data acquisition workflows supported include preview, edit, and refresh.

SAP Lumira data access extension framework provides you specifications for developing a new data source extension. You can create a data source extension using a programming language of your choice (Java, C, C++, C#, and so on) and perform

the standard SAP Lumira data acquisition workflows.

322


2015

Lesson: SAP Lumira

HA100

Figure 284: Sharing SAP Lumira Content

SAP Lumira Cloud The SAP Lumira Cloud API allows content stored in SAP Lumira Cloud to be consumed and embedded in mobile devices and webenabled technology. With the SAP Lumira Cloud API, you can do the following: • •

Retrieve content using Open Data Protocol (OData) with OAuth 2.0. View SAP Lumira Cloud stories.

Hint: SAP Lumira Cloud Development Guide: https://help.sap.com/lumira/ Hint: Lumira Sizing Guide on :http://scn.sap.com/docs/DOC53374

2015


323

Unit 6: Reporting

HA100

Figure 285: SAP Lumira Product description

Figure 286: SAP Lumira Acquire data

324


2015

Lesson: SAP Lumira

HA100

Figure 287: SAP Lumira Prepare data

Figure 288: SAP Lumira Visualization support

2015


325

Unit 6: Reporting

HA100

SAP Lumira SDK and VizPacker The SAP Lumira SDK lets you create new chart types as extensions and use them in

SAP Lumira. The SDK includes the VizPacker utility, which helps you package chart extensions. SAP Lumira lets you plot information by using a variety of different charts, including

bar charts, line charts, and so on. However, you might need to use other charts that are not included in the program, such as sunburst charts or chord diagrams. The SAP Lumira SDK solves this problem by providing an extension framework with an associated API that lets you develop your own chart and integrate it with SAP Lumira.

This guide focuses on using VizPacker to generate the folder structure, the feed definition, and a JavaScript code template for the extension. Using VizPacker instead of manually creating the files and folders lets you focus on modifying the content of your code.

Hint: SAP Lumira SDK:https://help.sap.com/lumira / Hint: Official Product Tutorials – SAP Lumira: http://scn.sap.com/docs/DOC 26507

326


2015

Lesson: SAP Lumira

HA100

SAP Smart Business cockpits Applying the Fiori design principles to the SAP Business Suite experience

• • • •

Responsive Bringing together transactions and analytics to close the gap between insight and action Personalized Offering personalized rolebased tailored KPIs, actionable insights, news feeds and tasks Seamless Delivering the same realtime insights and user experience across desktops, mobiles and tablets Beautiful Combining simplicity, easy of use, and functionality with a completely reimagined user interface

Figure 289: SAP Smart Business cockpits examples

SAP Smart Business for

Accounts Payable as one example

SAP Smart Business for accounts payable is an SAP Smart Business cockpit that provides you with an overview of the most important key performance indicators for

an accounts payable manager.

2015


327

Unit 6: Reporting

HA100

Key Features • • • • • • • • •

Future Payables Overdue Payables Cash Discount Forecast Cash Discount Utilization Aging Analysis Invoice Processing Time Days Payable Outstanding Analysis Vendor Payment Analysis (Manual and Automatic Payments) Vendor Payment Analysis (Open Payments)

Figure 290: Smart Business Cockpits Multiple deployment options

328


2015

Lesson: SAP Lumira

HA100

Figure 291: SAP Fiori Launchpad One entry point for the user

Hint: You could assign Lumira, Design Studio and Fiori Applications to the Fiori Launchpad. Content will be deliverd by SAP as SAP Smart Business Cockpits

Figure 292: SAP Smart Business Cockpits insight to action

2015


329

Unit 6: Reporting

HA100

Figure 293: SAP Smart Business – Technical Building Blocks

KPI Modeler SAP Smart Business applications provide insight into the realtime operations of your business by collecting and displaying KPIs and reports directly in your browser.

The KPI modeler allows you to model KPI and report tiles that enable targeted monitoring of key business data using the SAP Smart Business launchpad. With the KPI modeler, you can define KPIs and reports to which you can apply numerous different evaluations so that you can respond to the everchanging business landscape. You can also configure drilldown views that are accessed through the KPI tiles and offer additional perspectives on the relevant data.

Hint: Detail information on http://help.sap.com/ssb

330


2015

Lesson: SAP Lumira

HA100

Lesson Summary You should now be able to: • Get acquainted with SAP Lumira

2015


331

Unit Summary

HA100

Unit Summary You should now be able to: • Understand connectivity options for reporting on top of SAP HANA Understand the SAP Business Objects platform and reporting possibilities • Understand how SAP Business Objects Analysis for Office can report on HANA. • • Understand why an ODBC connection to HANA is needed when using SAP Business Objects Analysis for Office.

332

•


• • •

Understand how SAP Business Objects DesignStudio can report on HANA.

Understand the main topics about SAP Business Objects DesignStudio Get acquainted with SAP Lumira


2015

Test Your Knowledge

HA100

Test Your Knowledge

2015


333

Test Your Knowledge

HA100

Answers

334


2015

Test Your Knowledge

335

HA100


2015

Course Summary

HA100

Course Summary You should now be able to: • • • • •

336

Explain SAP HANA concepts Use SAP HANA Studio Create simple information models Understand how data can be loaded into HANA Get an overview of how to report on HANA using client tools


2015

Appendix 1 Useful Resources about SAP HANA

• • • • •

2015

http://help.sap.com/hana(SAP Help Portal) http://service.sap.com/hana(SAP Service MarketPlace) http://www.saphana.com(SAP HANA Dedicated Web Site) http://www.suiteonhana.com(SAP Business Suite Powered by SAP HANA) http://scn.sap.com/community/hanainmemory (SAP Community Network)


337

Appendix 1: Useful Resources about SAP HANA

338


HA100

2015

Feedback SAP SE has made every effort in the preparation of this course to ensure the accuracy

and completeness of the materials. If you have any corrections or suggestions for improvement, please record them in the appropriate place in the course evaluation.

2015


339

HA100_EN_Col09

Recommend Documents