Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
ashishpinjwani
Explorer
4,520
Hello Readers,

 

Purpose of this Blog :

In our last blog we went through SAP BODS ETL tool snapshot, you can check that out on below link

https://blogs.sap.com/2022/10/02/sap-data-service…ap-bods-snapshot/

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-

  • Case

  • Merge


Platform Transforms-

General Guidelines -

  1. Create a Project using Right Click in local object library and give it a relevant name. Open the project in project area

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

General Desc-

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.

Implementation-

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)



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)





Case Transform Options




  • You can use Add option to add different Cases/Conditions


Example-

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


Employee Source Table - Case Transform


Below Screenshot shows how to setup case transform for above requirement and Job design-


Case Transform Example



Case Trasnform Job Design


Below is the output of above case transform setup for employee source data -


Case Transform Output


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

General Desc-

Merge transform is used to combine the data from multiple sources into one, provided criteria to use merge transform is met.

Implementation-

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)



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





Merge transform Setup


Example -

We will use same multiple employee tables as a source which we got as output from case transform


Merge Transform Source data


Below screenshot will show you how to setup the dataflow to use merge transform-


Merge Transform Job Design


Below is the screenshot which will show you output of merge transform of above setup.


Merge Transform Output


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!! 😊

 
Labels in this area