SAP BODS – Commonly Used Platform Transforms
Purpose of this Blog :
In our last blog we went through SAP BODS ETL tool snapshot, you can check that out on below link
Today, in this blog i will cover the most commonly used platform transforms in SAP BODS during whole Extraction, Transformation and Load process.
What’s in it for you?
This blog focuses on giving details on how to use commonly used platform transforms of SAP BODS and this will also give you a good start to learn this ETL tool further.
Below platform transforms would be covered in this blog-
General Guidelines –
- Create a Project using Right Click in local object library and give it a relevant name. Open the project in project area
- Add test job and dataflow in the project, you can create one dataflow for each transform practice, thats how I am going to explain here
Case transform is used to split the data based on multiple conditions and redirect the output in different tables according to conditions. This is like a switch statement in other programming languages you may have seen/worked.
To implement Case transform in BODS, use below instructions –
- Use Dataflow named “Case Transform” to implement.
- Pull the source table with some relevant data
- Drag the case transform from – local object library – Transforamtions -Platform -Case (refer screenshot captioned- Case Transform)
- Now double click on case transform to setup different options and split conditions.
- Below are 3 different options available-
- Produce default output with label : This option specifies the label where all the records would be mapped those will not fall in any of the consitions i.e.default case
- Row Can be TRUE for one case only : This option is used to handle the scenario when you have record which can be true for multiple cases but you want it to be true only for one case
- Preserve expression order : This option is used to follow the order of cases/conditions cretaed within case transform, if you want to follow sequential order of conditions in which they are created then this option is checked (refer screenshot Case Transform Options)
- You can use Add option to add different Cases/Conditions
Lets say we have below source data for employee table, we want to split the data based on salary like below
a- Salary Less than 12K
b- Salary Greater than 12K
Below Screenshot shows how to setup case transform for above requirement and Job design-
Below is the output of above case transform setup for employee source data –
Note : The similar data split can be achieved using query transform also, but case transform can be handy when you have cases where multiple conditions can be true and order of execution is important.
Here I will conclude the case trasnform learnings, you can try different scenarios and conditions to see how case transform produces output.
Moving to next commonly used platform transform MERGE-
Merge transform is used to combine the data from multiple sources into one, provided criteria to use merge transform is met.
To implement Merge transform in BODS, use below instructions –
- Use Dataflow named “Merge Transform” to implement.
- Pull the multiple source table with some relevant data
- Drag the merge transform from – local object library – Transforamtions -Platform -Merge (refer screenshot captioned- Merge Transform)
- Connect all the sources to merge transform to get the single output
- Final output structure will be picked up from the first table which would be connected to merge transform
- Below points/criteria to be made sure before using merge transform-
- No of columns/fields in each input source should be same
- Datatypes of fields should match when the fields are in same order
- Order of fields are important though it is not mandatory but if not followed then the output will be distorted and will not be correct
We will use same multiple employee tables as a source which we got as output from case transform
Below screenshot will show you how to setup the dataflow to use merge transform-
Below is the screenshot which will show you output of merge transform of above setup.
We have come to the end of this blog, I have tried to give you the good explanation on 2 basic and commonly used data operations spliting and merging data using case and merge transforms in bods.
I will be covering other platform transforms in sap bods etl tool in my upcoming blogs so stay tuned!!
Please do provide your feedback which will help me in imporving my content and sharing more relevant knowledege on this community
Do follow and subscribe to learn and get insights in the space of Data Migration and Quality and their key topics. Please do share within your connections/networks.
Happy Learning Readers!! 😊