Inside Oracle's Data Integration community

User Interface and Terminology Changes in ODI 12c - Part 2

In the first
blog post in this series we have covered many of the UI changes made in ODI
12c. In this second blog post we will continue covering some of the changes
made at the Mapping level.

Declarative Flow-Based Design

The first major change you are probably going to see when
using ODI 12c coming from earlier versions is that we have augmented the ODI
Declarative Design concept with Flow-Based elements. The goal has been to take ELT/ETL Mapping design to the next level by providing greater flexibility and
advanced features like new Mapping Components while still keeping the Logical
and Physical representations separate. What it means in practice is that you
can now do more with ODI Mappings such as writing to multiple targets or
ordering your transformations the way you want it while still being more
productive than you would be in a conventional ETL tool.

As an example I’ve added below a screenshot of an ODI 11g
Interface and then a screenshot of its corresponding Mapping in 12c:

Aggregate Functions

In ODI 11g aggregate functions were directly entered in the
target datastore mapping expressions and the group by expression was
automatically created by ODI.

In ODI 12c aggregates must now be performed using the
Aggregate Component which is found in the Mapping Component palette. The
Aggregate component allows you to specify manually the group by and having
clauses which wasn’t possible in earlier ODI releases.

Using aggregate functions in a target datastore expression
will now trigger a Mapping validation error and prevent its execution.

Distinct

Doing a distinct operation in ODI 11g required checking the
‘Distinct Rows’ checkbox in the Target Properties panel in the Flow tab. In 12c
doing a distinct operation is more straightforward and is defined explicitly
using the Distinct Component which is available from the Mapping Components
palette.

Filter

Filters can still be defined in a similar way to ODI 11g by
simply drag and dropping a source Datastore attribute into the Diagram area
surrounding it. It is also possible to use the new Filter Component from the
Mapping Components palette. The Filter Condition is then provided in the
Properties of the Filter Component.

Join

Joins are created using the new Join Component from the
Mapping Components palette. Outside of a Dataset Component (which we will
review later), it is not possible anymore to drag and drop a column from one
source Datastore to another one to create a join like in ODI 11g. Once you have
added the Join Component into the Mapping diagram you can type the Join
expression manually or drag and drop columns/attributes from the source
datastores into the Join Component to build the Join expression graphically.

Ordered Joins

If you are using ordered joins in ODI 12c make sure you check
the ‘Join Order’ checkbox and provide an order number in the ‘User Defined’
field. If no value is provided in the ‘User Defined’ field the ‘Join Order’
checkbox will automatically be unchecked by ODI. Those fields are available in
the Condition panel of a Join Component Properties window as shown below.

Lookup

The Lookup wizard found in ODI 11g has been replaced by a new
Component named Lookup. We also spent some time optimizing the Lookup parameters
to make it more intuitive and powerful. It is now, for example, possible to
define what happens if there are multiple rows matched or if no records are
being matched.

We still have many more enhancements to cover, stay tuned for the last post in this blog series!