Skip to Content

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

/wp-content/uploads/2014/02/1_394299.png

We have another excel sheet which stores region data in the following format:

/wp-content/uploads/2014/02/2_394300.png

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:

/wp-content/uploads/2014/02/3_394307.png

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.

/wp-content/uploads/2014/02/5_394309.png

/wp-content/uploads/2014/02/6_394312.png

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.

/wp-content/uploads/2014/02/7_394313.png

/wp-content/uploads/2014/02/8_394314.png

Now give the required table/view name and use the import option.

/wp-content/uploads/2014/02/9_394315.png

Once we have the successful import, we will have our table name mentioned under the Tables heading

/wp-content/uploads/2014/02/10_394316.png

Now use the format option to create a new flat file datastore for importing the excel data:

/wp-content/uploads/2014/02/11_394317.png

/wp-content/uploads/2014/02/12_394319.png

/wp-content/uploads/2014/02/13_394320.png

Now a new object named region will be created as follows:

/wp-content/uploads/2014/02/14_394321.png

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

/wp-content/uploads/2014/02/15_394322.png

The BW credentials can be obtained from the System entry properties.

/wp-content/uploads/2014/02/16_394323.png

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)

/wp-content/uploads/2014/02/17_394324.png

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

/wp-content/uploads/2014/02/18_394325.png

In this case, the datasource was created as follows:

/wp-content/uploads/2014/02/19_394327.png

/wp-content/uploads/2014/02/20_394330.png

Now go to the BODS server and we will find two options Master  and Transaction transfer structure

/wp-content/uploads/2014/02/21_394334.png

Since we had created a TD datsource in BW, we will have to import the same under the Transaction transfer structure option.

/wp-content/uploads/2014/02/22_394335.png

/wp-content/uploads/2014/02/23_394337.png

Now create a Project

/wp-content/uploads/2014/02/24_394338.png

Create a new batch job under the project

/wp-content/uploads/2014/02/25_394339.png

Batch job would be created as shown below

/wp-content/uploads/2014/02/26_394340.png

Create a workflow under the project(not mandatory)

/wp-content/uploads/2014/02/27_394341.png

Create a dataflow under the workflow

/wp-content/uploads/2014/02/28_394342.png

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:

/wp-content/uploads/2014/02/30_394343.png

Double click on the flat file datastore. For my case, It would like below:

/wp-content/uploads/2014/02/31_394476.png

The Oracle datastore for my case would look like below;

/wp-content/uploads/2014/02/32_394477.png

We will be merging both the source datastores in the first query transformation.

/wp-content/uploads/2014/02/33_394541.png

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)

/wp-content/uploads/2014/02/34_394542.png

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)

/wp-content/uploads/2014/02/35_394544.png

Now, go back to BW and complete the modelling up to the cube level.

/wp-content/uploads/2014/02/36_394545.png

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).

/wp-content/uploads/2014/02/37_394546.png

Run the infopackage and now you will see the data in expected format in BW.

/wp-content/uploads/2014/02/38_394547.png

Hope you got some understanding on the BODS modelling with BW as a target data store.

BR

Prabhith

To report this post you need to login first.

15 Comments

You must be Logged on to comment or reply to a post.

  1. Shanthi Bhaskar

    Hi Prabhith,

    Very Good Document. I have one doubt here do you able to use 7.x Datasource (RSDS) in BODS as a Target???

    In the past when i tried do this i could able to connect only 3.x datasources.

    Thanks,

    SB.

    (0) 
  2. Dae Jin Swope

    Hi Prabhakaran,

    Great piece and well explained.  I have a more abstract question.  Why does BW even need to use BODS vs DB connect or HANA Virtual Tables and use the BW or Hana Studio ETL functionalities?  It seems BODS just adds another level the data has to move thru.

    Regards,

    Dae Jin

    (0) 
  3. Virag Upadhyay

    Hi Prabhith,

    I just go through ur this document, nicely explained.

    But I have one question for you.. can you please tell me how to how to do the same with delta load.

    Actually, as per the requirement I want to design job in Data Services, here my

    source is MySql and target is BW.

    I am successfully done with full load but not getting to implement/design the job for delta.

    Hope you know this.

    Thanking you in anticipation.

    Regards,

    Veer

    (0) 

Leave a Reply