Reverse Pivot Transformation in SAP BODS
Reverse Pivot Transformation
Reverse Pivot Transformation is used to Convert Rows Values of table\file in to Column Values.it creates a new column for every value that is specified as a pivoted column. Hence to summarize, this transforms converts row values to column values. Creates one row of data from several existing rows. The Reverse Pivot transform allows you to combine data from several rows into one row by creating new columns. For each unique value in a pivot axis column and each selected pivot column, Data Services produces a column in the output data set.
Following setting is required to be done at the Reverse_Pivot transform level, options available under Reverse_Pivot transform:
1) Non-pivot columns -> List of columns that needs to be displayed as it is in the target.
2) Pivoted columns -> The column data which the user wants to transfer into the same row. User can define some default value for the pivoted column selected.
3) Output Column ->
a) Pivot axis column: Based on the column selected under pivot axis column, the columns will be created in the target table.
b) Duplicate value: Incase the dataset holds duplicate values then the user can select either to fetch row, or last row, or can also abort the row.
c) Axis value: The value of the pivot axis column that will be representing a particular set of output column. Atleast one value should exists in the Axis column for a selected pivot axis column.
4) Column Prefix-> User can prefix column names for the transferred data into the new column.
- In this scenario, based on below rules we need to get output by using reverse pivot transformation, decode, gen_row_num_by_group functions in Query Transformation.
- In source we have five columns like Material no, Make, Model, and Year & Color shown in figure1. But in target we need to get extra column called’ Transaction‘. When Make is Nissan, Model is Sentra and year is 2005, default ‘Transaction‘ column is to get output as ‘sold‘ else blank and then if Make is Honda ,Model is civic and year is 2005, default ‘Transaction‘ column is to get output as ‘used car’ shown in figure2.
- whereas to color column, If color is red, default to ‘R’,If color is blue, default to ‘B’,If color is Green, default to ‘G’,If color is white, default to ‘W’,If color is Blue and Green, default to ‘B&G’& If color is White and Black, default to ‘W&B’.
- figure 1 shows sample source data of our example scenario
Figure 1: Source Data in file or Table
- figure2 shows sample target data of our example scenario
Figure 2: Target Data in file or Table
- In project area we have to create project ,Job, work flow and data flow shown in figure 3
- The following figure 4 indicate ETL flow of the job in data flow.
- Create excel work book by using “Imperio_Rev_Pivot ” file and drag into work area.
- Drag Query transformations ,Reverse Pivot transformation and template table into workarea after that connect all objects same as figure 4.
- Shown in figure5 under query transformation map all the columns in schemain in to schemaout.
- create new output column(sequence)which is used as pivot axis column in Rev_Pivot transformation
- Show the below figure6 drag the Material No,Make, Model columns in Non-pivot columns and Year ,Colour columns in pivot columns.
- Select pivot axis column as sequence and generate axis column and column prefix shown in figure 6 below.
- Apply Decode function for color column due to rules.
- Apply Decode function for Transaction column due to rules.
- Source data in file
- Target data in table or file
- Use the below file as source for working out on the transformation
- Import the below .atl file in the Data Services Designer to find the Job for the above transformation.