I've been tinkering with this data "heavyweight" for a couple of weeks now and I wanted to leave my first impressions. I'll tell you right now that my poor Pentium 4 with 1GB of RAM suffers unspeakably every time I open the heavy Windows clients, but in return, I'm sleeping more hours.

From spaghetti code to little boxes: Mappings and Workflows

For those of you who come from the pure development world like me, the idea of programming by dragging little boxes sounds almost like heresy. In PowerCenter, development is not done by writing code in a text editor, but by using graphical tools.

The architecture is mainly divided into the server and the desktop clients that we install on our local machines: the Designer, the Workflow Manager, and the Workflow Monitor.

The star concept is the Mapping. In the Designer, you connect data sources (for example, a table in Oracle 9i or a gigantic plain text file) with destinations, passing through a series of visual transformations. Do you need to cross data from two tables? You use a Joiner. Filter nulls or apply conditional logic? You pull out a Filter or an Expression.

On a technical level, what's interesting is how PowerCenter 8 has improved data partitioning management, allowing you to parallelize loads if you have servers with multiple physical processors or a good storage system underneath.

Running the beast from the console

Although everything is very visual for the developer, at the end of the day we need to integrate these loads into our corporate nightly schedulers. And this is where sysadmins and code-monkeys feel most comfortable: returning to the command line.

PowerCenter includes a very powerful utility called pmcmd. To launch a Workflow (the execution unit that groups several sessions or mappings) directly from a .bat script or a Unix cron, we use something like this:

pmcmd startworkflow -sv Integration_Service_Prod -d Domain_Prod -u admin -p password_super_secreta -f Ventas_DWH wf_Carga_Diaria_Ventas

Note: Please don't put passwords in plain text in production scripts. Use environment variables, we all know better by now.

The fascinating part is that when you launch the command, you can open the Workflow Monitor and see in real time how the rows flow, how many records per second are being processed and, most importantly, where the spark flies if something goes wrong. It gives you a visibility that our old handcrafted scripts lacked.

Lights and shadows of visual integration

Of course, not everything is rosy. The barrier to entry is high, and the graphical interface of the Windows clients is, to be honest, quite clunky and prone to crashing if you have many complex mappings open at once (I insist, 1GB of RAM is starting to be the vital minimum to work decently!). Also, versioning the code isn't as trivial as backing up text files; here we depend 100% on the tool's own relational repository.

However, the time saved on a day-to-day basis is brutal. Tasks that used to take me entire days programming control logic, error handling, and null cleaning in SQL, I now solve in a couple of hours connecting visual transformers.

I firmly believe that the future of large data integration processes involves adopting these enterprise platforms. We developers are too valuable a resource to be writing date "parsers" by hand or reinventing the wheel with every new data load. Our time should be focused on understanding the actual business logic, not fighting with string formats.

I'll let you know how it behaves in a few weeks when we start throwing millions of real production records at it. And what about you, are you still hitting the hard scripting for ETL loads or have you made the leap to these commercial tools?