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

Uli Bethke Best Practice, Oracle Warehouse Builder

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:


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.

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.