Facebook Twitter LinkedIn YouTube RSS
magnify
Home ETL /ELT OWB vs Informatica – Design Time Comparison
formats

OWB vs Informatica – Design Time Comparison

Published on 2010/05/21 by in ETL /ELT

If you’re like me, you work in many many environments.  I’ve been a long-time user of PowerCenter and Informatica’s products continue to shine for me.  I can develop stuff in Informatica very very fast.   I’ve recently (over the last month or two) gotten really good at building OWB mappings, and while I kind of like the 100% push-down end-result (Oracle Packages are produced), I am weary of working in the designer.  There are so many design time quirks and bugs that it’s hard to “get motivated” to create inside OWB Designer.  I’ve recently heard that Oracle plans to “replace” OWB/OMB with something new, something they just acquired… we’ll see what happens.  This entry is VERY SHORT, and compares designining mappings in OWB versus Informatica PowerCenter.   

I use design patterns to actually design the same functional mappings in both environments.  Highly restartable, fault-tolerant, parallel enabled, performance driven, etc…  To that end I spent some time in OWB designer building the mappings that I have built in PowerCenter in the past.

Here’s the Sunopsis (sorry for the pun ie: synopsis) of it all…

In OWB:
45 minutes to design, debug, and deploy a mapping with de-dup, expression (datatype re-cast), lookup, filter, constant, sequence generator, and target.

In Informatica:
10 minutes for the same thing, that’s a HUGE savings, but wait theres more!!

In OWB:
An additional 15 to 25 minutes was spent adding and moving fields around, realizing that I had to add a data type cast expression myself, and that the deisgn tool wouldn’t do it for me,  which ended up in 1 corrupted mapping, another edition of the mapping (re-written by hand + another 20 minutes) wouldn’t synchronize the lookup properly, so yet another re-write by hand (this time 20 minutes on top of it all)… and Finally I had a mapping that Validated.

In OWB:
Once it validated, it wouldn’t deploy properly – when I deployed I got Oracle “parenthesis” and other obtuse errors – when I examined the deployed code, there was nothing wrong with it, but I was pointed to “conditions” in the lookup, and the filter.  I had to go back to the lookup & filter, and manually validate EACH of these by pressing the VALIDATE function.  Once validated, it deployed – no errors.

So, 1.5 hours later I had a working mapping.  With Informatica, added a session and a workflow, and a total time of 15 minutes, and it was running.

Take your pick….  I’d say design time in OWB is SLOW SLOW SLOW…

By the way – what have you found?  Love to hear your numbers and feedback, add a comment below…

What about the FUN side of the Island?  (sorry, quip from the movie: Madagascar)…

The FUN side of the island is…  tada!  Code Generation….  I can now generate ETL code for importing to OWB, AND importing to Informatica.  Total Result?  I can import FLAWLESS 100% ready-to-run code for both tools in record time. 

In other words, I’m not STUCK on the designer in either tool anymore…  I can generate code from a cross-reference, a source, and a target model for ALL Hubs, ALL Links, and ALL Satellites – over 150 mappings in less than 20 seconds, and I can choose: run them in OWB OR run them in Informatica (makes no difference to me).

You can contact me for more information on this, I’m thinking of releasing this feature set as SaaS – let me know if you are interested.

Cheers,
Dan L
DanL@DanLinstedt.com

 
 Share on Facebook Share on Twitter Share on Reddit Share on LinkedIn
10 Comments  comments 

10 Responses

  1. Apex

    Hi Dan,

    By the way – what have you found? Love to hear your numbers and feedback, add a comment below…

    Besides its slowly development, it is very-very buggy and has horrible GUI. It almost useless in non-Oracle environment, so I actually hate this tool… but it has its own way to automate routine – OWB scripting. This is exactly that I don’t have in Informatica’s wold.
    By the way, Informatica also has a lot of drawbacks and many things should be made manually, for example setting a connection for every source\target in the every session object on Workflow manager, it’s so cumbersome… Working with variables also very tricky…

  2. dlinstedt

    Hi Apex,

    I agree – Once the Oracle Map is “done” and scripted, and debugged, it’s Generated output is good. Of course, yes – it’s an Oracle ONLY solution… That’s why Sunopsis got bought… Anyhow, I’m also going to produce Microsoft SSIS XML packages shortly, along with Infa Mappings, so hopefully someone out there will want to use the services I am going to make available. If you’re interested in beta testing for a few months, let me know. There’s very little ramp-up or learning time for this, it’s just that the code-output is rigid for now (you have to use the mapping designs I produce from the code). I’m working on embedding templating – but I need a NICE GUI for that.. . :)

    Regarding Infa, I’ve been importing/exporting mappings, sessions, workflows for years using their XML command line interface like PMREP… So I’m not sure what the drawback is you are mentioning.

    Regarding all the manual changes, yes – I totally agree – there are no really good ways to “find & replace” matching things like session connection settings, etc… in the mappings. However, you can (as of v6) actually use Infa Variables to control session connections, but right again – getting the variables “right” is tricky.

    I’ll be blogging more about the generation capabilities for Infa as I move forward. Great ideas, thanks for the comments.

    Cheers Mate,
    Dan L

  3. Hi,

    Yes, OWB only writes to Oracle and that’s why Sunopsis is bought. People I know from Oracle state that OWB’s ‘shelf time’ is simply up but they will continue to support it for a long time coming. The latest OWB release installs on a ‘unified repository’ and so does ODI (Sunposis). Both tools are code generators so that’s also positive for future integration. As far as I’ve seen ODI does not have a scripting component and doesn’t run on TCL. While OWB’s Designer is a glorified scripting engine in itself ODI is developed differently. The Oracle product roadmap is vague but it seems like there is some light for OWB:

    “Today, ODI and OWB are released separately. For the short term, both products will continue to deliver stand-alone releases. Although released independently, both products are currently being developed with a common goal of integrating with each other and, ultimately, merging into a single product. In the next release, for example, OWB will support the knowledge module framework developed by ODI. And in future releases, both products will share the same user interface framework and will be able to launch each other processes.”

    What I personally liked from OWB is that it can handle the GEOMETRY datatype and made it possible to combine spatial with regular data. The resulting joins were fun :). Apart from the mapping generation bit that is.

    Regards,
    Roelant Vos

  4. dlinstedt

    Hi Roelant,

    Yes, I’ve heard that too… But if you look closely in to OWB – the components are actually Sunopsis based (I can see it written all over the comments, and the code, and the transformation objects)… But are the components the same as ODI of the future? Don’t know.

    What I will say this: if they remove scripting from ODI, they will have a huge outcry from the die-hards that do use OWB/OMB for their work. They do however need to make the GUI much better in the near future.

    Geometry joins… interesting concept. I’ll have to consider that one. By the way, ANYONE wishing to load an example HUB Map that I would use on my projects, only needs to download the OWB scripting code and install it. If you want help getting it in to your environments, let me know.

    Thanks,
    Dan L

  5. laker_42

    Dan,
    Have you done any similar comparisons with ODI & PowerCenter?

    John

  6. dlinstedt

    Hi John,

    No I don’t yet. I don’t have ODI installed. I will talk to Oracle to see if I can get a copy of ODI to install. Then I’d let you know after what the numbers are.

    Thanks,
    Dan L

  7. laker_42

    That would be great Dan! I have yet to find a good comparison of ODI & Power Center.

    Thanks,
    John

  8. As I do not have much experience with Informatica I’m a little biased. The main difference between OWB and Informatica is that both the (dimensional)datamodel and the ETL-process is modelled in OWB. Because of Datamodel-centric approach of OWB the ETL-code can be generated easily or regenerated when changes in the datamodel occur as well. This implies that the datamodel itself should be maintained in OWB.

    Informatica on the other hand focuses on the ETL part of the datawarehouse only so it doesn’t surprise me that the construction of ETL-code is easier and faster to perform. But how fast can it process changes in the datamodel ?

    Denny

  9. dlinstedt

    Hi Denny,

    It can process changes to the data model very easily. It has metadata bridges that can absorb changes to sources and targets, and it can process these changes in to the mappings downstream. The difficulty in PowerCenter lies in propogating the changes across ALL the mappings that need the new fields. Of course this would only be handy if the new field is to be “copied” from source to target without going through business rules. Otherwise, the point is moot – as in: if you have to change the data, you have to manually apply the rules anyhow, so you better be able to go in to each of the maps that are affected and manually modify the code to deal with the new field.

    It’s the same question with ANY ETL tool on the market. So, bottom line: absorbing data model changes is fast and easy in most ETL tools, it’s the propogation of the changes to all impacted mappings that usually requires manual work.

    Hope this helps,
    Dan L

  10. John

    I hate OWB. Can’t believe that Oracle put their name on it!!!