Aim:To load data from third party system (Oracle) to BI/BW using Business Objects Data Services (BODS)
Consider the below data from Oracle which is obtained using a select statement on a table "SITES".
Select ID, CODE, COMPANY_ID, STATUS from SITES where ID in (9427,9430,9510,9429) to get the below output in Oracle. This data will be loaded to SAP BW using Business Objects Data Services.
Steps in BW:
Create Infoobject TPLTID as target object with attributes TPLCODE, TPLCOMP and TPLSTATUS.
For the master data infoobject create a 3.x Infosource as below:
While creating the Infosource it will prompt for Infosource name and description.
After creating the infosource TPLTID_IS, create transfer rules and assign the Business Objects Data Services (BODS) BO_DI_DEV as source system.
su
Assignment of source system "BO_DI_DEV" is done as follows:
After creating the transfer rules, the structure of the data source will be as follows:
The flow for infoobject TPLTID will be as below after the creation of transfer rules and source system assignment.
For the master data Infoobject create Transformations as below:
After creating the transformation, create a data transfer process to load the data in BW.
Steps in Business Objects Data Services (BODS):
Step 1:Create Project
A project in Business Objects Data Services is used to hold the jobs created. A new project "TEST" has to created.
Step 2: Create a Batch Job
A batch job in Business Objects Data Services is used to hold the associated objects such as Datastore, source objects, target objects and transforms.A new batch job ZPLTID_IS is created as below:
Step 3: Identifying the Source Table and Target Infosource in Data Store.
In our case the source table in Oracle is SITES and the target infosorce is TPLTID_IS. The SITES table need to imported as below:
On re-importing the table, if it is new table it will be copied to BODS and if the table is already existing it will be refreshed.
On re-importing the table, it will be visible in Data Stores:
Step 4: Creating a Data Flow using "Transform:Query"
For creating a data flow, Datastores of Source (Oracle), Target (BW) and Transforms are needed.
Now drag the source table "SITES" from datastores and "Make Source" as below:
Drag Infosource TPLTID_IS from Datastores pane and Transform-Query from Tranforms pane.
BW Datastore pane:
The Transform Pane in Business Objects Data Services will be as below.We have to use "Transform:Query" for mapping the source and target fields.
The data flow will be as below after dragging the objects from Data Stores and Transform pane.
Mapping between source and target has to be done using "Transform:Query".
On double clicking the Query,the below screen will appear for mapping the fields:
Fields after mapping will be as follows:
Since only four plants need to loaded,a where condition should be applied as below,so that only the necessary records are loaded.
The data flow after creating the objects will be as follows:
Now save all the objects created.
Execute the job to load the data from Oracle to BW.
After the data load is completed, system will prompt that the "Job ZPLTID_IS completed".
Now in BW execute the DTP created to load data to the infoobject.
The infoobject will contain the below data, after the data is loaded in BW.
3 Comments
