Friday Feb 28, 2014

Pivoting Data in ODI 12c

We have recently added several new Mapping Components in Oracle Data Integrator 12c such as Pivot or Unpivot. In this blog post we will walk you through an example of how to use the new Pivot Component.

You can use the following SQL statements to recreate this example in your environment. It will create the source (PIVOT_TEST) and target (TRG_PIVOT_TEST) tables used in this article in your database then you can reverse engineer them in ODI.

CREATE TABLE pivot_test (
year NUMBER,
quarter VARCHAR2(255),
sales NUMBER
);

insert into pivot_test values (2012, 'Q1', 10.5);
insert into pivot_test values (2012, 'Q2', 11.4);
insert into pivot_test values (2012, 'Q3', 9.5);
insert into pivot_test values (2012, 'Q4', 8.7);
insert into pivot_test values (2013, 'Q1', 9.5);
insert into pivot_test values (2013, 'Q2', 10.5);
insert into pivot_test values (2013, 'Q3', 10.3);
insert into pivot_test values (2013, 'Q4', 7.6);
commit;

CREATE TABLE trg_pivot_test (
year NUMBER,
q1_sales NUMBER,
q2_sales NUMBER,
q3_sales NUMBER,
q4_sales NUMBER
);

Our goal is to pivot the data on the Quarter column when going from PIVOT_TEST into TRG_PIVOT_TEST as shown below:


Follow these steps to add and configure a Pivot Component in an ODI 12c Mapping:

  1. First add the Source table PIVOT_TEST into your Mapping, to do so drag and drop the PIVOT_TEST datastore from the Models into the Mapping.
  2. Next add a Pivot component into the Mapping. This is done by clicking on the Pivot Component in the Component palette and then clicking on the Mapping diagram. A new PIVOT component will appear in the Mapping:

  3. Drag and drop the YEAR column from PIVOT_TEST into the PIVOT component. There is no need to add the QUARTER and SALES attributes yet, they will be used later in the Row Locator and Attributes sections.

  4. Click on the PIVOT component and in the Properties window select the Row Locator panel. In our example the Row Locator will be the QUARTER column which is transposed from rows into 4 columns in our target table TRG_PIVOT_TEST.

  5. Open up the Expression Editor next to the Row Locator field and select the QUARTER column from our source table PIVOT_TEST. Then click OK.


  6. Now specify the various values the QUARTER column can take. This is done using the Row Locator Values table. Click on the + icon under Row Locator Values and add the 4 possible values: ‘Q1’, ‘Q2’, ‘Q3’ and ‘Q4’.

  7. Then click on the Attributes panel and add the 4 output attributes which correspond to each Row Locator values: Q1_SALES, Q2_SALES, Q3_SALES and Q4_SALES.

  8. Next select a Matching Row for the output attributes you just created. The Matching Row values come from the Row Locator Values entered earlier.
    Pick ‘Q1’ for Q1_SALES, ‘Q2’ for Q2_SALES, ‘Q3’ for Q3_SALES and ‘Q4’ for Q4_SALES.
    Finally enter an expression for each of the new attributes, use PIVOT_TEST.SALES for all of them as we are interested in getting the Sales data into those columns. You can type the expression using the Expression Editor or drag and drop the SALES column from PIVOT_TEST into each of the newly created attributes.

  9. Finally add the target table TRG_PIVOT_TEST and connect the PIVOT component to it. Unselect the Create Attributes on Source checkbox in the Attribute Matching window and click OK to finish the Mapping configuration.

  10. 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.
  11. Click on Run to execute the Mapping, 2 inserts are performed and you should see the following data in your target table.

  12. If you review the generated code you will notice that ODI leverages the PIVOT function on Oracle to perform such operation. The Pivot component supports Oracle as well as any other database supported by ODI 12c.

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

Monday Dec 16, 2013

ODI 12c - Pivoting Data and Correlated subqueries

Here are more data transformation illustrations using the ODI 12c SDK demonstrating pivoting/unpivoting data and correlated subqueries. The SDK examples are on the shared java.net site and are in addition to the existing examples I posted on the blog entry on Mapping Ins and Outs here.

You can download these ODI_12c_Mappings_SDK Examples here unzip and inspect the groovy and the images of the mappings within. There are examples from the SQL documentation on correlated subqueries and examples previously posted for OWB here on pivot/unpivot.

The mappings I have covered include (click on hyperlink for an image of each);

  1. unpivot data from a table with YEAR,Q1_sales,Q2_sales,Q3_sales and Q4_sales columns to YEAR,QTR,SALES (UNPIVOT component).
  2. pivot data from a table with YEAR,QTR,SALES columns to YEAR,Q1_sales,Q2_sales,Q3_sales and Q4_sales columns (PIVOT component).
  3. perform a subquery to get the employees who are in a department that exists in the subquery (SUBQUERY_FILTER component).
  4. perform a subquery to get the employees who have a salary greater than the average salary for employees in their department (SUBQUERY_FILTER component).

These examples all use the mapping SDK to build data integration flows, the existing ODI interface SDK is still supported for now. You can see the mapping built from the SDK to perform unpivot of data as an illustration. There are now 14 examples of components in the download illustrating each one, they can operate on arbitrary data and have generic KMs as well as specific Oracle implementations.

Plenty more to show including some of the nitty gritty details on the physical deployment specification side to help illustrate how to build flows using the SDK and backup the javadoc. A lot of great transformation components and plenty more to explore and discover!

Friday Nov 15, 2013

ODI 12c - OWB Migration, Integration Plus Components

Today is another big day for ODI and OWB! Get your hands on the much anticipated functionality covering everything from new components added into the ODI 12c mapping designer, to the ETL design migration utility (announced here) to move designs from OWB 11.2.0.4 into ODI 12c which all complement the integration and auditing of OWB jobs from ODI which was included in 12c.

The patches for ODI and OWB are great news on many fronts, not only in being a major point in the roadmap. As well as the OWB migration this is great for the ODI the product, the new components provide a lot of great capabilities (and will help with a lot of those 'how do I do this' questions).

Included in the ODI patch you'll find a component bundle including transformation components from pivot/unpivot through subquery filtering on to table functions and the like. All great examples of the product's extensibility - a fusion of declarative design and technology specific knowledge modules in harmony. Lots of good stuff that you will hear much more of.

There are multiple patches - if you are only interested in ODI features and enhancements then all you need is the ODI patch below;

  • ODI 12.1.2.0.0 installed (ODI 12.1.2.0.0 plus patch number 17053768)

If you want the OWB migration utility then you will need the following patch to OWB as well as the ODI patch above;

  • OWB 11.2.0.4 installed (OWB 11.2.0.4 plus patch number 17547241)

If you install the ODI patch you will find some new components in the palette, the components let you express many more transformations - these were transformations which could be designed in OWB, now in ODI as well as the components we have knowledge modules to support their code generation for heterogenous environments.

The ODI 12c release included a new ODI Tool and support for monitoring OWB jobs - jobs can be initiated using the tool OdiStartOwbJob tool which is available for use from within packages, procedures, KMs or command line. The jobs can be monitored from ODI, this includes OWB mappings and process flows. The patch provides the migration utility plus components.

In subsequent postings we will deep dive into these areas. 

About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today