Pivoting Data in OWB

转载 2012年03月29日 19:14:56

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:

YEAR   Q1_sales      Q2_sales Q3_sales   Q4_sales
---------- ---------- ---------- ---------- ----------
      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
Firstly in our example we define the key column to be YEAR, this will be the same for each pivoted row;

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.


数据仓库(七):Oracle Warehouse Builder(OWB)创建数据仓库


数据挖掘:Top 10 Algorithms in Data Mining(六)PageRank


Displaying a Sorted, Paged, and Filtered Grid of Data in ASP.NET MVC

A Multipart Series on Grids in ASP.NET MVC Displaying a grid of data is one of the most common ...

Oracle Built-in Data Types

The table that follows summarizes 总结 Oracle built-in data types. Refer to the syntax in the precedin...

Recent Evolution of Zero Data Loss Guarantee in Spark Streaming With Kafka

When properly deployed, Spark Streaming 1.2 provides zero data loss guarantee.

Accessing static Data and Functions in Legacy C

http://www.renaissancesoftware.net/blog/archives/430 http://www.renaissancesoftware.net/blog/archi...

New Apache project will Drill big data in near real time

New Apache project will Drill big data in near real time Dremel-based project accepted as...

Advanced Topics in Data Mining Spring 2011

Books (PDFs): Mining Massive Datasets by A. Rajaraman, J. Ullman. Networks, Crowds, and ...

How Data Is Stored In CEPH Cluster

How Data Is Stored In CEPH Cluster HOW :: Data is Storage Inside Ceph Cluster  This...

Saving data to a file in your Android application

Internal storage Each application has its own private internal storage to save files. This is the...