4-1 5 oracle data integrator transformations: adding more complexity
TRANSCRIPT
![Page 1: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/1.jpg)
4-1
5Oracle Data IntegratorTransformations: Adding More Complexity
![Page 2: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/2.jpg)
4-2
Multiple Sources
• You can add more than one source datastore to an interface.
• These datastores must be linked using joins.• Two ways to create joins:
• References in the models automatically become joins in the diagram.
• Joins must be manually defined in the diagram for isolated datastores.
![Page 3: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/3.jpg)
4-3
1. Drag and drop a column from one datastore onto a column in another datastore.
A join linking the two datastore appears in the diagram. In the join code box, an expression joining the two columns
also appears.2. Modify the join expression to create the required relation.
• You can use the expression editor.3. Check the expression’s syntax if possible.4. Test the join if possible.
Manually Creating a Join
![Page 4: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/4.jpg)
4-4
Setting up a Join
SQL join expression (technology dependant)
Execution location
Join typeInner/Outer, Left/Right.
Use ISO-92 syntax
Join order (ISO-92 Syntax)
Expression editor
Save expression
Validate expression
Automatically calculate order
Joins can be defined across technologies (here a database table and a flat file)
The number of joins per interface is not limited
![Page 5: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/5.jpg)
4-5
Types of Joins
The following type of joins exist:
Cross Join
Inner Join
Left Outer Join
Right Outer Join
Full Outer Join
Cartesian Product. Every combination of any Customer with any Order, without restriction.
Only records where a customer and an order are linked.
All the customers combined with any linked orders, or blanks if none.
All the orders combined with any linked customer, or blanks if none.
All customers and all orders.
![Page 6: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/6.jpg)
4-6
Options for Filters, Joins and Mappings
• Active Mapping• When unchecked, the filter, join or mapping is disabled for this
interface
• Enable mapping for update and/or insert• Allows mappings to only apply to updates or inserts.
• By default, both insert and update are enabled
• Choose the update key by selecting the Key checkbox• Change the execution location of the filter, join or mapping.
![Page 7: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/7.jpg)
4-7
Setting Options for Filters, Joins and Mappings
Activate/DeactivateFor mappings, filters or joins
Execution LocationFor mappings, filters or joins
Insert/UpdateFor mappings
Part of the Update KeyFor target columns (mappings)• Active Mapping
• When unchecked, the filter, join or mapping is disabled for this interface
• Enable mapping for update and/or insert
• Allows mappings to only apply to updates or inserts.
• By default, both insert and update are enabled
• Choose the update key by selecting the Key checkbox
• Change the execution location of the filter, join or mapping.
![Page 8: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/8.jpg)
4-8
Note – Update Keys for Flow Control
To perform
Updates or use
Flow Control, you
must define an
update key for the
interface
![Page 9: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/9.jpg)
4-9
What is an Update Key?
An update key:• is a set of columns capable of uniquely identifying
one row in the target datastore• is used for performing updates and flow control• can be:
• one of the primary/unique keys defined for the datastore• defined specially for the interface
![Page 10: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/10.jpg)
4-10
How to Define the Update Key
1. Go to the Diagram tab of the interface2. Select the Target Datastore.3. Select the Update Key in the properties panel.
To define a new key in the Interface only1. Choose <Undefined> for the update key.2. Select one target column to make part of the update key.3. Check the Key checkbox in the properties panel.4. Repeat for each column in the update key.
To define a new key for the table that could be used in other interfaces1. Go back in the Model2. Expand the table3. Right-click on Constraints and add a new key (more on this in a
later chapter)
![Page 11: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/11.jpg)
4-11
How to Change the Execution Location
For mappings, filters and joins, you can choose where the operation will take place: source database, staging area or target database (mappings only, and for the mappings, only literals and database functions)
1. Go to the interface’s Diagram tab
2. Select the filter, join or mapping to edit.
3. Select an execution location from the properties panel.• Not every execution location is always possible.• Must be set to Active first.
![Page 12: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/12.jpg)
4-12
Why Change the Execution Location?
You may need to change the execution location if:• The technology at the current location does not have the
features required• Files, JMS, etc do not support transformations
• A required function is not available
• The current location is not available for processing• The machine can’t handle any more demand
• ODI does not allow this location• It is not possible to execute transformations on the target.
![Page 13: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/13.jpg)
4-13
What is the Flow?
Flow – The path taken by data from the sources to the target in an ODI interface.The flow determines where and how data will be extracted, transformed, then integrated into the target.
![Page 14: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/14.jpg)
4-14
What Defines the Flow?
Three factors:• Where the staging area is located
• On the target, on a source or on a third server
• How mappings, filters and joins are set up• Execution location: Source, target or staging area• Whether transformations are “active”
• Choice of Knowledge Modules• LKM: Loading Knowledge Module• IKM: Integration Knowledge Module
![Page 15: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/15.jpg)
4-15
A Data Integration Scenario
Source Sybase
ORDERS
LINES
CORRECTIONSFile
Target Oracle
SALES
Mapping- SALES = SUM(LINES.AMOUNT) + CORRECTION.VALUE.- SALES_REP = ORDERS.SALES_REP_ID…
Filter- ORDERS.STATUS=‘CLOSED’…
Join- ORDERS.ORDER_ID = LINES.ORDER_ID…
![Page 16: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/16.jpg)
4-16
The Basic Process
Source: Sybase
ORDERS
LINES
CORRECTIONSFile
Target: Oracle
SALES
Sequence of operations with or without an integration tool
C$_011
Extract/Join/Transform
C$_122
Extract/Transform
I$_SALES33Join/Transform
55
Transform & Integrate
![Page 17: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/17.jpg)
4-17
What Is the Staging Area?
Staging Area – A separate, dedicated area in an RDBMS where ODI creates its temporary objects and executes some of your transformation rules.By default, ODI sets the staging area on the target data server.
![Page 18: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/18.jpg)
4-18
Case Study: Placing the Staging Area
• The Staging Area may be located:• On the target database (default).• On a third RDBMS database or the Sunopsis
Memory Engine.• On the source database.
• The Staging Area cannot be placed on non relational systems (Flat files, ESBs, etc.)
![Page 19: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/19.jpg)
4-19
How to change the Staging Area
1. Go to the interface’s Definition tab of your Interface.
2. To choose the Staging Area, check the Staging Area Different From Target option, then select the logical schema that will be used as the Staging Area.
3. To leave the Staging area on the target, uncheck the Staging Area Different From Target option
4. Go to the Flow tab. You can now see the new flow.
![Page 20: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/20.jpg)
4-20
Case #1 in ODI
Staging Area in the Target
Source Sets
Staging Area + Target
![Page 21: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/21.jpg)
4-21
Case #2 in ODI
Staging Area is the Sunopsis Memory Engine
Source Sets
Target
Staging Area
![Page 22: 4-1 5 Oracle Data Integrator Transformations: Adding More Complexity](https://reader036.vdocuments.us/reader036/viewer/2022062500/56649eeb5503460f94bfc101/html5/thumbnails/22.jpg)
4-22
Case #3 in ODI
Staging Area in the Source
Source Sets
Target
Staging Area