Lessons learned

I've learned a few tricks in Informatica that makes things easier and simpler. I'm a fan of simpler.

In a lookup, you can uncheck the ports that you don't need. This makes the lookup faster, since it's not pulling back columns that aren't needed from the database. But never, ever delete these columns from the lookup transformation. Just uncheck them. Deleting them doesn't break it, but you never know when you will want to add a column back to it at some future date, and then you'll have to recreate the lookup from scratch.

There's also no reason to send every column into an expression if you're only going to modify one column. How many times have you seen a mapping with a source, source qualifier, every column moved into an expression, where one column is transformed slightly, and then every column is then written to a target - or some variation. I have. It's a waste. redirect all those columns directly to the target if you're not modifying them in the expression.

I also find it very helpful to rename my targets. There's nothing more useless than having 9 targets all called target, target1, target11, target111, etc. If you're trying to figure out if updates are working for a certain scenario, you can't unless the target is actually called what it's purpose is. I like being description. Table_name_insert and table_name_update are popular. If there's more than those, I try to add what kind of insert or update it is. That makes checking the run properties a lot easier.

Don't forget to set your schema names for tables in lookups and targets, and define the cache folder for lookups. This is especially true for lookups in mapplets which get re-used. None of the original developers defined the cache folder or the schema name for any of the tables in the mapplet lookups. For example, all of our tables are in the IDS schema in our database, which means I have to qualify them all, since SQL Server defaults to dbo. And I have to do it every time I re-use a mapplet. That drives me bonkers. Set it once, and then you don't have to override it at the session level. I find that the less I have to override in the session, the happier I am.

Another area that can really bite you is the SQL Qualifier. This thing has caused me a ton of grief. The major thing to remember when working with a SQL Qualifier is that the column order of you select is the same order your columns need to be listed in. So, if you're SQL override selects A, C, B from your table, your ports damned well better be in that order, otherwise you're going to get B in your C column, and vice-versa. And if they're both the same datatype (or even remotely close), Informatica won't care. I find a good healthy dose of double-checking can alleviate testing headaches here. Generating the SQL inside the SQL qualifier is also a good idea sometimes to see what you're producing. It's also a good idea to override the query here if you don't need to process everything in your source. I mean, you could select every row from your table, and then use a filter to ignore the rows you don't need - but that's not effecient. A better choice is to just select only those rows you need to begin with.

Lastly, I know there are people reading the blog, but you guys have been quiet. Any particular areas of discussion you'd like me to cover?