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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 | |
2 | |
2 |