0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×

Reverse Pivot Transformation

Reverse Pivot TransformationSummary:

            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.

Prerequisite:

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.

Example Scenario:

  • 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
Material No Make Model Year Color
123 Nissan Sentra 2005 Blue
123 Nissan Sentra 2007 Green
456 Toyota Corolla 2006 red
789 Honda Civic 2005 white
789 Honda Civic 2005 black

Figure 1: Source Data in file or Table 

  • figure2 shows sample target data of our example scenario
Material Make Model Transaction Color
123 Nissan Sentra Sold B&G
456 Toyota corolla X R
789 Civic Civic User Car W&B

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
Reverse Pivot Transformation in SAP BODS
Figure 3: Reverse Pivot Transformation Job Hierarchy
  • 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.
Reverse Pivot Transformation in BODS
Figure4: ETL Flow
  • 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
Reverse Pivot Transform in BODS
Figure5: Query transform
  • 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.
Reverse Pivot Transformation in SAP BODS 2
Figure 6: Reverse Pivot transformation
  • Apply Decode function for color column due to rules.
Reverse Pivot Transformation in BODS 1
Figure7: decode function for color column in Second Query transform
  • Apply Decode function for Transaction column due to rules. 
Reverse Pivot Transformation in BODS 2
Figure8: decode function for Transaction column in Second Query transform
  • Source data in file
Reverse Pivot Transformation in SAP BODS 3
Figure9: source data in file\table
  • Target data in table or file
Reverse Pivot Transformation in BODS 3
Figure9: target data in file\table

Attachment:

  • Use the below file as source for working out on the transformation

Click Here to Download Source File.

  • Import the below .atl file in the Data Services Designer to find the Job for the above transformation.

Click Here to Download ATL File

That’s it.

Enjoy 🙂

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×