Pivoting Data in OWB
By David Allan on Mar 17, 2008
The pivot transformation operator enables you to transform a single row of attributes into multiple rows in an efficient manner. This example illustrates transforming a table that has a row for each year with the quarterly sales in a table with a row for each quarter. The OWB pivot operator makes this simple (there is also an unpivot).
So taking a simple example as follows:
---------- ---------- ---------- ---------- ----------
2005 10000 15000 14000 25000
2006 12000 16000 15000 35000
2007 16000 19000 15000 34000
we wish to transform the data set to the following with a row for each quarter:
---------- -- ----------
2005 Q1 10000
2006 Q1 12000
2007 Q1 16000
2005 Q2 15000
2006 Q2 16000
2007 Q2 19000
2005 Q3 14000
We can design this in the OWB mapping as;
Looking at the internals of the operator we see how this is described. The pivot operator allows you to define the input columns, the output columns and how the data is pivoted. This is achieved by defining a few pieces of information;
- the key columns (the columns from the source that will appear in the output of the pivoted data)
- the row locator (this is the pivot column)
- the pivot transformation (which values to project for the pivoted columns
Then we define the QUARTER column as the pivot column, this is the row locator (in OWB terms).
Finally we define how the row is transformed from a row with columns to a number of rows, we do this by entering a row in the table for each case we desire (so we have a row for Q1, a row for Q2, a row ... etc.).
This makes the map design so much simpler as you can see, since the operator encapsulates the transformation in a simple manner. The example has been scripted (get the script here) so that you can create it and have a look around at how this is done....
Here is the sample data I used also:
-- Execute the following where you deployed SALES_BY_QTR
-- I manually added some rows in SALES_BY_QTR for the example:
insert into SALES_BY_QTR values (2005, 10000, 15000, 14000, 25000);
insert into SALES_BY_QTR values (2006, 12000, 16000, 15000, 35000);
insert into SALES_BY_QTR values (2007, 16000, 19000, 15000, 34000);
The reverse of this scenario is the unpivot, the script for the unpivot example can be found here.
You select the key just like the pivot above, then define the row locator (or unpivot column), defining the values for each match row:
Then define the output attributes for the unpivot:
Finally define the unpivot transformations (how the column data is taken from the matching row):
If your data has many rows with sales values for a quarter (for a single year) you will need to aggregate the data before unpivoting, for example the map below first aggregates and sums sales before unpivoting. The data is grouped by YEAR (key) and QUARTER (row locator) and
the output expression has SUM(SALES), the map then unpivots that data. (you cannot tweak the agg function
just now in the unpivot)
Hope this is useful and helps illustrates the pivoting transformation capability.