RE: artificial values vs null

We use a surrogate key (integer) for the key of the time (date) dimension in
our datamart - saves space.In theory we we can distinguish between "date to
be determined", "date not applicable" (see the datawarehouse toolkit -
kimball 2nd ed). The key is is never null (but the date is).


-----Original Message-----
From: STEVE OLLIG [mailto:sollig@xxxxxxxxxxxxx]
Sent: Friday, September 24, 2004 9:45 AM
To: oracle-l@xxxxxxxxxxxxx
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

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@xxxxxxxxxxxxxx> wrote:
> > ----- Original Message -----
> > From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
> > >> 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.
> > 

Other related posts: