Case and Merge Transformations in BODS

Case Transformation is used to break a single set of data into multiple sets using a condition.

For example in a spread sheet there is data for Employee Name and Region ID. Using Case and Merge transformation, you can break the Data (Single set of Data) in to multiple sets based on the region ID and store them in separate tables. You can again merge the data from all these tables in to a single permanent table using the Merge transformation.

Below are the steps on how to do it in BODS-

  • Below is the structure of the input file-
Name of Customer Region ID
Samuel 1
Neo 4
David 3
Harry 2
Wendy 1
Gartner 2
Bill 2
Smith 4
Tom 3
Michael 3

Using Case Transformation we will group the data based on region id and store them in separate table

  • Call the spreadsheet above into data services using file format in the local object library

case2

Click on New and Give the below Credentials

Directory : Path to desktop where the file is saved

File Name :- CaseMerge.xls

Work Sheet :- Sheet1

Check Use First Row values as Col Names and click on “Import Schema “ button.

And click on ok.

case3

With this, you have imported the spread sheet data in to the Data Services.

Now Create a Batch Job “Case_N_Merge” and under it, create a Dataflow “DF_Case_Merge”  and in the data flow drag the flat file in to the designer window-

case4

Drag a Query from the Right side pane/ Query under Query transform under local object library. Connect the Spread sheet to the Query, Open the Query and drag the fields from schema in to schema out.

case5

Now Drag  “ Case” transformation to the designer window, which is present under the Platform under the Query Transforms under the local object library, Rename it as Case_Region  and connect the Query to Case as below-

case6

case7

Click on Case_Region to open the case transformation-

case8

Give the cases which will break the single set of data in to multiple sets in our case region id. Click on ADD button. Give the Label “ west” and drag and drop RegionID from schema to the below case window and assign value = 1 as shown below-

case9

Similarly do for the remaining regions North, South, East and change the Produce Default output value with label as UNK (Unknown)

case10

Click back  and create 5 Temporary tables to hold the data ( Multiple sets) and connect the Case to the tables. While connecting just make sure you connect the respective Case to the respective table.

case11

Similarly connect Case UNK to unknown table. Now save and run the job. The output tables will now show the data as below-

West     =  RegionID 1

North    =  RegionID 2

South    = RegionID 3

East        = RegionID 4

Now after running the job click on the magnifying glass to check the data.

For doing Merge Transformation we will make all the region tables (West, East, North, South, Unknown) into permanent tables, so that we can use them as multiple sources and then use merge transformation to combine multiple sets into a single set.

To make the template table as a permanent table, right click on the table and click on import-

case12

Merge Transformation combines multiple sets of information in to single set, provided all the multiple sets should have the same table structure (Field name, data type and Length).Create another dataflow DF_Merge1 and delete the DataFlow  “ DF_Case_Merge”. Now Drag all the above created permanent tables to the designer under the Data Flow “ DF_Merge1” as shown below. These tables will be available under your DataStore Training.

case13

Drag the Merge transformation which is present under the Platform in Query transformations in local object library and connect all the 5 tables to the Merge-

case14

Click on merge to see that all the 5 tables are merged in to single table-

case5

Create a template table and link that with the Merge  and run the job to see that multiple sets of data is merged in to single set-

case15

Click on the magnifying glass symbol on the template table to see the multiple sets of data merged in to single set.

This concludes the transformations Case and Merge.