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:

  1. First
    add the Source table UNPIVOT_TEST into your Mapping, to do so drag and drop the
    datastore from the Models into the Mapping
  2. Next
    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
    Mapping:

  3. Drag
    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.

  4. Then
    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).

  5. Click
    on the General panel and select QUARTER in the Row Locator drop-down list. We
    will be performing the unpivot operation using this column.

  6. Next
    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:

  7. Finally
    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

  8. 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
  9. Finally
    click on Run to execute the Mapping, 8 inserts are performed and you should see
    the following data in your target table:

  10. 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