In this second part of SAP S/4 Embedded Analytics multi-blog series(First part: SAP S/4 HANA Embedded Analytics https://blogs.sap.com/2017/06/29/sap-s4hana-embedded-analytics), we cover fundamental concepts of Core Data Services (referred to as CDS), a core component of SAP S/4 HANA Embedded Analytics with detailed examples.
In the next blog, we will cover the Implementation and Activation of Embedded Analytics with SAP S/4 HANA.
The Core Data Services (CDS) offers domain-specific languages and services to define and consume semantically rich data models.
Core Data Services at a Glance
Figure1: Building Blocks
*************************************************************************
Figure 2 shows the complete life cycle of CDS View from the creation to the consumption.
Step 1 : Creation of CDS view.
Step 2 : Activation of a DDL source.
Step 3 : Consumption of CDS view using Open SQL.
Figure 2: Life Cycle of CDS View
****************************************************************************
Annotations enrich CDS data models with additional metadata. They begin with @
There are two types of annotations:
****************************************************************************
ABAP CDS Views can only be created using the ABAP in Eclipse plug-in IDE(Integrated Development environment).
Download from https://tools.hana.ondemand.com/<eclipse version>
Eclipse versions are planetary objects, mars, luna, keplar, etc.
If using HANA Studio, use Help->About HANA Studio to get your Eclipse version number.
How to connect to HANA system to start the development?
Create an ABAP Project
Connection to an ABAP backend system
Favorite Packages:
Generating Some data
*****************************************************************************
The SELECT statement defines a query performed on the data sources specified in data_source for a CDS view. Possible data sources are database tables defined in ABAP Dictionary, classic views, or other CDS views.
Syntax:
SELECT [DISTINCT]
Variant1
{ select_list
FROM data_source
[association1 association2 ...] }
or
Variant2
{ FROM data_source
[ association1 association2 ...]
{select_list} }
[clauses]
WHERE
Both forms of the code have the same semantics. Curly brackets { } must be used in the second variant to set select_list. The optional additions clauses are SELECT clauses that enable conditions, groupings, and union sets to be specified or created.
Sample Code where all the columns from the database table are selected.
Annotations
@AbapCatalog.sqlViewName: 'ZISFLIGHT' - Database view in ABAP dictionary that is generated at activation
@EndUserText.label: 'Sample Demo for sflight' - Defines a human-readable text that is displayed.
Sample Code
@AbapCatalog.sqlViewName: 'ZISFLIGHT'
@EndUserText.label: 'Sample Demo for sflight'
define view ZI_SFLIGHT as select from sflight
{ * }
Selecting Columns in CDS View
This will be comma separated list of column names. This includes table aliases and key fields. CDS View key fields need not be same as of the underlying data source.
Sample Code 1
@AbapCatalog.sqlViewName: 'ZISFLIGHT'
@EndUserText.label: 'Sample Demo for sflight'
define view ZI_SFLIGHT as select from sflight as sf
{
key sf.fldate as Flightdate,
key sf.connid as Flightno,
key sf.carrid as FlightCarrierID
}
**************************************************************************
Dictionary Representation: When activating a CDS view, the following objects are created in the ABAP Dictionary a shown in Figure 3.
Figure 3: CDS view Building Architecture
Below sample code select Distinct Columns and include where clause in CDS View.
Sample Code 2
@AbapCatalog.sqlViewName: 'ZISFLIGHT'
@EndUserText.label: 'Sample Demo for sflight'
define view ZI_SFLIGHT as select distinct from sflight as sf
{
key sf.fldate as Flightdate,
key sf.connid as Flightno,
key sf.carrid as FlightCarrierID,
sf.planetype
}
where sf.planetype='747-400'
Dictionary Representation of above sample code is shown in Figure 4. Transaction SE11 can be used to check the DDL SQL View and DDL Source.
Figure 4: Dictionary Representation of CDS Entity and Database View
Right click on DDL source and Expand “Open With” as depicted in Figure 5.
Figure 5 Options Available under “Open With”
Active Annotations View
The CDS annotations that are used for an active CDS view are displayed in the Active Annotations view as shown in Figure 6.
In the Active Annotations view, following columns are displayed:
Figure 6: Example of Active Annotations view
Dependency Analyzer:
Use the Dependency Analyzer to investigate which database objects (such as CDS database views, database tables, database views, and table functions) are used in your CDS view. In addition, it helps you to understand SQL dependencies and complexity. The SQL Dependency Tree tab displays dependencies as hierarchy as shown in Figure 7.
Figure 7: Example of Dependency Analyzer
Graphical Editor
The CDS Graphical Editor provides a read-only view containing CDS entities, user-defined types, and the relationships between entities in graphical representations as shown in Figure 8.
Figure 8: Example of Graphical Editor
***************************************************************************
Rich Set of Built-in SQL functions for calculations, aggregation and grouping etc are available for use in CDS view.
There are couple of Built-in Database Functions (SQL and Special Functions) managed by ABAP Dictionary which can be specified in the Select list of a CDS View and in operand positions. Below shows sample Code for case and arithmetic expressions
Sample Code 3
@AbapCatalog.sqlViewName: 'ZISFLIGHT1'
@EndUserText.label: 'Sample Demo for case expression'
define view ZI_SFLIGHT1 as select distinct from sflight as sf
{
key sf.connid as Flightno,
key sf.carrid as FlightCarrierID,
sf.planetype,
sf.price,
case sf.connid
when '0017' then ( sf.price + 500 )
when '0790' then ( sf.price + 200 )
when '0408' then ( sf.price + 100 )
else ( sf.price + 300 )
end as final_price
}
Figure 9 shows Output of Sample Code 3.
Figure 9 Output
Sample Code for unit and currency conversion.
Sample Code 4
@AbapCatalog.sqlViewName: 'ZISFLIGHT2'
@EndUserText.label: 'Sample Demo for unit and currency coversion'
define view ZI_SFLIGHT2
with parameters
to_currency : abap.cuky( 5 )
as select distinct from sflight as sf
inner join spfli as _spfli on sf.carrid = _spfli.carrid
{
key sf.carrid as carrier,
sf.price as amount,
currency_conversion(
amount => price,
source_currency => currency,
target_currency => :to_currency,
exchange_rate_date => fldate,
error_handling => 'SET_TO_NULL'
) as convert_amount,
_spfli.distance as distance,
round(unit_conversion(
quantity => _spfli.distance,
source_unit => _spfli.distid,
target_unit => cast( 'KM' as abap.unit( 3 )),
error_handling => 'SET_TO_NULL'
), 3) as convert_distance
}
where _spfli.distid = 'MI'
Input parameter is passed in Figure 10 and Figure 11 shows Output of Sample Code 4
Input 10 Input Parameter
Figure 11 Output
************************************************************************
Parameter types can be predefined or data element. This view also shows how to associate input parameters with ABAP system fields. Parameters can be accessed by :<param_name> or $parameters.<param_name>.
Environment Annotation: Below parameter is specified with default value sy-mandt. No explicit actual parameter can be assigned to an input parameter to which the system field sy-mandt was assigned using #CLIENT. Open SQL always passes (implicitly) the value of sy-mandt
@Environment.systemField : #CLIENT
Sample Code 5
@AbapCatalog.sqlViewName: 'ZISFLIGHT3'
@EndUserText.label: 'Sample Demo for input parameters'
define view ZI_SFLIGHT3
with parameters
@Environment.systemField : #CLIENT
p_mandt : syst_mandt,
p_carrid : s_carr_id
as select from sflight as sf
{
key sf.fldate as Flightdate,
key sf.connid as Flightno,
key sf.carrid as FlightCarrierID,
sf.planetype
}
where
sf.mandt = :p_mandt
and sf.carrid = :p_carrid
Figure 12 enter the Input Parameter P_CARRID and execute to display the result set which is shown in Figure 13.
Enter the Input parameters:
Figure 12 Input Parameter
Figure 13 shows Data Preview in HANA Studio for Sample Code5.
Figure 13 Output
*****************************************************************************
Associations define relationships between entities in the data model.
Association Types:
Association definition contains Target Entity with optional Alias (recommended to start with _), Cardinality and Join condition.
Note: Associations used in where clause, projection list, having clause and group by clause leads to left outer join while association used in from clause will lead to inner join.
Sample Code 6
@AbapCatalog.sqlViewName: 'ZISFLIGHT4'
@EndUserText.label: 'Sample Demo for associations'
define view ZI_SFLIGHT4
as select distinct from sflight as sf
association[1..1] to spfli as _spfli
on sf.carrid = _spfli.carrid and sf.connid = _spfli.connid
{
// Expose fields used in the ON condition
// Prerequiste for exposed associaton
key sf.carrid as carrier,
key sf.connid as connid,
// Exposed association
_spfli,
sf.planetype,
// ad hoc association
_spfli.distid
}
Figure 14 shows Data Preview in HANA Studio for Sample Code 6.
Figure 14 Output
************************************************************************
Extend View statement is used to extend the data definition. In the extend view statement, you define the name of the CDS view where you want to add further database fields. After the keyword “WITH”, you enter the name of the extend view itself. Extensions are used to enhance CDS views with Additional fields, Arithmetic and Case expressions and Literals.
Note: CDS view enhancements can’t be enhanced further.
Annotation
@AbapCatalog.sqlViewAppendName is used to generate Append view in Dictionary
Sample Code7
@AbapCatalog.sqlViewAppendName: 'ZSFLIGHTEXT'
@EndUserText.label: 'Sample Demo for Extension'
extend view ZI_SFLIGHT with ZSFLIGHT_EXT {
concat(sf.carrid, sf.connid) as flightno
}
Figure 15 shows Data Preview in HANA Studio for Sample Code7
Figure 15 Output
************************************************************************
CDS Table Functions allow usage of natively implemented database-functions in SAP HANA DB directly from CDS. Standard ABAP class methods are used as containers for the implementation of AMDPs. Classes with AMDPs must use interface IF_AMDP_MARKER_HDB. The implementation is done within an AMDP method inside AMDP class and is managed as an AMDP function by the AMDP framework in the database system.
Necessary parts for CDS Table Functions are illustrated in Figure 16.
Figure 16 Necessary Parts of CDS Table Functions
How to get the flight details of an Airline using CDS table function? In this case, we are fetching the data from two database tables “scarr” and “spfli”.
Step 1 CREATE A CLASS
Sample Code8
CLASS Z_DEMO_CDS_TF DEFINITION
PUBLIC
FINAL
CREATE PUBLIC.
PUBLIC SECTION.
** Classes with AMDPs must use interface IF_AMDP_MARKER_HDB
INTERFACES: if_amdp_marker_hdb.
** TABLE FUNCTION
CLASS-METHODS GET_DATA_Z_CDS_TF
FOR TABLE FUNCTION ZCDS_TF.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS Z_DEMO_CDS_TF IMPLEMENTATION.
METHOD GET_DATA_Z_CDS_TF
BY DATABASE FUNCTION FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING scarr spfli.
RETURN
** PASS CLIENT AND CARRIER ID IN WHERE CLAUSE
SELECT sc.mandt AS client, sc.carrid,
sc.carrname, sp.connid, sp.cityfrom, sp.cityto
FROM scarr AS sc
INNER JOIN spfli AS sp
ON sc.mandt = sp.mandt
AND sc.carrid = sp.carrid
WHERE sp.mandt = :p_clnt
AND sp.carrid = :p_carrid”
ORDER BY sc.mandt, sc.carrname, sp.connid;
ENDMETHOD.
ENDCLASS.
Step2 CDS TABLE FUNCTION DECLARATION
Annotation
@ClientDependent: true - The CDS view is client-specific. When accessed using SELECT, automatic client handling is performed.
Sample Code 9
@ClientDependent: true
@EndUserText.label: 'TABLE FUNCTIONS CDS'
define table function ZCDS_TF
with parameters
@Environment.systemField: #CLIENT
p_clnt :abap.clnt,
p_carrid :s_carr_id
returns {
client :s_mandt;
carrid :s_carr_id;
carrname :s_carrname;
connid :s_conn_id;
cityfrom :s_from_cit;
cityto :s_to_city;
}
implemented by method
Z_DEMO_CDS_TF=>GET_DATA_Z_CDS_TF;
Step3: ABAP Program Consuming CDS table function Using Open SQL
Sample Code 10
*&---------------------------------------------------------------------*
*& Report z_demo_amdp_cds_tf
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT z_demo_amdp_cds_tf.
PARAMETERS: carrid TYPE scarr-carrid.
*** USING OPEN SQL
SELECT *
FROM ZCDS_TF( p_carrid = @carrid )
INTO TABLE @DATA(result)
##db_feature_mode[amdp_table_function].
cl_demo_output=>display_data( result ).
Execution and Output:
Pass the input parameter as American Airlines(AA) as specified in Figure 17.
Figure 17 Output
In the result set we obtained two rows for American Airlines as shown in Figure 18.
Figure 18 Output
*************************************************************************
The CDS authorization concept is a new concept which co-exists with the classic authorization concept. It can be used either independently or in combination with the classic concept. While the classic authorization concept is based on authorization objects with authorizations checked either implicitly (e.g. when transactions are called) or explicitly (using the statement AUTHORITY-CHECK), the CDS authorization concept is based on implicit checks automatically performed by the ABAP runtime when the CDS entity is accessed using Open SQL as shown in Figure 19.
Figure 19 Data Control Language Overview
Step1: Create a view
Annotation @AccessControl.authorizationCheck: #CHECK - Perform access control, syntax check warning if no role is assigned.
Sample Code 11
@AbapCatalog.sqlViewName: 'ZDEMOIAUTH'
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Demo for authorization'
define view ZDEMO_I_AUTH as select from sflight {
key carrid as FlightCarrierId,
key connid as FlightNumber,
key fldate as FlightDate,
planetype
}
Figure 20 shows Output of Sample Code 9 by performing Data Preview in HANA Studio.
Figure 20 Output
Step2: Role Creation
Annotaion @MappingRole true is used. The role is assigned to all users automatically regardless of the client.
Sample Code 12
@EndUserText.label: 'Demo for Access Control'
@MappingRole: true
define role Zdemo_Ac {
grant
select
on
ZDEMO_I_AUTH
where
// -- Example WHERE condition
// -- Equals-or-initial operator
// or
// -- Literal condition
// isPublic = 'X';
(FlightCarrierId) = aspect pfcg_auth (zs_carrid, carrid, actvt='03');
}
No result is obtained while doing the data preview in HANA studio as shown in Figure 21.
Figure 21 Output
Step 3: Create Authorization and Authorization Class from SU21 as shown in Figure 22.
Figure 22 Authorization and Authorization Class
Step 4: Create Role using transaction PFCG as shown in Figure 23. Assign the required selection parameter and generate the profile.
Figure 23 PFCG Role
Step 5: Next step is to assign role to required user.
Go back to HANA studio to test authorization. Result set is now obtained while performing data preview again in HANA studio as shown in Figure 24.
Figure 24 Output
Figure 25 shows two values are permitted for “CARRID” selection ‘AA’ and ‘LH’ as specified.
Figure 25 Filter Condition
Additional feature added to the role. Figure 26 shows the combination of literal and aspect conditions.
Sample Code 13
@EndUserText.label: 'Demo for Access Control'
@MappingRole: true
define role Zdemo_Ac {
grant
select
on
ZDEMO_I_AUTH
where
// -- Example WHERE condition
// -- Equals-or-initial operator
// ( CustomerCountry ) ?= aspect pfcg_auth( S_ACM_DEMO, SACMCNTRY )
// or
// -- Literal condition
// isPublic = 'X';
(FlightCarrierId) = aspect pfcg_auth (zs_carrid, carrid, actvt='03')
and FlightNumber = '0017';
}
Figure 26 Output
Regards,
Esha Rajpal and Megha Gandhi
(Authors)
In the next blog, we will cover the Implementation and Activation of Embedded Analytics with SAP S/4 HANA.
Core Data Services
The Core Data Services (CDS) offers domain-specific languages and services to define and consume semantically rich data models.
Core Data Services at a Glance
- Semantically Rich Data Models – Introduced Domain specific languages (Data Definition Language, Query Language, Data Control Language) and services for defining and consuming semantically Rich Data Models.
- Completely based upon SQL -- Any ‘Standard SQL’ features directly available like joins, union, union all, projection, selection, built-in functions etc.
- Infrastructure Ensure Any-DB support -- Fully compatible with any database. SQL views are created which can be consumed by Open SQL.
- Built in functions -- Rich Set of Built-in SQL functions for calculations, aggregation and grouping etc.
- Code Push down -- Follow Code to data paradigm. More data-intensive calculations can be pushed down to the database through extended view functionality.
- Extensible -- Extend the data definition of CDS view at any time.
- Nested View supported -- Highly Reusable. One view consumes another view.
- Fully integrated in ABAP infrastructure -- Proven and consistent ABAP Lifecycle Management.
Figure1: Building Blocks
*************************************************************************
CDS View Life Cycle
Figure 2 shows the complete life cycle of CDS View from the creation to the consumption.
Step 1 : Creation of CDS view.
Step 2 : Activation of a DDL source.
Step 3 : Consumption of CDS view using Open SQL.
Figure 2: Life Cycle of CDS View
****************************************************************************
Annotations
Annotations enrich CDS data models with additional metadata. They begin with @
There are two types of annotations:
- The first type of annotations provide metadata which applies to the whole CDS view entity (e.g. specifying the table buffer settings of a view). Such annotations are listed before the statement DEFINE VIEW.
- The second type of annotations are used to have influence on a single element from the select list (e.g. specifying a field as currency code with @Semantics.currencyCode or as unit of measure with @Semantics.unitOfMeasure). Annotations of this type are specified directly before the specific field.
****************************************************************************
Pre-requisites
ABAP CDS Views can only be created using the ABAP in Eclipse plug-in IDE(Integrated Development environment).
Download from https://tools.hana.ondemand.com/<eclipse version>
Eclipse versions are planetary objects, mars, luna, keplar, etc.
If using HANA Studio, use Help->About HANA Studio to get your Eclipse version number.
How to connect to HANA system to start the development?
Create an ABAP Project
Connection to an ABAP backend system
- Can use Logon pad entries or manual
- Must be active. No offline mode
- User-specific view of the backend system
Favorite Packages:
- Convenient way to organize your personal view of the backend
- Can Add or remove as necessary, does not affect the actual packages
Generating Some data
- We have used SFLIGHT – Airline Flight Model for our examples
- You can use report SAPBC_DATA_GENERATOR Report, transaction SE38 for this.
*****************************************************************************
Syntax of CDS View
The SELECT statement defines a query performed on the data sources specified in data_source for a CDS view. Possible data sources are database tables defined in ABAP Dictionary, classic views, or other CDS views.
Syntax:
SELECT [DISTINCT]
Variant1
{ select_list
FROM data_source
[association1 association2 ...] }
or
Variant2
{ FROM data_source
[ association1 association2 ...]
{select_list} }
[clauses]
WHERE
- select_list defines the components read in a list.
- association1, association2, ... define associations for the current SELECT statement. These associations can be accessed in data_source and in select_list using path expressions.
- DISTINCT removes duplicates from the results list.
Both forms of the code have the same semantics. Curly brackets { } must be used in the second variant to set select_list. The optional additions clauses are SELECT clauses that enable conditions, groupings, and union sets to be specified or created.
Sample Code where all the columns from the database table are selected.
Annotations
@AbapCatalog.sqlViewName: 'ZISFLIGHT' - Database view in ABAP dictionary that is generated at activation
@EndUserText.label: 'Sample Demo for sflight' - Defines a human-readable text that is displayed.
Sample Code
@AbapCatalog.sqlViewName: 'ZISFLIGHT'
@EndUserText.label: 'Sample Demo for sflight'
define view ZI_SFLIGHT as select from sflight
{ * }
Selecting Columns in CDS View
This will be comma separated list of column names. This includes table aliases and key fields. CDS View key fields need not be same as of the underlying data source.
Sample Code 1
@AbapCatalog.sqlViewName: 'ZISFLIGHT'
@EndUserText.label: 'Sample Demo for sflight'
define view ZI_SFLIGHT as select from sflight as sf
{
key sf.fldate as Flightdate,
key sf.connid as Flightno,
key sf.carrid as FlightCarrierID
}
**************************************************************************
Dictionary Representation, Active Annotations, Dependency Analyzer and Graphical Editor
Dictionary Representation: When activating a CDS view, the following objects are created in the ABAP Dictionary a shown in Figure 3.
- The actual CDS entity
- A CDS database view
Figure 3: CDS view Building Architecture
Below sample code select Distinct Columns and include where clause in CDS View.
Sample Code 2
@AbapCatalog.sqlViewName: 'ZISFLIGHT'
@EndUserText.label: 'Sample Demo for sflight'
define view ZI_SFLIGHT as select distinct from sflight as sf
{
key sf.fldate as Flightdate,
key sf.connid as Flightno,
key sf.carrid as FlightCarrierID,
sf.planetype
}
where sf.planetype='747-400'
Dictionary Representation of above sample code is shown in Figure 4. Transaction SE11 can be used to check the DDL SQL View and DDL Source.
Figure 4: Dictionary Representation of CDS Entity and Database View
Right click on DDL source and Expand “Open With” as depicted in Figure 5.
Figure 5 Options Available under “Open With”
Active Annotations View
The CDS annotations that are used for an active CDS view are displayed in the Active Annotations view as shown in Figure 6.
In the Active Annotations view, following columns are displayed:
- Annotated Elements: The view, its parameters, fields, and associations, and their active CDS annotations.
- Annotation Value: Values of the corresponding CDS annotations.
- Origin Data Source: Name of the development object, for example, a data definition or database table from which the corresponding annotation is inherited. You can navigate to this object by double-clicking its name.
- Origin Data Element: Name of the data element from which the corresponding annotation is inherited. You can navigate to this object by double-clicking its name.
Figure 6: Example of Active Annotations view
Dependency Analyzer:
Use the Dependency Analyzer to investigate which database objects (such as CDS database views, database tables, database views, and table functions) are used in your CDS view. In addition, it helps you to understand SQL dependencies and complexity. The SQL Dependency Tree tab displays dependencies as hierarchy as shown in Figure 7.
Figure 7: Example of Dependency Analyzer
Graphical Editor
The CDS Graphical Editor provides a read-only view containing CDS entities, user-defined types, and the relationships between entities in graphical representations as shown in Figure 8.
Figure 8: Example of Graphical Editor
***************************************************************************
Expressions and Built-In Functions in CDS View
Rich Set of Built-in SQL functions for calculations, aggregation and grouping etc are available for use in CDS view.
There are couple of Built-in Database Functions (SQL and Special Functions) managed by ABAP Dictionary which can be specified in the Select list of a CDS View and in operand positions. Below shows sample Code for case and arithmetic expressions
Sample Code 3
@AbapCatalog.sqlViewName: 'ZISFLIGHT1'
@EndUserText.label: 'Sample Demo for case expression'
define view ZI_SFLIGHT1 as select distinct from sflight as sf
{
key sf.connid as Flightno,
key sf.carrid as FlightCarrierID,
sf.planetype,
sf.price,
case sf.connid
when '0017' then ( sf.price + 500 )
when '0790' then ( sf.price + 200 )
when '0408' then ( sf.price + 100 )
else ( sf.price + 300 )
end as final_price
}
Figure 9 shows Output of Sample Code 3.
Figure 9 Output
Sample Code for unit and currency conversion.
Sample Code 4
@AbapCatalog.sqlViewName: 'ZISFLIGHT2'
@EndUserText.label: 'Sample Demo for unit and currency coversion'
define view ZI_SFLIGHT2
with parameters
to_currency : abap.cuky( 5 )
as select distinct from sflight as sf
inner join spfli as _spfli on sf.carrid = _spfli.carrid
{
key sf.carrid as carrier,
sf.price as amount,
currency_conversion(
amount => price,
source_currency => currency,
target_currency => :to_currency,
exchange_rate_date => fldate,
error_handling => 'SET_TO_NULL'
) as convert_amount,
_spfli.distance as distance,
round(unit_conversion(
quantity => _spfli.distance,
source_unit => _spfli.distid,
target_unit => cast( 'KM' as abap.unit( 3 )),
error_handling => 'SET_TO_NULL'
), 3) as convert_distance
}
where _spfli.distid = 'MI'
Input parameter is passed in Figure 10 and Figure 11 shows Output of Sample Code 4
Input 10 Input Parameter
Figure 11 Output
************************************************************************
Input Parameters in CDS View
Parameter types can be predefined or data element. This view also shows how to associate input parameters with ABAP system fields. Parameters can be accessed by :<param_name> or $parameters.<param_name>.
Environment Annotation: Below parameter is specified with default value sy-mandt. No explicit actual parameter can be assigned to an input parameter to which the system field sy-mandt was assigned using #CLIENT. Open SQL always passes (implicitly) the value of sy-mandt
@Environment.systemField : #CLIENT
Sample Code 5
@AbapCatalog.sqlViewName: 'ZISFLIGHT3'
@EndUserText.label: 'Sample Demo for input parameters'
define view ZI_SFLIGHT3
with parameters
@Environment.systemField : #CLIENT
p_mandt : syst_mandt,
p_carrid : s_carr_id
as select from sflight as sf
{
key sf.fldate as Flightdate,
key sf.connid as Flightno,
key sf.carrid as FlightCarrierID,
sf.planetype
}
where
sf.mandt = :p_mandt
and sf.carrid = :p_carrid
Figure 12 enter the Input Parameter P_CARRID and execute to display the result set which is shown in Figure 13.
Enter the Input parameters:
Figure 12 Input Parameter
Figure 13 shows Data Preview in HANA Studio for Sample Code5.
Figure 13 Output
*****************************************************************************
Associations in CDS View
Associations define relationships between entities in the data model.
Association Types:
- Ad Hoc Association -> Association consumption constitutes a JOIN
- Exposed Association -> Exposure does not automatically lead to a JOIN. It follows “JOINs ON DEMAND”
Association definition contains Target Entity with optional Alias (recommended to start with _), Cardinality and Join condition.
Note: Associations used in where clause, projection list, having clause and group by clause leads to left outer join while association used in from clause will lead to inner join.
Sample Code 6
@AbapCatalog.sqlViewName: 'ZISFLIGHT4'
@EndUserText.label: 'Sample Demo for associations'
define view ZI_SFLIGHT4
as select distinct from sflight as sf
association[1..1] to spfli as _spfli
on sf.carrid = _spfli.carrid and sf.connid = _spfli.connid
{
// Expose fields used in the ON condition
// Prerequiste for exposed associaton
key sf.carrid as carrier,
key sf.connid as connid,
// Exposed association
_spfli,
sf.planetype,
// ad hoc association
_spfli.distid
}
Figure 14 shows Data Preview in HANA Studio for Sample Code 6.
Figure 14 Output
************************************************************************
CDS Views -Extensions
Extend View statement is used to extend the data definition. In the extend view statement, you define the name of the CDS view where you want to add further database fields. After the keyword “WITH”, you enter the name of the extend view itself. Extensions are used to enhance CDS views with Additional fields, Arithmetic and Case expressions and Literals.
Note: CDS view enhancements can’t be enhanced further.
Annotation
@AbapCatalog.sqlViewAppendName is used to generate Append view in Dictionary
Sample Code7
@AbapCatalog.sqlViewAppendName: 'ZSFLIGHTEXT'
@EndUserText.label: 'Sample Demo for Extension'
extend view ZI_SFLIGHT with ZSFLIGHT_EXT {
concat(sf.carrid, sf.connid) as flightno
}
Figure 15 shows Data Preview in HANA Studio for Sample Code7
Figure 15 Output
************************************************************************
CDS Table Functions and AMDP
CDS Table Functions allow usage of natively implemented database-functions in SAP HANA DB directly from CDS. Standard ABAP class methods are used as containers for the implementation of AMDPs. Classes with AMDPs must use interface IF_AMDP_MARKER_HDB. The implementation is done within an AMDP method inside AMDP class and is managed as an AMDP function by the AMDP framework in the database system.
Necessary parts for CDS Table Functions are illustrated in Figure 16.
Figure 16 Necessary Parts of CDS Table Functions
How to get the flight details of an Airline using CDS table function? In this case, we are fetching the data from two database tables “scarr” and “spfli”.
Step 1 CREATE A CLASS
Sample Code8
CLASS Z_DEMO_CDS_TF DEFINITION
PUBLIC
FINAL
CREATE PUBLIC.
PUBLIC SECTION.
** Classes with AMDPs must use interface IF_AMDP_MARKER_HDB
INTERFACES: if_amdp_marker_hdb.
** TABLE FUNCTION
CLASS-METHODS GET_DATA_Z_CDS_TF
FOR TABLE FUNCTION ZCDS_TF.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.
CLASS Z_DEMO_CDS_TF IMPLEMENTATION.
METHOD GET_DATA_Z_CDS_TF
BY DATABASE FUNCTION FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING scarr spfli.
RETURN
** PASS CLIENT AND CARRIER ID IN WHERE CLAUSE
SELECT sc.mandt AS client, sc.carrid,
sc.carrname, sp.connid, sp.cityfrom, sp.cityto
FROM scarr AS sc
INNER JOIN spfli AS sp
ON sc.mandt = sp.mandt
AND sc.carrid = sp.carrid
WHERE sp.mandt = :p_clnt
AND sp.carrid = :p_carrid”
ORDER BY sc.mandt, sc.carrname, sp.connid;
ENDMETHOD.
ENDCLASS.
Step2 CDS TABLE FUNCTION DECLARATION
Annotation
@ClientDependent: true - The CDS view is client-specific. When accessed using SELECT, automatic client handling is performed.
Sample Code 9
@ClientDependent: true
@EndUserText.label: 'TABLE FUNCTIONS CDS'
define table function ZCDS_TF
with parameters
@Environment.systemField: #CLIENT
p_clnt :abap.clnt,
p_carrid :s_carr_id
returns {
client :s_mandt;
carrid :s_carr_id;
carrname :s_carrname;
connid :s_conn_id;
cityfrom :s_from_cit;
cityto :s_to_city;
}
implemented by method
Z_DEMO_CDS_TF=>GET_DATA_Z_CDS_TF;
Step3: ABAP Program Consuming CDS table function Using Open SQL
Sample Code 10
*&---------------------------------------------------------------------*
*& Report z_demo_amdp_cds_tf
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT z_demo_amdp_cds_tf.
PARAMETERS: carrid TYPE scarr-carrid.
*** USING OPEN SQL
SELECT *
FROM ZCDS_TF( p_carrid = @carrid )
INTO TABLE @DATA(result)
##db_feature_mode[amdp_table_function].
cl_demo_output=>display_data( result ).
Execution and Output:
Pass the input parameter as American Airlines(AA) as specified in Figure 17.
Figure 17 Output
In the result set we obtained two rows for American Airlines as shown in Figure 18.
Figure 18 Output
*************************************************************************
CDS Authorizations
The CDS authorization concept is a new concept which co-exists with the classic authorization concept. It can be used either independently or in combination with the classic concept. While the classic authorization concept is based on authorization objects with authorizations checked either implicitly (e.g. when transactions are called) or explicitly (using the statement AUTHORITY-CHECK), the CDS authorization concept is based on implicit checks automatically performed by the ABAP runtime when the CDS entity is accessed using Open SQL as shown in Figure 19.
Figure 19 Data Control Language Overview
Step1: Create a view
Annotation @AccessControl.authorizationCheck: #CHECK - Perform access control, syntax check warning if no role is assigned.
Sample Code 11
@AbapCatalog.sqlViewName: 'ZDEMOIAUTH'
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Demo for authorization'
define view ZDEMO_I_AUTH as select from sflight {
key carrid as FlightCarrierId,
key connid as FlightNumber,
key fldate as FlightDate,
planetype
}
Figure 20 shows Output of Sample Code 9 by performing Data Preview in HANA Studio.
Figure 20 Output
Step2: Role Creation
Annotaion @MappingRole true is used. The role is assigned to all users automatically regardless of the client.
Sample Code 12
@EndUserText.label: 'Demo for Access Control'
@MappingRole: true
define role Zdemo_Ac {
grant
select
on
ZDEMO_I_AUTH
where
// -- Example WHERE condition
// -- Equals-or-initial operator
// or
// -- Literal condition
// isPublic = 'X';
(FlightCarrierId) = aspect pfcg_auth (zs_carrid, carrid, actvt='03');
}
No result is obtained while doing the data preview in HANA studio as shown in Figure 21.
Figure 21 Output
Step 3: Create Authorization and Authorization Class from SU21 as shown in Figure 22.
Figure 22 Authorization and Authorization Class
Step 4: Create Role using transaction PFCG as shown in Figure 23. Assign the required selection parameter and generate the profile.
Figure 23 PFCG Role
Step 5: Next step is to assign role to required user.
Go back to HANA studio to test authorization. Result set is now obtained while performing data preview again in HANA studio as shown in Figure 24.
Figure 24 Output
Figure 25 shows two values are permitted for “CARRID” selection ‘AA’ and ‘LH’ as specified.
Figure 25 Filter Condition
Additional feature added to the role. Figure 26 shows the combination of literal and aspect conditions.
Sample Code 13
@EndUserText.label: 'Demo for Access Control'
@MappingRole: true
define role Zdemo_Ac {
grant
select
on
ZDEMO_I_AUTH
where
// -- Example WHERE condition
// -- Equals-or-initial operator
// ( CustomerCountry ) ?= aspect pfcg_auth( S_ACM_DEMO, SACMCNTRY )
// or
// -- Literal condition
// isPublic = 'X';
(FlightCarrierId) = aspect pfcg_auth (zs_carrid, carrid, actvt='03')
and FlightNumber = '0017';
}
Figure 26 Output
Regards,
Esha Rajpal and Megha Gandhi
(Authors)
- SAP Managed Tags:
5 Comments
