ABAP News for Release 7.50 – CDS Table Functions Implemented by AMDP
I just started with blogging about important ABAP News for ABAP 7.50 and – whoosh – I am asked for CDS news. OK then, a blog about the new CDS table functions (but hey, I have also real work to do).
ABAP CDS is the ABAP-specific implementation of SAP’s general Core Data Services (CDS) concept. ABAP CDS is open, meaning that you can use it on all database platforms supported by SAP. And yes, CDS views with parameters, introduced with ABAP 7.40, SP08, are supported by all databases with ABAP 7.50.
While openess has its merits, developers working only on the HANA platform might miss some code-push-down capabilities in ABAP CDS. One of these missing capabilities was the usage of database functions in data models built with CDS. Up to now, only CDS views were available. With ABAP 7.50 ABAP CDS also supports CDS table functions as CDS entities. Two problems had to be solved:
- how to make table functions that are implemented natively on the database callable in CDS
- how to manage the life cycle of native table functions to be constantly available to a data model built on the application server
Two questions, one answer: ABAP Managed Database Procedures (AMDP), introduced with ABAP 7.40, SP05. AMDP is a class-based framework for managing and calling stored procedures as AMDP procedures in AS ABAP. For the time being, AMDP is supported by the HANA platform only. Before ABAP 7.50, AMDP knew only database procedures without a return value. With ABAP 7.50, AMDP supports also database functions with a tabular return value. And the main purpose of these AMDP-functions is the implementation of CDS table functions. They cannot be called as functional methods in ABAP, while AMDP-procedures can be called as ABAP methods.
In order to create a CDS table function, you have two things to do:
- define it in a CDS DDL source code,
- implement it in an AMDP method with a return value.
Both steps are possible in ADT (Eclipse) only.
The definition in CDS DDL is straight forward, as e.g.:
@ClientDependent: true
define table function DEMO_CDS_GET_SCARR_SPFLI_INPCL
with parameters @Environment.systemField: #CLIENT
clnt:abap.clnt,
carrid:s_carr_id
returns { client:s_mandt;
carrname:s_carrname;
connid:s_conn_id;
cityfrom:s_from_cit;
cityto:s_to_city; }
implemented by method
CL_DEMO_AMDP_FUNCTIONS_INPCL=>GET_SCARR_SPFLI_FOR_CDS;
A CDS table function has input parameters and returns a tabular result set, that is structured as defined behind returns. You see, that the annotation @ClientDependent can be used to switch on an automatic client handling for Open SQL. You also see a new parameter annotation @Environment.systemField, also available for views, that is handled by Open SQL by implicitly passing the value of sy-mandt to that parameter. Such a CDS table function is a fully fledged CDS entity in the ABAP CDS world and can be used like a CDS view: It is a global structured data type in the ABAP Dictionary and it can be used as data source in Open SQL’s SELECT and in CDS views. Behind implemented by method you see the AMDP class and method where the function has to be implemented in.
After activating the CDS table function you can go on implement the functional AMDP method in an AMDP class, that is a class with the marker interface IF_AMDP_MARKER_HDB. An AMDP method for a CDS table function must be a static functional method of a static AMDP class that is declared as follows:
CLASS-METHODS get_scarr_spfli_for_cds
FOR TABLE FUNCTION demo_cds_get_scarr_spfli_inpcl.
The declaration is linked directly to the CDS table function. The parameter interface is implicitly derived from the table function’s definition! Implementation looks like you might expect it:
METHOD get_scarr_spfli_for_cds
BY DATABASE FUNCTION FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING scarr spfli.
RETURN SELECT sc.mandt as client,
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 = :clnt AND
sp.carrid = :carrid
ORDER BY sc.mandt, sc.carrname, sp.connid;
ENDMETHOD.
Nothing really new but BY DATABASE FUNCTION and that READ-ONLY is a must. Implementation is done in native SQLScript for a HANA database function. And native means, you have to take care for the client. Automatic client handling is done on the Open SQL side only. Of course, a real CDS table function would do more HANA specific things (e.g. wrapping a HANA function) than a simple join as shown in the simple example here! A join you can code also in Open SQL or in a CDS View.
Speaking about Open SQL, last but not least, the usage of our CDS table function as data source of SELECT in an ABAP program:
SELECT *
FROM demo_cds_get_scarr_spfli_inpcl( carrid = @carrid )
INTO TABLE @DATA(result)
##db_feature_mode[amdp_table_function].
Not different to an access of a CDS view with parameters. But you must switch off a syntax warning with a pragma to show that you are sure what you are doing, namely coding for HANA only.
Note that we don’t need to pass the client explicitly. This is because the according parameter was annotated for implicit passing of the respective system field. Since the CDS table function was annotated as client dependent, the result set of Open SQL’s SELECT does not contain a client column – as it is for CDS views. Furthermore all lines of the result set, that do not belong to the current client are implicitly removed. That’s why the return list of a client dependent table function must have a client column. For the sake of performance, the native implementation should deliver only lines of the current client. But since it is native it has to take care for that itself. Confusing? That’s when open and native meet. In ABAP, normally the DBI does this handling for you. But this is not possible here.
For more information see
Wow! That was super-fast! š
It's also nice to have the 7.50 version of ABAPDocu available. Especially to have an overview of all changes. Changes in Release 7.50 - ABAP Keyword Documentation
Peter
In fact I succeeded in getting the docmentation pushed to the help portal last night. Should have been there since RTC, but ran into some naming convention problems regarding the URL ...
Now that you can see the documentation, couldn't I stop blogging? š
Noooo. We're just getting hungry for more details, explanations and examples š
Well,
For the matter of fact, I couldn't wait for your upcoming posts and have already read the changes documentation (briefly).
There are many exciting new features out there....
Hello Horst,
I have some queries regarding Table functions: Will be really appreciable if you can help:
1. How does authorization concept work with table functions?can we create DCL roles for them?
2. Can we create ODATA service or Analytical query using annotations for View created via Table Functions?
3. What is the advantage of using these Table functions over Standalone AMDP functionality? We can access Data Functions using AMDP without invloving CDS..What extra does Table function provide??
Really looking forward for these answers....
1, no you must wrap their usage in an interface view
2, you can write any annotation everywhere, it depends on the frameworks that analyze them, if they support the direct usage of table functions. Please refer to the corresponding documentation
3, To incorporate them in your data model (DDL!)
Thanks for your prompt reply Horst...
Hi Horst,
Is Table function faster than an AMDP procedure. In case I am having the same logic within both...In which case i should go for Table function and which case I should go for a AMDP procedure?
Thanks,
Ramesh
Each ABAP CDS table function is implemented by an AMDP procedure. Therefore the execution time is the same if you have the same logic in both.
You go for table functions if you want to use them as data sources in other CDS entitties. You go for normal AMDP procedures if you want to call them from ABAP via meth( ).
Horst
Thanks Horst š
For question 2, is there any annotation for table function right now? Ā Iām mainly interested in analytic ones that I can utilize in the BO Design Studio. Ā For CDS view, we have following,
https://help.sap.com/saphelp_nw75/helpdata/en/c2/dd92fb83784c4a87e16e66abeeacbd/content.htm
Ā
Ā
Ā
Hereās the overview of all annotations and their scope available for 7.51:
https://help.sap.com/http.svc/rc/abapdocu_751_index_htm/7.51/en-US/index.htm?file=abencds_annotations_frmwrk.htm
(A page I'm proud of, since generated in ABAP ...)
I have added the analytical annotation but in BO Design Studio, it doesn't show up as transient data source. Ā Would you give us some example?
Hi Horst,
Thanks for posting the article. It's very helpful and informative.
I'm trying to implement a table function but getting an issue. Hopefully you can answer this.
We have some non-operational data in S4HANA which we have stored in a separate schema. We are trying to expose that data in reporting layer, so we are trying to create some CDS views on top of that datasets. Since we can't access cross schema tables in CDS views, so we are using table functions to do that. But in table function class, when I specify the schema_name.table_name it says the table is invalid. so how can I reference the table in the class implementation?
Attached is a screenshot of my class. Custorderline is the table which is stored under the Analytics_Demo schema.
Regards
Saub
Pls. read the documentation for USING. You specify dictionary tables behind USING, not those from other schemas. These can be used without being listed behind USING.
Hi,
My table name start from '/' because of this I am getting an error:
SQLSCRIPT: sql syntax error: incorrect syntax near "/"
For example, my table name is: /ABCD/T_XYZ.
Please let me know how this can be fixed.
Regards,
Gaurav
Put double quotes around the name?
See
http://help.sap.com/hana/sap_hana_sql_script_reference_en.pdf
Horst,
Thank you for your original article.Ā Is it possible to call pure ABAP class->method from either within a CDS view or an AMDP method? We have some sophisticated ABAP in a regular class->methodĀ that returns exactly what we need to pass back to a CDS view. With AMDP we are in the SQL script world and re-writing the ABAP into SQL script to beĀ utilized as a CDS table function will take quite abit of time. We are on 7.5 SP05.
Thank you,
Alex
Hi Alex,
How should that work, calling ABAP code on the application layer from SQL on the database layer?
Horst
Horst,
I understand it's wishful thinking that SQL script on the dbase layer would talk to ABAP on the app layer, it's just that in our case re-writing ABAP into SQL is very time-consuming at this point. And i thought a table function could come to rescue as we are already in the familiar OO environment, though the AMDP class/method are very specific.
So is it correct to assume that anything related to CDSes today can only be extended via the SQL script via the AMDP? ABAP doesn't come close into the CDS framework anywhere?
Thank you
ABAP CDS entities are under discussion. Those would be accessed like other CDS entities but executed in ABAP. But I wouldn't expect that you can use them in or join them with SQL views.
Hi, I'm getting following error while trying your tutorial.Ā I have found OSS note, 2289860, forĀ BASIS 750 release and the SP has been applied.Ā Currently I am using the BASIS 751 release so I'm checking to seeĀ if relevant OSS note for this error is available for BASIS 751 release.Ā Thanks.
The relationship between CDS table function and implementing method is one to one. You cannot use my implementation in your CDS table function. This is also clearly expressed by the syntax pair IMPLEMENTED BY <-> FOR TABLE FUNCTION.
You are right. Ā Thanks for your help. Ā But do you know what the OSS note is about?
Ā
Ā
There was a bug that the syntax error occurred without reason.
Thanks.
One more dire question, using CDS view for CRUD operation has issue of not being able to incorporate enhancement framework. Ā Another issue is using view is a direct table read/update which we should never do for SAP tables, due to thousands of table being accessed and updated. Ā How can these problems be avoided and remedied?
Hello Horst,
Is SAP also planning to make database procedures callable in CDS like table functions?
Thank you
I don't think so. You need return values for that purpose. AMDP-functions have been introduced primarily to serve as implementations of CDS table functions.
Hello Horst,
what is the reason CDS table functions can't be queried via SE16N?
CDS table views with parameters are supported with note https://launchpad.support.sap.com/#/notes/2453672/ (at least in S/4HANA), and I wonder whether CDS table functions will be supported via SE16N for NW 7.5X in the future.
Regards
Daniel
That“s a question  that I can“t answer. Maybe you post it as a Question and a SE16 guy sees and answers it.
Horst
Hello Keller,
I have a CDS view with input parameter, once it is created(actually generated at run time). I will have a procedure to read the data from it. And from ABAP stack, a table function( I suppose) will be generated when CDS view is called at first time. But I am using procedure to call it so I got an error: Invalidated function: xxxxx. What can I do in this situation? Can I force HANA to genereate the artifacts under the hood at the beginning which means once CDS view with input parameter is generated?
I think a workaround is just call that CDS view with dummy input parameters by OPEN SQl and then the function will be generated, but I am wondering if there is any formal way.
Thanks for your help in advance:)
BR,
Steve
Your assumption is quite wrong.
A view with parameters is not instantiated as a DB procedure but as a DB view and cannot be called as a procedure.
Hi Keller,
I take some screen shot for my questions:
1. A CDS view with input parameter has a corresponding function
2. I cannot select it in my procedure/console because it shows an error: invalidated function
3. After preview the CDS view with some dummy input parameter, I could select it in console and get the results
The question is why I will encounter this issue and what I can do to solve it?
Many thanks.
BR,
Steve
Have to correct myself.
The instantiation of a view with parameters depends on the DB platform and can be a function.
On HANA it is a function.
Hi Keller, thanks for the info. Then how can I make the function generated and validated at the very begin when CDS view is generated? BR,Steve
SAP does not support this. For reasons like this and some others, it is not recommended to accessĀ DB objectsĀ in the ABAP database schema directlyĀ from the database but only from ABAP via the ABAP DBI (Open SQL or Native SQL).
Hi Keller, Actually we create a procedure in HANA layer to read the data from table function. I think that case is valid, isn't it? So we are still facing this trouble,right? BR,Steve
Ā
No, that case is not valid.
The DB objects created by ABAP are created for ABAP. They can depend on settings that are available only when accessed ideally by Open SQL or by SAP“s Native SQL. When accessing them from DB  layer, you don“t have any control. Examples are the ABAP specific session variables that are evaluated by the DB objects and that are only set correctly when accessed by Open SQL. You must regard the DB objects of ABAP Dictionary including CDS or of AMDP as SAP-internal implementation details. There is no guarantee that this implementation is the same from release to release. E.g., the internal implementation of automatic client handling has changed for CDS views and functions. If you access a view with Open SQL, no problem. But accessing them natively, oh my.
Hi Keller,
Got it, thanks a lot for help:).
BR,
Steve
Hi Horst,
Thanks for the hints with theĀ CDS table functions.
I'm trying to use the table function to read BP detailsĀ sequential, if partner a is maintained, show a, if not show partner b.
Therefor I've maintained a CDS view with parameters to call the table function.
Now I'm trying to use this CDS view in my main CDS view. Do you have a hint how to implement this?
Unfortunately, this does NOT work.
Cheers, Claudia
SeeĀ Ā https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abencds_f1_select_parameters.htm
You canāt specify columns as actual parameters. You put a colon in front of hlink_dat.guid_set, but this is not make it a parameter.
Why do you wrap the table function in a view? You can use it in the main view directly.
You can use an CDS entity as a data source in a join, but you can't use columns as parameters. You can use columns in the ON condition for the result set of the table function.
Wouldn't a case expression better suite your use case?
Hi Horst,
Thanks for the hint with the case, I've adoptedĀ the cds view.
But I still have a second requirement, where I need to first loop over the BP hierarchy and then select the marketing attribute, where I would like to useĀ a table function.Ā Even if I directly embed the table function into the main view, I need to pass the parameter (customer id), but I cannot access the columns as parameters, could I?
Cheers, Claudia
Hi Claudia,
No, you can't pass columns as parameters. In fact how should that work? You're inside a view,Ā how to pass a column valueĀ to a function that returns a tabular result? There'sĀ not a loop concept in CDS views.
For looping you might either create a "main" table function and loop in SAPScript or use Open SQL in ABAP, where you can SELECT from CDS table functions and can pass ABAP variables as parameters.
Horst
Hello Claudia,
Looping over BP hierarchies is not always needed as HANA provides such an object like hierarchy view, optimized for usage of hierarchies.
On HANA 1, the syntax looks like:Ā CREATE COLUMN VIEW <ZHIE1> TYPE HIERARCHY WITH PARAMETERS ( ... But better to use HANA Studio to model this. There were some guides in the internet...
On HANA 2, the things are a bit different. There are Hierarchy Functions. A bit more powerful and a bit more standard approach.
The benefit of hierarchies is the set of predefined standard functions or expressions.
E.g.: SELECT * FROM <ZHIE1>("expression" => 'ascendants("123456")') ORDER BY level
will return a set of ascendants for the node 123456, etc.
If, at certain, point of time you would need to join your hierarchy and "unpack" it, check this post:
https://answers.sap.com/questions/283947/how-to-unpack-hana-hierarchy-view-to-a-more-useful.html
Alternatively you can build a function or procedure, which would combine a complete logic you want to implement. Or just put certain things onto ABAP layer, but to not use LOOPs, only one call. You can pass a table of partner guids intoĀ AMDP via TT:Ā BUP_PARTNERGUID_T.
BR, Dima
HI Horst,
Is it possible to implement CDS table function via database proxy procedure?
What option do I have if I need to call a database stored procedure (or say HANA table function) inside an ABAP-CDS entity?
If I understand correctly, with AMDP we need to write the entire script in ABAP layer. If I need to re-use pre-existing database procedures (instead of writing new via AMDP), what options do I have?
Thanks,
Anup
Wrap the calls of native Hana procedures/functions in AMDP.
Perfect! That's simple indeed. Thanks a lot for quick response:-)
Hi Horst,
My system Netweaver 7.5 SP7, HANA 1.0 SPS 12, BO 4.2 SP3, Analysis for Office Version 2.3.0.57241
I have created an AMDP that I would like to consume either directly or indirectly from one of the Business Object front end tools (WEBI, Analysis for Office, etc). How do I accomplish this? I have tried the following:
What is the recommended approach for consuming an AMDP with BO front end tools?
Thanks for any help,
Mike
I'm not an expert in these frameworks and don't know which CDS entities they support, but wrap theĀ CDS table function as data source of a CDS view, if only views are supported?
If I define a table function Z_FUNC1 implemented via ZCL_FUNC1=>get_func1
can't i then create another function Z_FUNC2 implemeted via ZCL_FUNC2=>get_func2
METHOD get_func2 BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING Z_FUNC1
This is giving me the error:
Views with AMDP implementations are not supported.
But also
METHOD get_func2 BY DATABASE PROCEDURE FOR HDB
LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING ZCL_FUNC1=>get_func1
Is giving me another error:
The user declaration for the object "ZCL_FUNC1=>z_func1" is not needed.
I would like to access data from ABAP in a different schema on the same HANA database.Ā This will be used to drive a UI that needs to have capabilities like fuzzy search.
If I have to retrieve the data using a CDS table function/AMDP pairing and I need to use annotations likeĀ @Search.fuzzinessThreshold, will this involve the database copying all the data from the other schema before performing the search?Ā The ADMP itself appears to return everything rather than passing the restrictions down (with the exception of parameters).
Hello,
is it possible to use correlated subquery in CDS like in ABAP. We would replace ABAP statement with correlated subquery in OData service implementation by OData based on CDS but I cannot find solution with similar performance.
ABAP (old)
select * from A as t1 where date = ( select MAX(date) from A as t2 where t2~key = t1~key )
CDS (new) 2 steps below - which is slow:
1. CDS_A
select key MAX(date) from table A group by key
2. CDS
select * from A as t1 inner join CDS_A as t2 on t1~key = t2~key and t1~date = t2~date
thanks
Horst Keller Thanks for the informative Blog !
Have a clarification. In AMDP class, we can have multiple Exporting Table Result Sets. Whereas as far as I tried, CDS Table Functions can have only single Return Parameter that too the Structure can be defined only with type Data Elements. Would be great to know if there is any workaround to return multiple Tables from CDS Table Functions.
Thanks in Advance !
HiĀ Horst KellerĀ ā I am trying to run a table function on our ECC system which is 7.50 SPS 11 on IBM DB6 on AIX
Ā
However, even when I try to run the SAP table function, I get the error:
"The addressed database does not support the feature AMDP_TABLE_FUNCTION."
<img />
So I am not sure table functions and AMDP is supported in our system ā but I thought that from ABAP 7.50, the capability is not limited to systems with a Hana database.
Thanks,
Jay
Hello Horst,
Thanks a lot for a detailed Blog.
I created Table function using the above steps and wrapped it in CDS view using table Function as a data source for the View. Everything is working fine when I am executing the CDS View and the desired output is being fetched.
But when I try to fetch data using SQL from HANA database directly(with HANA DB user) using "SCHEMA"."SQLVIEWNAME" for this CDS view , the output is blank.
Note: For CDS views created without using the Table function as data source, even the SQL query from HDB is giving the same result as CDS View.
Could you please help me in understanding what might be the problem?
Thanks and Regards,
Alex
Hell Horst,
Thanks for sharing such a nice blog.
Just a simple question to you on select statement in AMDP class.
Can we select data from association of CDS view inside AMDP class ??
My scenario is to use standard CDS view entity āI_PLANNEDORDERā inside AMDP class,
now i need some data from same view as well as from published association of this view like ā_Materialā ā_MRPPlantā.
In open SQL we can read association data like this ā\_MATERIAL.(ColName) but how can we use similar statement in AMDP to read associated columns.
Your help is much appreciated.
Ā
Thanks & Regards,
Praveen Gupta
Hello Horst, hello Community Members,
I would like to implement Unit conversion based on material specific information from MARM table.
I am currently able to achieve this by using table function implemented by AMDP class which is returning a conversion factor for each material and combination of source and target unit, which can then be multiplied with the original quantity in a CDS view.
However, what would be much nicer is buliding this similar to the built-in functions "unit_conversion" and "currency_conversion" which I can use inside the CDS view and pass all information including original quantity.
If possible, can you please point me in the right direction how to implement this?
Thanks,
Daniel