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

Uli Bethke Oracle Data Integrator (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.


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


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.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.