Creating First SAP BODS Job

By | January 25, 2017

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.

SAP BODS Job Designer Overview
Lets see the above labelled sections in brief.

Tool Palette

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.

Project Area

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.

Workspace

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.

SAP BODS JOB 1

SAP BODS JOB 2

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.

SAP BODS JOB 3

SAP BODS JOB 4

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_*.

SAP BODS JOB 5

SAP BODS JOB 6

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.

SAP BODS JOB 7

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.

SAP BODS JOB 8

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.

SAP BODS JOB 10

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

SAP BODS JOB 9

Whenever you drag a table to workspace you can either make it source or target.

SAP BODS JOB 11

For this job we will use EMP table as source and EMP_DS as target.

SAP BODS JOB 12

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.

SAP BODS JOB 13

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.

SAP BODS JOB 14

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.

SAP BODS JOB 15

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.

SAP BODS JOB 16

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.

SAP BODS JOB 17

Once done, you can validate your job from the validate button on the toolbar to identify errors if any.

SAP BODS JOB 18

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.

SAP BODS JOB 21

SAP BODS JOB 19

To execute the job you can either right click the job and choose execute or can click the execute button from the toolbar.

SAP BODS JOB 20

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.

SAP BODS JOB 22

Also you can go to monitor tab where you can view the number of records transferred.

SAP BODS JOB 23

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.

SAP BODS JOB 24

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.

Leave a Reply

Your email address will not be published. Required fields are marked *