Short Presentation Title - SCN - SAP.com

SAP HANA Things you need to know about Data Modeling Werner Steyn, Customer Solution Adoption (CSA), SAP Labs

March 2013

Disclaimer

This presentation outlines our general product direction and should not be relied on in making a purchase decision. This presentation is not subject to your license agreement or any other agreement with SAP. SAP has no obligation to pursue any course of business outlined in this presentation or to develop or release any functionality mentioned in this presentation. This presentation and SAP's strategy and possible future developments are subject to change and may be changed by SAP at any time for any reason without notice. This document is provided without a warranty of any kind, either express or implied, including but not limited to, the implied warranties of merchantability, fitness for a particular purpose, or non-infringement. SAP assumes no responsibility for errors or omissions in this document, except if such damages were caused by SAP intentionally or grossly negligent.

© 2012 SAP AG. All rights reserved.

2

Things you need to know about Data Modeling • Unified Modeling o Analytical Views & Attribute Views

• Analytical Views o Count & Temporal Joins o Multi Dimensional reporting flag

• Calculation Views o Count, Keep flag, Constant Column

• SAP HANA XS o HTTP access to data & artifacts o Text-based search o Transport > roles, DML/DDL, procedures

• Query execution insights o Preview of intermediate results o Visualize plan & timeline

• Input Parameters o Derived from tables & manage parameters UI

• Decision Tables o Based on tables, models, table types o Transactional behavior

• Text Search o Fuzzy search models o Rule sets o Full Text Search (PDF, MS Office documents)

• Predictive Analytics o Application Function Library (PAL & BFL)

• Analytical Privileges o Dynamic role assignments

SAP HANA SPS 05 What’s New – Release Notes http://help.sap.com/hana/hana_sps5_whatsnew_en.pdf


3

Unified Modeling SAP HANA Developer Guide http://help.sap.com/hana/hana_dev_en.pdf

Unified Modeling - New Model Editor

New integrated view designer for Attribute and Analytical Views  Simplification of the view authoring process with just one (technical) view  Apply most-all semantic modeling in the same output node

Overview

Node Detail

Node columns

Output Semantics

Improved efficiency for SAP HANA modeling


5

Temporal Joins

Temporal Joins • The join condition between fact data and attribute data is extended with a timedependency • In the Logical Join, you can create a temporal join between the date field of the fact table to an interval (to and from) field of the attribute view.

 The temporal join has to start from the fact table such that the single column must 

be in the fact table and, the to and from columns must be in the table that is directly joined to the fact table. The join type must be a referential join. The supported data types are timestamp, date, and integer.


7

Input Parameters

Input Parameters Table Input Parameters

• Values are automatically derived from a Table instead of user input View A Prompts User to enter Discount Rate when this view is executed independently

View B

View B Derives Discount from a Table, passing discount automatically into View A without prompting the user

Discount Table


9

Count

Count - Analytical View New Count Aggregation type in Analytical Views

• Enables counters to be push down


11

Count - Aggregation Node Aggregate type on count

• Add count measure as an aggregated Column • Support for sum, min, max aggregation types


12

Keep Flag & Constant Column Aggregation Node Keep Flag will force columns to be retrieved from the database even when not requested

SELECT DIM_A, DIM_B, GROUP_CONSTANT FROM VIEW WHERE (GROUP_CONSTANT = A AND DIM_A IN 2,3,4,5) OR (GROUP_CONSTANT = B AND DIM_A IN 6,7,8,9)

Constant Column


13

Multi Dimensional Reporting

Multi Dimensional Reporting Analytical View

Calculation View

• Disable if no measures exist • Aggregation & measures optional

• Output node deployed as a projection

SELECT

ORDER_ID, SALES_ORG, MATERIAL FROM GROUP BY ORDER_ID, SALES_ORG, MATERIAL


SELECT

ORDER_ID, SALES_ORG, MATERIAL FROM 15

Analytical Privileges SAP HANA Security Guide http://help.sap.com/hana/hana_sec_en.pdf

Analytical Privileges - Dynamic Business User

Restricted coffee list

Business User

Bus. User Bus. User

Authorization

John

Table

John John

• Create Dynamic Privileges using SQL Editor • Privileges can be dynamically maintained within tables, without re-activation © 2012 SAP AG. All rights reserved.

17

Query execution insights SAP HANA Administration Guide http://help.sap.com/hana/hana_admin_en.pdf

Intermediate results-set Preview

Previewing intermediate result set data

• Data Preview supported for nodes within a Calculation View • This provides intermediate level data previews in addition to the data preview on the full model itself


19

Visualize Plan & Timeline Administration > Performance > SQL Plan Cache

SQL Editor > Context Menu

Time Line View

Visualize Plan


20

Decision Tables

Decision Table Example .. are compact representation of IF-THEN-ELSE rules

Conditions

Actions

Interest-rate Calculation IF the borrower needs a loan for $14000 for a period of 36 months and he/she resides within the same state then he/she would be eligible for a 10.25 interest rate

Import/Export Rules


22

Decision Tables Modeling Ability to model decision tables in SAP HANA Studio

• Business Rules vocabulary can be extracted from models based on Calculation Views,

•

Analytical Views, Physical Tables and Table Types Model rules get stored in a compact decision table and provide the ability to simulate and modify physical data based on actions (e.g. discount %) SAP HANA Studio … select the source

Decision Table © 2012 SAP AG. All rights reserved.

23

Decision Tables - High Level Architecture

Information Modeler

HANA Studio Decision Table Editor

Business User

Browser

SAP HANA XS Rules Web-editor

SAP HANA

Rules Compiler

SQL Script compiler

HANA Repository

SQL Engine


24

Decision Tables - Based on a Table

Sample Input Data Set LOANREQUESTS

• Decisions based on table types require a input table when executed • Parameters will enable the creation of a result view (RV) SELECT * FROM TABLE_TYPE_DT/RV (PLACEHOLDER = ($$IN_TT$$, LOANREQUESTS)) CALL TABLE_TYPE_DT (LOANREQUESTS,?)


25

Decision Tables - Based on a (Table or Modeled View)

Table INTERESTRATES

• Parameters will enable the creation of a result view (RV) • Results are dynamically calculated, without updating the table CALL TABLE_RAW_DT (?) SELECT * FROM TABLE_RAW_DT/RV


26

Decision Tables - Based on a Table

Initial Table ZINTERESTRATES

• Absence of Parameters will materialize the result • CALL TABLE_UPDATE_DT (?) will re-execute the rules and update the table results SELECT * FROM ZINTERESTRATES


27

Predictive Analytics SAP HANA Predictive Analysis Library (PAL) Reference http://help.sap.com/hana/hana_dev_pal_en.pdf SAP HANA Business Function Library (BFL) Reference http://help.sap.com/hana/hana_dev_bfl_en.pdf

Application Function Library (AFL) Application Functions – Written in C++ and delivered as AFL content

HANA Clients (App Server, Analytics Technology, etc)

– Users create procedures through a procedure wrapper generator SAP HANA

PAL Algorithms

SQLScript

– Association Analysis (Aprior, Apriori Lite) – Cluster Analysis (K-Means, Kohonen Self Organized Maps) – Classification Analysis (C4.5 Decision Tree Analysis, CHAID Decision Tree Analysi, K Nearest Neighbour, Multiple Linear Regression, Polynomial

Parameter Table

AFL Framework

– Etc. Application Functions (C++)

BFL Algorithms – Cycles, Delay Stock, Driver, Forecast Agents, Forecast Driver, Future, Grow, Lag, Seasonal Simple, Volume Driver, Seasonal Complex, Cumulate, Days, Days Outstanding , Annual Depreciation, Diminishing Balance Depreciation, etc.

Business Function Library

Predictive Analysis Library

…

Consult the documentation for complete list of available Algorithms © 2012 SAP AG. All rights reserved.

29

Working with AFL - (PAL/KMEANS) Example Step 1 – Generate a PAL/BFL Procedure using the AFL wrapper generator Procedure Name

AFL or BFL

Algorithm

Signature Table

CALL AFL_WRAPPER_GENEREATOR('PAL_KMEANS_KHNC', 'AFLPAL', 'KMEANS', PDATA)

Step 2 – Execute the Procedure (i.e. using SQL Script, Calculation View, Client) CALL PAL_KMEANS_KHNC(DATA_TAB, CONTROL_TAB, OUT1_TAB, OUT2_TAB)

Input Data


Input Control Data

3 clusters (ages) Output

discovered

30

Text Search SAP HANA Developer Guide http://help.sap.com/hana/hana_dev_en.pdf

Text Search - SAP HANA SPS05

Capabilities 

Native full text search



In-database text analysis



Graphical modeling of search models



Information Access (InA) toolkit for HTML5 UIs

Benefits 

Exploit unstructured content in SAP HANA without additional costs



Extract meaningful information from text



Less data duplication and movement – leverage one infrastructure for analytical and search workloads



Easy-to-use modeling tools – use HANA Studio Modeler to create search models



Build Search Applications quickly – HANA Information Access toolkit


32

Text Search - SQL search examples Fuzzy search SELECT CAMP_NO, DEFECT FROM MODELED VIEW WHERE CONTAINS(DEFECT, 'thrtle', FUZZY(0.7));

Relevance ranking SELECT SCORE() AS S, CAMP_NO, DEFECT FROM TABLE WHERE CONTAINS(DEFECT, 'throttle engine‘, FUZZY(0.7)) ORDER BY S DESC;


33

Text Search - Search Models

SELECT SCORE() AS S, CAMP_NO, DEFECT FROM MODEL WHERE CONTAINS(DEFECT, 'throttle engine') ORDER BY S DESC;


34

Text Search - Definition of Search Rules    

Editor is part of the SAP HANA Development perspective No need to write complex SQL, code can be shared Rules can be changed without changing the SQL code Search rules are called using the EXECUTE_SEARCH_RULE_SET function


35

Full Text Search



Full text search features has been enhanced to support binary formats



This allows you to search text in .pdf documents or Microsoft Office file formats Document Repository Table

Uploaded binary PDF documents

CREATE FULLTEXT INDEX PDF_BLOB_IDX ON DOCUMENTREPO("PDF_BLOB") TEXT ANALYSIS ON CONFIGURATION 'EXTRACTION_CORE'

SELECT SCORE(), ID, FILENAME, SNIPPETS(PDF_BLOB), HIGHLIGHTED(PDF_BLOB) FROM DOCUMENTREPO WHERE CONTAINS(PDF_BLOB, 'temporal')


36

SAP HANA XS SAP HANA Developer Guide http://help.sap.com/hana/hana_dev_en.pdf SAP HANA XS JavaScript Reference http://help.sap.com/hana/jsapi/index.html SAPUI5 http://scn.sap.com/community/developer-center/front-end

SAP HANA Extended Application Services XS Applications Browser

Complete UI Rendering HTML5: Javascript execution

Built-in services Inbound channel •

HTTP

OData http(s); OData (pure data only)

HANA

• Drastic code reduction

Outbound connectivity

XSEngine

HTTP, SMTP

Procedural Appl. Logic

UI services (minimal data volume)

IndexServer

•

SAPUI5

•

Portal Services

Data-oriented Appl. Logic


38

SAP HANA XS - Programming model

UI Rendering should be implemented completely in the Client    

Server generically supports data exposure (via OData) and static UI content provisioning Browser-based applications should use HTML5, especially SAP UI 5 Ability to leverage the Open Social component model Additional UI features provided by the Portal Services: navigation, page building, mash-ups, personalization

Server-side procedural logic in JavaScript Data-intensive logic to be pushed down to the index server processes via Native SAP HANA View Types or SQLScript Application artifacts are stored in the SAP HANA Repository


39

SAPUI5

SAPUI5 is an extensible JavaScript-based HTML5 browser rendering library for Business Applications.         

Supports RIA like client-side features based on JavaScript Supports an extensibility concept regarding custom controls Allows change of themes for customer branding in an effective manner Allows usage of own JavaScript and HTML Uses the jQuery library as a foundation Fully supports SAP Product standards Open AJAX compliant and can be used together with/uses other standard JS libs Can be used from various platforms (Java, ABAP, etc) Produced in a release independent code line to enable short shipment cycles


40

HANA Studio - Development Perspective SAP HANA XS Project Server-side - OData Service Definition

Project artifacts Server-side JavaScript - SAP HANA XS JavaScript API

Transportable project artifacts: i.e. roles, schemas, tables, table content, (DML & DDL), procedures © 2012 SAP AG. All rights reserved.

Client-side - HTML/JavaScript/SAPUI5

41

OData - Exposing Calculation Views 1 2

Server-side - OData Service Definition

http://server:port/odata/PlannedvsActual.xsodata/PlannedvsActualCalcView?$select=SALESORG,MATERIAL…..

3 4

Client-side - HTML/JavaScript / SAPUI5

REST your Models on SAP HANA XS http://scn.sap.com/community/developer-center/hana/blog/2013/01/22/rest-your-models-on-sap-hana-xs © 2012 SAP AG. All rights reserved.

42

Information Access (InA) toolkit - Search-based apps 1

Attribute View

Maintain Search Attributes 2

3

Client-side - InA

4


43

SAP HANA XS - Examples

Search-based Application built with the Information Access (InA) toolkit for HTML5

Custom-applications built with SAP UI5 toolkit


44

Thank you!

Werner Steyn Platinum Specialist SAP HANA Customer Solution Adoption (CSA) SAP Labs, LLC [email protected]

Reference Information SAP Customer Solution Adoption Know-How Webinars http://scn.sap.com/community/webinars/technology-rig-know-how SAP HANA SPS 05 What’s New http://help.sap.com/hana/hana_sps5_whatsnew_en.pdf SAP HANA Developer Guide http://help.sap.com/hana/hana_dev_en.pdf SAP HANA XS JavaScript Reference http://help.sap.com/hana/jsapi/index.html SAP HANA Predictive Analysis Library (PAL) reference http://help.sap.com/hana/hana_dev_pal_en.pdf SAP HANA Business Function Library (BFL) Reference http://help.sap.com/hana/hana_dev_bfl_en.pdf SAP HANA SQLScript Reference http://help.sap.com/hana/hana_dev_sqlscript_en.pdf SAP HANA Security Guide http://help.sap.com/hana/hana_sec_en.pdf REST your Models on SAP HANA XS http://scn.sap.com/community/developer-center/hana/blog/2013/01/22/rest-your-models-on-sap-hana-xs


46

SAP Notes SAP HANA Central Note https://service.sap.com/sap/support/notes/1514967 SAP HANA SPS 05 - Release Notes https://service.sap.com/sap/support/notes/1771591 Debugging User Authorization Errors https://service.sap.com/sap/support/notes/1809199 Analytical privilege does not work for shared attributes https://service.sap.com/sap/support/notes/1812023 Modeler behavior when adding objects https://service.sap.com/sap/support/notes/1783668 Activation error “user is not authorized” https://service.sap.com/sap/support/notes/1774476 SAP HANA XS: Restrictions / Development Perspective SP5 https://service.sap.com/sap/support/notes/1779803 Using Cardinality in SAP HANA Models https://service.sap.com/sap/support/notes/1801838


47


No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Excel, Outlook, PowerPoint, Silverlight, and Visual Studio are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, z10, z/VM, z/OS, OS/390, zEnterprise, PowerVM, Power Architecture, Power Systems, POWER7, POWER6+, POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System Storage, Storwize, XIV, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Informix, and Smarter Planet are trademarks or registered trademarks of IBM Corporation. Linux is the registered trademark of Linus Torvalds in the United States and other countries. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are trademarks or registered trademarks of Adobe Systems Incorporated in the United States and other countries. Oracle and Java are registered trademarks of Oracle and its affiliates. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.

Google App Engine, Google Apps, Google Checkout, Google Data API, Google Maps, Google Mobile Ads, Google Mobile Updater, Google Mobile, Google Store, Google Sync, Google Updater, Google Voice, Google Mail, Gmail, YouTube, Dalvik and Android are trademarks or registered trademarks of Google Inc. INTERMEC is a registered trademark of Intermec Technologies Corporation. Wi-Fi is a registered trademark of Wi-Fi Alliance. Bluetooth is a registered trademark of Bluetooth SIG Inc. Motorola is a registered trademark of Motorola Trademark Holdings LLC. Computop is a registered trademark of Computop Wirtschaftsinformatik GmbH. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, StreamWork, SAP HANA, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects Software Ltd. Business Objects is an SAP company.

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

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

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

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

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

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

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


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

48