Pivot Transformation with Multiple Sets processing in Data Services

[Former Member]

Pivot Transformation – Multiple in Data Services

Here I am not disclosing the business requirement but just want to illustrate with sample job…please have a look….

What is Pivoting ?

Pivot transformation allows us 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.

The below is the source file where it has cost center information comprises of monthly transaction values and GRP values for a financial year (12 months).TRX and GRP values are the pivot columns here and will see how the data should converted to 12 rows per cost center comprises of TRX and GRP values in a row.

Note:-

Creation of format files for excel is not covered

Below is the input file data

Create the format for excel work book, drag and drop source file and pivot transform in the work area .Map it to pivot transform as shown below.

Double click the pivot transform,

• select the non-pivot columns from source and drag it to Non-Pivot Columns section
• select the first set of Pivot columns(TRX) from pivot transform source fields and drag it to Pivot Columns section and make the pivot set 1.Give the appropriate display values in Data field column and Header column value

• Add one more pivot set2, select the second set of Pivot columns (GRP) from pivot transform source fields and drag it to Pivot Columns section  and Give the appropriate display values in Data field column and Header column value

Select the target table where the data to be loaded and map it.

            

Validate and execute the job and check the details as follows. There are 3 source records and converted to 12 rows per 1source record. Total we can see 3 x 12 = 36 records.

Hope the example is helpful to understand the usage of Pivot Transform with multiple sets.