In the previous posts we have seen how to Setup BODS Repository and Create Datastore in SAP BODS which leaves us with all the required things to create our first SAP BODS Job. In this article we will get familiar with BODS Designer and will create our first simple job which will extract data from one table of SQL Server and will populate the other table using a basic transformation.
Getting Familiar With BODS Designer
Below is the start screen of BODS Designer which appears after login.
This section contains all the shortcuts commonly used. Few important buttons it has are :
Create Project, Open Project, Save All, Close All, Execute, Debug etc.
This is the area which will show all the jobs in a particular project, and once we navigate into them the objects within them are displayed on workspace.
Local Object Library
As we have already discussed about repositories in Understanding SAP BODS article, we can have objects under local and central repository. Under this section we can view and create objects for local repository. It has different tabs where different types of objects are handled.
This is the area where the actual designing of job takes place, we can drag objects from local object repository to workspace and start connecting them in order to create a meaningful flow of any module.
So now we have a basic idea of how the designer looks like, lets get started and create our first simple SAP BODS job.
Before moving ahead if you can recall the object hierarchy explained in the previous post (Understanding BODS Job Components) it will be very useful because the below steps will be in order of the same hierarchy.
Step 1 – Creating Project
In this step we will create a project first so that we can keep our jobs at one place.
Choose New from the tool palette, and then give a name to your project.
Once the project is created you can see on the left hand side under the project area the name of the project.
Step 2 – Creating Job
After the project is created right click on the empty section under project area or on the project name and chose create new batch job, then enter name for your job and a blank job will be created.
Step 3 : Creating Workflow
After creating the job if you double click on the job the blank workspace will open up which is the area where we will design our job. On right hand side you can find a quick access toolbar from where we can add the job components. You just have to choose the component and click on the empty workspace it will be added to your job and you can name it. Usually as per the naming standards we start the workflow name as WF_*.
Step 4 : Creating Dataflow
After creating the workflow you can double click on it to navigate inside it and there you can add dataflow in the similar manner you did for workflow from quick access toolbar.
Step 5 : Building source and target tables for dataflow
For tables the initial requirement is creating a datastore which we already did in the previous post (What is Datastore in SAP BODS ?), so if you missed that you can catch up now.
Once the setting up of datastore is done you can import the tables to SAP BODS. Double click on the datastore and it will show you the external and repository metadata, from external metadata chose your table and right click import them.
Here I have selected two tables, one will be used as source and the other as target. You can view all the imported tables in a datatsore under tables.
Before starting the actual dataflow design, let me explain to you what we are going to achieve in this job.
We have two tables with different schemas as stated below. Using BODS we will just add data to new columns in table EMP_DS (ChangedBy and ChangedOn).
Whenever you drag a table to workspace you can either make it source or target.
For this job we will use EMP table as source and EMP_DS as target.
We can simply connect these two tables and the data from source table will be copied to target table on execution. But since we have to add data to two new columns we will have to use a transformation which will do the tasks for us. For basic overview of transformation you can view the previous post (Understanding BODS Job Components) and for detailed information on transformation we will have an article later. For now you can take transformations as a object which will take source inputs, modify them and map them to target output. For this job we will use QUERY transform, this is one of the most commonly used yet the most powerful transform in BODS. We will see its full features in a different post but for now we will map the source columns to target columns and use BODS to assign values to new columns.
From Local Object Library, under transforms tab you will find QUERY transform in Platform transformations. You can simply drag this to workspace to use it.
QUERY transform takes at least one input and one output, so now connect your source table and target table using a connector from the small box in front of the table icon.
Once you have connected source and target to query transform, you can view schema in and schema out and various other options related to query transform.
To map a column from source to target just drag the column from schema in to schema out on the column name which you want to map. On the bottom side you can see the mapping tab which will show the mapping details for each column, you can map using source columns, fixed values, or some functions. In this tutorial we will map first three columns from source, ChangedBy as fixed value and ChangedOn as function.
In the below screenshots you can see the mapping details.
In the above screenshot you can see the first three columns are mapped directly from source table and for ChangedBy I have used a fixed value – which is passed in single quotes. Similarly for ChangedOn we have used a function sysdate() which will return the current system date and time.
Once done, you can validate your job from the validate button on the toolbar to identify errors if any.
Now you job is all complete and you can execute it to see the results. Before executing the job let us see the data first so that we can compare after the completion of job. To view data you can use small lens icon on the bottom of table icon.
To execute the job you can either right click the job and choose execute or can click the execute button from the toolbar.
It will show you various execution options in the next screen which we will discuss later, for now you just press OK. Job Log shows every activity the job is carrying out and once it is completed the below message appears.
Also you can go to monitor tab where you can view the number of records transferred.
If there is any error in the job the third button gets activated and here you can view your error.
So now the job is completed lets see the data once again.
Now you can see that the target table is populated with 5 records with ChangedBy and ChangedOn values.
So this is all it takes to create a simple SAP BODS job, we will take a look into deeper and complex scenarios later till then keep learning.