Pivot Transformation in SAP BODS

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:

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 :-

(The Green color outline has been highlighted by me)

LOCATION:

Following is the Excel input file used for this process:

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:

(Please ignore the “PIVOT_OUTPUT.txt” file)

THE DATAFLOW CONTENT:-

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:-                                                                           

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:

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:-

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.

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

EXECUTION OF THE ‘PIVOT JOB’:-

Job executed successfully:-

VIEW THE TEMP TABLE OUTPUT:

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