Hello SCN-community,
I tried my first steps in the world of CDS. As you may recognized in my last blog, I have to provide a kind of HANA-readiness. The Core Data Services (CDS) are available on both platforms – HANA and non-HANA.
BRFplus – DB Lookup expression and currencies
In order to solve a problem in BRFplus, Carsten Ziegler gave me the advice to take a look at CDS. I have to build a few aggregates upon the new General Ledger for some kind of statistics. The problem addresses to sum a few amount columns whose currency information points to another table. This is currently
not supported by the BRFplus database lookup expression. Technically in the case of the ledger it’s getting more complicated due to its table design.
But what if you only operate with one single currency? In fact if you have the same problem with another table, CDS is definitely something you may look
at.To give you an overview I built a small example table:
All amount fields refer to T000-MWAER (the clients default currency). For now, we want to sum each amount column of the table in BRFplus. This gives us the following errors:
In order to solve it we think about a view in which we explicit forget the currency details. This is done by creating a CDS view. But in CDS we are able to do more. We can create a view hiding the currency information or we can map all amount columns to a constant defined currency. I prefer the last variant because we take the currency information into BRFplus and do not need to append it later to the data. But sometimes you may not need the currency so I provide examples for both implementations.
CDS-View 1: Removing currency Information
By using the CAST function we transform the values of type amount to simple decimal numbers.
CDS-View 2: Mapping all amount columns to a static defined currency
In order to give the currency information back to BRFplus we have to normalize all amount columns. This is done by defining a new currency key column. This column is annotated with @Semantics.currencyCode. To provide a constant value as currency information, I use the scalar value ‘EUR‘ and cast it to the appropriate type. Then we have to link all currency fields of the underlying table to the new currency key column. To do this we annotate each amount column with @Semantics.amount.currencyCode and provide the currency key column as the property.
Putting it all together
With one of those CDS views we can finalize the example in BRFplus.
If we try to simulate the database lookup expression we get something like this:
CDS – Built In SQL-Functions
The example was written on a SAP NW7.40SP6 on HANA where I did not find many built in SQL-Functions.
Aggregations:
- MIN
- MAX
- AVG
- SUM
Control and/or programming:
- CAST
- CASE
- LPAD
- SUBSTRING
- MOD
- CEIL
On SCN I noticed a thread where the following functions are announced for SP8:
- ABS
- DIV
- DIVISION
- FLOOR
- ROUND
- CONCAT
- REPLACE
That’s something I will check when SP8 is officially out.
Summary
CDS with it’s Advanced SQL will be one important functionality in todays application development. It even solves problems of already released projects like BRFplus. Due to the fact CDS is available for non-HANA and HANA (here with a wider feature set) it becomes the backbone to build HANA-ready solutions I think.
Hi Daniel,
thank you for sharing this blog entry.
I completely agree with you that CDS are a cornerstone of new ABAP applications and I see them more and more:
It is great that you showed a use case in BRFplus to overcome limitations of the DB Lookup expression. This shows the potential of the technology.
Please be aware that not many ABAP developers will know CDS since many of them will think that they have good old DDIC so CDSis not that important. For CDS newbies I recommend to read the blog by Christiaan Edward Swanepoel about CDS: http://scn.sap.com/community/abap/eclipse/blog/2014/02/04/new-data-modeling-features-in-abap-for-hana
I like your blog entry you proved that CDS is not only important for creation of high quality semantic data models inside HANA – it has use cases in plain ABAP scenarios as well.
In fact I don’t know whether the ABAP in Eclipse space is optimal in your blog since in this place I would expect more tutorials like the above mentioned one by Chris. But we should Point Carsten Ziegler to this blog entry so that he can link from his BRFplus/DSM collection to this blog entry.
The reason is that knowing the limitations of the database lookup expression of BRFplus will be worth another blog entry. I like that you pasted many screenshots to your blog but unfortunately the error messages are hard to read. So could you explain the problems of DB Lookup for the ones who have not that much experience with this expression type like you?
Cheers and keep on blogging,
Tobias
Hi Daniel,
Thanks a lot for verification of the idea with a CDS view. Thanks to your blog we now also have some kind of reference for future projects. We will definitely add it to our list of resources.
I have to agree with Tobias: Keep on blogging.
Carsten
Hi Daniel,
Thanks a lot for this.
I will use one of the two Solutions in our BRFplus Projekt – at least on the development enviroment.
Cheers,
Attila