Extracting data from SAP.

Communicating with SAP  is often thought of as cumbersome or almost impossible for mere mortals. This picture is false, SAP is very open and very simple to talk to . There are several well documented SAP interfaces that can be utilized for exchanging data with SAP. Here I’m going to describe how we use the SAP RFC api to communicate with SAP. RFC is a speedy program interface well suited for large data volumes. Normally you develop your own SAP RFC programs in the SAP ABAP  language. SAP comes with a large set of prebuilt RFC BAPI’s (Business Application Programming Interfaces) , but often you can make speedier downloads by tailor made RFC s. ( R ecently I have started to write some post s  how to use SQVI and mail to extract data from SAP.)

I  have created an ETL engine  that is built on top of an in-house developed job scheduler ADAP . (ADvanced Application Processor ).  Via ADAP’s SAP integration adaptor we import data from SAP. ADAP is built in PHP  and uses SAPRFC (a free software  package for PHP to SAP communication) .  

Update:

Google on SAPRFC by Eduard Koucky, this project looks dead to me, but Axel  Bangert seems to have picked up SAPRFC. Piers Harding have created a Unicode aware interface SAPNWRFC.  You should Google around and pick the right interface for you. (I’m slowly migrating from SAPRFC to SAPNWRFC. Examples in this post are using Koucky’s original  SAPRFC, which is the simpler interface to use.)

Example (Other examples can be found here ).

In this example I will use SAP BAPIs ^[1]  for extracting information about currencies. OK here we go. First we need to find appropriate BAPIs . For that we use SAP BAPI Explorer, log on to SAP and start transaction ‘BAPI’. Do an alphabetical search for ‘Currency’ and open it see Figur 1. Her we find getList which gives a list of currencies which is a good start.

Figur 1

Here you can find out all details about the Currency getList BAPI. But we only want to find out how to use this BAPI, so we click on the tools tab, then on ‘function builder’ and finally on ‘single test’ see Figur 2.

Figur 2

Pressing ‘single test’ takes us to the ‘Test function module: initial screen’. Now click the clock icon, this will execute the Currency getlist program and take us to the ‘Result Screen’ seeFigur 3.

Figur 3

Here we can see we got a table CURRENCY_LIST with 188 rows as a result. We can display the table by click on it. This takes us to the ‘structure editor’ which displays the result table CURRENCY_LIST see Figur 4.

Figur 4

Now we have run the Currency getList BAPI and we are familiar with the result. We have all information we need to set up an import job in ADAP. Jobs are organized in ADAP schedules. Here we see a schedule (Figur 5) that contains one job executing the currency getList from a remote system.

Figur 5

 And a brief log from running this schedule (Figur 6)

Figur 6

The <autoload> feature of ADAP loads the result into the ADAP data store. Here we can see a display of the first rows of the CURRENCY_LIST table in ADAP (Figur 7).

Figur 7

This example shows how simple it is to extract information from SAP, but this information is not particularly useful. Let’s expand our example to something more useful. We only extracted basic Currency information.  Now we are going to import exchange rate information. Searching the BAPI Explorer we find exchangeRate, open it we find GetListRateTypes (Figur 8), we ‘single test’ as we did

Figur 8

with currency. Based on this we create another job in ADAP job schedule bapiCurrency (Figure 9).

Figur 9

From the new ‘ratetypes’ job we get a table RATETYPES_LIST that looks like Figur 10

Figur 10

The interested reader is challenged to do the single testing in SAP BAPI explorer to verify the result table. Looking at the exchange rate types alone is not very useful, so we continue our pursuit on useful information. Looking at the BAPI explorer menu we see another Bapi  GetCurrentRates and now it gets exciting (to a certain degree), with exchange rates we can actually do currency exchange arithmetic. So first we do a single test on BAPI_EXCHRATE_GETCURRENTRATES Figur 11.

Figur 11

This BAPI needs some import parameters. Now we see the true beauty of the ‘Test Function Module’, it’s a very neat way of trying out programs in SAP. We type a date ‘20110109’ and a rate type ‘EURO’ from the table RATETYPES_LIST and give the BAPI a spin by clicking the clock icon.

Figur 12

Figur 12 shows the result, we got a result table ‘EXCH_RATE_LIST’ with twelve rows. We take this knowledge with us when we set up the ADAP job to extract current rates for rate types. With a ‘job iterator’ and ‘job references’ in ADAP it is easy to connect job in series and we will use these features to extract all current exchange rates for all currency rate types ^[2] . Here we have the job schedule with the two new jobs, ‘generate_iterator’ and ‘currencyRates’ Figur 13. Note how well the <import> maps to the import parameter specification in the SAP BAPI Explorer and how we feed the job currencyRates with data from the imported table RATETYPE_LIST.

Figur 13

The log (Figur 14) shows there were four successfully executed jobs, so we’re happy.

Figur 14

And the table EXCH_RATE_LIST looks like Figur 15

Figur 15

By exploring the standard SAP BAPIs and creating the XML script in Figur 13 we extract quite some information about currencies from our SAP system. Of course you need to know both SAP and ADAP in some detail to be able to do this. If that was not the case I (and probably you too) would not have a job ^[3] . The Figur 13 script is just a bare bone demo script, in production you probably need to add some prerequisites like waiting for some batch jobs in SAP to finish. And you would certainly use the extracted information e.g. create some fancy formatted Excel sheets and mail them to someone explaining our currency exchange rates. But that is another story. This was to show how easy it is to extract information from SAP. Not only are the communications channels easy to set up. SAP also has some great tools to assist you finding the information you like to extract. I have shown the BAPI Explorer. Tracing online sessions is another way to find information in SAP systems.

---

[1]  BAPIs are not limited to import, there are BAPIs for exporting data to SAP

[2]  This is just to show how easy it is to communicate with SAP, not to explain the details of ADAP.

[3]  . It is preposterous to think you can do anything without knowledge.