OracleBIBlog Search

Monday, August 3, 2009

7 Basic Steps to Succeed with Oracle Warehouse Builder

This article shows the 7 basic steps to succeed when start working with OWB. Those steps were taken based on my 6 years experience with the technology and mentoring a few developers and data architects about how to work with OWB 3.X, 9.X, 10g R1, R2 (Paris) and recently 11G.

OWB has improved a lot on the past years. Oracle made significant enhancements on OWB since its Release 2 Paris version and recently on 11g; the strategy was to integrate the OWB core with the Oracle Enterprise Edition database. Regardless of the version of the tool those are some basic steps to succeed when start working with OWB:

  1. Take baby-steps, even after you get more knowledgeable about this tool. Create a simple source to target map first, deploy, run and check the results. Doesn’t matter how complex your mapping logic is, it is much easier to work iteratively in instead of try to accomplish everything at once.
  2. Make the mappings simple and try to get some DFD documentation (data flow diagrams) or source-to-target diagrams before start developing your ETL. Don’t forget to keep the documentation updated! Sometimes creating SQL queries helps you to imagine about how to design the ETL in a visual way based on the results. If you are not hands-on SQL, I strongly recommend learning the ANSI coding basics before working with this tool.
  3. Keep it clean, delete any unused objects and rely on the recycle bin. If you are not sure about removing the object simply rename the mappings as “delete_me_please” and remove it later. OWB still has the snapshot feature which allows you to work iteratively without making copies of your mappings, even if this feature is slow. Do not forget to purge the recycle bin frequently.
  4. Do not change any default properties of the mappings until you finish the ETL framework. You can start playing with some properties such commit frequency or execution modes such as set based or row based after making sure everything runs.
  5. Keep the Metadata synchronized with the physical tables and manage the data warehouse tables from the OWB tool. It is a good practice to keep all the objects information such indexes and primary keys within the Metadata and try to avoid changing objects directly on the database.
  6. Try to include all of your mappings and transformations with a Process Flow. It integrates the mappings, defines the load sequential order and helps to identify unused mappings under your module.
  7. I have seen a few developers and managers complaining about the performance and maintainability of the tool. Majority of the time the performance issue may be related to the overall database performance. If your project is not maintainable you may have some extremely complex mappings or undocumented process. Isolate the issue or even try to recreate your mapping in a separate environment first until you find the real issue.

Once you get more comfortable with the tool there are dozens of interesting and advanced techniques that will allow you to be more effective and successful on your projects!