what a cds-view can do for you | sitnl 2016
TRANSCRIPT
© Ctac - Better Business. Ctac Cloud
SITNL 2016
What a CDS-view can do for You
Practical use of CDS Views on ECC EHP7 on HANA -
tricks & tips
Ben Meijs
© Ctac - Better Business. Ctac Cloud 2
ABOUT ME
© Ctac - Better Business. Ctac Cloud 3
Pictogram
CDS Quick RECAP
PictogramPictogram
Use Case – BW Extract
PictogramPictogram
Unit Conversions
PictogramPictogram
Date defaults/ functions
PictogramPictogram
Use Case – Report
PictogramPictogram
Texts in one go
Pictogram
Pictogram
Tips
PictogramPictogram
Outlook
Pictogram
© Ctac - Better Business. Ctac Cloud 4
What is a CDS?
© Ctac - Better Business. Ctac Cloud 6
Core Data Services – it’s an ABAPPER thing!
• Just like defining an Database View in SE11, but with a lot more options:
• INNER and OUTER JOINS
• ASSOCIATIONS
• UNION
• Built-in functions
• Parameters
• Annotations => DB-viewname, client dependency, buffering. generating Odata-services and BOPF-artifacts
• How do you define a CDS View?
• In ECLIPSE!
• Using a Data Definition Language
• Generates a Database View
• You use a CDS-view
• With Open SQL
• In other CDS-view
© Ctac - Better Business. Ctac Cloud 7
Creating a CDS with a JOIN - example
© Ctac - Better Business. Ctac Cloud 8
CDS – options and Built-in functions
© Ctac - Better Business. Ctac Cloud 9
CDS – options and Built-in functions
© Ctac - Better Business. Ctac Cloud 10
Creating a CDS with a JOIN - example
© Ctac - Better Business. Ctac Cloud 11
Pictogram
CDS Quick RECAP
PictogramPictogram
Use Case – BW Extract
PictogramPictogram
Unit Conversions
PictogramPictogram
Date defaults/ functions
PictogramPictogram
Use Case – Report
PictogramPictogram
Texts in one go
Pictogram
Pictogram
Tips
PictogramPictogram
Outlook
Pictogram
© Ctac - Better Business. Ctac Cloud 12
Use Case – CDS view for BW extractor without using any ABAP
Question: is it possible to define a fast BW extractor in an ECC-on-HANA environment, without any ABAP-statement?
• Selection of sales orders, purchase orders, inbound and outbound deliveries within a timeframe of next 6 weeks;
• Quantities in base unit of measure (MARA-MEINS);
• Aggregate this data on Material Number, Plant, Week number;
• Make it as fast as possible;
Technical Challenges:
• Material Unit conversion (every quantity in base unit of measure MARA-MEINS);
• No system variables in CDS on WAS740;
• No calculations possible with date fields in CDS-context on WAS740;
Answer: Yes, it is possible. A weekly run including all the necessary steps on BW now only takes a few minutes
© Ctac - Better Business. Ctac Cloud 13
Pictogram
CDS Quick RECAP
PictogramPictogram
Use Case – BW Extract
PictogramPictogram
Unit Conversions
PictogramPictogram
Date defaults/ functions
PictogramPictogram
Use Case – Report
PictogramPictogram
Texts in one go
Pictogram
Pictogram
Tips
PictogramPictogram
Outlook
Pictogram
© Ctac - Better Business. Ctac Cloud 14
Trick 1 - Unit Conversion using CDS
Option 1 – Use the ABAP function for material unit conversions
Option 2 – Let BW do the unit conversions
Option 3 – Include Material Conversion Table MARM in the CDS
© Ctac - Better Business. Ctac Cloud 15
Pictogram
CDS Quick RECAP
PictogramPictogram
Use Case – BW Extract
PictogramPictogram
Unit Conversions
PictogramPictogram
Date defaults/ functions
PictogramPictogram
Use Case – Report
PictogramPictogram
Texts in one go
Pictogram
Pictogram
Tips
PictogramPictogram
Outlook
Pictogram
© Ctac - Better Business. Ctac Cloud 16
Trick 2 – Default Date and Date +/+ 42 days
Option 1 – Use of two view parameters for these two dates
Option 2 – Use HANA: M_TIME_DIMENSION via an External view
© Ctac - Better Business. Ctac Cloud 17
Trick 3 – Get week number for date
Option 1 – Use of ABAP calendar function to calculate week numbers
Option 2 – Use HANA: M_TIME_DIMENSION via an External view
© Ctac - Better Business. Ctac Cloud 18
Snapshot of resulting CDS
Main CDS (extraction)
CDS – Open Deliveries
SUM group by MATNR/WERKS/WEEKNR
CDS – Open Deliveries
Details from VBBE
CDS – Time (Weeknumbers)
Weeknumber, based on VBBE-MBDAT
Stock
Shipments
Open Orders
© Ctac - Better Business. Ctac Cloud 19
Pictogram
CDS Quick RECAP
PictogramPictogram
Use Case – BW Extract
PictogramPictogram
Unit Conversions
PictogramPictogram
Date defaults/ functions
PictogramPictogram
Use Case – Report
PictogramPictogram
Texts in one go
Pictogram
Pictogram
Tips
PictogramPictogram
Outlook
Pictogram
© Ctac - Better Business. Ctac Cloud 20
Use Case – Just a simple ABAP Report
Question: Is it possible to read the first line of a ‘SAPscript’ text using a CDS-view?
• Selection of Sales order data with partners and status information
• The user wants the first line of a specific Sales order text in the output
Technical Challenges:
• How to get the first line of the text
• How to read a field of type LRAW (STXL-CLUSTD)
• Vague error messages
Solution
• Extra CDS-views needed in order to be able to read the first line of a text
• All primary key fields of all JOINED tables must be specified in the field list. Otherwise a DB-view is generated with all fields as key field. LRAW-fields cannot be key fields!
• The text can be converted in the ABAP ALV report using a specific IMPORT statement that converts from RAW to Text format.
Answer: Yes, if the implicit View key is generated correctly.
© Ctac - Better Business. Ctac Cloud 21
CDS-view with first line of long text
© Ctac - Better Business. Ctac Cloud 22
ABAP - Converting the LRAW text to readable format
© Ctac - Better Business. Ctac Cloud 23
Pictogram
CDS Quick RECAP
PictogramPictogram
Use Case – BW Extract
PictogramPictogram
Unit Conversions
PictogramPictogram
Date defaults/ functions
PictogramPictogram
Use Case – Report
PictogramPictogram
Texts in one go
Pictogram
Pictogram
Tips
PictogramPictogram
Outlook
Pictogram
© Ctac - Better Business. Ctac Cloud 24
Tips
How and when to use CDS-views:
• CDS offers options to limit number of needed ABAP-statements (Code Pushdown)
• Limit use of WHERE-clause in CDS-views (reusability!);
• CDS has more options than Open SQL
• Important role of CDS in SAP Architecture
Be careful and precise when using JOINS:
• Are you sure that you should use an INNER JOIN?
• Are you sure that you should use an OUTER JOIN?
• Did you specify all relevant JOIN fields in the ON-clause?
• Don’t forget the DISTINCT option of the SELECT-statement
Technical tips:
• CDS not limited to HANA
• If not all key fields of joined tables are specified in field list, all fields are considered key field in the generated VIEW
• External Views: synchronize and re-activate usage
© Ctac - Better Business. Ctac Cloud 25
Pictogram
CDS Quick RECAP
PictogramPictogram
Use Case – BW Extract
PictogramPictogram
Unit Conversions
PictogramPictogram
Date defaults/ functions
PictogramPictogram
Use Case – Report
PictogramPictogram
Texts in one go
Pictogram
Pictogram
Tips
PictogramPictogram
Outlook
Pictogram
© Ctac - Better Business. Ctac Cloud 26
Outlook – CDS as common data model in S/4HANA
© Ctac - Better Business. Ctac Cloud 27
Outlook – CDS as central model in future developments
© Ctac - Better Business. Ctac Cloud 28
© Ctac - Better Business. Ctac Cloud 29
Outlook – CDS as central model in future developments
© Ctac - Better Business. Ctac Cloud 30
© Ctac - Better Business. Ctac Cloud
Thank you