Slowly Changing Dimension Stage: Issues with Null

Bill_G · Post by **Bill_G** » Fri Apr 18, 2008 12:38 pm

We're using an SCD stage with a sequential source (comma delimited with double-quote enclosures) and Oracle Lookup/Target.

The initial run completed successfully, however the "change" test failed:

Code: Select all

APT_CombinedOperatorController(0),0: Field 'DVP' from input dataset '0' is NULL. Record dropped.

The field that is failing is not used in any functions, constraints, etc. It is mapped straight through the job without transformation. This field is nullable throughout the job.

However, this field is purposed as 'Type 2' and is compared to the value in the lookup/target.

The field for the matching record is NULL in the lookup/database as well.

The only thing I can think of is that the SCD stage is having issues with NULL during the Type 2 compare.

Any thoughts on how to correct this? I've tried NullToEmpty() in the Transformer Stage before the SCD as well as within the Upsert Derivation.

Code: Select all

kumar_s · Post by **kumar_s** » Fri Apr 18, 2008 12:45 pm

You mean to say, you getting this error even after this Null handling??
Make the Combinability Mode to False. So that you can nail down the error.
Does that DVP used in any of derivation Expression other than straight mapping?

Bill_G · Post by **Bill_G** » Fri Apr 18, 2008 1:21 pm

kumar_s wrote:Does that DVP used in any of derivation Expression other than straight mapping?

There are absolutely no derivation expressions on this field.

If I convert the incoming NULL value to empty sting or something else, then it wouldn't match the value being returned from the lookup...and would force a Type 2 change.

Is there a standard setting for NULL comparison in SCDs/Lookups?

Thanks in advance.

Bill_G · Post by **Bill_G** » Fri Apr 18, 2008 1:59 pm

kumar_s wrote:Make the Combinability Mode to False. So that you can nail down the error.

Good Idea. This points out that it is the SCD stage:

scdT_EMP_PROFILE,0: Field 'DVP' from input dataset '0' is NULL. Record dropped.

Any thoughts on how to handle NULLS in an SCD?

kumar_s · Post by **kumar_s** » Fri Apr 18, 2008 2:46 pm

May it could be simple databae NULL to NULL compare, which will obviously fail. Try to do the Same Null handling before SCD stage. On both input and reference link.

Bill_G · Post by **Bill_G** » Mon Apr 21, 2008 6:54 am

kumar_s wrote:May it could be simple databae NULL to NULL compare, which will obviously fail. Try to do the Same Null handling before SCD stage. On both input and reference link.

Thanks for the response.

So there is no job-level setting that will more cleanly handle NULLs within Parallel jobs? I would think that comparing NULLs is a common occurrence in SCD processing as not every field is populated 100% of the time.

I was hoping that it isn't necessary to implement NULL handling logic on every single input and reference field.

I'm not sure it is possible to handle NULLs in the reference link. According to everything I've read, the Oracle Enterprise Stage (reference) is to be directly connected to the SCD stage (so that it can dynamically create the SQL). Can I put a Transform Stage in between the two?

kumar_s · Post by **kumar_s** » Mon Apr 21, 2008 12:47 pm

You can have NVL function in Oracle select query to convert varchars/chars to spaces and Number to 0 or all 9's/

Bill_G · Post by **Bill_G** » Mon Apr 21, 2008 4:20 pm

As I suspected (read: hoped), this is a known bug and is resolved in Fix Pack 1a. We will install the pack tomorrow and retest.

Bottom line, the SCD stage should be able to handle NULLs in the comparison without kludging a special NULL transformation for each input and reference field. Let's hope Fix Pack 1a gets it right.