Skip to Content

Hi All

        Here I am going to share some of ideas related to Pivot Transforms in SAP BODS and the steps to create it as well

Pivot Transform is kind of ‘Data Integrator Transform’ present among various transforms in the Sap BODS(Designer).

VISUALIZING PIVOT TRANSFORM:-


Pivot Transformation is used to Convert Column Values of table\file into Row Values. Below is a simple example which would help us to

understand this very easily:

pivot ex.png

Here we can see how the column values containing “.NET “and “JAVA” are split into multiple rows.

It relieves us from the need of writing complex formulas, which are required when dealing with operations involving ‘Groups’ or ‘Aggregation functions’.          For e.g. If we want to calculate the total of amount incurred on the .NET and JAVA domains, we would need the across specific year:-

         Select Year, Sum (.NET) + Sum (Java)

However, If we use the ‘PIVOTED OUTPUT’, then our query would be easier:

       Select Year, Sum (Earning)


STEPS TO CREATE THE PIVOT TRANSFORMATIONS IN SAP BODS:


This Transform could be found if we click the ‘Transformation’ icon present under the ‘Local Object Library’ window in SAP BODS Designer as shown below:-

The icon is :-

/wp-content/uploads/2016/03/icon_911300.png(The Green color outline has been highlighted by me)

LOCATION:

/wp-content/uploads/2016/03/location_911301.png


Following is the Excel input file used for this process:

pivot input file.png

Now we need to create a ‘Dataflow’ followed by uploading the Excel file via the ‘Format file’ tab present below the ‘Local objects Library’ window. Then we have to use the ‘Pivot Transform’, followed by populating the data in the temp table which is created by adding the custom ‘Data store’.


The Workflow for the process:

contents of pivot transform.png

(Please ignore the “PIVOT_OUTPUT.txt” file)


THE DATAFLOW CONTENT:-


DF content.png

Checking the data of the PIVOT_INPUT file:


If we click on the Magnifier icon attached on the PIVOT_INPUT (source) then we will get the output below:-                                                                            data pivot inp.png

THE ‘PIVOT EDITOR WINDOW’:-


TRANSFORM OPTION

  • Pivot sequence column : The name we assign to the sequence number column. For each row created from a pivot column, Data Services increments and stores a sequence number. Data Services resets the sequence to 1 when creating an output row from an original row. For example, if the row corresponds to the first pivoted column, the sequence number for the row is 1.
  • Non-pivot columns : The input schema columns that appear in the output schema without modification.
  • Pivot set : The number that identifies a pivot set. For each pivot set, we need to define a group of pivot columns, a pivot data field, and a pivot header name. Each pivot set must have a unique Data field column and the Header column. Data Services automatically saves this information.
  • Pivot columns : A set of columns to be rotated into rows. Describe these columns desired output name in the Header column and corresponding data in these columns inthe Data field column.
  • Data field column : The name of the column that contains the pivoted data. This column contains all of the Pivot columns values. The data type of this column is determined by the data type of Pivot columns. If two or more Pivot columns contains different data types, Data Services converts the columns to a single data type i.e. the data type of the first column we added to the pivot set.
  • Header column : The name of the column that contains the pivoted column names. This column lists the names of the source columns where the corresponding data originated.


INITIAL PIVOT TRANSFORM WINDOW:


Initial pivot editor.png

We have to change the values for the ‘Data field column’ and ‘Header column’ and drag and drop the relevant fields  from the ‘PIVOT_INPUT’ into the ‘Non Pivot Column’ and ‘Pivot Column’ as shown below:-

Pivot_Editor_Window.png

CREATION OF THE TEMPLATE TABLE(Target Table)

Create a Customized Data store Object, followed by creation of Template table. The template table icon will be present in the Tool palette in the ‘Right hand side’ of the designer window.


Template table icon.png

Here ‘test’ is the Customized Data store object created :


/wp-content/uploads/2016/03/temptable_911359.png

EXECUTION OF THE ‘PIVOT JOB’:-

/wp-content/uploads/2016/03/execute_911375.png

Job executed successfully:-

pivot job.png

VIEW THE TEMP TABLE OUTPUT:

Temp_table op.png

For further references you can have a look over the ‘Business Object Data Integrator Reference Guide’ which is available at the following link:-

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

Websites:-

http://sapsimplified.com/sap-bods/pivot-transformation-in-sap-bods/

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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply