OWB Best Practice Part 1: Logically organise your DW project with Collections

November 12, 2008

Once you have gone beyond the implementation of more than one data mart for your Enterprise Data Warehouse project in Oracle Warehouse Builder, it becomes harder and harder to find a particular object for editing amongst the hundreds or thousands of objects.
OWB Collections come to the rescue. They allow you to logically organise your project into virtual folders by creating shortcuts to the actual objects.
In my projects, I typically create three collections per data mart. One that contains all the objects (mappings, transformations, tables etc.) that relate to extract phase of the ETL for the data mart. One for the lookup phase, e.g. lookup tables and mappings that load these. And finally one for the load phase. This last phase contains the fact and dimension tables and any transformations, mappings etc. that load these.
For a Sales data mart I would create three collections:
sales_extract
sales_lookup
sales_load
Unfortunately, you can’t nest Collections. It would be a nice feature to have one subcollection per object type. So within the sales_extract collection I would like to have a sales_extract_tables, sales_extract_mappings etc. subcollection. If I get around I will log this as an enhancement request, or maybe someone from OWB product management reads this.