I would just do a NVL, ISNULL check around the fields.
lookup sql on dim: select PK, field A ,field B, NVL(field A,0), NVL(field B,0) from table_x;
--note the changed value i used 0 but you can use whatever will never appear in the table to represent NULL
From the source you can uses an expression to check if the fields are null and default to 0 if they are.
Now when in the lookup you will not be comparing NULL's which is always tricky.
I am checking with Product Management if we have a better way but this is the method I have used before and works just fine.
Let me preface by saying I am the Business Analyst, not the Powercenter Architect, but when they walked me through it, they were doing a lookup. They used "Out_" parameters on the source side to change the nulls to placeholders, but on the target Dimension side, they were just mapping straight to the fields. Is there a way to put "Out_" parameters on both sides of a lookup so we can handle nulls on both sides? Really appreciate the help.
KB 113010 was the answer. It indicated that NULL will match to NULL during a lookup, and we just tried it, and it worked! Not sure why my developers didn't try that before immediately trying to get rid of the NULLs, but it worked out. Thanks to everyone for your help!
There might be a very simple answer to why they haven't tried before:
NULL should - as of database theory - not match anything, in particular not other NULL values. Frankly spoken I didn't know that, I didn't expect it, and I never would have tried that; I expected Informatica to behave "correctly" here, and that means (in my opinion) that NULL values should simply not match. Period.
It's inacceptable for me personally that PowerCenter behaves that way, but well, Informatica won't change that implementation just because I'm moaning about it (no matter how good my reasoning might be). So I just have to accept that PowerCenter works in a "dirty" manner here. So I learned something new from this your question, thanks for that.