Skip to Content

Update(21.07.2017):  After thorough testing and communicating with SAP I can say that it works when DTP is set to “Get All new Data Request by Request”. It is caused by an error in SAP which is not able to sort the result accordingly. You should install OSS Note 2467323 to solve AMDP Field routines otherwise they provide wrong results. Side effect of the OSS Note is that SAP does add Technical Fields RECORD and SQL__PROCEDURE__SOURCE__RECORD here. THis should be installed fast as all the AMDP Field routines then have to be manually adjusted as they cause syntax errors without the technical fields.

****

I started creating BW Transformations in HANA stack as I wanted to leverage its speed and features. I decided to take the additional effort to move from ABAP programming to code Push down with AMDP Transformations BW 7.50 offers. From BW 7.50 SP04 you are able to use AMDP scripts for Start-, Field-, End-, and Expert- routines.

 

The biggest difference when switching from ABAP to AMDP for me was that Field routines take the whole columns instead of 1 field value as in ABAP stack. This means you can process the whole datapackage field in 1 go instead of 1 by 1 processing in the old ABAP world. This is great as many developers wrote their code in Start- or End- routines to improve performance compared to Field routines though sometimes they wanted to change only 1 field. With AMDP it is now possible to process only that field in 1 go.

I had a case where I just wanted to enrich data from universal FlatFile Datasource with fixed structure with a field that would tell me which File was uploaded. Problem was that for other external reasons I could not include such field in the Datasource structure. I decided then to take another approach and read the File Number from its name which I can find in table RSSELDONE.

For this I did not need any other field from my universal Datasource but I did need technical ID of the request to be able to derive its Filename. As I did for many years in ABAP transformations I started looking for the P_R_REQUEST which would give me all the information I needed. But in AMDP script I could not find it (if someone knows how to get it more conveniently let me know please).

There is however Technical field SQL__PROCEDURE__SOURCE__RECORD which holds the SID of REQUEST from Source (in my case Datasource) throughout the transformation. With this I looked into table /BI0/SREQUID to get the Request ID and then in table RSSELDONE I got all the details I needed. Below is my example of AMDP Field routine.

Feel free to share any optimizations and suggestions for the code below with me. I hope this will help other developers migrating from the ABAP world into the new AMDP HANA world. 🙂

Few technical things I tried to consider:

  • SQL__PROCEDURE__SOURCE__RECORD stores not only the SID but also other technical fields. First 30 digits should be for the SIDs. I decided to take the last 10 digists as SID in the other tables were 10 digits long
  • FILENAME field stores the complete path and I wanted just the first 2 digits from filename. I found a hint to use SQL command INSTR  with -1 to search the first occurence of “\” from the end. FILENAME looks in my case like this C:\xxx\…\612017.csv
  • later I will do some error handling into ERRORTAB. The code below is to fill empty into the Error table as it is mandatory to fill something there
  • you have to add all tables you use into the Method header generated by Eclipse automatically.

 

METHOD PROCEDURE BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY USING  /BI0/SREQUID RSSELDONE.

outtab = select substr(request.FILENAME,INSTR(request.FILENAME, '\', -1,1) + 1,2) as "/BIC/ZFILENR",
*After instaling OSS 2467323
record,
sql__procedure__source__record
*end of addition

from :inTab as input
left OUTER join
( select rid.sid as sid,
 seldone.filename as filename
from "/BI0/SREQUID" as rid
inner join RSSELDONE as seldone
on rid.requid = seldone.rnr ) as request
ON substr(input.sql__procedure__source__record,21,10) = request.sid;
 
errortab = select
       '' as "ERROR_TEXT" ,
       '' as "SQL__PROCEDURE__SOURCE__RECORD"
       from dummy
       where dummy <> 'X';
ENDMETHOD.

 

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Pavol Feranec Post author

    After thorough testing and communicating with SAP I can say that it works when DTP is set to “Get All new Data Request by Request”. It is caused by an error in SAP which is not able to sort the result accordingly. You should install OSS Note 2467323 to solve AMDP Field routines otherwise they provide wrong results. Side effect of the OSS Note is that SAP does add Technical Fields RECORD and SQL__PROCEDURE__SOURCE__RECORD here. THis should be installed fast as all the AMDP Field routines then have to be manually adjusted as they cause syntax errors without the technical fields.

    (0) 
  2. Raf Boudewijns

     Hey Pavol,

    cool that you got it to work! We’ve been playing with AMDP based field routines for a while now and I like it! However…

    Have you already transported a transformation using AMDP based field routines? We’re currently trying to prepare UAT testing and I cannot transport any of our transformations containing AMDP based field routines. According to the official documentation the code of the procedure as well as its signature should “automatically” be added to the transport, but that’s not the case in our system (BW 7.50 SP05).

    It seems that the “link” between the generated ABAP Class and the “routine” itself is somehow not made. We always get a message that the routine (which can be found in table RSTRANSTEPSCRIPT column CODEID) does not exist?! In fact, it doesn’t exist in table TADIR, but there is a routine with that ID (hence it wouldn’t have been written to table RSTRANSTEPSCRIPT, right?).

    We’ve opened an incident at SAP, but we need to move fast (our entire planning is being jeapordized for something stupid like this). So, if you have any clue, I’d be happy to hear/read it.

    Kind regards,

    Raf

     

    (0) 
    1. Pavol Feranec Post author

      Hi Raf,

      One consultant here failed to transport AMDP transformation here which was retransported from Prod back to Dev and somehow the AMDP Class ID was different. He got also strange errors during transports. I spotted it and wondered why the class name is different in Dev and same in Test and Prod. He also got old sequence of fields hardcoded in the AMDP script. After he aligned it with help of Excel and transported with overwriting originals it worked.

      BUt to be honest we are still in Development as there are several issues with the AMDP Field routines. That OSS note adds additional fields to the Field routines together with lots of other fixes and depending notes so it has to be moved first for us but we are still not sure if it won’t affect other projects. We are testing it still though it seems promising now (only time depending attributes derivation seems still wrong in version 85). SAP created a new version 86 of this OSS note couple of days ago so we are thinking to update Development, test again and then we can shift to Test System.

       

      I was transporting only AMDP HANA Script transformations before and it was automatically added. BUt I experienced lots of issues with them. Sometimes it helped to completely delete the transformation and AMDP classes and create them again from scratch.

      Just a guess:

      I suggest to try with one transformation. Back it up in MS Word, delete it with DTPs and classes in Eclipse and create it again. Make sure the technical ID of the class is different from the original one as it remaps the original sometimes if it exists.

       

      Double check in the target system the AMDP classes do not exist nor exists the transformation. Make sure the source and target of the transformation are up to date in the target system and also the sequences on IOs match. Then transport.

       

      If this does not help you can send me details via email with screenshots. I am not sure I will help but I can have a look.

       

      Good luck,

      Pavol

       

       

      (0) 
      1. Raf Boudewijns

         

        Hey Pavol,

        we got it working by following this wiki. You have to implement ALL notes relevant for your version… in our case that was ALL notes for BW7.50 as well as the “Important corrections related to HANA scripts”.

        Cheers,

        Raf

        (0) 

Leave a Reply