How to use Data Services Pivot Transformation
The Pivot transformation allow the developer to change how the relationship between rows is displayed. For each value in each pivot column, Data services produces a row in the output data set. We can create pivot sets to specify more than one pivot columns. It basically convert Columns to Rows.
Implementation:
Let us consider we have source data of sales for different stores based on quarters. we may want to transform the sales data based on the quarters for each of the stores. Find below the implementation Data Flow.
To solve the problem we are using the SAP Data Services Pivot transform. We first set the Pivot sequence column values as PIVOT_SEQ. So the sequence value will be 1 for QUARTER1 column, 2 for QUARTER2 column. When transforming to multiple rows, select the STORE as the NON-Pivoted column. In this example, we are dealing with only one Pivot set for QUARTER. Hence the Pivot set is set to 1. Next include all the input QUARTER columns as the Pivot Columns. Set the output Header Column name as QUARTER and the coresponding Data fields as SALES.
Finally we map the quarter to the Pivot Sequence in the Query transform. So, for the first quarter sales value that corresponding QUARTER will be 1 and so on.
The Pivot transformation allow the developer to change how the relationship between rows is displayed. For each value in each pivot column, Data services produces a row in the output data set. We can create pivot sets to specify more than one pivot columns. It basically convert Columns to Rows.
Implementation:
Let us consider we have source data of sales for different stores based on quarters. we may want to transform the sales data based on the quarters for each of the stores. Find below the implementation Data Flow.
To solve the problem we are using the SAP Data Services Pivot transform. We first set the Pivot sequence column values as PIVOT_SEQ. So the sequence value will be 1 for QUARTER1 column, 2 for QUARTER2 column. When transforming to multiple rows, select the STORE as the NON-Pivoted column. In this example, we are dealing with only one Pivot set for QUARTER. Hence the Pivot set is set to 1. Next include all the input QUARTER columns as the Pivot Columns. Set the output Header Column name as QUARTER and the coresponding Data fields as SALES.
Finally we map the quarter to the Pivot Sequence in the Query transform. So, for the first quarter sales value that corresponding QUARTER will be 1 and so on.
0 comments:
Post a Comment