Perl and Informatica, or, Applications, like curves, may be open, closed, both, or neither

[adamsj]

Okay, granted, my favorite pseudonym is Paul E. Anna, but I'm convinced that an event being seen as disastrous for us locally is actually a great opportunity, if we can only grasp it.

Our data warehouse is undergoing rapid expansion, and a management decision has been made to eliminate "all that Perl and SQL" by bringing in Informatica, Perl being considered a non-approved tool.

What makes this an opportunity is that Perl is specifically recommended by Informatica as an extension language!

So--has anyone else worked with Perl and Informatica? Any advice?

On a larger level (and I guess this might be a Meditation topic), what about putting together proprietary technology and open source products? Is this combination a threat, or a menace? And to whom?

adamsj

They laughed at Joan of Arc, but she went right ahead and built it. --Gracie Allen

[BMaximus]

You know. The first thing that came to mind is that the "Powers That be" need to listen to the video presentation that Merlyn gave (Merlyn on "Reducing Business Risk Using Perl"). Perfect thing to explain that Perl can be an asset to the business, not a risk. Did they say that it was an open and shut case? They made up their minds that Perl is out? Looks like you have a job to convince them otherwise. Contact Merlyn through his site to see if you can get it on tape so they're not stuck watching it on a tiny screen. :)

BMaximus

[poqui]

Maybe the best thing that you could do, and what I would do, is to talk to the Powers That Be who have decided that "Perl is a non-approved tool" and maybe even get someone from Informatica to talk to them as well about why Perl is non-approved and why Informatica considers it worthy of being their extension language?

I have worked with both Perl and Informatica, but not "together", although it shouldn't be too hard, the external -program calls from Informatica work fine with shell scripts. The basic utility of Perl that I see in this situation is as a complex filter.

As for the proprietary and open-source together approach, it only really becomes a problem, as I see it, when you intend to distribute, and I suspect that you don't.

Share and Enjoy!

"That that is, is... for what is that but that? and is but is?" Shakespeare, Twelfth Night, Act IV, Scene 2

"Yet THAT which is not neither is nor is not That which is!" Frater Perdurabo (pseud. Aleister Crowley), Liber CCCXXXIII, The Book of Lies

[toma]

In dealing with this class of tool, the most important data in the system is the metadata. Metadata is used to automatically generate the code that performs the neat tricks done by the system. If you want to make a big contribution to the system, be sure to work on the metadata. The metadata probably doesn't exist in your legacy systems, so the cool features of Informatica won't be available until the metadata has been generated.

One of the claims made by Informatica seems to be that they generate this sort of metadata automatically, for example by reading COBOL source code.

If you want to keep using perl, I would advise you to learn XS, due to this quote from Informatica's web site:

"Informatica MetaData Exchange SDK is implemented in C++ and offers an advanced object-based API for accessing and manipulating the Informatica Repository from different programming languages such as C, C++, Visual Basic, and Java."

A cool thing to do might be to make a perl module that creates Informatica access routines on the fly, based on the metadata that you get from XS routines written in C. The C routines would use the C-language API to access the metadata. Use the metadata to automatically generate perl methods to access the Informatica data.

This should effectively enable perl to be a first-class tool for working with Informatica, instead of being relegated to a minor role.

I have had lots of fun linking proprietary tools to perl. The DBD database drivers are examples of how this is done properly. In the Oracle and Informix systems that I have worked on, the DBD code links to a proprietary library when the module is installed. The proprietary library comes from the vendor as a binary, and the rest is available on CPAN. In some cases the vendor support for the CPAN module was better than the support for the rest of the vendor's system!

Try to attend meetings of Informatica users to find others interested in using perl. Also try to find the perl advocates within Informatica.

It should work perfectly the first time! - toma

[mpeppler]

My partner in crime has done a lot of warehousing/data migration work, both with perl and with Informatica. He's also been placed in a similar position, where Informatica was essentially mandated as the tool to use. His comments have always been that Informatica can't touch a decent perl program.

I've also worked on a few large migrations, and I know that te only way to get those to work right (and be fast enough) was to use perl - there are always a ton of exceptions, and handling these with an integrated tool is non-trivial.

If I were in your shoes I'd try to show that I can do the same job in less time, and with a system that is easier to maintain.

Michael

[runrig]

We use Informatica here, but I'm not yet familiar enough with it to decide if I like it or not, or if a well designed cron (update: now using AutoSys to schedule jobs...good reasons to use it, but it would be fun to build a similar system in perl) and perl based system would be just as well. It seems to me that its great advantage/feature (questionable either way) is that it allows non-programmers to set up data transformations in a GUI (i.e. point 'n click) client (if you like that sort of thing). Informatica can handle certain kinds of simple transformations itself (e.g. concatenating strings), but for more complicated things (the example I'm looking at in the book now is converting the hex chars in a URL string to ascii) you can write a 'Perl advanced external procedure transformation' (Update: Can't find reference to that anymore, but I have run into custom transformation written in 'C'...yuck!).

I'm going to guess that there will be times when someone will say "Oh, I can't do that," and you'll be able to say "We can just write an advanced procedure transformation" (don't even mention the perl or external part - until you have it working :-). That's not even counting the pre or post session 'commands' which are mostly unix shell scripts at our site here, but could easily be perl scripts (to, say, email notification of session success or failure, or ftp the results somewhere).

I say just go with it, some manager has decided to blow their budget on this, so once you get a handle on it, it'll be another buzz-word to add to your resume :-)

Update (2008): After several years of using Informatica, I have to agree with mpeppler's friend below. You gain nothing from eliminating "all that Perl and SQL" except a pretty GUI, which can sometimes take a lot of pointing and clicking to make a simple change whereas with perl you just open the file in your favorite text editor and make your simple change. I once found it was easier in Informatica to export objects as XML, edit the XML in Vim, and then import the XML back into Informatica (not for every sort of edit, and not for the faint-hearted).

(update cont.): You still have SQL (and other crap), except now it's scattered across GUI widgets that you have to click and click and click to get to (if you can even find what you're looking for...there's no "grep"). And if people think Informatica cleans up their perl and SQL "mess", well, if you've ever look at the database that Informatica stores its metadata in, then you'd know that you're just trading one sort of mess for another...and with text files at least the information is easier to get to. If someone wants to migrate away from Informatica, let me know, because I'd love to develop a perl ETL framework...though working with Informatica has given me more XML parsing experience than I've ever had anywhere else (though most "developers" that know Informatica will never see the XML)

Update: My favorite ETL rant here.

YA Update: I'll say a couple of good things about Informatica...it does provide built in logging that the developer doesn't have to deal with developing, and nice real-time monitoring of running processes in a prettily packaged GUI. The bad thing is that you'll probably be replaced by someone who can't program their way out of a paper bag.