Data modelling in BODS with BW as data target - Pa...

Technology Blogs by Members

Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!

Hi All,

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)

Environment:

BODS : Version 14.0.3.273 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)