RE: artificial values vs null
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <sollig@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 24 Sep 2004 15:59:38 +0200
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,
Lex.
-------------------------------
visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>
-------------------------------
skype me <callto://lexdehaan>
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of STEVE OLLIG
Sent: Friday, September 24, 2004 14:45
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
(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@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.
> >
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
