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|
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
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.
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-
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.
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-
Click on Case_Region to open the case transformation-
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-
Similarly do for the remaining regions North, South, East and change the Produce Default output value with label as UNK (Unknown)
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.
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-
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.
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-
Click on merge to see that all the 5 tables are merged in to single table-
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-
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.