Pivot Transformation in SAP BODS
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)
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’:-
- 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:-
Suppose everyday new column added should be changed as row.Is it acheivable in this?
No, the pivot columns cannot be dynamic. But isn't that requirement a bit odd? After 10 years you will have 3650 columns in the source table???
Is that really the case or did I get your requirement wrong?
Maybe a compromise might do the trick. You add *somehow" a view on top of your table and the view has always 365 column, the last 365 day-columns. Then the view is static and you can do use a pivot. And the problem of the dynamic content is at the point of "How can I update such view every day".
Hi Werner ,
Consider for example :
Country 11/04/2020 12/04/2020
X 10 20
Y 1 3
And for the next day
Country 11/04/2020 12/04/2020 13/04/2020
X 10 20 15
Y 1 3 100
So even for the date 13/04/2020 the transformation need to apply.
Okay, so I understood correctly. Will new columns be added or old columns removed also?
And my idea was to create a view on top of the table like
On the next day, the view is replaced with a new version
This way the view structure remains constant and can be used in BODS. And creating a new view is easier than creating a new dataflow every day.
Or maybe a view that has year, month and 31 columns, one per day?
I did not get the idea Please explain me.
And it must hold records from Jan to Current date.And source I am using is csv which is everyday updated.
I would like to restart the discussion and start at the beginning. What do you have and what do you want to get.
When a CSV file is the starting point, how does it look like, how does the file format definition in DS look like, who creates that DS object (as it seems to have more columns every day) etc...
I have created seperate thread for the above :