post by David Allan
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
- 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.
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
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.
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.
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
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.
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).
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.