12 Comments on “Oracle Data Integrator vs. Oracle Warehouse Builder: What to do, what to do?”

1. 
   #1 @lex said at 2:16 pm on July 13th, 2009:

   Great research, many thanks for the nice article.

   have a nice day

   @lex

2. 
   #2 Gareth Roberts said at 12:07 am on July 14th, 2009:

   Nice post!

3. 
   #3 Apoorva Awasthi said at 4:45 am on July 14th, 2009:

   Hi ,

   Good research..It has come out with the clear cut idea of the product path and features. Thx

   Apoorva Awasthi

4. 
   #4 Amudhan said at 2:37 pm on July 15th, 2009:

   Gr8 work and good research, additional screenshots would have been much more beneficial. Many thanks

5. 
   #5 Cezar Santo said at 1:11 pm on July 16th, 2009:

   Good Work Uli, very good!

6. 
   #6 Stan said at 1:58 pm on July 16th, 2009:

   What to do?

   Do nothing and don’t use either one. Both tools are depository centric and unless the Data Warehouse installation is also OWB/ODI depository centric, which most of them are not, you will create a very costly maintenance and support problem (metadata synchronization) for the organization.

7. 
   #7 Holger Friedrich said at 2:52 pm on July 16th, 2009:

   Hello Uli,

   your post shows a nice feature comparison of a subset of features of ODI & OWB, however, I do disagree with your analysis and recommendation.

   The major problem I have with your blog entry is, that you leave the requirements of projects’ completely out of the equation when assessing the usefulness of the two Oracle tools. But looking at different application areas, everyone sees instantly that the match of project requirements versus tool features are key in order to take a decision between ODI & OWB.

   When you look at data migration, transport and simple integration tasks, the feature set of ODI is a very good match, since these projects need especially rich connectivity, data transport and only moderate modeling & transformation capabilities for heterogeneous environments. Moreover, hand coding is more appropriate in projects like these, since most of them are pretty restricted in size. The footprint of these requirements shows clearly, that ODI is the right tool for that kind of job.

   On the other hand, if you have a serious Data Warehouse project at hand, requirements are quite different. You need code generation and maintenance capabilities for very complex operations (pivoting, SQL*Analytics, SCD etc), tool support for database specific performance features, and modeling support for dimensional structures. These are of course only just a few typical DWH project requirements. None of these are, in my opinion and experience, sufficiently covered by ODI so far, but are fields in which OWB excels. Moreover, you typically have only one database vendor’s RDBMS as target system in DWH projects. Therefore it doesn’t hurt if your tool is focused on serving just that one vendor’s RDBMS as target as long as it knows how to handle that one’s different releases and important performance features well.

   Therefore, my recommendation goes like this:
   - if you have to cope with a heterogeneous target environment, have to ship data around, transform it only moderately, or have to migrate stuff, happily use ODI today.
   - However, if Oracle is your target DB and you do have to do a DWH project, which is demanding regarding resources, amount of data, and transformations, go with Warehouse Builder.

   Further down the road, if you want to use ODI-Knowledge Modules for extracting data from heterogeneous source and loading it into your Oracle DWH staging area, migrate to OWB 11.2 once it’s going to be released. Given the slides available on OTN, this is going to be the first step of ODI/OWB integration. It should bring together the best of both worlds. Then you can do E&L with ODI-technology, followed by modelling, T&L into Oracle targets with OWB technology, all from within OWB 11.2. Let’s just hope that Oracle will really release it, as the outlook in the May-2009 product update on OTN suggests.

   Best regards,
   Holger

8. 
   #8 Antonio Romero said at 12:10 am on July 23rd, 2009:

   Holger,

   Thanks for your usual incisive analysis.

   There’s certainly a lot of legwork in this post, and much of the detail is right, but… when the oracle’s prophesies seem obscure, the impulse to see into the future can lead us to read too much into too few signs.

   As for a release of OWB 11.2, rest assured, it’s on track, and will add much of the flexibility associated with ODI to the other benefits OWB has always delivered (along with other new treats you’ll just have to wait for). We can’t say anything specific about timeframes, other than the usual “it will release with Database 11.2.”

   ODI is of course gettiing a lot of attention right now, and has a prominent place in a lot of conversations, but it would be a mistake to infer from that that either tool is supplanting the other.

   Each will contribute concepts and technology to future Oracle DI offerings, each will continue to be available, supported and enhanced until a future DI product is delivered, and even at that point, customers should not fear being forced to migrate away from the tool of their choice.

   Each customer can therefore safely choose whichever is a better fit for their technical requirements, and know that there will be a continuity between today’s products and Oracle’s future DI offerings. License ODI-EE today, and know that investing in skills and design effort in either tool is a safe bet for the future.

   -Antonio Romero, Senior PM, Oracle Data Integration

   Now, the shameless plugs: To keep up with Oracle Data Integration, join our LinkedIn group and follow our blogs:

   http://www.linkedin.com/groups?gid=140609

   http://blogs.oracle.com/dataintegration/

   http://blogs.oracle.com/warehousebuilder/

9. 
   #9 ubet said at 9:36 am on July 25th, 2009:

   @Stan: Can you explain what you mean by depository (repository??) centric and why this creates maintenance problems?

   @Holger: I agree on your statement that business requirements should drive the choice of tool.

   I also agree that modelling features in ODI need improvement esp. around physical design and forward engineering. Why not bring in a FKM (Forward-engineering Knowledge Module?)?

   I am negative on your analysis that OWB is a better fit for a DW project for the following reasons:

   – Tool support for database features in OWB lags behind the database features, e.g. still no support in OWB 10GR2 for delete in a Merge statement (introduced in 10GR1 of the RDBMS), no support for pivot/unpivot operator (introduced in 11GR1 of RDBMS), support for DML error logging was not in sync with RDBMS release etc. In ODI you can always be one step ahead by modifying/writing a KM to benefit from database performance features immediately and still enjoy the advantages of the ETL tool such as data lineage, metadata support etc.
   – Complex operations need tool support: For performance reasons really complex operations (such as multi-level inter-row calculations, analytics etc.) rarely fit the tool support of an ETL tool without awkward workarounds and resulting performance issues. Again no problem here with ODI. Hand code your KMs make full use of the database features of the target RDBMS and still enjoy the ETL tool benefits.
   - From my experience it is a common requirement for near-real time feeds to the DW. Another area where ODI excels.

10. 
    #10 Stan said at 3:50 pm on July 30th, 2009:

    Stan: Can you explain what you mean by depository (repository??) centric and why this creates maintenance problems?

    Hello Uli,

    Yes, I meant repository.

    To answer your question, I have to expand on my original comment first. Primarily, I reacted to the title of your article, regardless of how sound or meaningless the comparison is between OWB and ODI features. To me, this article is a classical example of how using a tool to support a project can become more complex than the project itself. It is frustrating enough to experience this type of situation once and with only one tool. When you expand to using two tools at the same time – even if you get many positive reactions from supposed experts – you really shouldn’t use those tools at all because it will result in a complete misunderstanding of their purpose and clients needs. This problem is related not only to OWB or ODI, but any of the numerous tools available. However, I really appreciated the very eloquent comment contributed by Holger .

    I will try to explain the maintenance issue based on a real project experience.

    As a consultant, I worked on a project creating a Data Warehouse using about 15 tables from an Oracle ERP application as the data source and for starters, creating one fact table and 6 to 10 dimensions. One would say that is not too complicated an assignment. The client insisted on using OWB to support the ETL process for this project. While several ETL supporting tools were considered, OWB was selected primarily based on pricing at that time. You can train an analyst/programmer using OWB in about a week. A competent analyst/developer, when working on a real project, will probably become an “expert” in a month because scripting tools with fancy GUI have been around for decades and most of us used such tools on more then on one occasion.

    OWB consists of two main components: the design and runtime environments. Thus, one has to consider not only project requirements but the actual OWB environment in a particular client setup, including the client’s organization support structure. Out of the box installation might work but not too well.

    The developers GUI works only on data structures copied from a run time environment into the Design Environment Repository and can’t be used without going through the repository and directly mapping to the run time environment source data and Warehouse target structures. Thus there is a need to synchronize these two environments, because overall data integrity and process integrity will hang on what is in the Repository and term repository centric.

    A question arises; which environment is going to be the governing one and which one will be the subordinated one to maintain this repository centric assumption. I don’t think that the Oracle ERP applications meta data definitions will be copied from the OWB repository. Most of the time this type of data will be imported to the OWB repository.. On the other hand, it is very tempting to use all these OWB available features to create target tables and objects in OWB and deploy them into a target environment instead of creating the target environment tables outside of OWB and importing them into the OWB repository as is done with Source data objects. Once all the tables are in this repository, you can make some modifications and add some features. When that is completed, you deploy the results including packages /procedures into your target runtime environment.
    Here are the specific problems that can result from these processes:

    Problem 1. People who created the initial processes move on to other projects and nobody remembers why thing were done the way they are. If you are lucky and the initial application was created by somebody who is business programming oriented, there is a chance you will be able to understand the graphical representation of what was done. If the same requirement was implemented by a scientifically minded programmer, you can spend days scrolling left /right, up/down in effort to understand the whole picture. If the work was done by a creative developer only, chances are that it will be time consuming and expensive, to understand anything. Management was sold that the GUI is intuitive and self documenting (in my example). I had a case where a column had to be added to a dimension. It was not so simple as it would seem with this type of tool. The wrong option had been used to import the table from the target runtime environment and the result was a default option (truncate). The problem with a truncated dimension and a completely new set of key values can be easily identified, but to recover and analyze why it happened is a costly process. Implementing additional tool related processes to avoid similar problems in the future also is also time consuming, not to mention the fear a developer will have for some time when it is necessary to touch anything in the GUI without damaging something else.

    Problem 2. The Company decides that, to comply with SOX requirements, passwords have to be changed more frequently for all systems. OWB run time module starts issuing messages denying Oracle connectivity because the source data runtime environment was sending a warning message that Oracle passwords will expire in 3 days. It can be fixed but it is frustrating, taking the user time to figure out what is happening and fixed it procedurally or in OWB GUI (complicating code). Looking for all these links and place where OWB is storing this information and redeploying it in all environments when passwords are frequently changed is really not fun.

    Problem 3. The Project started when the company was using Oracle 9i. To synchronize Oracle 9i releases and patches with OWB releases and patches, it took the dba support literally months just to make it work. As always, the promise was that Oracle 10g will fix whatever wasn’t fixed under 9i. After Oracle 10g, which had features such as parallel processes and cost based optimizer was installed, unearthly things would start happening to OWB created code. Highly priced vendor consultants were engaged and the first thing they could think of was that the OWB generated code was not good code, or the server where the OWB run time module was running was at fault (hanging up system). Proving that OWB code was not a problem using the OWB tool alone is truly an impossible exercise – especially from a position at the bottom of the totem pole looking up to world of DBA gurus. At that point you have no choice but to cut the ETL related functionality (about 10% of overall code) from the OWB created package in the target runtime environment and give it to your dba to execute using SQLPlus under his id at the closest server where the schema is running. Then you have to hover over him/her and have everyone observe what GRID is showing. Big surprise. The new optimizer was ignoring hints generated by OWB , creating a somewhat reasonable plan and executing something not even closely related to that plan.. How can you effectively find this information within OWB or even test alternative hints is not apparent either. Not to mention a situation when parallel processing was turned on. Whether a client continued to use a cost based optimizer and parallel processing or not, there was a positive result. The client decided that it was not worthwhile to have these types of issues, playing referee among the groups of experts debating the merits of a number of tools. The PL/SQL code used in that performance analysis exercise was enhanced and implemented as traditional PL/SQL packages and supported by a rank and file PL/SQL developer in about two months. So a project that could have been completed in several months became literally years of a learning experience and frustration, just because a tool had been inserted without really understanding why.

    It is true that I usually get involved with projects when they reach a distress level and maybe the experience of the rest of the world is as pleasant as advertised. I am not saying that all this can’t be avoided and done better, but it is my experience that it is done at considerable cost because project management will listen to his tool experts, not understanding what the issues are or even what they are talking about.

    In this commentary I am not trying to address product quality, vendor support or practices. I am addressing a simple fact of creating unnecessary and really costly support issues by inserting another layer of repository based tools into the ETL process, which is often a straight forward and simple process. Probably even the vendor is realizing that for most of their client’s projects, OWB is an overkill. Thus the vendor has purchased a simpler working product, gave it new name and is trying to simplify the process. So the cycle of acquisitions, mergers and enhancements will start again unnecessarily increasing the complexity with each new release.

    Regards,

    Stan

11. 
    #11 Antonio Romero said at 8:51 am on August 14th, 2009:

    Ubet,

    You do have a point about OWB 10.2 features having lagged behind the DB somewhat… but 10.2 was after all released in 2006. Several patches later much of this gap has been filled.

    Stan,

    I feel your pain, especially around some of the politics (which is eternal)…

12. 
    #12 Antonio Romero said at 8:59 am on September 6th, 2009:

    All,

    Just a quick note on this: It’s about six weeks since my last post here, and since then OWB 11.2 has shipped, adding the promised KM support, among many other new features. So… that great uncertainty is lifted.

    ubet, in such cases as there are where OWB lags support for this or that database feature, you now have the “escape hatch” of implementing or altering KMs to take advantage of the new features without abandoning OWB. Also, the near-real-time capabilities you have turned to ODI for are now available in OWB 11.2.

    For customers who are already invested in OWB, there are limited reasons to move. Existing projects in particular and shops with a lot of OWB skills can leverage those by licensing ODI-EE and applying the OWB 11.2 enhancements that derive from ODI.

Leave a Reply

 Subscribe in a reader

Complete list data warehousing books