Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: artificial values vs null

RE: artificial values vs null

From: Lex de Haan <>
Date: Fri, 24 Sep 2004 15:59:38 +0200
Message-ID: <>

interesting idea ... and of course I have been following this thread with great interest :-)
I am not a seasoned DWH designer either, but this idea sounds strange to me. The "<" comparison operator does not know about this special meaning of "January 1, 1900"
and the GROUP BY operator is equally ignorant...

Is this approach meant to avoid nulls in the FK column in the fact table?

Kind regards,

visit <>

skype me <callto://lexdehaan>

-----Original Message-----
[]On Behalf Of STEVE OLLIG Sent: Friday, September 24, 2004 14:45
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
> <> wrote:
> > ----- Original Message -----
> > From: "Niall Litchfield" <>
> > >> 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.
> >


Received on Fri Sep 24 2004 - 09:00:11 CDT

Original text of this message