Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

December 14, 2004

Maplets in Oracle Warehouse Builder?

We are running a comparison of Oracle Warehouse Builder and Informatica on my current project. Our ETL requirements are modest. We are taking small(ish) amounts of data from an Oracle Applications database into a data mart of our own design. There we are going to run some static reports and some ad-hoc queries against the data.

All has been going swimmingly well so far, until today. We have some common processing which is required in all of our mappings, mainly to ensure we only pick up data from the correct operating unit. This is just essentially an extra "WHERE" clause on every SQL statement we send to the source database.

In Informatica this can be encapsulated as a maplet and included in every mapping. There doesn't seem a way to do this in Oracle Warehouse Builder though. We've looked at creating it as a seperate mapping and linking it to the other mappings in process flows. But as far as we can tell that would involve writing out intermediate results to some kind of temporary table and we would rather not do that. Does anyone have any suggestions, the simpler the better?

For the moment we have bitten the bullet and just copied the relevant mapping tables, joins and filters to each of our mappings. This isn't a huge issue as we've only currently got a dozen or so mappings and this preselection only applies to three quarters of them. I'm just worried that it could be a maintenance pain if we don't address it now.

Posted by Andy Todd at December 14, 2004 08:50 AM

Comments

Andy, Sorry meant to reply to you earlier.

It's probably not much use at this point, but maplets (or reusable mappings as I think they're going to be called) will be a feature in the forthcoming "paris" release of OWB.

A possible solution now, is to use the OMB*Plus feature in OWB to put a TCL script together that programatically add the transformation to each mapping. Again not elegant, but it may make the mass amendment of the mappings a bit more palatable.

Mark

Posted by: Mark Rittman on December 17, 2004 07:49 PM