Best practice of organizing interfaces and data stores into projects and models in ODI
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
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.
