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).

chaim
http://www.learntorah.net

-----Original Message-----
From: STEVE OLLIG [mailto:sollig_at_lifetouch.com] Sent: Friday, September 24, 2004 9:45 AM To: oracle-l_at_freelists.org
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