How to get to P_R_REQUEST information in BW HANA Transformation using AMDP

[PavolF]

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.