ODI 12c's Mapping Designer - Combining Flow Based and Expression Based Mapping
- by Madhu Nair
post by David Allan
ODI is
renowned for its declarative designer and minimal expression based paradigm. The new ODI 12c release has extended this even further to provide an
extended declarative mapping designer. The ODI 12c mapper is a fusion of ODI's
new declarative designer with the familiar flow based designer while retaining ODI’s key differentiators of:
Minimal expression based
definition,
The ability to incrementally
design an interface and to extract/load data from any combination of
sources, and most importantly
Backed by ODI’s extensible
knowledge module framework.
The
declarative nature of the product has been extended to include an extensible
library of common components that can be used to easily build simple to complex
data integration solutions. Big usability improvements through consistent
interactions of components and concepts all constructed around the familiar
knowledge module framework provide the utmost flexibility.
Here is a
little taster:
So what is
a mapping? A mapping comprises of a logical design and at least one
physical design, it may have many. A mapping can have many targets, of any
technology and can be arbitrarily complex. You can build reusable mappings
and use them in other mappings or other reusable mappings.
In the
example below all of the information from an Oracle bonus table and a bonus
file are joined with an Oracle employees table before being written to a
target. Some things that are cool include the one-click expression cross
referencing so you can easily see what's used where within the design.
The
logical design in a mapping describes what you want to
accomplish (see the animated GIF here illustrating how the above
mapping was designed) . The physical design lets you configure how it
is to be accomplished. So you could have one logical design that is realized as
an initial load in one physical design and as an incremental load in
another. In the physical design below we can customize how the mapping is
accomplished by picking Knowledge Modules, in ODI 12c you can pick multiple nodes
(on logical or physical) and see common properties. This is useful as we can
quickly compare property values across objects - below we can see knowledge
modules settings on the access points between execution units side by side, in
the example one table is retrieved via database links and the other is an
external table.
In the
logical design I had selected an append mode for the integration type, so by
default the IKM on the target will choose the most suitable/default IKM - which
in this case is an in-built Oracle Insert IKM (see image below). This supports
insert and select hints for the Oracle database (the ANSI SQL Insert IKM does
not support these), so by default you will get direct path inserts with Oracle
on this statement.
In ODI 12c,
the mapper is just that, a mapper. Design your mapping, write to multiple
targets, the targets can be in the same data server, in different data servers
or in totally different technologies - it does not matter. ODI 12c will derive and
generate a plan that you can use or customize with knowledge modules. Some of
the use cases which are greatly simplified include multiple heterogeneous targets,
multi target inserts for Oracle and writing of XML.
Let's
switch it up now and look at a slightly different example to illustrate
expression reuse. In ODI you can define reusable expressions using user
functions. These can be reused across mappings and the implementations
specialized per technology. So you can have common expressions across Oracle,
SQL Server, Hive etc. shielding the design from the physical aspects of the
generated language. Another way to reuse is within a mapping itself. In ODI 12c
expressions can be defined and reused within a mapping. Rather than replicating
the expression text in larger expressions you can decompose into smaller
snippets, below you can see UNIT_TAX AMOUNT has been defined and is used in two
downstream target columns - its used in the TOTAL_TAX_AMOUNT plus its used in
the UNIT_TAX_AMOUNT (a recording of the calculation).
You can
see the columns that the expressions depend on (upstream) and the columns the
expression is used in (downstream) highlighted within the mapper. Also multi
selecting attributes is a convenient way to see what's being used where, below
I have selected the TOTAL_TAX_AMOUNT in the target datastore and the
UNIT_TAX_AMOUNT in UNIT_CALC.
You can
now see many expressions at once now and understand much more at the once time
without needlessly clicking around and memorizing information.
Our mantra
during development was to keep it simple and make the tool more powerful and do
even more for the user. The development team was a fusion of many teams from
Oracle Warehouse Builder, Sunopsis and BEA Aqualogic, debating and perfecting the mapper in ODI 12c. This was quite a project from supporting the
capabilities of ODI in 11g to building the flow based mapping tool to support
the future.
I hope this was a useful insight, there is so much more to
come on this topic, this is just a preview of much more that you will see of
the mapper in ODI 12c.
