Re: artificial values vs null

I'm a bit lost here as dw is not my forte. But let's see:you'd use the timedim in an inner join, no? To the fact table, I mean.

And you'd be looking for matches on transaction date for analysis of time distribution, I presume. Because transaction date is NOT NULL, no problem either way.

But for product ship date, you'd NEED to have a NULL there to avoid an inner join: you don't want a product to be shown as shipped (has a ship date) when in fact it wasn't.

But using the artificial value here will falsify the results, won't it? That is the real danger in this case. Not the efficiency of the join.
Unless of course you mangle the Discovery query to make sure it "understands" that 1900 is a special value.

Once again I feel the problem stems from assigning a meaning to NULL: the ship date missing does not mean "no shipping". One can happen without the other: that is why your ship date is nullable. Having an artificial value there may cause problems if the code is not prepared to recognize that special value.

And that is the core of the problem: NULL should have no meaning whatsoever. Once it receives one, basically you MUST include handling of special cases in your code. And that opens a Pandora's box of potential problems.

Note: not saying it can't be solved or it can't be good in some exceptional cases. I'm talking general design approach here.

Cheers
Nuno Souto
nsouto_at_bizmail.com.au
----- Original Message -----
From: "STEVE OLLIG" <sollig_at_lifetouch.com> To: <oracle-l_at_freelists.org>
Sent: Friday, September 24, 2004 11:45 PM Subject: RE: artificial values vs null

> I'm not a NULLophobe (one who fears NULLs), but have an example where it
> might make sense to use an artificial value instead of a NULL. I'd be
> interested in hearing other's thoughts on this specific case.
>
> This is a star schema datamart and the should be NULL in question is the
> PK
> of the time dimension. I'm relatively new to datawarehousing, and we're
> building a new datamart. So I cannot contend this is will stand the test
> of
> time. But some members of my team are seasoned datawarehousers, so I
> don't
> think we're too far out in left field with this approach. We happened to
> choose January 1, 1900 as the artificial "Unknown" date in timedim (our
> company didn't exist in 1900 so our mart would care little about anything
> that may have happened then). All facts who have an unknown date have
> this
> value. Some dates are never unknown (transaction_date), but others can be
> (product_ship_date).
>
> Seems to be working well for us. FWIW, Oracle Discoverer is the user
> facing
> app for this mart. I think we could have set Discoverer up to work with
> NULL dates, but inner joins are the default for relating a fact to a
> dimension. I inherently shy away from having to tweak the default
> behavior
> of such an app so significantly.
>
> So what would a NULL zealot think of such blasphemy?
>>
>> On Fri, 24 Sep 2004 21:51:33 +1000, Nuno Souto
>> <nsouto_at_bizmail.com.au> wrote:
>> > ----- Original Message -----
>> > From: "Niall Litchfield" <niall.litchfield_at_gmail.com>
>> > >> Whatever the optimiser may do with 31 dec 2099,
>> > >> it will be a darn long shot better than a NULL value
>> > >> that can't be indexed...
>> > >
>> > > you may regret making that statement.... see below.
>> >
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l