Archives

Tagged ‘odi best practice‘

Best practice of organizing interfaces and data stores into projects and models in ODI

Have you ever wondered what the best way is to structure your objects in ODI into projects? Look no further. I will outline what works well for an Enterprise Data Warehouse.
Let’s assume you follow the Oracle reference architecture for data warehousing and you have a couple of source systems, a staging area, a foundation layer (core data warehouse), and a bunch of data marts.
For each of these layers we will create a model folder and a project (minus the source layer for projects.
We should end up with a structure similar to below.

Staging

You can further subdivide or group the Source System model by type of technology, e.g. File, MS SQL, Oracle, XML etc.

Similarly, you should subdivide your stage model into various sub-models based on source.

The same applies to the Stage project

EDW

Now we come to the interesting part. The EDW should be structured based on the subject areas in your Enterprise Data Model. If your organization is not mature enough and does not have one then it should get one asap (easier said than done). In the meantime, structure ODI based on the analysis performed for the data warehouse.

Each subject area in your EDM gets its own folder in the EDW project. Each entity in the EDM has a home in one of the subject areas. The interface that populates the corresponding ODI data store will go to the corresponding ODI project folder.

Similarly you structure your EDW model into sub-models corresponding to the EDM subject areas.

Data Marts

You could take the same approach for the data marts as for the EDW. However, as your dimensions are de-normalized and may span multiple EDM entities rooted in different subject areas my preference is to split out the Data Marts project into a Dimension, Facts, and Aggregate Facts folder.

One note at the end: You find statements out there claiming that you should never exceed 300 objects in a project. I am not sure where these come from. I have never seen any issues exceeding this number. If it was the case then this would be a severe limitation in ODI.

Craig Stewart’s Oracle Data Integrator (ODI) video tutorials

Craig Stewart has put together a superb set of ODI video tutorials. This stuff is just brilliant. Thanks a lot Craig, for letting me publish these on the blog. You’ve really earned your new master of the universe title :-)

How to define a PostgreSQL in ODI – then reverse engineer in the Designer to access the data
Defining a PostgreSQLserver in ODI

ODI has some automatic features which will generate the Group By Statement for you when you use any of the aggregation functions, this is a short demo of how to use it
How to use Aggregation Functions in ODI

What is the Common Format Designer (CDF), and what can we do with it? Short demo of the features, generating schemas and generating interfaces automatically.
ODI’s Common Format Designer

Short demo on the Metadata Navigator of ODI
ODI’s Metadata Navigator

OdiZip is a useful tool and this short screencam illustrates its use
OdiZip How to use

Sybase ASE to IQ knowledge module
Sybase ASE to IQ KM demo

How to use custom Java classes in your ODI procedures
Using custom Java code in ODI

Using Excel in ODI, including getting round the problem with the limitation of fixed named ranges
Using Excel in ODI

How to define and use Flexfields to extend ODI’s metadata
Using FlexFields in ODI

Defining XML file in Topology and reverse engineering
XML 1 Defining

What does the XML structure look like in ODI?
XML 2 The Rendered Data Structure

When the definition doesn’t work, how do you find out what has gone wrong?
XML 3 Topology

How to use ODI to populate a simple XML structure
XML 4 Populating Simple XML

Populating a more complex XML structuire – requiring the use of multiple interfaces. Writes to a file with a dynamic name – in this case the session_id.xml
XML 5 Populating Complex XML

How to configure external database storage for the JDBC Driver for XML- useful when dealing with large XML files
XML 6 External Database Storage

How to set up the file name for the XML file so that it accommodates the use of multiple different file names
XML 7 Reading a Dynamically named XML file

If you want to master scripting in ODI get the following books.

Need ODI Training? Learn ODI from the experts!

I am proud to announce that Sonra are now offering ODI classes.

Value-priced. Customised. Onsite-Offsite. Online.

Five day standard training course.
Online.
Starts 24 February 2014.
Book before 15 January 2014. Pay $949.

More information on our ODI courses.

ODI Tutorials & Documentation: Oracle Data Integrator Resources for Beginners

I have put together a link list of all the ODI resources I know off on the web. If you know any other relevant resources contact me and I will add it to the list below.

ODI Training. Learn ODI from the experts.

You may also be interested in our ODI training courses. Value-priced. Customised. Onsite-Offsite. Online. Get all the details from our ODI training site.

In my opinion the best place to get started with is the Data Warehouse Best Practices using Oracle Data Integrator Enterprise Edition guide. This explains the concepts very well and also has some detailed examples on Knowledge Modules, the Substitution API, setup, deployment etc.

ODI OTN Discussion Forums

I monitor the ODI forum on OTN at the end of each day to see if any relevant items have been discussed.

Oracle By Example: Oracle Data Integrator

The Oracle By Example (OBE) series should be one of the starting points for learning any new Oracle technology. The OBE for ODI series is a must.

Documentation

Download all relevant PDFs on Knowledge Modules, the Substituion API, Jython, ODI Tools etc. from the documentation page.

ODI homepage on the Oracle website

Blogs

Rittman/Mead

ODI Experts

John Goodwin

My own blog Uli Bethke

The official Oracle Data Integration Blog

ODI webcasts

No new webcasts for a while now, but still useful stuff.

Oracle Data Integration (ODI) Resource Kit

LinkedIn Groups

Oracle Data Integration: Warehouse Builder (OWB), Data Integrator (ODI), Data Profiling and Quality

Oracle Data Integrator (ODI)

Metalink

Metalink has very useful information on Oracle Data Integrator. To get an overview what is available, log on to Metalink and search for ODI FAQ.
This will return a list of valuable documents

Oracle Data Integrator, Change Data Capture (CDC), and (near-)realtime data integration requirements

Mark Rittman – An Introduction to Real-Time Data Integration

Gartner: ODI and CDC for Activity Monitoring

Improve Data Integration with Changed Data Capture (PDF)

Installation

Complete installation guide including Lightweight Designer, Axis 2 etc.

Knowledge Modules

Have a look at my own article on OTN for an introduction to knowledge modules

Uli Bethke – Developing a Knowledge Module in Oracle Data Integrator

Oracle Data Integrator documentation library: XML guide, LDAP guide, Java APIs Reference etc.

No link here. This is installed when you install ODI. Access via \ oracledi\doc

ODI Data Quality

Data Quality Tutorial

Misc.

Import Export Best Practices PDF: Metalink document 549318.1

ODI 10.1.3 Work Repository Documentation: Note 579751.1 on Metalink

Craig Stewart has put together a superb set of ODI video tutorials.

How to define a PostgreSQL in ODI – then reverse engineer in the Designer to access the data
Defining a PostgreSQLserver in ODI

ODI has some automatic features which will generate the Group By Statement for you when you use any of the aggregation functions, this is a short demo of how to use it
How to use Aggregation Functions in ODI

What is the Common Format Designer (CDF), and what can we do with it? Short demo of the features, generating schemas and generating interfaces automatically.
ODI’s Common Format Designer

Short demo on the Metadata Navigator of ODI
ODI’s Metadata Navigator

OdiZip is a useful tool and this short screencam illustrates its use
OdiZip How to use

Sybase ASE to IQ knowledge module
Sybase ASE to IQ KM demo

How to use custom Java classes in your ODI procedures
Using custom Java code in ODI

Using Excel in ODI, including getting round the problem with the limitation of fixed named ranges
Using Excel in ODI

How to define and use Flexfields to extend ODI’s metadata
Using FlexFields in ODI

Defining XML file in Topology and reverse engineering
XML 1 Defining

What does the XML structure look like in ODI?
XML 2 The Rendered Data Structure

When the definition doesn’t work, how do you find out what has gone wrong?
XML 3 Topology

How to use ODI to populate a simple XML structure
XML 4 Populating Simple XML

Populating a more complex XML structuire – requiring the use of multiple interfaces. Writes to a file with a dynamic name – in this case the session_id.xml
XML 5 Populating Complex XML

How to configure external database storage for the JDBC Driver for XML- useful when dealing with large XML files
XML 6 External Database Storage

How to set up the file name for the XML file so that it accommodates the use of multiple different file names
XML 7 Reading a Dynamically named XML file

Online resources that every ODI developer should know about Oracle Data Integrator!

I have put together a link list of all the ODI resources I know off on the web. If you know any other relevant resources contact me and I will add it to the list below.

In my opinion the best place to get started with is the Data Warehouse Best Practices using Oracle Data Integrator Enterprise Edition guide. This explains the concepts very well and also has some detailed examples on Knowledge Modules, the Substitution API, setup, deployment etc.

ODI OTN Discussion Forums

I monitor the ODI forum on OTN at the end of each day to see if any relevant items have been discussed.

Oracle By Example: Oracle Data Integrator

The Oracle By Example (OBE) series should be one of the starting points for learning any new Oracle technology. The OBE for ODI series is a must.

Documentation

Download all relevant PDFs on Knowledge Modules, the Substituion API, Jython, ODI Tools etc. from the documentation page.

ODI homepage on the Oracle website

Blogs

Rittman/Mead

ODI Experts

John Goodwin

My own blog Uli Bethke

The official Oracle Data Integration Blog

ODI webcasts

No new webcasts for a while now, but still useful stuff.

Oracle Data Integration (ODI) Resource Kit

LinkedIn Groups

Oracle Data Integration: Warehouse Builder (OWB), Data Integrator (ODI), Data Profiling and Quality

Oracle Data Integrator (ODI)

Metalink

Metalink has very useful information on Oracle Data Integrator. To get an overview what is available, log on to Metalink and search for ODI FAQ.
This will return a list of valuable documents

Oracle Data Integrator, Change Data Capture (CDC), and (near-)realtime data integration requirements

Mark Rittman – An Introduction to Real-Time Data Integration

Gartner: ODI and CDC for Activity Monitoring

Improve Data Integration with Changed Data Capture (PDF)

Installation

Complete installation guide including Lightweight Designer, Axis 2 etc.

Knowledge Modules

Have a look at my own article on OTN for an introduction to knowledge modules

Uli Bethke – Developing a Knowledge Module in Oracle Data Integrator

Oracle Data Integrator documentation library: XML guide, LDAP guide, Java APIs Reference etc.

No link here. This is installed when you install ODI. Access via oracledidoc

ODI Data Quality

Data Quality Tutorial

Misc.

Import Export Best Practices PDF: Metalink document 549318.1

ODI 10.1.3 Work Repository Documentation: Note 579751.1 on Metalink

Craig Stewart has put together a superb set of ODI video tutorials.

How to define a PostgreSQL in ODI – then reverse engineer in the Designer to access the data
Defining a PostgreSQLserver in ODI

ODI has some automatic features which will generate the Group By Statement for you when you use any of the aggregation functions, this is a short demo of how to use it
How to use Aggregation Functions in ODI

What is the Common Format Designer (CDF), and what can we do with it? Short demo of the features, generating schemas and generating interfaces automatically.
ODI’s Common Format Designer

Short demo on the Metadata Navigator of ODI
ODI’s Metadata Navigator

OdiZip is a useful tool and this short screencam illustrates its use
OdiZip How to use

Sybase ASE to IQ knowledge module
Sybase ASE to IQ KM demo

How to use custom Java classes in your ODI procedures
Using custom Java code in ODI

Using Excel in ODI, including getting round the problem with the limitation of fixed named ranges
Using Excel in ODI

How to define and use Flexfields to extend ODI’s metadata
Using FlexFields in ODI

Defining XML file in Topology and reverse engineering
XML 1 Defining

What does the XML structure look like in ODI?
XML 2 The Rendered Data Structure

When the definition doesn’t work, how do you find out what has gone wrong?
XML 3 Topology

How to use ODI to populate a simple XML structure
XML 4 Populating Simple XML

Populating a more complex XML structuire – requiring the use of multiple interfaces. Writes to a file with a dynamic name – in this case the session_id.xml
XML 5 Populating Complex XML

How to configure external database storage for the JDBC Driver for XML- useful when dealing with large XML files
XML 6 External Database Storage

How to set up the file name for the XML file so that it accommodates the use of multiple different file names
XML 7 Reading a Dynamically named XML file

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

Great Jython cheat sheet by the guys from ODI experts. Covers a lot of the recurring Jython code snippets that you need for ODI. Thanks Cezar and Dev.

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)