We've had a couple of interesting discussions this week regarding data quality. One of the major areas that we've been dealing with lately has to do with email addresses. We had a problem last Friday pulling a list of email addresses for a marketing campaign, and a couple of the records were obviously bogus (something like notvalidemail@acme-hackme.com) and then as an added bonus, whoever created them on the website added a carriage return to it. So, not only was the email bogus, but it ended up crashing my job because it threw the line count off.
I made the suggestion to the web team that they filter this kind of stuff out - turns out they do, but this particular record was loaded as part of a mass import of data from our 3rd party vendor which used to manage that stuff.
In investigating these records, it occurred to me that there could be a whole level of data quality "corrections" that could be made to the data that would make it better. For example, not only do we want to exclude the bad ones, but we also want to encourage users to correct the good ones - because if you're specifically signing up for an email alert, then it doesn't do you any good if the email address isn't valid at all.
One of the examples I'd discovered in our database were things like joe2yahoo.com or joe@yahoo.co, - where they fingered the comma instead of the m - or even cp, where their hand was off just slightly (conversely, cim or cin were also present). The joe2yahoo example resulted when someone didn't hold the shift key down, and got a 2 instead of an @.
I suggested that these patterns could be matched on, and then replaced, fixing the email address, but the web developers were all very hesitant - "What if joe@yahoo.com isn't even their real email address?", they asked, "You'd be turning an intentionally bogus email address into a valid one, and then we'd be spamming poor Joe who never even asked for our emails.
In fairness, I suspect if joe@yahoo.com is a valid email address, he probably gets a ton of spam anyway.
But their argument had some merit. So yes, it would seem that sometimes, data cleansing can sometimes have unforeseen side-effects.
Friday, May 29, 2009
Friday, May 22, 2009
I'm ready to kill someone
Apparently,
NULL != a number
Doesn't evaluate to anything in Informatica.
So, if you have two variables "existing_x" and "new_x" and you're trying to route off whether they are different or not, and you do something like this:
"existing_x != new_x" in your router condition, it doesn't satisfy the condition if one of the values is NULL, and the other one is not null.
What a pile of garbage.
I had to force the NULL value to be zero before it would work.
I'm also ready to kill someone. Whoever wrote the code to handle that would be a good candidate.
NULL != a number
Doesn't evaluate to anything in Informatica.
So, if you have two variables "existing_x" and "new_x" and you're trying to route off whether they are different or not, and you do something like this:
"existing_x != new_x" in your router condition, it doesn't satisfy the condition if one of the values is NULL, and the other one is not null.
What a pile of garbage.
I had to force the NULL value to be zero before it would work.
I'm also ready to kill someone. Whoever wrote the code to handle that would be a good candidate.
Beware the bit
This is probably unique to SQL Server, so if you don't use SQL server, you can probably ignore this tip.
Beware the bit data type. It's a curious thing. This just bit me in the butt, so I thought I'd share it.
If you pull in a bit datatype from a source, Informatica converts it to a string (1) in the qualifier. Odd, considering a bit is either 0 or 1.
If you try to write that to an int target, you'll always get 0 - why? Because the string(1) doesn't store "0" and "1", it stores "T" and "F".
When I get some free time (ha!) one of these days, I'm going to change the qualifier to a string(10) just to see if it actually stores "TRUE" and "FALSE", but I suspect it doesn't, since it defaults to just a string(1).
But, if you're going to write a bit to your target, you'll need to use an expression, something like:
IIF(bit_Flag = 'T', 1, 0)
Consider yourself warned. :)
Beware the bit data type. It's a curious thing. This just bit me in the butt, so I thought I'd share it.
If you pull in a bit datatype from a source, Informatica converts it to a string (1) in the qualifier. Odd, considering a bit is either 0 or 1.
If you try to write that to an int target, you'll always get 0 - why? Because the string(1) doesn't store "0" and "1", it stores "T" and "F".
When I get some free time (ha!) one of these days, I'm going to change the qualifier to a string(10) just to see if it actually stores "TRUE" and "FALSE", but I suspect it doesn't, since it defaults to just a string(1).
But, if you're going to write a bit to your target, you'll need to use an expression, something like:
IIF(bit_Flag = 'T', 1, 0)
Consider yourself warned. :)
Tuesday, May 19, 2009
Binary log files
I'm not a fan of version 8's binary log files. They're kind of hard to work with, and especially so from the Windows Explorer viewpoint, since I haven't yet found a command line tool to associate to those types which can open them (if you know of one, I'd love to hear what it is!).
But Informatica isn't unforgiving in this regard, there's an option you can set if you're kind of a luddite like me, and prefer the standard text files. Just choose "Write Backwards Compatible Log File" in the Session's properties.
Be aware that this will actually create a PAIR of log files for your job - a .bin logfile, and a regular ASCII log file. Curiously enough, the .bin log file will be twice as large as your regular ASCII log file (so much for progress). Workflow manager will still read the .bin file - so it will still look all fancy (as compared to previous Informatica versions), but you'll also have the regular file in case you need to do some historical investigation, you can do so easily.
You can use the "Import" function within Workflow monitor, but I find it to be kind of a nuisance. Go to "Tools" and "Import Log" to import .bin files directly into Workflow Monitor if you want to read old .bin files.
But Informatica isn't unforgiving in this regard, there's an option you can set if you're kind of a luddite like me, and prefer the standard text files. Just choose "Write Backwards Compatible Log File" in the Session's properties.
Be aware that this will actually create a PAIR of log files for your job - a .bin logfile, and a regular ASCII log file. Curiously enough, the .bin log file will be twice as large as your regular ASCII log file (so much for progress). Workflow manager will still read the .bin file - so it will still look all fancy (as compared to previous Informatica versions), but you'll also have the regular file in case you need to do some historical investigation, you can do so easily.
You can use the "Import" function within Workflow monitor, but I find it to be kind of a nuisance. Go to "Tools" and "Import Log" to import .bin files directly into Workflow Monitor if you want to read old .bin files.
Monday, May 18, 2009
2 sessions, 1 mapping
One of the neat things about Informatica is its ability to re-use code. You can take advantage of this by writing generic mappings that you intend to run in different ways, perhaps with a slight variation on the SQL Override, for example.
We have a couple of mappings which we are doing this with. Our customer match/consolidation sessions are replicated into 8 pieces, each doing a subset of the alphabet. We use a parameter file in the workflow to tell each session which letter to process. This way, we only have to maintain one version of the mapping, and it gets called 8 different ways.
The parameter file we use has entries for each session which look something like this:
[s_work_CustMasterMatched_AtoB]
$$STREAM=AtoB
$$STREAM_BEGIN=A
$$STREAM_END=Bzzzzz
$PMSessionLogFile=s_work_CustMasterMatched_AtoB.log
This is handy, because if the volume of data is too large to process (and we run out of memory trying to process it), I can cut it in half by processing only A to Azzzzz. And I don't have to change the code.
In your session properties, be sure to set your log file name to $PMSessionLogFile - anything other than this, and you're not going to get the name of your logfile right. Informatica looks for this variable name specifically. You can't even be clever and use something like $logfile here. It just won't work - you'll get a log named $logfile.bin - and all sessions sharing that name will write to the same log. No good.
Also remember that if you modify the SQL override at the workflow level, and changes you make to the mapping won't be reflected in the workflow (since workflow changes override session changes, which override mapping changes). So, be diligent in how you make updates to these kinds of jobs.
We have a couple of mappings which we are doing this with. Our customer match/consolidation sessions are replicated into 8 pieces, each doing a subset of the alphabet. We use a parameter file in the workflow to tell each session which letter to process. This way, we only have to maintain one version of the mapping, and it gets called 8 different ways.
The parameter file we use has entries for each session which look something like this:
[s_work_CustMasterMatched_AtoB]
$$STREAM=AtoB
$$STREAM_BEGIN=A
$$STREAM_END=Bzzzzz
$PMSessionLogFile=s_work_CustMasterMatched_AtoB.log
This is handy, because if the volume of data is too large to process (and we run out of memory trying to process it), I can cut it in half by processing only A to Azzzzz. And I don't have to change the code.
In your session properties, be sure to set your log file name to $PMSessionLogFile - anything other than this, and you're not going to get the name of your logfile right. Informatica looks for this variable name specifically. You can't even be clever and use something like $logfile here. It just won't work - you'll get a log named $logfile.bin - and all sessions sharing that name will write to the same log. No good.
Also remember that if you modify the SQL override at the workflow level, and changes you make to the mapping won't be reflected in the workflow (since workflow changes override session changes, which override mapping changes). So, be diligent in how you make updates to these kinds of jobs.
Wednesday, May 13, 2009
Problem solved
Two blog entries in one day. I must be trying to make up for yesterday.
I just spent a couple of hours trying to solve this problem, and I think I finally did.
I had a session with a $$LASTRUN date which should receive the sessstarttime of the mapping and then set that when the session completes so that it knows where to pick up from the previous run.
Except it wasn't working. When I ran the session, the mapping variable ended up with the same exact value as it had started with. Which frustrated me, because I have other jobs that do the exact same thing and they do work. So, I ended up comparing this mapping (which wasn't working) and one that was and found this:
The mapping which worked had a default mapping variable value of '2009-04-17 12:00:00'.
And the default mapping variable value of the one not working was '04/17/2009 12:00:00'.
Note the ever-so-subtle difference.
Now, why this caused the mapping variable not to get assigned the correct value, I'll never know.
But keep this tip in your back pocket in case you run into problems with mappings behaving in this way.
I just spent a couple of hours trying to solve this problem, and I think I finally did.
I had a session with a $$LASTRUN date which should receive the sessstarttime of the mapping and then set that when the session completes so that it knows where to pick up from the previous run.
Except it wasn't working. When I ran the session, the mapping variable ended up with the same exact value as it had started with. Which frustrated me, because I have other jobs that do the exact same thing and they do work. So, I ended up comparing this mapping (which wasn't working) and one that was and found this:
The mapping which worked had a default mapping variable value of '2009-04-17 12:00:00'.
And the default mapping variable value of the one not working was '04/17/2009 12:00:00'.
Note the ever-so-subtle difference.
Now, why this caused the mapping variable not to get assigned the correct value, I'll never know.
But keep this tip in your back pocket in case you run into problems with mappings behaving in this way.
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?
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?
Subscribe to:
Posts (Atom)