Skip to Content
Author's profile photo Moumita Maity
Moumita Maity
March 18, 2016 3 minute read

Reverse Pivot Transform in SAP BODS

Hi All

In this document I am going to share few details on the ‘Reverse Pivot Transforms’ in SAP BODS-Designer and steps to create it with a simple example.

REVERSE PIVOT TRANSFORM:-

This transform could be categorized under the ‘Data Integrator Transform’ in SAP BODS. This transform converts the Rows into Columns. It will group the datasets of different rows into a single row with different 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. It basically creates one row of data from several existing row.

EXAMPLE SCENARIO

Below is a simple example which helps us in understanding the Reverse Pivot transform concept.

Reverse Pivot example.png

STEPS TO CREATE THE REVERSE PIVOT TRANSFORMS:-

This transform could be located under the ‘Transform tab’ present in the ‘Local Object Library’ window in SAP BODS Designer .Its denoted by the icon:-

Rev_pivot_loc.png

LOCATION:-

Rev_pivot_loc - Copy.png

Following is the Excel input file for the process:-

Revr-pivot-in.png

The JOB/WORKSPACE CONTENT:

Rev_Pivot_contents.png

THE DATA-FLOW CONTENT:

Rev_Pivot_DF_content.png

View the data in the Input (Source) file:-

Rev_Pivot_inp_content.png

REVERSE PIVOT EDITOR WINDOW:-

TRANSFORMS:-

  • Non-pivot columns : The columns in the input schema that will appear in the output schema without any modifications.
  • Input data is grouped : Enable this option if the input rows are already sorted based on columns specified in the “Non-pivot columns” list. This will improve the performance of the transformation.
  • Pivoted columns : The columns containing data that we want to rotate or convert into the same row. A set of columns will be created for each unique values in the Pivot axis column.
  • Default value : The value stored when the rotated column has no corresponding data. The default is “null” if we do not enter a value. Do not enter a blank.
  • Pivot axis column : The column that determines what new columns are needed in the output schema. At run time, a new column is created for each Pivoted column and each unique value in this column.
  • Axis value : The value of the pivot axis column that represents a particular set of output columns. A set of Pivoted columns is generated for each axis value. There should be one Axis value for each unique value in the Pivot axis column.
  • Column Prefix : Text added to the front of the Pivoted column names when creating new column names for the rotated data. An underscore separates the prefix name from the pivoted column name.

       Duplicate value : Action taken when a collision occurs. A collision occurs when there is more than one row with the same key and value in the Pivot axis column. In this case, we can select either the first row or the last row, or we can abort the transformation process.

The Initial window before mapping:-

Initial Rev_Pivot.png

Now we have to drag and drop the values from the ‘Input Schema’ to the respective windows as shown:-

Rev_Pivot_editor_window.png

EXECUTION OF THE JOB AND THE TRACE LOG FILE:

Just right click on the Reverse_Pivot job and execute,then the following window will open displaying the success message at the end:-

rev pivot trace.png

The final output of the Target (template table):-

If we click the magnifier icon attached with the target-temp table then we can find the following output data:

Rev_Pivot _Output.png

For more information following sites will be useful:

http://help.sap.com/businessobject/product_guides/xir2acc/en/DIReferenceGuide.pdf

http://dwbi.org/etl/sap-data-services/102-how-to-use-data-services-reverse-pivot-transformation

http://dwhnotes.com/etl-tools/sap-bo-data-services/rpivot-transform

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.