DMM360 Advanced Data Modeling in

SAP HANA

Public

Disclaimer
The information in this presentation is confidential and proprietary to SAP and may not be disclosed without the permission of
SAP. Except for your obligation to protect confidential information, this presentation is not subject to your license agreement or
any other service or subscription agreement with SAP. SAP has no obligation to pursue any course of business outlined in this
presentation or any related document, or to develop or release any functionality mentioned therein.
This presentation, or any related document and SAP's strategy and possible future developments, products and or platforms
directions and functionality are all subject to change and may be changed by SAP at any time for any reason without notice.
The information in this presentation is not a commitment, promise or legal obligation to deliver any material, code or functionality.
This presentation 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. This presentation is for informational
purposes and may not be incorporated into a contract. SAP assumes no responsibility for errors or omissions in this
presentation, except if such damages were caused by SAPs intentional or gross negligence.
All forward-looking statements are subject to various risks and uncertainties that could cause actual results to differ materially
from expectations. Readers are cautioned not to place undue reliance on these forward-looking statements, which speak only
as of their dates, and they should not be relied upon in making purchasing decisions.

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

Public

Speakers
Las Vegas, Sept 19 - 23

Bangalore, October 5 - 7

Barcelona, Nov 8 - 10

Srinivas Rapthadu

Vaishnavi Dharmaraja

Yves Augustin

Werner Steyn

Rajesh Panditi

Christoph Morgen

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

Public

Agenda
Overview Whats New in SAP HANA Modeling SPS12
SAP HANA Studio Calculation View Modeling Enhancements
New capabilities and enhancements
Migration enhancements

New SAP Web IDE for SAP HANA Graphical Development Tools
Graphical CDS Editor
Calculation View Development
Lineage and Impact Analysis
Exercises

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

Public

SAP HANA SPS12 Whats

New in SAP HANA Modeling
SAP HANA View Modeling Enhancements Overview

Public

SAP HANA SPS12 Whats New in SAP HANA Modeling

SAP HANA View Modeling Enhancements Overview
SAP HANA Studio-based Modeling

SAP Web IDE for SAP HANA-based Modeling

Calculation View Editor Enhancements

Calculation View Editor

Hierarchies unassigned member handling support

Data Sources SQL (CDS) views from tenant databases
JOIN modeling enhancements temporal join,
Spatial expression in filters

Support for
major and most
capabilities

Graphical Core Data Services

(CDS) editor
SAP PowerDesigner
Enterprise Architecture / HANA Development
PowerDesigner support for CDS artifacts for enterprise
architecture top down modeling

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

Cross object Impact Analysis and Data Lineage

Public

SAP HANA SPS12 Whats New in SAP HANA Modeling

SAP HANA Model Editor Enhancements Overview
Calculation View Editor Enhancements
Data Source Enhancements
Support SQL views from tenant database
Catalog table function support
Temporal Join support
Currency conversion on inner aggregation nodes
Hierarchies enhancement
Unassigned / Null member handling support
Composite Keys for Parent-Child Hierarchies

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

Refactoring enhancements
Aggregation node measure conversion support
Spatial Expressions
Support functions and predicates in filter expressions
Calculated Columns of type geometry / spatial expression
support
Semantic Node reorganizing column order

Public

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Data Source Enhancements
Data Source Enhancements
Search for data sources
in remote tenant db
Multitenant database container remote data source to Calculation Views
Remote tables (SPS10) and remote Calculation Views (SPS11) have been supported
Enhanced support for remote SQL-views (e.g. CDS generated) as data sources
Warning is issued if remote Calculation View is of type scripted or embeds a SQLScript Table Function, as
both not yet supported.
Catalog table functions now supported as data sources
Icon indicating catalog object-type

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

Public

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Join Enhancements
Join Enhancements in Calculation Views
Temporal Join-conditions are supported
In Calculation View join-nodes (standard join and starJoins) based on Inner-Joins
Support for additional temporal join conditions
with VALID_FROM and VALID_TO properties

Fact

Temporal Attributes

Contact_ID

Contact_ID

DATE

DATE_TO

Amount

DATE_FROM

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

Public

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Union Enhancements
Union-Node Enhancements in Calculation Views
Union Pruning Enhancement
Technical Union-Source Name,
maintained by applications
like Data Lifecycle Manager
is displayed in properties

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

Public

10

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Currency Conversion Enhancement
Currency Conversion Enhancements
Support for conversion-measures
on lower aggregation-nodes
You wanted to maintain currency and unit conversion
at an intermediate level of your calculation view,
thus e.g. maintain different flow operations within the
view for differently converted measures

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

Public

11

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Redesigning a Scenario
Scenario Redesign Replacing Nodes or Data Sources
When redesigning an existing view, you may require
Insert an aggregation-node / switch a projection-node to an aggregation-node
By default in aggregated / switched to aggregation-node measures are not automatically changed to
aggregated column
o Workaround has been a delete / re-add as aggregated column + propagate again, thus however, semantic information of
the measure throughout the flow might be get lost

Now, convert to aggregated column is supported

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

Public

12

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Semantic Node
Semantic Node Enhancement
Managing / reorganize column order
Instead of only using
to maintain the order of column
Now Drag-and drop of multiple selected columns
is supported
Order is kept of course for all consuming
activities incl. data preview

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

Public

13

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Full-Text Search Metadata
Full-Text Search Metadata
Implicit BIMC text search metadata
Calculation Views based on columns which are inherently
text-/fuzzy searchable or where a full-text index has been defined
Populate BIMC_DIMENSION_VIEW-metadata view
to notify BI clients or applications, that a view column is mapped to a table column, which is backed by a text
index / is fuzzy searchable

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

Public

14

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Spatial Expressions
Spatial Expression Support
Calculated Column using spatial expressions
Allow calculated columns of type geometry
Allow all spatial functions in the expressions
(not just the predicates)

Filter using spatial expressions

Spatial filter expressions

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

Public

15

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Hierarchies
Hierarchy Enhancements
NotAssigned- and NULL value handling in hierarchies
NULL values as dimension-key in fact data (if present) would
typically be ignored in calculations

NotAssigned members in a hierarchy are dimension-keys in

fact data, which are not present in the hierarchy data
For both cases, Hana hierarchies can handle these using a
pseudo member that adds a node in the hierarchy to
encompass values in fact tables to which the hierarchy is
related that have no corresponding master data values in the
corresponding hierarchies.
For level- and parent-child hierarchies
Only supported for facts joined with left outer joins to
dimensions

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

Public

16

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Hierarchies
Parent-Child hierarchy example
NotAssigned- and NULL value handling in hierarchies
Multidimensional reporting client,
here MDX-based client

Employees FOO and the NULL

employee have booked values for
SALES but do not exist in the
hierarchy source data.

N.a. maybe a root member itself

or a children of the all member;

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

Public

17

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Hierarchies
Level hierarchy example
NotAssigned- and NULL value handling in hierarchies
Multidimensional reporting client,
here MDX-based client

Products FOO and the NULL

employee have booked values for
SALES but do not exist in the
hierarchy source data.

Drill down across all levels to detail;

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

Public

18

SAP HANA SPS12 - Modeling Enhancements

Calculation View Enhancements Hierarchies
Hierarchy Enhancements
Full composite key support for Parent-Child Hierarchies
Composite keys are required in cases where there is no single
field in a hierarchy that provides uniqueness for master data
values of a hierarchy.
o The most common case is in organization structures where a
CostCentre alone is not unique only the combination of the
CostCenter and its ControllingCostCenter together provide a unique
value that can be used to define and build a hierarchy.

Joins between facts and dimension must include all

composite hierarchy key columns

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

Public

19

Converting Calculation Views

Attribute-, Analytic- and Scripted Calculation View conversion to graphical Calculation View

Public

SAP HANA SPS11 - Modeling Enhancements

Converting all SAP HANA Views to Calculation Views
Going forward with Graphical Calculation Views
Graphically modeled Calculation Views target to unify all SAP HANA View capabilities,
with respect to functionality as well as performance

Attribute Views
Analytic Views
Script-based CVs
CVs
Modeled Analytic
Privileges

Graphical Calculation Views

Table Functions
SQL-based Analytic Privileges

Convert using
the HANA Studio Migration Tool

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

HDI-based design-time artefacts

Calculation Views
Table Functions
SQL-based Analytic Privileges

Migration using a separate DU

Migrations tool, target to help migrations
of XSC to XSA applications

Public

21

SAP HANA SPS11 - Modeling Enhancements

Converting all SAP HANA Views to Calculation Views
Going forward with Graphical Calculation Views
Converting your SAP HANA Views
Dimension-Type graphical Calculation Views replacing Attribute-Views
Cube-/StarJoin-Type graphical Calculation Views replacing Analytic-Views
Table Functions as Data Sources in graphical Calculation Views
replacing Scripted-Calculation Views
SQL-based Analytic Privileges replacing classic modeled Analytic Privileges

With SPS11 a migration utility is initially provided

to support the conversion process

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

Public

22

SAP HANA SPS12 - Modeling Enhancements

Converting all SAP HANA Views to Calculation Views
Conversion enhancements with SPS12
Converting your SAP HANA Views

N:m cardinality in joins can be migrated to Calculation View

Temp joins can be migrated
Views generated by BW containing specific data source references
Analytic view migration shows dependent object as option (to simulate impact on referenced objects)
Keep assigned privileges to SQL roles and users during migration
o Keep assigned privileges to SQL roles and users (view & privilege migration)
o Adjust privileges on star join calc views and add dimension calc views as secured model

Activation can be initiated by migration-utility as the last (optional) step

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

Public

23

SAP HANA SPS12 - Modeling Enhancements

Converting all SAP HANA Views to Calculation Views
Conversion enhancements with SPS12
Migrate individual Views
Selective migration of selected views, not just packages

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

Public

24

SAP HANA SPS12 - Modeling Enhancements

Converting all SAP HANA Views to Calculation Views
Conversion enhancements with SPS12
Privilege assignment as part of the migration activity
MIGRATION Keep assigned privileges to SQL roles and users

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

Public

25

SAP Web IDE for SAP HANA

Graphical Development Tools
Introduction and Overview to the new Graphical Development Tools
within the SAP Web IDE for SAP HANA

Public

SAP HANA Graphical Development Tools Innovation Path

SAP HANA Studio Modeler Eclipse-perspective
Primarily focused on HANA View modeling artifacts
SAP HANA Studio Development Eclipse-perspective
Support for all application and database design-time
artifacts and providing additional repository functionality
E.g. Script-based CDS development, flowgraphs, .
SAP HANA Web-based Development Workbench
Database & Application Development
Initial releases of graphical development tools

SAP Web IDE for SAP HANA

New Development tooling platform for
SAP HANA, initially delivered in SPS11*
major release with SAP HANA SPS12
All development tools
(re-) implemented as
XS Advanced applications
leveraging new UI frameworks of the
SAP Web IDE for SAP HANA and
commonly building on the new
HANA deployment infrastructure.
Web-based graphical editors
Calculation Views
CDS entities
Data flows
Lineage and impact analysis
*SAP Note: 2233866 - SAP HANA XS Advanced Model SPS11

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

Public

27

SAP HANA SPS12 Whats New in SAP HANA Modeling

SAP HANA XS Advanced View Modeling Enhancements Overview
SAP Web IDE for SAP HANA-based Modeling
Calculation View Editor

Graphical Core Data Services (CDS) editor

Support for
major and most
capabilities

Cross object Impact Analysis and Data Lineage

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

Public

28

SAP HANA SPS12 - Modeling Enhancements

SAP HANA XS Advanced Calculation View Editor Getting Started
Setting up your development environment
in the SAP Web IDE for SAP HANA

Creating a MTA (Multi-Target Application)

Project

Creating a HDB module

An isolated HANA Deployment Infrastructure

Container (~ isolated schema as your HANA
development workspace)

Specifying the namespace used for

all development artefacts of this project

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

Public

29

SAP HANA SPS12 - Modeling Enhancements

SAP HANA XS Advanced Calculation View Editor Getting Started
Setting up your development environment
in the SAP Web IDE for SAP HANA

Building your HDB Module

Physically creates the HDB module (the database

schema and all configurations)
Activates all content (under \src) and creates
all runtime artefacts

webIDE console log

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

Public

30

SAP HANA SPS12 - Modeling Enhancements

SAP HANA XS Advanced Calculation View Editor Getting Started
Creating a Calculation View
in the SAP Web IDE for SAP HANA

Graphical development of HANA Calculation Views

Graphical Calculation Views as the overall and unified
view modeling object*
Building on new usability approaches: mapping based
data and column-flow fewer dialogs, instead more real
estate and focused view in the current activity context
when editing Calculation View details
Analytic- and Attribute-View modeling have been
included into StarJoin / Dimension Calculation Views
SQLScript table functions can be used to embed
script-based calculation

*With SAP HANA SPS12, the Calculation View Editor is not yet functional
eqivalent to the SAP HANA Studio based modeling editors.
2016 SAP SE or an SAP affiliate company. All rights reserved.

Public

31

SAP HANA SPS12 - Modeling Enhancements

SAP HANA XS Advanced Calculation View Editor Getting Started
Setting up your development environment
in the SAP Web IDE for SAP HANA

Project setup and configuration files

Menu > View > show hidden files
shows hidden folders and files

.hdiconfig > lists HDI registered development

artefacts deployed at build time
.hdinamespace > configures the package
namespace for your development objects*.
All development objects will be preceeded with the
namespace (replacing old repository content
structure), e.g.
[Link] configuration file specifies service
modules, their configuration and dependencies to
other services
*As we no longer use the HANA Repository to hold design-time objects, this file
provides the same service as the folder structure in the repository used to.
2016 SAP SE or an SAP affiliate company. All rights reserved.

Public

32

SAP HANA SPS12 - Modeling Enhancements

SAP HANA XS Advanced Calculation View Editor Data Sources
Calculation View Data Sources

Data Sources within your HDI environment

Design-Time objects local to your development
container
Tables, Table Functions, SQL Views, Calculation
Views are supported inputs
This incl. Smart Data Access virtual table definition
(hdbvirtualtable)
Synonym definitions are required to model using data
sources outside of your current development
container
MDC remote data sources are not yet supported

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

Public

33

SAP HANA
Graphical CDS Editor
Introduction and Overview to the new Graphical CDS Editor
within the SAP Web IDE for SAP HANA

Public

SAP HANA SPS12 New Features and Enhancements

SAP HANA XS Advanced Graphical CDS Editor Overview
Graphical development of CDS objects

Graphical CDS Editor

Supported capabilities incl.

Contexts
Entities, incl. HANA native types
Structured Types
Indexes, incl. Search/Full Text Indexes
Table Groupings, Partitioning
Associations

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

Public

35

SAP HANA SPS12 New Features and Enhancements

SAP HANA XS Advanced Graphical CDS Editor
CDS development editor choices

Graphical and Script-based CDS editors

Graphical editor
opens any hdbcds-file
Persist any nongraphically
supported
capability

Script-based editor
opens any
graphically
developed
hdbcds-file

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

Public

36

SAP HANA SPS12 New Features and Enhancements

SAP HANA XS Advanced Graphical CDS Editor
Graphical development of CDS objects

Graphical CDS Editor

Example

Context
Inner Entities and Types

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

Public

37

SAP HANA SPS12 New Features and Enhancements

SAP HANA XS Advanced Graphical CDS Editor
Graphical development of CDS objects

Graphical CDS Editor

Example

External entities / references

Associations (here unmanaged)

Grey objects are, readonly entiies, higherlevel or external

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

Public

38

DMM360 - Exercises
Overview

Public

CYCLIST
1:1 ATM Alerts

SNOWBOARDER
Detect unusual transactions

Debugger

Visualize Plan

Calculated Columns

2:1 Column Data Masking

Hide columns based on

user-rights

Stored procedure

Input Parameters

SQL Expressions

Count number transactions

Restricted Columns vs
using Unions

Conditional Exception
Aggregation

2:2 Rolling Sales/Slowly Changing Dimensions

2:3 Dynamic Time based Analysis

List most valuable players

Rank node

Value Help Prompts

Visualize Plan

Current Quarter sales vs

Previous Year Quarter Sales

SQL Table functions

Time series data

Current year sales vs historical

sales

Smart Data Access

Pruning Configuration Table

Implicit/Explicit Input pruning

Derived Input Parameters

20 Minutes / 49 Steps

3:2 State of the Union Speeches

Analyze linguistic differences

Text Analysis

Counters

Union

Calculate rolling sales by month and product

Dynamic Join, Temporal Join

Transparent / Keep flag

Helper materialized table

Debugger
20 Minutes / 74 Steps

15 Minutes / 32 Steps

1:3 Soccer Player Rankings

3:1 Hot vs Cold Data

10 Minutes / 26 Steps

10 Minutes / 33 Steps

1:2 ATM Activity

ASTRONAUT

20 Minutes / 40 Steps

3:3 UFO Sightings

Analyze and predict UFO sightings

GEO Spatial

Predictive Analytics

SQL Table functions

SQL Procedures

20 Minutes / 44 Steps

35 Minutes / 106 Steps

20 Minutes / 45 Steps

1:4 CO-PA Analysis

Analyze Planned Vs Actual Sales

Unions, Star-Join, Text Join

Multi-Language Support

2:4 Restricted Sales Report

20 Minutes / 38 Steps

1:5 Basket Analyses

Analyze frequently ordered

product combinations

Exception aggregation

Multi-level aggregation

Currency conversion
20 Minutes / 20 Steps

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

Restrict users to their assigned

regional sales areas

SQL Analytical Privileges

Restricting shared dimensions

Debug authorization errors

Role assignment
20 Minutes / 53 Steps

2:5 Dilbert HR Org Chart

Calculates managers total sales

and their employee sales

SQL Analytical Privileges

SQL Hierarchies

Hierarchy Prompts, Roles

Not Assigned members

20 Minutes / 58 Steps

3:4 FIFO Inventory Management

Calculate profitability using
First-In-First-Out

Advanced SQL

Table functions

Time series
15 Minutes / 23 Steps

3:5 Non-cumulative Daily stock balance

Calculates the daily stock balance per
product based on inflow and outflow

Advanced SQL knowledge

Cross-joins, dynamic exploding &

imploding
20 Minutes / 36 Steps

Public

40

SAP TechEd Online

Continue your SAP TechEd
education after the event!
Access replays of

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

Keynotes
Demo Jam
SAP TechEd live interviews
Select lecture sessions
Hands-on sessions

Public

41

Further information
Related SAP TechEd sessions:
DMM269 - End-to-End Model Performance Analysis in Native SAP HANA Platform
DMM203 - New Approaches for Data Modeling with SAP HANA
DMM260 - Introduction to Data Modeling in SAP HANA
DMM162 - SAP HANA Data Warehousing: Build and Run a SQL Data Warehouse on SAP HANA
SAP Public Web
[Link]
[Link]

SAP Education and Certification Opportunities

[Link]/education

Watch SAP TechEd Online

[Link]/online

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

Public

42

Feedback
Please complete your
session evaluation for
DMM360

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

Contact information:
Werner Steyn
HANA & Analytics Competence Center
[Link]@[Link]

Public

43

 2016 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 or an SAP affiliate company.
SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE (or an SAP affiliate
company) in Germany and other countries. Please see [Link] for additional trademark information and notices.
Some software products marketed by SAP SE and its distributors contain proprietary software components of other software vendors.
National product specifications may vary.
These materials are provided by SAP SE or an SAP affiliate company for informational purposes only, without representation or warranty of any kind, and SAP SE or its
affiliated companies shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP SE or SAP affiliate company 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.
In particular, SAP SE or its affiliated companies have no obligation to pursue any course of business outlined in this document or any related presentation, or to develop
or release any functionality mentioned therein. This document, or any related presentation, and SAP SEs or its affiliated companies strategy and possible future
developments, products, and/or platform directions and functionality are all subject to change and may be changed by SAP SE or its affiliated companies at any time
for any reason without notice. The information in this document is not a commitment, promise, or legal obligation to deliver any material, code, or functionality. All forwardlooking statements are subject to various risks and uncertainties that could cause actual results to differ materially from expectations. Readers are cautioned not to place
undue reliance on these forward-looking statements, which speak only as of their dates, and they should not be relied upon in making purchasing decisions.

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

Public

44