Using the Unpivot Component in ODI 12c
By Julien Testut-Oracle on Mar 07, 2014
As we’ve seen last week the latest ODI 12c patches added several new Mapping Components such as Pivot or Unpivot. In this blog post we will walk you through an example of how to use the new Unpivot Component.
You can use the following SQL statements to recreate this example in your environment. It will create the source (UNPIVOT_TEST) and target (TRG_UNPIVOT_TEST) tables used in this article in your database then you can reverse engineer them in ODI.CREATE TABLE unpivot_test ( year NUMBER, q1_sales NUMBER, q2_sales NUMBER, q3_sales NUMBER, q4_sales NUMBER );
insert into unpivot_test values (2012, 10.5, 11.4, 9.5, 8.7); insert into unpivot_test values (2013, 9.5, 10.5, 10.3, 7.6); commit;
CREATE TABLE trg_unpivot_test ( year NUMBER, quarter VARCHAR2(255), sales NUMBER );
Our goal is to unpivot the data on the Quarter columns when going from UNPIVOT_TEST into TRG_UNPIVOT_TEST as shown below
Follow these steps to add and configure an Unpivot Component in a Mapping:
add the Source table UNPIVOT_TEST into your Mapping, to do so drag and drop the
datastore from the Models into the Mapping
add an Unpivot component into the Mapping. This is done by clicking on the
Unpivot Component in the Component palette and then clicking on the Mapping
diagram. A new UNPIVOT component will appear in the
and drop the YEAR column from UNPIVOT_TEST into the UNPIVOT component. There is
no need to add the remaining source attributes yet, we will be using them in
the Unpivot Transforms section later.
click on the UNPIVOT component and select the Attributes panel. We will add 2
new attributes representing the row and value locator attributes: QUARTER
(VARCHAR) and SALES (NUMERIC).
on the General panel and select QUARTER in the Row Locator drop-down list. We
will be performing the unpivot operation using this column.
add the transformations to unpivot the data. Click on the Unpivot Transforms
panel and create a transformation rule for each of the 4 quarters and match the
appropriate QUARTER and SALES values as shown below:
add the Target datastore TRG_UNPIVOT_TEST and link the UNPIVOT component to it.
Leave the default settings in the Attributes Matching panel and click OK
- In this example you can use the default Physical settings for your Mapping. Integration Type is set to Control Append by default and the IKM Oracle Insert is used
click on Run to execute the Mapping, 8 inserts are performed and you should see
the following data in your target table:
- If you review the generated code you will notice that ODI leverages the UNPIVOT function on Oracle to perform such operation. The Unpivot component supports Oracle as well as any database
You can recreate the following example using the ODI 12c Getting Started VirtualBox image which is available on OTN: http://www.oracle.com/technetwork/middleware/data-integrator/odi-demo-2032565.html