DEV211 – Core Data Services:
Next
‐Generation Data Definition and Access
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 SAP’s 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.
Speakers
Bangalore, October 5 - 7
Raghuvira Bhagavan
Las Vegas, Sept 19 - 23
Christian Stork
Andreas Grünhagen
Barcelona, Nov 8 - 10
Christian Stork
Agenda
•
Introduction
•
CDS Views and modern Open SQL in detail
•
Associations
•
Extending views
•
Annotations
•
Working with CDS Views
•
CDS Table functions
•
Data Control Language (DCL)
Building Applications with ABAP Using
Agenda
•
Introduction
•
CDS Views and modern Open SQL in detail
•
Associations
•
Extending views
•
Annotations
•
Working with CDS views
•
CDS Table Functions
Before CDS and modern Open SQL …
1.
Semantic gap between task and SQL Code
Task: Get the id, name and the respective zip code of the home address for all
employees in org-unit
’Development’
SQL:
SELECT
e.id, e.name, a.zipCode
FROM
Employee e
INNER JOIN
OrgUnit u
ON
u.id = e.orgid
LEFT OUTER JOIN
Address a
ON
e.id = a.id
AND
a.type
= ’
HOMEADDR
’
WHERE
u.name = ’
Development
’.
Before CDS and modern Open SQL …
2.
SQL complexity leads to ABAP code in Application Server
Task: Due to the complexity of SQL, developers are using code instead of
SQL
Code:
SELECT
*
FROM
Employee
INTO TABLE
it_empl
WHERE
orgunit
=
4711.
“ I know 4711 is the
LOOP AT
it_empl
.
“ id of the dev unit
WRITE
it_empl-id
.
WRITE
it_empl-name
.
SELECT
*
FROM
Addresse
INTO TABLE
it_addrs
WHERE
id
=
it_empl-id
.
LOOP AT
it_addrs
.
IF
it_addrs-type
=
‘
HOMEADDR
’
.
WRITE
it_addrs-zipcode
.
Performance gap: nested loops and queries with many
round trips server - database
With CDS and modern Open SQL:
Concept layer and implementation layer move closer
Task: Get the id, name and the respective zip code of the home address for all
employees in org-unit
’Development’
CDS:
SELECT
id, name, homeAddress.zipCode
FROM
OrgUnit[ name = ’
Development
’ ].employees
Smaller semantic gap,
less code required
Next generation data definition and access for database centric applications
ABAP
CORE DATA SERVICES
</>
Common data model
Semantically rich
Declarative
Capture business intend
Reduced complexity
Extending SQL
Improved programming model
For all application domains
Rich set of build-in functions
Availability of CDS in SAP Platforms
SAP Business Suite EHP7 (Suite on HANA)
SAP NetWeaver 7.4 SP05
S/4HANA
SAP Business Warehouse 7.3
SAP HANA SPS6
A family of domain specific languages
Data modelling and retrieval
on a higher semantic level
Extends native SQL means for
Consume CDS entities via
Open SQL in ABAP
Fully transparent SQL
Define authorizations
Modelled and declarative
(Re)use classic
DDL
QL
DCL
Graph representation of a S/4HANA Development System
1
Data Model using the Association as Edges
12.239
CDS Views
22.847
Associations
724.656
Lines of Code
in CDS
Views
ABAP integration
SAP NetWeaver ABAP
(S)QL Engine
Li
fecyc
le
M
an
agemen
t
CDS ABAP
OPEN SQL
Leverage DDIC semantics
Infrastructure ensures Any-DB
support
Proven and consistent ABAP
Lifecycle Management and
extensibility
Highly reusable and extensible CDS
artifacts
CDS development environment
Eclipse based
Part of ABAP
Development tools
Development tools
Code completion
Element info
Quick-Fix function
Syntax highlighting
01
02
Supportability
Enhanced data preview
Dependency analyser
Activation graph
Active annotations view
Unit testing
Agenda
•
Introduction
•
CDS Views and modern Open SQL in detail
•
Associations
•
Extending views
•
Annotations
•
Working with CDS views
•
CDS Table Functions
Code push-down with CDS and modern Open SQL
SCENARIOS
Build in
SQL
functions
CDS Table
Functions
Push non trivial
logic to any SAP
supported DB
Use natively
implemented SAP
HANA-DB functions
CDS
Views and
Open SQL
CDS
Table
Functions
Open SQL: Modern syntax
Target: Introduce expression in Open SQL
SELECT
int_column1 – int_column2 FROM
db_table
INTO
(result1, result2)
WHERE
int_column2 + name = int_variable2.
What is selected?
What is “name”? Column or ABAP variable?
SELECT
int_column1, – int_column2 FROM
db_table
WHERE
int_column2 + @name = @int_variable2
INTO
(@result1, @result2).
leads to
Separate lists with commas
Escape host variables with @
INTO clause at the end
Old statements remain valid
!
Joins (CDS and Open SQL)
select
*
from
where
LastSaleDate~LastSoldDateTime
is null
“not sold at all
or
LastSaleDate~LastSoldDateTime < CurrentDateTime~FourWeeksAgoDateTime
into table @data(result).
Supported Join kinds:
•
Inner Join
•
Left Outer Join
•
Right Outer Join
SEPMRA_I_Product_E
as
Product
left outer join SEPMRA_P_OVW_DateTimeOffset
as
CurrentDateTime
on
CurrentDateTime~CurrentDateTime
is not null
left outer join
SEPMRA_P_OVW_LastSaleD000 as
LastSaleDate
Union (CDS and Open SQL)
@AbapCatalog.sqlViewName: 'SEPM_PEMPHIER1'
define view
SEPM_P_EmployeeHierarchy1
as select from
SEPM_I_OrganizationalUnit {
key
OrganizationalUnitUUID
as
NodeUUID,
ParentOrganizationalUnitUUID
as
ParentNodeUUID,
OrganizationalUnit,
cast
(''
as
abap.char(10) )
as
Employee,
'19900101
' as
ValidityStartDate,
'99991231'
as
ValidityEndDate
}
select from
SEPM_I_EmployeeOrgAssignment
{
key
EmployeeOrgAssignmentUUID
as
NodeUUID,
OrganizationalUnitUUID
as
ParentNodeUUID,
''
as
OrganizationalUnit,
_Employee.Employee,
AssignmentStartDate
as
ValidityStartDate,
AssignmentEndDate
as
ValidityEndDate
}
Supported Union kinds:
•
UNION ALL
•
UNION ( DISTINCT )
CASE (CDS and Open SQL)
@AbapCatalog.sqlViewName: 'sepmraovwlastsld'
define view
SEPMRA_C_OVW_LastSaleDate
as select from
SEPMRA_I_Product_E
as
Product
{
key
Product.Product,
-- enforce the default placeholder image as initial value for property ProductPictureURL:
}
where
LastSaleDate.LastSoldDateTime
is null
-- not sold at all
or
LastSaleDate.LastSoldDateTime < CurrentDateTime.FourWeeksAgoDateTime
case
Product.ProductPictureURL
when
''
then
'/sap/public/bc/NWDEMO_MODEL/IMAGES/no_photo.jpg'
else
Product.ProductPictureURL
end
as
ProductPictureURL
Session Variables (CDS) and concat (CDS and Open SQL)
@AbapCatalog.sqlViewName: 'SEPMRASOCUSTOMER'
define view
SEPMRA_C_SalesOrderCustomer
as select from
SEPM_I_BusinessPartner_E
as
BusinessPartner
{
key
BusinessPartner.BusinessPartner,
)
as
Address
}
concat
(
concat
(
concat
(
concat_with_space
( BusinessPartner.StreetName, BusinessPartner.HouseNumber, 1 ),
'\n'
),
concat
(
concat_with_space
( BusinessPartner.PostalCode, BusinessPartner.CityName, 1 ),
'\n'
)),
BusinessPartner._Country._Text
[
1: Language =
$session.system_language ]
.CountryName
Views with Parameters (CDS Definition)
@AbapCatalog.sqlViewName:'SEPMRAIORGAKD'
define view
SEPMRA_I_OrgAsgmntForKeyDate
as select from
SEPM_I_EmployeeOrgAssignment_E
as
Assignment
association[
0..1
] to
SEPMRA_I_Employee
as
_
Employee
on $projection.
Employee = _Employee.Employee
association[
0..1
] to
SEPMRA_I_OrganizationalUnit
as
_
OrganizationalUnit
on
$projection.
OrganizationalUnit = _OrganizationalUnit.OrganizationalUnit
{
key
Assignment.Employee,
key
Assignment.ValidityStartDate
}
where
Assignment.ValidityStartDate <=
Assignment.ValidityEndDate
>=
with parameters
p_keyDate: abap.dats
$parameters.
p_keyDate
and
$parameters.
p_keyDate
Views with Parameters (Usage)
@AbapCatalog.sqlViewName:'DEV211_V_PARAMS'
define view
DEV211_E_PARAMS
with parameters
@Environment.systemField: #SYSTEM_DATE
p_keyDate: abap.dats
as select from
SEPMRA_I_OrgAsgmntForKeyDate( )
{ * }
p_keyDate :
$parameters.
p_keyDate
select from
DEV211_E_PARAMS( )
fields
*
into table @data(result)
p_keyDate =
‘20160926‘
select from
DEV211_E_PARAMS( )
“use the “default value” sy-datum
fields
*
Built in SQL functions and expressions
Date / Time
Conversion
Generic
Arithmetic
Build in
SQL
functions
Supported functions and expressions for any DB
Conversion
Unit conversion
Currency conversion
Cast
Hex2Bin
Bin2Hex
Decimal shift
Arithmetic
Abs
Ceil
Div (integer)
Division (dec)
Floor
Mod
Round
Generic
Simple- /
searched-case
Coalesce
X Y ZString
Concat
Instr
Left / right
Length
Lpad / rpad
Ltrim / rtrim
Replace
Date / Time
Days between
Add days
Add month
Current tmstp
Date is valid
Tmstp is valid
Seconds btw. tmstps
Add seconds
Agenda
•
Introduction
•
CDS Views and modern Open SQL in detail
•
Associations
•
Extending views
•
Annotations
•
Working with CDS views
•
CDS Table Functions
SQL Joins as Means for Combining Fields from Two or More Tables
define view
zddl_join
as select from
SEPM_I_SalesOrderItem
as
I
left outer join
SEPM_I_Currency
as
C
on
I.TransactionCurrency = C.Currency
{ … }
How we work with SQL joins
Not reusable
Do not contribute to the data model
!
Associations as reusable relationships
define view
zddl_assoc
as select from
SEPM_I_SalesOrderItem
association [0..1] to
SEPM_I_Currency
as
_Currency
on $projection.
TransactionCurrency
=
_Currency.Currency
{
_Currency.CurrencyISOCode,
_Currency
}
Define Association with Cardinality
Expose Association for reuse
Use Association => select a column
define view
zddl_use_assoc
as select from
zddl_assoc
{
_Currency._Text[ language =
'EN'
] .CurrencyLongName
}
Reuse association => select another
column
Used CDS Associations - Translation into Joins
Used (!) associations are implicitly translated into SQL joins
Where
Clause
Projection
List
From Clause
Having
Clause
Group by
Clause
Left
Outer
Join
Inner
Join
Filter Expressions
define view
zddl_bpa
as select from
SEPM_I_BusinessPartner
{
_Address
[1:
Country
=
'DE'
].
CityName
}
Filter expression with cardinality
annotation
Restrict the result set when using [0..n] or [1..n] associations
Change the cardinality, e.g. in the WHERE clause only [0..1]
associations are allowed
Same filters can be configured to only generate a single join by
using annotations
Use associations in Open SQL from ABAP
SELECT FROM
sepm_i_businesspartner
FIELDS
\_Address-PostalCode
INTO @DATA(result).
Association with leading backslash
Associations can directly be used in Open SQL when using
select on a CDS entity
Agenda
•
Introduction
•
CDS Views and modern Open SQL in detail
•
Associations
•
Extending views
•
Annotations
•
Working with CDS views
•
CDS Table Functions
Hierarchy of views and extension support
Basic View
View on View
Consumer
Extensions
C1
V1
B1
&
Hierarchical view-on-view
concept
Optimized result-sets with
minimum data transfer
Append additional columns,
arithmetic expressions or
literals to result set
Consumer
Consumer
C2
C3
View on View
View extends
extend view
SEPM_I_BusinessPartner
with
zddl_bp_extend
{
_Address
.
cityname
}
Extending view Business Partner
Adding new field via association
Views can be extended to add additional fields
Extend is stored and transported in separate DDL source
Even „union“ and „group by“ clauses can be extended
i
Agenda
•
Introduction
•
CDS Views and modern Open SQL in detail
•
Associations
•
Extending views
•
Annotations
•
Working with CDS views
•
CDS Table Functions
Common basis for domain-specific frameworks
CDS
ANNOTATIONS
Enrich meta model with consumer-specific vocabularies
Reusable and unified view model for all use cases
Enabling flexible usage in different contexts
Annotation query-api and annotation framework
Analytics
Business
Logic
Planning
OData
BI-Tools
Search
Annotations example
@AbapCatalog.sqlViewName : ‘ZDDL_ANNOTATIONS’
@EndUserText.label: 'Business Partner with street'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view
zddl_bpa
as select from
SEPM_I_BusinessPartner
{
@Semantics.address.street: true
_Address
[1 :
Country
=
'DE'
].
StreetName
}
Define access control means
Domain specific annotation
Some ‘core’ annotations
Take Away Annotations
•
Separation of metadata and core data model
•
Allow applications to add their specific view
properties to the Data Model
•
Annotations can be defined for view header,
columns and parameters
Annotation allows
independency from Core Data Model
And can be defined at many view
parts!
Agenda
•
Introduction
•
CDS Views and modern Open SQL in detail
•
Associations
•
Extending views
•
Annotations
•
Working with CDS views
•
CDS Table Functions
Understanding SQL Processing Steps
SQL string
Logical Plan Rewriting Logical Plan Enumeration Physical Algorithm Enumeration Cost-based Best Plan SelectionExecution
Plan
Parsing from clause where clause select clause select * from T1,T2,T3,T4 where T1.A=T2.A and T2.B=T3.B and T3.C=T4.C and T1.A = 100 T1 T2 J1 T3 J2 T4 J3 J4 J5 J7 J9 J8 J10 J6 T1 T2 J1 T3 J2 T4 J3 F T2 T1 J1 T4 J2 T3 J3Execution Plan
= Cached Plan
= Physical Plan
Result
Visualize via XML Disable/enable/ visualize rewrite rules (what-if analysis) Visualize via XML Change to alternative plans and execute(what-if analysis)
Visualize via XML with execution
statistics
Fix application (SQL)
Fix data model
(hot-spot analysis)
Fix optimizer’s decision
(via SQL hint)
1
2
3
HANA SQL Processor
Single logical plan
Logical plan forest
(= plan search space=alternative plans)
Runtime Compilation
