Oracle Data Integrator
A Success Story
Jason Jones
Key Performance Ideas, Inc.
About Jason Jones
l
l
l
Oracle Essbase Certified Developer
experience since 2005
Oracle Data Integrator since 2009
Extensive experience in developing, tuning and
enhancing Essbase, Hyperion Planning, and
ODI
Programming expertise:
Developing software in Java
Mobile solutions for the iOS platform (Objective-C)
Relational databases (SQL Server, Oracle)
Agenda
l
l
l
l
l
l
l
l
3
Company Situation
What is Oracle Data Integrator?
Problems & Pain Points
Solutions & Opportunities
ODI Functionality & Benefits
Lessons Learned
How to Get Started
Q&A
Company Situation
l
l
l
l
l
Large health services company
Significant Oracle database implementation
In process of implementing Essbase
Transitioning to new database servers
Data movement processes have grown
organically over the years
What is Oracle Data Integrator?
l
History
Originally developed by Sunopsis, acquired in 2006
Originated Extract Load Transform (ELT) as
alternative to ETL
Data movement between heterogeneous data
sources
Future
Strategic product for data integration
Significant development resources
ETL vs. E-LT
Traditional Architecture
Source
ETL Server
Target
E-LT Architecture
Source
Target
ETL Server
Problems & Pain Points
l
l
l
l
l
l
l
l
l
SQL procedures being used as de facto ETL solution
Time-consuming to develop
Hard to maintain
Little error visibility
Difficult to integrate with other data sources
Have to maintain in multiple environments
Manual logging facility
Lacking documentation/comments
Difficult to troubleshoot
Solutions & Opportunities
l
l
l
l
l
l
Easier development and maintenance
Work with different relational database
technologies
Load to/from text files, Essbase/Planning
Email alerts
Easily switch between physical environments
Process logging, easily pinpoint errors
Typical Data Flow
OLTP
(Oracle)
Data Movement
ODI Server
Essbase
Operational
Data Store
(Oracle)
Knowledge Modules
l
l
l
l
l
RKM: Import existing table/data structure
LKM: Load data from tables
IKM: Insert/update data in target table
JKM: Process new rows of data only
CKM: Validate data being moved
Models
Enhanced Functionality
Create models for relational tables, text files, and Essbase
dimensions to load data to and from
Easily create models automatically based on existing tables with
RKM
Serve as the source and target of interfaces
Benefits
Enforce data integrity!
Exist as logical units irrespective of physical environment
Health Services Organization Value Achieved
Define requirements for data entering target tables
Reuse for multiple interfaces
Model Datastore
12
Interfaces
Enhanced Functionality
Moves data between models
Source is typically multiple models (tables)
Destination is always one and only one model (table)
Declarative!
Benefits
Easy to create, modify, and maintain
Work across different technologies
Document business rules
Leverage power of existing database server to transform data
Auto mapping speeds development time
Health Services Organization Value Achieved
Visual replacement for many SQL procedures
Quickly define criteria for updating tables
Choose location of data transformation
Interface Screen
14
Interface Flow
Enhanced Functionality
Validate data being processed
Automatically recycle data
Choose loading/integrating strategy
Benefits
Very easily configure complex actions that would normally be tons
of code
Select Knowledge Modules (strategy used to load, integrate,
validate)
Health Services Organization Value Achieved
Turn on data validation, error recycling with mouse click
Interface Flow Screen
16
Procedures
Enhanced Functionality
Useful for performing one-off actions if they cant be put into
interface
e.g. call a stored procedure
Benefits
Perform action that doesnt fit into an interface
Health Services Organization Value Achieved
Reference existing data transformation process without having to
rebuild it
Interface Overview
18
Procedure Steps
19
Procedure Step Definition
20
Packages
Enhanced Functionality
Chain together multiple interfaces, procedures, and other steps
Allow for error control flow
Benefits
Gracefully handle errors
Restart process automatically, if desired
Send an email alert!
Health Services Organization Value Achieved
Treat execution of multiple interfaces as one job
Add email step in case of error (or success)
Package Flow
22
Scenarios
Enhanced Functionality
Freezes an interface, procedure, or package in place
Changes can be made to procedure/interface/package that wont
affect existing functionality
Scenarios are unit of work that can be scheduled and called from
command-line
Benefits
Avoid breaking existing processes when need arises to change/
augment functionality in interface
Health Services Organization Value Achieved
Call ODI functionality from command-line when needed
Deploy functionality and not be scared to make changes to it
Generated Scenarios
24
Operator
Enhanced Functionality
Easily view status of all jobs
Scenarios, packages, interfaces, procedures, load plans
Replaced need to have manual logging statements
Benefits
See exactly where and why a process failed
Health Services Organization Value Achieved
Insight into currently executing and already executed jobs
Drill down to exact cause and reason of error
Operator Overview
26
Operator Step Detail
27
Operator Step Generated Code
28
Scheduler
Enhanced Functionality
More robust than Windows Task scheduler
Easy to set schedule for jobs to run
Can call jobs from command-line but use scheduler if possible!
Benefits
Easier to use than setting up Windows Task Scheduler to run a
batch file to run a scenario
Health Services Organization Value Achieved
Directly schedule ODI job to run without having to setup batch file
Run ODI jobs without needed additional deployment step
Scheduler
30
Journalized Data
Enhanced Functionality
Pattern for only consuming updated/inserted rows of data
Easy to implement
Single checkbox in interface for using journalized data
Benefits
Avoid processing all data, block of data by day
Avoid maintaining timing variables
Health Services Organization Value Achieved
Get away from day of data processing paradigm
Move data from one system to another more often
Journals
32
Journalized Data in Interface
33
Topologies
Enhanced Functionality
Logical Customer system
Physical Customer system in Development, QA, Production
Benefits
Use same logical job for both development and production
environments
Health Services Organization Value Achieved
Save significant effort not having to copy/deploy code only differing
by environment
Pick and choose test/production systems
Lessons Learned
l
l
l
l
l
l
l
l
Critical to leverage someone who has done this to lay foundation!
Leverage an experienced individual to set critical first steps up
correctly
Start with simple ETL job and build a roadmap to larger data
movement needs
First step/phase can be large, subsequent jobs much easier
Dont re-implement functionality, build idiomatic ODI jobs
Always try to use iterative development model
Reusability, consistency, maintainability
Implementation duration
What to look for
How To Get Started
l
l
l
l
ODI is now Oracles data movement tool standard
Serious thought must be made to implement platform initial step is typically an architectural discussion
Utilize experts to help build a roadmap and identify new
idiomatic ODI functionality
Initial functionality development
Software install, physical topologies, logical
topologies, models reverse engineered
Interfaces, procedures, packages, scenarios,
solutions
WALKTHROUGH OF SQL TO
SQL INTERFACE STEPS
37
Drop Work Table
drop
table
ZODI.C$_0DIM_PETS
38
Lock Journalized Table
update
ZODI.J$PETS
set
JRN_CONSUMED
=
'1'
where
(1=1)
And
JRN_SUBSCRIBER
=
LAB_RESULTS_TRANSFER_PETS
39
Create View/Table on Source
create
or
replace
view
ZODI.C$_0DIM_PETS
(
C1_PET_ID,
)
as
select
*
from
(
select
PETS.PET_ID
C1_PET_ID,
from
[Link]$PETS
PETS
where
(1=1)
And
JRN_SUBSCRIBER
=
LAB_RESULTS_TRANSFER_PETS
40
Drop Synonym on Target
drop
synonym
ESSBASE.C$_0DIM_PETS
41
Create Synonym on Target
create
synonym
ESSBASE.C$_0DIM_PETS
for
ZODI.C$_0DIM_PETS@ZOASIS
42
Drop Flow Table
drop
table
ESSBASE.I$_DIM_PETS
43
Create Flow Table
44
create
table
ESSBASE.I$_DIM_PETS
(
PET_ID
NUMBER(38)
NULL,
PET_NAME
VARCHAR2(32)
NULL,
JRN_FLAG
VARCHAR2(1)
NULL,
JRN_DATE
DATE
NULL,
DIM_PET_ID
NUMBER
NULL
,IND_UPDATE
char(1)
)
NOLOGGING
Insert Flow Into Table
insert
/*+
APPEND
*/
into
ESSBASE.I$_DIM_PETS
(
PET_ID,
,IND_UPDATE
)
select
C1_PET_ID,
from
ESSBASE.C$_0DIM_PETS
where
(1=1)
45
Analyze Integration Table
begin
dbms_stats.gather_table_stats(
ownname
=>
'ESSBASE',
tabname
=>
'I$_DIM_PETS',
estimate_percent
=>
dbms_stats.auto_sample_size
);
end;
46
Synchronize Deletions from Journal Table
delete
from
ZSTAGING.DIM_PETS
where
exists
(
select
'X'
from
ESSBASE.I$_DIM_PETS
I
where
ZSTAGING.DIM_PETS.PET_ID
=
I.PET_ID
and
IND_UPDATE
=
'D'
)
47
Create Index on Flow Table
create
index
ESSBASE.I$_DIM_PETS_IDX
on
ESSBASE.I$_DIM_PETS
(PET_ID)
NOLOGGING
48
Merge Rows
49
merge
into
ZSTAGING.DIM_PETS
T
using
ESSBASE.I$_DIM_PETS
S
on
(
T.PET_ID=S.PET_ID
)
when
matched
then
update
set
T.PET_NAME
=
S.PET_NAME,
when
not
matched
then
insert
(
T.PET_ID,
Commit Transaction
/*commit*/
50
Drop Flow Table
drop
table
ESSBASE.I$_DIM_PETS
51
Cleanup Journalized Table
delete
from
ZODI.J$PETS
where
JRN_CONSUMED
=
'1'
And
JRN_SUBSCRIBER
=
LAB_RESULTS_TRANSFER_PETS'
/*
AND
JRN_DATE
<
sysdate
*/
52
Drop Synonym on Target
drop
synonym
ESSBASE.C$_0DIM_PETS
53
Drop View/Table on Source
drop
view
ZODI.C$_0DIM_PETS
54
Thank You!
Jason Jones
Direct: 206.427.1373
Email: jjones@[Link]
