Oracle Data Integrator 11g Bootcamp
Lesson 8 : Interfaces
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Agenda
Understand the role of interfaces in projects
See how interfaces split mappings into business logic and technical details
Define basic interfaces involving joins and transformations
Create filters within interfaces
Create temporary interfaces as data sources
Add additional datasets through set operations
Add check controls into mappings to handle data auditing
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Interfaces within ODI
Interfaces define the mappings between sources and targets
Uses an E-L-T approach
Extract from sources; Load onto target; and Transform
Defined using the Designer navigator
Sequenced using packages
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Business Rules and Technical Details
ODI splits data interfaces in to business rules (mappings), and
technical implementation (flows)
Business rules define what goes where,
and using which transformation rules
Technical implementation defines
how data is moved
Changed Data Capture
SQL to SQL
File to SQL
Slowly Changing Dimension
Allows you to split mapping role into
business analyst and technical specialist
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Interface Designer within ODI Studio
Available Sources & Targets
Property Inspector
drop objects onto
Define keysDtorag
beand
used
mapping
canvas
Expressions
for columns
Target
Datastore
Access
files,
database tables,
XML Canvas
Other properties
of the
datastore
Sources
files & other datastores
Datastore that will be loaded
Data sources for the interface
by the interface
Add joins, filters
Columns can be transformed
Additional data sets through
Can be defined as temporary
set operations (union, intersect..)
interface (input to other interface)
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Components of an Interface
An interface is made up of three elements
The Mapping : the set of logical expressions mapping sources to target
The Flow : the choice of KMs plus their run-time settings
Controls (optional) : data-checking KMs that enforce data rules
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating an Interface Step 1 : Create & Name Interface
Right-click on Interfaces section in Project panel, select New Interface
Name the interface, provide description
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating an Interface Step 2 : Select Sources
Drag and drop source datastores from Models panel onto sources area
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating Joins Between Multiple Source Datastores
More than one datastore can be dropped onto the sources canvas
Join datastores by dragging and dropping key columns between datastores
Set join condition in the properties panel if join is non-equijoin
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating Filters
Individual datastores can be filtered
Drag and drop the column from the datastore onto the canvas
Set filter condition in the properties inspector
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Combining DataSets using Set Operations
DataSets with same column definition can be combined using set operations
Union, Union All, Intersect, Minus etc
Press Add/Remove Dataset button, choose set operation
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating an Interface Step 3 : Select Target Datastore
Interfaces have one target datastore
Drag and drop from the Models panel into the Target Datastore area
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating an Interface Step 4 : Define Mappings - Part I
Columns from the source area can either be dragged and dropped directly onto
the target datastore
Or they can be created or transformed using SQL expressions
SQL syntax is based on where the transformation is executed
Source, staging or target
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating an Interface Step 4 : Define Mappings - Part II
Execute on property specifies where the mapping expression will get substituted
in the query generated by KM task
Source - substituted in query to load data from source datastores into C$ table
for LKM and I$ table for IKM
Staging - substituted in query to load data from C$ table into I$ table for LKM or
source datastores into I$ table for IKM
Target - substituted in query to load data into target datastore either from I$
table or source datastores
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Temporary Datastores ( Yellow Interfaces )
Interfaces can become inputs into other interfaces
Designated by yellow interface icon in Project panel
Enter details in Temporary Target Properties panel to designate in this way
Interface icon becomes yellow, can be dropped onto other interfaces as source
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating an Interface Step 5 : Define Flows (KMs)
Second part of the interface, defines the technologies used for the mapping
Mapping is broken down into execution units
Select the appropriate KM for each unit
Select options presented by the KM (truncate table, check controls etc)
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating an Interface Step 6 : Optionally, Use Check KM
When enabled, runs all the checks defined by the constraints on the tables
Moves error rows to E$ error tables
Can be recycled later on if errors fixed
Generally good practice to include CKM in interface if constraints defined
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating an Interface Step 7 : Execute and Test Interface
Press Execute to run interface
View progress and outcome using the Operator navigator
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Hands-On Labs
Lab 8-1 : Interfaces (Part 1)
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Loading Data Warehouse Structures using ODI
Loading DW structures in ODI uses a number of additional ODI features
Reading from sequences (to provide values for surrogate keys)
Loading slowly-changing dimensions
Loading fact tables (and performing dimension surrogate key lookups)
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Accessing Sequences from Interfaces
Sequences are defined in the Project panel
Can be global or local; Standard, Specified (based on table) or Native
Used for populating surrogate keys used in dimension tables
Accessed through :SEQ_NAME_NEXTVAL in mapping expressions
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Populating Slowly Changing Dimensions
SCD Behavior properties for a dimension table are defined in the datastore
Surrogate Key, Last Update Date, New Row on Update etc
To make use of these, select an SCD IKM in the Flows view
ODI will then take care of creating new rows, end-dating old ones etc
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Fact Table Loading
Fact tables typically need two extra levels of processing from the staging source
Convert dimension table natural keys to surrogate keys
Add aggregate functions to the measures
Dimension table lookups now enabled
through the Lookup Wizard
Retrieves surrogate key based on
natural key lookup
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Lookup Wizard for Surrogate Key Lookup Step 1 : Start Wizard
Add the fact table source to the interface
Press the Add a new Lookup button to start the Lookup Wizard
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Lookup Wizard for Surrogate Key Lookup Step 2 : Select Table
Select the table that will contain the lookup value to be returned
This will be the dimension table that contains the natural and surrogate keys
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Lookup Wizard for Surrogate Key Lookup Step 3 : Specify Join
Specify the join condition between the fact and lookup table
Select Staging for Execute on setting
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Lookup Wizard for Surrogate Key Lookup Step 4 : Map Column(s)
The lookup table will then be added to the mapping canvas
Drag and drop the lookup column onto the target datastore
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Hands-On Labs
Lab 8-2 : Interfaces (Part 2)
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Summary
Interfaces are where the heavy lifting happens on projects
Defines the transformation of data from source to target
Uses SQL expressions performed on source, staging or target
Divides data movement into business logic, technical details and audit checks
New features in 11g around joining datasets via set operations, lookups etc
Temporary interfaces and new KMs
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
Oracle Data Integrator 11g Bootcamp
Lesson 8 : Interfaces
T : +44 (0) 8446 697 995 or (888) 631 1410 (USA) E : enquiries@rittmanmead.com W: www.rittmanmead.com
