How OWB Manages SLOWLY Changing Dimension

[prosix]

I have a table which I have to maintain as SLOWLY changing dimension. That table has 20 columns and 10 columns I need to match before inserting a new row. I was trying to use OWB mapping to do that, but when one of the column has got null values then it inserts a new row even though column was having null earlier. What should I configure to avoid these null value comparison in OWB mapping.

Please give some thought.

TIA

Prosix

 

[MattOh]

I'm not sure what OWB is or what features it supports, but if possible, I would recommend replacing nulls with some known, but not normally available value before you load them to your dimension table (e.g. "?" or "N/A&quot.

You have to be very careful when testing for nulls. I'd bet that's what's messing up OWB. Nulls also generally mess up comparisons and canculations.

A missing value (null) may have several meanings based on the data. For example, a missing SalesRep ID may mean "Error!" for some fact rows but it might mean "Not Applicable" for other facts.

I know it didn't answer your question, but hopefully it helped. Good luck.

Matt

 

[prosix]

MathOh
I am agree with you with few possibilites that data is character or number then I can easily put Space(' ') or "No value" or 'Unknown' or '0'. but I have some date field which have null values and these are valid values like Person's Termination dates or Probation End dates, which I don't want to insert a value like '31-dec-4712' or '01-jan-1800'.

So the problem is how do I resolve Nulls in date field.

I appreciate what you suggested for char & nums.

Any Idea further.
TIA.

 

[MattOh]

Hi TIA,
Actually, I do have a suggestion for dates and nulls, but it's going to sound funny. Use characters.

As you have pointed out, putting dummy dates into a date field is problematical. You typically can't put in "0" (which is illegal) and dates like 31-dec-4712 have their own unique set of problems ("You were hired when!?&quot

The trick here is to have TWO fields, one is the regular date field (which might be null)and the other is a character version of the date (e.g. "2002-01-01&quot. This way, you can load any value you want into the "CharDate" field, like "?" or "N/A". You only use the "real" date column when you want to perform date manipulation.

BTW, I recommend using the ISO standard format of YYYY-MM-DD in the character field. All DB vendors (and different countries) want to use their own version (e.g. dd-mmm-yyyy) and it always leads to confusion sooner or later.

Try it and see how that works for you.
Matt