We used the date as the key of the time dimension but
we were using a time dimension to drive the queries.
At my last client, I was surprised to see no time
dimension in the datawarehouse but I was even more
surprised to that there were no date at all in the
fact tables of this datawarehouse.
It was in a biotech company and the datawarehouse was
developped by Oracle Corp.
Time was having no meaning for the users (biologists).
- Jack Silvey <jack_silvey_at_yahoo.com> a écrit : >
Jared / all,
>
> Agree, date column in the fact is a bad idea.
>
> I have experience with a warehouse that had a fact
> table partitioned on a date column. This system does
> not use a date dimension, and queries are directly
> constrained on the date column in the fact.
>
> You are right, in this system, if you use a date
> dimension, you do not get partition elimination. You
> can get partition elimination if you use use
> partition
> key in the WHERE clause, but this setup has two
> major
> problems: 1) no true star execution, and 2) lack of
> flexibility.
>
> No true star execution: In true star schema
> execution
> the query visits all the dimension tables first,
> makes
> a cartesian join of all of the relevant dimension
> records, and use this dataset to select records from
> the fact table. This works since the fact table
> visit
> is usually the most expensive, and it is cheaper to
> do
> a cartesian join of dimensions rather than use the
> fact table as part of the regular query.
>
> Since the date column is in the fact table, the
> enduser must directly constrain queries against the
> fact. Therefore, this system does not use the STAR
> execution plan, since it must use the fact as part
> of
> the normal query and is not able to save it until
> the
> last step. This hampers query performance.
>
> Poor flexibility: This system is not flexible, since
> you cannot store date information other than just
> the
> date. For instance, since there is no date
> dimension,
> queries can't just look in the dimension table and
> see
> which dates comprise fiscal third quarter. The query
> issuer has to hardcode dates into the query, instead
> of saying "where datetab.3qflag = 'Y'".
>
> Kimball talks about direct fact constraint on dates
> with an air of disfavor in his book about data
> warehousing. I am sure that these two problems are
> not
> the only ones, they are just the first two that leap
> to mind.
>
> Jack
>
>
>
> > --- Jared.Still_at_radisys.com wrote:
> > > This discussion raises an interesting question.
> > >
> > > Do your fact tables have a date column in them?
> > >
> > > If so, how do you correlate that with the
> > date/time dimension table?
> > >
> > > If you use a local date column to control the
> > partitioning rather
> > > than a PK from the date/time dimension, user
> > queries based on
> > > the date/time dimension won't be able to use
> > partition elimination.
> > >
> > > Personally, I see no need for a date column in
> the
> > fact table.
> > >
> > > Jared
> > >
> > >
> > >
> > >
> > >
> > >
> > > paquette stephane <stephane_paquette_at_yahoo.com>
> > > Sent by: root_at_fatcity.com
> > > 08/06/2002 08:43 AM
> > > Please respond to ORACLE-L
> > >
> > >
> > > To: Multiple recipients of list
> > ORACLE-L
> > > <ORACLE-L_at_fatcity.com>
> > > cc:
> > > Subject: Re: Rolling partitions
> > >
> > >
> > > I've done that in a datawarehouse system.
> > > The fact tables were partitionned by date.
> > > Some fact tables were keeping different number
> of
> > > months : 6o, 12, 15,...
> > > This was handle in the metadata tables and was
> > written
> > > with PL/SQL (Oracle 8). You can do a lot of
> things
> > > with PS/SQL.
> > > So the metadata was containg the table name, the
> > > naming convention of the partition, the naming
> > > convention for the tablespace, disks name, the
> > number
> > > of partition per table, ...
> > > Data was moved into retention data tables then
> the
> > > partition were rolling : creation of new
> > tablespaces,
> > > creation of new partition, moving data to the
> > > retention tables, dropping partition,...
> > >
> > > HTH
> > >
> > > --- Tracy Rahmlow <tracy.rahmlow_at_aexp.com> a
> > écrit :
> > > > I am finally looking to implement partitioning
> > and
> > > > have some issues with
> > > > rolling partitions by date. (Ie add partion
> > p0802
> > > > and drop p0801) Does
> > > > anybody have or know of a generic
> > procedure/process
> > > > that will allow the
> > > > automation of this? I envision a number of
> > > > parameters being passed in. For
> > > > example, time period & table ... Or is there
> an
> > > > alternative that I need to
> > > > consider? (other than manual)
> > > >
> > > > Thanks
> > > >
> > > >
> > > > --
> > > > Please see the official ORACLE-L FAQ:
> > > > http://www.orafaq.com
> > > > --
> > > > Author: Tracy Rahmlow
> > > > INET: tracy.rahmlow_at_aexp.com
> > > >
> > > > Fat City Network Services -- (858) 538-5051
>
> > FAX:
> > > > (858) 538-5051
> > > > San Diego, California -- Public
> Internet
> > > > access / Mailing Lists
> > > >
> > >
> >
>
> > > > To REMOVE yourself from this mailing list,
> send
> > an
> > > > E-Mail message
> > > > to: ListGuru_at_fatcity.com (note EXACT spelling
> of
> > > > 'ListGuru') and in
> > > > the message BODY, include a line containing:
> > UNSUB
> > > > ORACLE-L
> > > > (or the name of mailing list you want to be
> > removed
> > > > from). You may
> > > > also send the HELP command for other
> information
> > > > (like subscribing).
> > >
> > > =====
> > > Stéphane Paquette
> > > DBA Oracle, consultant entrepôt de données
> > > Oracle DBA, datawarehouse consultant
> > > stephane_paquette_at_yahoo.com
> > >
> > >
> >
>
> > > Do You Yahoo!? -- Une adresse @yahoo.fr gratuite
> > et en français !
> > > Yahoo! Mail : http://fr.mail.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > > --
> > > Author: =?iso-8859-1?q?paquette=20stephane?=
> > > INET: stephane_paquette_at_yahoo.com
> > >
> > > Fat City Network Services -- (858) 538-5051
>
=== message truncated ===
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail :
http://fr.mail.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Aug 07 2002 - 17:16:09 CDT
