Based on SAP NW 7.4 sp13
Background:
We recently needed to quickly create an extract of sales order related data with ~200 columns using ~20 source tables from ECC on anyDB. With a relatively straightforward pull of data, with little or no calculated columns or complex transformations but a short timeline, we decided to use CDS views to exclusively gather the data.
There are other blogs within SCN warn or even advise against using CDS on a anyDB system, e.g. cds views oh behave, however in our case this approach really worked really well. Quick development time, very easy to test/debug, excellent performance and we now have multiple re-usable data models for Sales Order Header & Line related data.
This link is also an excellent document for CDS development in AnyDB.
ABAP CDS on AnyDB
Disclaimer:
It is imperative that all CDS views be thoroughly tested for performance & efficient database execution. The example below is from an ECC system where the customer values are setup unique per Sales Order, this may not be the case in your equivalent system.
Requirement:
We needed to ensure unique entries per sales order & sales order lines, but also had a requirement to include multiple customer/partner function data (e.g. sold to, ship to, bill to etc).
Using conditional aggregation in SQL to pivot multiple rows into a reduced number of rows (e.g. single) with corresponding column headings is a common approach used in all rdbms for some time, especially before dedicated sql functions like pivot/unpivot became available.
What I would like to show in this blog is how we can use conditional aggregation in ABAP CDS views to accomplish this task, while avoiding having to bring the records into ABAP and having multiple looping iterations to produce the same output.
Step 1:
To get one record per Sales Order transaction, we need to split out the BP function values (e.g. Ship To Country) into it's own individual field on the Select statement.
We do this by using the CASE statement as follows..
We now have separate fields per each BP function and associated field value. There is also one row per BP function, in the example below, one row for Sold To, separate row for Bill To where only the Bill To fields are populated.
Step2:
Aggregate the rows for each BP function down to a single row per Transaction.
We achieve this by using MAX function with a group by as shown below.
Querying this view now on Vbeln will give you one row with all the associated BP values split out in individual columns.
Conclusion:
I hope this gives folks a working example of how to apply conditional aggregation using CDS views, these views above were created on an non-HANA underlying database. The extraction method executes at a large order of magnitude faster over standard BAPI sales order detail calls for the same volume.
- SAP Managed Tags:
3 Comments
