These days, BODS is playing a very important role in the SAP system landscape.
Many customers use BODS to pull data from Non SAP systems and load to SAP BW.
These documents are primarily intended for BW consultants who would like to know more about integration, modelling and transport mechanism related to BODS.
In part 1, we have gone through how to integrate BW and BODS using RFC connection.(http://scn.sap.com/docs/DOC-48917)
In this part 2 document, we will try to understand how modelling is done in BODS especially when we have SAP BW as the data target
In part 3, we have gone through the transport mechanism within BODS.(http://scn.sap.com/docs/DOC-52808)
BODS : Version 126.96.36.1993 and BW 7.3SP4
Let me explain this with an real time example.
We have an oracle source table which stores customer call data in the following format:
Oracle table name: LDR_COMMON_INBOUND_CALL_B
We have another excel sheet which stores region data in the following format:
Our idea here is to merge state (here Andhra Pradesh) from first source and region (here 1) from the second source and map it to 0Region object in BW whose structure would be a follows:
All other fields would be mapped(1 is to 1) as per requirement.
We will further use some of the navigational attributes of 0Region later in the Bex reports.
In BODS, Create a Oracle data store and give the required credentials.
Now an oracle datastore is created like shown below.We have a table option under the newly created datastore.Right click on that and use the ‘Import by Name’ option.
Now give the required table/view name and use the import option.
Once we have the successful import, we will have our table name mentioned under the Tables heading
Now use the format option to create a new flat file datastore for importing the excel data:
Now a new object named region will be created as follows:
Now we need to create a target datastore and in this case we will create a SAP BW Target Datastore by giving the BW system credentials
The BW credentials can be obtained from the System entry properties.
In BW7.3 system, we have BO Dataservices as a new source system(the RFC between BW and BODS has not been explained in this document)
Create a new application component.(as a best practise, create MD and TD applicaton components).
In this case, we will create a Transactional datasource under the TD application component
In this case, the datasource was created as follows:
Now go to the BODS server and we will find two options Master and Transaction transfer structure
Since we had created a TD datsource in BW, we will have to import the same under the Transaction transfer structure option.
Now create a Project
Create a new batch job under the project
Batch job would be created as shown below
Create a workflow under the project(not mandatory)
Create a dataflow under the workflow
Now in the designer workspace, we need to model our data.
Drag in the flat file, Oracle and BW target datastores from the left side.
Add the query transformations(available in the right palette).
Join all the objects as follows:
Double click on the flat file datastore. For my case, It would like below:
The Oracle datastore for my case would look like below;
We will be merging both the source datastores in the first query transformation.
We can use different BODS functions to model our data.
In this case, some functions like ‘UPPER'(below screenshot Mapping Tab) have been used to change the lower to upper case.(which purely depends on the type of the requirement)
In our case, a additional query transformation has been added to include some other functions like decode, lpad and so on.(purely depends on the requirement)
Now, go back to BW and complete the modelling up to the cube level.
For BODS related infopackage, we now have a new option ‘3rd party selections’.
Enter the details like ‘Repository’, ‘Job server’ , and Job name(Job name is the batch job that you have created in BODS).
Run the infopackage and now you will see the data in expected format in BW.
Hope you got some understanding on the BODS modelling with BW as a target data store.