With SAP Business Objects Data Services, there are around 22 different adapters to external systems available. However using them in BW was a quite clumsy task, especially if you are a BW modeler and not so much of a Data Services crack.
With BW 7.30 and Data Services XI 4.0 there is now a modeling integration, where you do not need to leave BW Data Warehousing Workbench at all in order to connect to any of these external systems and get the data out!
But of course, since Data Services still is a separate installation, there are some prerequisites, which you need to do on data services in order to be able to retrieve remote function calls from your BW system. Let me fly through them quickly, before I describe the actual modeling integration based on this RFC connection.
Open up the Data Services Management Console,
Log in and go to the Administrator Section. What you need first and foremost, is an SAP connection, i.e. an RFC server listening to BW:
In my screenshot, there are already quite some Servers configured, initially, this tab is empty. So go to the “RFC Server Interface Configuration” Tab and add one:
The type of connection on ABAP side is a “registered server program” and the logon data you enter here is needed to register this program on the SAP application server. For that reason, you need a separate RFC server configuration per SAP application server in data services. Give them all the same “RFC ProgramID” and remember it. I called mine “DI_GEMINI”. You will need it later when you do the inverse connection from BW to Data Services. You may also maintain the SNC settings in order to secure the connection between BW and the Data Services system and enable logon check when a BW user calls up a function in Data Services. For this, you need also to set up the BOE CMC user configurations, but it would go beyond the scope of this blog to describe this in detail.
Going back to the Status tab, you find a stopped instance, which you start:
Now Data Services is able to receive RFC calls from BW.
Now, all basic configuration has been done, we finally can enter BW data warehousing workbench and start connecting our external system (in my example, I have a MySQL DataBase). Start with the source system area of data warehousing workbench:
Let’s create one:
Now you would expect to enter something like “Data Services System”. But no – you do not want to connect to Data Services, do you? You want to connect to your MySQL-Database where your business data is, isn’t it? So let’s call the source system SALESDB resp. “Sales Database”, because I have stored sales data in my external database.
Next, you are thrown to the RFC connection screen:
Here you enter the RFC program ID, which you had named your RFC servers, we had called it DI_GEMINI, remember? If you did maintain the SNC settings in the RFC server, switch to the “Logon & Security” tab and check the radiobutton “Send Logon Ticket without Ref. to a target system”:
Save, test and leave the screen.
Following will pop up:
Now this screen needs some explanation, since now, unfortunately but unavoidable, the terminology of data services and BW both jump at you. The first two fields are still quite straightforward; you need to enter the name of the repository of your data services system. You may use the value help to do so, which goes RFC to the data services system to retrieve the information. Same with the Job Server.
The second frame on the attributes popup is about sending the data from data services to BW. Data services uses an object called “data store” to define the target of data. It has absolutely nothing to do with the BW object DataStore or DSO. The data services term sometimes has a tiny space between “data” and “store”, sometimes the “Store” is not capitalized in “Datastore” 😉 Ok, in order for us to define such a data store, we need the user and password to logon to the BW when sending the data – all other information like host and client the system knows itself since we are currently logged on to this machine. We should use the BW background user BWREMOTE or ALEREMOTE or however you called it here.
The third frame on the attributes popup is the most complex one, even though it has only one field… In order to access the business data on my MySQL database, I have to define yet another data store – remember we access it via data services. This time it is a source data store (a data store might be source or target). Also here there is a value help:
Now in our case, there is no data store yet available which connects to the MySQL database where my data is. So I enter a new name like SalesDatabase into the input field and press the “create” button. Now the complexity overwhelms:
Obviously, the system is asking me, which type of data store I want to create. MySQL is a database. More exactly, it is a relational database management system without change delta capture. So let’s pick “RDBMSDataStore”, but not “CDCRDBMSDataStore”. Who, why are these terms so crude and no explanation? I am sorry, but the reason is, we are composing an XML message here – these names are XML tags defined by the set of XSD files which data services publishes to describe all kind of data stores. The popup is generically generated from parsing these XSDs. The good thing about this is, that by thisthe popup is always up do date with the data store definition offered by the corresponding data services installation, if it offers a new type of data store, which is not known today, it will automatically be available also in BW. The bad thing is that there is some user guidance missing, like explanations or some value helps.
A new input field in the name column pops up, the “configuration”. A data store in data services may have different configurations, each connecting to a different system. We only need one configuration and it is automatically named DEFAULT. But the type of configuration we need to choose with the value help:
And there it is – MySQLDataStoreConfiguration! These two popups give you an impression on the variety of source technologies which data services is able to connect to. Let’s pick ours.
This is now the complete XML defining the data store – we “just” need to fill in the values. Luckily, there are value helps (enumerations) for most of the fields, like Version – but unluckily not for “DataSource”.
DataSource!? No, not what you think. Again, this term here has absolutely nothing to do with the BW DataSource. What is meant is the ODBC-Datasource, because MySQL database is accessed via ODBC technology. We enter the name of the database: SALESDB.
Then we need user and password to connect to the database. For entering the password, we click on the input field and obtain another popup where we can enter the password invisibly:
This is the last input we need, let’s confirm all popups and the system will go to data services and create the two mentioned data stores and store their names and the name of the repository and job server as attributes of the source system in BW. The data store configuration itself will not be stored in BW – but you can change it by clicking on the edit button in the source system attributes, it then will be retrieved from data services:
Stay tuned for the second part of this blog, which shows how to actually access a particular table in our MySQL database!
Don’t miss any of the other Information on BW 7.30 which you can find SAP BW Developers SDN Blog Series Accompanying the BW 7.3 Ramp-Up Phase