interesting ... what was the partition key? Phylum?
Genus?
- paquette stephane <stephane_paquette_at_yahoo.com>
wrote:
> 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
> > > > >
> > > >
> > >
> >
>
=== message truncated ===
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: jack_silvey_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 Thu Aug 08 2002 - 11:43:32 CDT