Unleashing the Power of CDS Views in SAP HANA

[Avinashborate]

Introduction to CDS Views in SAP HANA

Core Data Services (CDS) views are a crucial aspect of SAP's data modeling capabilities, allowing for efficient and flexible data management. In this blog post, we'll explore what CDS views are, how they work, and why they are essential for SAP users and developers.

Pre-requisites:SAP HANA Studio or Eclipse with ABAP Development Tools (ADT)

• ABAP Backend System
• Basic Knowledge of SQL and ABAP

What are CDS Views?

Core Data Services (CDS)

CDS is a framework for defining and consuming data models on SAP HANA, providing a powerful way to manage and retrieve data efficiently. Data models are fundamental in designing databases, ensuring data integrity, and optimizing data retrieval and manipulation. They serve as blueprints for how data is stored, accessed, and utilized across different SAP applications and modules.

Key Features and Benefits of CDS Views

1. Enhanced Performance: CDS views optimize data access, making it faster and more efficient.
2. Simplified Data Modelling: With CDS views, you can define complex data models using simple annotations and syntax.
3. Seamless Integration: CDS views integrate seamlessly with other SAP tools and applications.

Note: In ABAP, during select queries, it works like SELECT – SE11 – DB – SE11 – Query. In CDS, it works like SELECT – CDS – Query. Select options cannot be used in CDS Views; instead, annotations are required.

Components of CDS

1. CDS Views: These are like database views but more powerful, allowing complex joins, associations, and calculations. They are defined using the CDS language and can be consumed in ABAP programs.
2. CDS Entities: These are the core building blocks of CDS, representing data models that include tables, views, and associations.
3. Annotations: Annotations provide metadata about the CDS elements, such as descriptions, semantics, and UI information. They help in enhancing the functionality and integration with other SAP components.
4. Associations: Associations define relationships between CDS entities, making it easier to navigate and query related data.

Step-by-Step Guide to ABAP CDS View Annotations and Buffering

@AbapCatalog.sqlViewName: 'ZVSQL_SAMPLE_01' // In this ABAP catalog, we can set the name for the DDIC.
@EndUserText.label: 'This is my first DDIC-based CDS' // Here we can set the description for this SQL view.
@AbapCatalog.preserveKey: true // With the help of this, we can set the key field as per our requirement.

@@@@@@@@@@@@@@@@@ If we Add Buffering then @@@@@@@@@@@@@@
@AbapCatalog: {
buffering: {
status: Buffering,
type: #NONE,
numberOfKeyFields: 000
},
dbHints: [{
dbSystem: '', // Here we can add a specific database
hint: ''
}],
ViewEnhancementCategory: [], // Here we can allow or not for CDS enhancement.
sqlViewName: '',
preserveKey: true,
compiler: {
compareFilter: true
}
}

Explanation

1. @AbapCatalog.sqlViewName: Sets the name for the DDIC.
2. @EndUserText.label: Sets the description for the SQL view.
3. @AbapCatalog.preserveKey: Allows setting the key field as per requirements.
4. Buffering Section:
   • status: Buffering status.
   • type: Type of buffering (#NONE means no buffering).
   • numberOfKeyFields: Number of key fields to be buffered.
5. dbHints: Database hints section:
   • dbSystem: Specifies the database system.
   • hint: Provides hints for the database.
6. ViewEnhancementCategory: Allows or disallows CDS enhancements.
7. compiler.compareFilter: Compares the filter for the compiler.

Creating CDS Views in SAP

1. Open the SAP Development Environment: Launch SAP HANA Studio or Eclipse with ABAP Development Tools.
2. Create a New CDS View: Right-click on your project and select New > Other ABAP Repository Object > Data Definition.
3. Define the CDS View: Enter the required details, such as the name and description of the CDS view. Define the SQL-like syntax to specify the data model.

@AbapCatalog.sqlViewName: 'ZC_MY_CDS_VIEW'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'My CDS View'
define view ZC_My_CDS_View as select from SFLIGHT {
    carrid,
    connid,
    fldate,
    price
}​

• .Activate the CDS View: Once you've defined the view, activate it to make it available for use.
• Test the CDS View: Test the CDS view by executing it in the development environment to ensure it retrieves the expected data.

CDS View Entity with Join and Literals

define view ZDDLS_SAMPLE as select from vbak inner join vbap on vbak.vbeln = vbap.vbeln {
    key vbak.vbeln as Sales_Order,
    vbak.vkorg as Sales_org,
    vbak.vkgrp,
    vbap.posar,
    vbap.matnr, 
    0.23 as num_lit,
    'CDS' as char_lit
}
where vbak.vbeln = '0237259939'

CDS View with Input Parameters

define view ZDDLS_SAMPLE with parameters p_vbeln : vbeln as select from vbak inner join vbap on vbak.vbeln = vbap.vbeln {
    key vbak.vbeln as Sales_Order,
    vbak.vkorg as Sales_org,
    vbak.vkgrp,
    vbap.posar,
    vbap.matnr, 
    0.23 as num_lit,
    'CDS' as char_lit
}
where vbak.vbeln = $parameters.p_vbeln

Key Points to Remember

• We can give the SQL view name up to 16 characters.
• Annotations provide vital metadata and enhance CDS functionality.
• CDS views support various join types: inner join, left outer join, right outer join, union, and union all.

Using Session Variables in CDS Views.

In SAP CDS views, session variables such as the current user or date are handled differently compared to traditional ABAP programming. When you need to use session variables like sy-uname (current user) or sy-datum (current date), you utilize the $session prefix in CDS views.

define view ZDDLS_SAMPLE
as select from vbak
inner join vbap on vbak.vbeln = vbap.vbeln
{
    key vbak.vbeln as Sales_Order,
    vbak.vkorg as Sales_org,
    vbak.vkgrp,
    vbap.posar,
    vbap.matnr,
    0.23 as num_lit,
    'CDS' as char_lit,
    $session.user as current_user,
    $session.client as current_client,
    $session.system_language as current_language,
    $session.system_date as current_date
}

Explanation of Session Variables

1. $session.user: Retrieves the current user's ID.
2. $session.client: Retrieves the current client number.
3. $session.system_language: Retrieves the system language.
4. $session.system_date: Retrieves the current system date.

 Make sure you use the $session prefix followed by the appropriate keyword. Pressing Ctrl + Space after $session. will show you the available options, such as user, client, system_language, and system_date.

SAP CDS View String Operations

1. Concatenating Strings

In CDS views, you can concatenate strings using the CONCAT keyword. However, by default, you can only concatenate two fields at a time. To concatenate more than two fields, you need to perform concatenation multiple times.

define view ZCDS_CONCATENATION
as select from vbak
{
    key vbeln,
    erdat,
    ernam,
    concat(concat(vkorg, spart), 'additional_text') as concatenated_field
}

2. Trimming Leading Characters

The LTRIM function removes leading spaces or specific leading characters from a string.

@AbapCatalog.sqlViewName: 'ZCDS_string_oper'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS string operations'
define view ZCDS_STRING_OPERATIONS 
as select from vbak
{
    key ltrim(vbeln, '0') as sales_order_no,
    erdat,
    ernam,
    ltrim(vkorg, '0') as vkorg,
    spart
}

3. Trimming Trailing Characters

The RTRIM function removes trailing spaces or specific trailing characters from a string..

@AbapCatalog.sqlViewName: 'ZCDS_string_oper'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS string operations'
define view ZCDS_STRING_OPERATIONS 
as select from vbak
{
    key ltrim(vbeln, '0') as sales_order_no,
    erdat,
    ernam,
    ltrim(vkorg, '0') as vkorg,
    spart,
    rtrim(vkorg, '0') as vkorg_trimmed
}

4. Combining LTRIM and RTRIM

You can combine both LTRIM and RTRIM functions to clean up a string by removing leading and trailing characters.

@AbapCatalog.sqlViewName: 'ZCDS_string_oper'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS string operations'
define view ZCDS_STRING_OPERATIONS 
as select from vbak
{
    key ltrim(vbeln, '0') as sales_order_no,
    erdat,
    ernam,
    ltrim(vkorg, '0') as vkorg,
    spart,
    rtrim(vkorg, '0') as vkorg_trimmed,
    ltrim(rtrim(vbeln, '0'), '0') as cleaned_vbeln
}

5. Finding Substrings

The INSTR function is used to find the position of a substring within a string.

@AbapCatalog.sqlViewName: 'ZCDS_string_oper'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS string operations'
define view ZCDS_STRING_OPERATIONS 
as select from vbak
{
    key ltrim(vbeln, '0') as sales_order_no,
    erdat,
    ernam,
    ltrim(vkorg, '0') as vkorg,
    spart,
    rtrim(vkorg, '0') as vkorg_trimmed,
    ltrim(rtrim(vbeln, '0'), '0') as cleaned_vbeln,
    instr('Avinash Vasnat Borate', 'Borate') as position_of_substring
}

6. Extracting Left Substrings

The LEFT function extracts a specified number of characters from the beginning of a string.

@AbapCatalog.sqlViewName: 'ZCDS_string_oper'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS string operations'
define view ZCDS_STRING_OPERATIONS 
as select from vbak
{
    key ltrim(vbeln, '0') as sales_order_no,
    erdat,
    ernam,
    ltrim(vkorg, '0') as vkorg,
    spart,
    rtrim(vkorg, '0') as vkorg_trimmed,
    ltrim(rtrim(vbeln, '0'), '0') as cleaned_vbeln,
    instr('Avinash Vasnat Borate', 'Borate') as position_of_substring,
    left('SAP ABAP HANA', 3) as left_substring
}

7. Extracting Right Substrings

The RIGHT function extracts a specified number of characters from the end of a string.

@AbapCatalog.sqlViewName: 'ZCDS_string_oper'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS string operations'
define view ZCDS_STRING_OPERATIONS 
as select from vbak
{
    key ltrim(vbeln, '0') as sales_order_no,
    erdat,
    ernam,
    ltrim(vkorg, '0') as vkorg,
    spart,
    rtrim(vkorg, '0') as vkorg_trimmed,
    ltrim(rtrim(vbeln, '0'), '0') as cleaned_vbeln,
    instr('Avinash Vasnat Borate', 'Borate') as position_of_substring,
    left('SAP ABAP HANA', 3) as left_substring,
    right('SAP ABAP S4 HANA', 4) as right_substring
}

8. Padding Strings

The RPAD (Right Pad) function pads a string on the right side with a specified character until the string reaches a desired length.

@AbapCatalog.sqlViewName: 'ZCDS_string_oper'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS string operations'
define view ZCDS_STRING_OPERATIONS 
as select from vbak
{
    key ltrim(vbeln, '0') as sales_order_no,
    erdat,
    ernam,
    ltrim(vkorg, '0') as vkorg,
    spart,
    rtrim(vkorg, '0') as vkorg_trimmed,
    ltrim(rtrim(vbeln, '0'), '0') as cleaned_vbeln,
    instr('Avinash Vasnat Borate', 'Borate') as position_of_substring,
    left('SAP ABAP HANA', 3) as left_substring,
    right('SAP ABAP S4 HANA', 4) as right_substring,
    rpad('EWM team', 6, 'XY') as padded_string
}

9. Replacing Substrings

The REPLACE function replaces all occurrences of a specified substring within a string with another substring.

@AbapCatalog.sqlViewName: 'ZCDS_string_oper'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS string operations'
define view ZCDS_STRING_OPERATIONS 
as select from vbak
{
    key ltrim(vbeln, '0') as sales_order_no,
    erdat,
    ernam,
    ltrim(vkorg, '0') as vkorg,
    spart,
    rtrim(vkorg, '0') as vkorg_trimmed,
    ltrim(rtrim(vbeln, '0'), '0') as cleaned_vbeln,
    instr('Avinash Vasnat Borate', 'Borate') as position_of_substring,
    left('SAP ABAP HANA', 3) as left_substring,
    right('SAP ABAP S4 HANA', 4) as right_substring,
    rpad('EWM team', 6, 'XY') as padded_string,
    replace('Avinash Borate', 'Avinash', 'Vicky') as updated_name
}

10. Extracting Substrings

The SUBSTRING function extracts a specific portion of a string, starting from a given position and optionally for a specified length.

@AbapCatalog.sqlViewName: 'ZCDS_string_oper'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS string operations'
define view ZCDS_STRING_OPERATIONS 
as select from vbak
{
    key ltrim(vbeln, '0') as sales_order_no,
    erdat,
    ernam,
    ltrim(vkorg, '0') as vkorg,
    spart,
    rtrim(vkorg, '0') as vkorg_trimmed,
    ltrim(rtrim(vbeln, '0'), '0') as cleaned_vbeln,
    instr('Avinash Vasnat Borate', 'Borate') as position_of_substring,
    left('SAP ABAP HANA', 3) as left_substring,
    right('SAP ABAP S4 HANA', 4) as right_substring,
    rpad('EWM team', 6, 'XY') as padded_string,
    replace('Avinash Borate', 'Avinash', 'Vicky') as updated_name,
    substring(erdat, 5, 2) as month,
    substring(erdat, 1, 4) as year,
    substring(erdat, 7, 2) as date
}