Interfacing data into BW using Perl, Ruby, or Python

I’ve just completed the first SAP related course that I’ve been on for many years – BW310 – in London, Clockhouse Place. Surprisingly, I have really enjoyed it, and the lecturer has been excellent (my thanks go to Gurjeet Dosanjh).

On the back this, and other fiddling with the Business Warehouse, I’ve had a look at loading data in “push” mode, and have struck yet another great use for RFC support available in scripting languages. With the unrivaled abilities of scripting languages to munging, mash, and manipulate any kind of data, coupled with the automatically generated RFC interface (DataSource) attached to an InfoSource within BW, the job is trivial.

The RFC DataSource piggy-backs on the BW <-> XI SOAP interface that is generated when you go to edit your InfoSource in the AWB (transaction RSA1). This is accessed by selecting the Extras menu, and then choosing “Create BW DataSource with SOAP Connection”.

Edit InfoSource view – automatically generating the SOAP RFC connector

This is discussed in detail on help.sap.com at this page You must pay carefull attention to the details surrounding “Creating XML DataSources” and “Activating Data Transfer to the Delta Queue”. You can check if you have this right by looking at transaction RSA7 – BW Delta Queue Maintenance, and making sure the traffic lights are green.

Once you have activated the RFC interface, you need to determine what the real “technical name is”. The technical name for the DataSource that I created for the RFC interface example below is 6AZODSINFOXX (as viewed in RSA7) – this translates to an RFC name of /BI0/QI6AZODSINFOXX_RFC – just open yours up in SE37 for the details.

The following example is written in Ruby, however, the language bindings exist for RFC for Ruby, Perl, and Python, so – please – choose what you fancy – you can find out more about them all at here or look at previous articles that I have written here.

Here is a basic step through of what it takes to get the data loading scenario going:

require "SAP/Rfc" # connect rfc = SAP::Rfc.new(:ashost => "seahorse.local.net", :sysnr => 00, :lang => "EN", :client => "010", :user => "developer", :passwd => "developer", :trace => 1) # lookup the BW RFC interface i = rfc.discover("/BI0/QI6AZODSINFOXX_RFC") # set the datasource to connect to i.datasource.value = "6AZODSINFOXX" # grab the structure of the transfer structure str = i.data.structure # start processing the input file - doesnt have to be a file though fh = File.open("./data1.csv") i.data.value = [] fh.each { |line| # drop the first line next if fh.lineno == 1 # remove the line end line.chomp! # split the columns on "," flds = line.split(",") # remove any quotes around column values flds.each {|f| f.sub!(/^"(.*?)"$/, '1') } # fillout the BW transfer structure str.getField("RECORDMODE").value = flds[0] str.getField("/BIC/ZEQUIP_XX").value = flds[1] str.getField("/BIC/ZNAME_XX").value = flds[2] # add the row to the table i.data.value.push(str.value) } fh.close # call and test the return rfc.call(i) # basic error checking of the inserted record batch - check the exception thrown, if any print "Error is: #{i.error}
"

In this example I have just used a typical CSV input file as displayed below to provide input data – but you can use your imagination here – it could be MySQL, a POE server, your legacy AS400 system – it’s up to you 🙂 File:

Recmode,Equipment,Name ,0000009011,"CONTROLS FAILED - 3" ,0000009012,"VALVE LEAK - 3" ,0000009013,"OIL LEAK - 3" ,0000009014,"NUT WIBBLED - 3" ,0000009012,"TYRE WOBBLES - 3" ,0000009011,"NUT FAILURE - 3"

Enjoy! 🙂