(c) Mornington Consulting Ltd, 2010
Oracle Warehouse Builder (OWB) has been around for a while, but it is only really in the 10gR2 and 11i releases that it has become user/developer-friendly. But is it a tool that you should consider for that next migration?
Well, that depends. No tool is ever perfect for every job, so lets look at a few examples and see if OWB is applicable.
1. Small scale, one-off migration.
For this scenario, you want to make it as fast and efficient as possible. The size of the project should mean (hopefully!) that your development team will be around for the duration of it, which in turn means that documentation such as the data catalogue (note 1) will be more important than the maintainability and documentation of the code itself.
This code is only going to run a few times - in development, test and then for real – before being discarded, so spending a great deal of time and effort in pretty code isn’t worthwhile for this project. It won’t matter a great deal if the code is a bit “quick and dirty”, but it should be fast and efficient – both to produce and in operation.
Unless your migration is extremely complicated – in which case I would question whether it is truly small-scale! – I recommend that you use SQL and PL/SQL for your migration solution [see seperate Mornington white paper on PL/SQL data migration techniques] and ignore OWB. Although OWB will be able to perform the job, the investment in the tool itself and the time to define the migration are probably not cost-effective for this project. Like any tool, the code that OWB generates and runs is sub-optimal. The tool has to cope with every eventuality and this is reflected in the code that it produces.
2. Small scale, repeated migration.
Performing the same migration repeatedly, for example, feeding a data warehouse from an operational system, is a task for which OWB is eminently suitable, due to its easy interaction with Workflow Manager and the ability to schedule migrations.
But a combination of SQL and PL/SQL for the migration, as above, but ensuring that the code is better documented, more robust and maintainable, with Oracle job queuing (DBMS_JOB) will do the job just as well, if not better, without the expense of an OWB licence. The resulting code should also be easier to tune to ensure that the migration runs within any designated time window.
3. Large migration, one-off.
For larger, more complex migrations, although using hand-crafted code is possible, I recommend that you use a tool. It will almost certainly be faster to develop the migration, and although you may lose out in ultimate performance, the reporting facilities within the tool will make it easier to trace and track data items, migration progress and data exceptions.
But would I recommend OWB as the tool of choice? Possibly.
OWB is being phased out by Oracle (post-11i) in favour of ODI, but for a one-off migration, long-term support should not be an issue. In this case, my recommendation is to look at the relative costs of the OWB and ODI licences to your organisation, together with any other planned or forecast migration work, and make the decision on which direction to go based on the bigger picture.
4. Large, repeated migration.
As above, I recommend that a tool is used in preference to hand-crafted code.
But OWB is not going to be the tool that I recommend. Although it is a near-perfect match for the project criteria, Oracle have already announced that it is not their tool-of-choice going forwards and that they have already commenced phasing it out of their product set. If the migration is to be repeated into the foreseeable future, I would strongly recommend that you examine ODI, as it has the features of OWB, but has the advantage of on-going Oracle support.
Conclusion
These scenarios have assumed that you are dealing with a blank piece of paper when it comes to choosing the “how” of your migration project. If you already have OWB in your organisation, are familiar with its operation, and have no performance constraints on your migration, then OWB may fit the bill perfectly!
I am merely suggesting that OWB isn’t a universal panacea for all migration projects and that there are other tools and techniques available within the Oracle product set which may be more applicable. Additionally there are other third-party products, both historical and present, which may also be worthy of consideration.
There are situations where OWB is going to be the best tool for the job, but those are getting ever narrower and at the time of writing [July 2010], I don’t feel that I could recommend to a client that they start investing in OWB, especially as it is not going to be a part of the on-going Oracle product set.
If you are only running Oracle 10g and intending to stay there for the foreseeable future, have good experience with OWB and/or OLAP AWM (due to several shared and similar screens) and are performing a number of reasonably complex and repeated migrations, then there is a case for using OWB. But ODI (and a number of third-party tools) would be equally at home in this situation.
Notes
1. Data Catalogue
This is a document detailing
- where each of the target data items have been sourced from
- what has become of the source data items (still present/archived/deleted/…)
- any data transformations which were required
- how data exceptions are handled
and any other relevant information.