Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Time dimension

Time dimension

From: S. K. <askme_at_foo.com>
Date: Wed, 17 Apr 2002 09:55:02 +0200
Message-ID: <a9j9pj$3pcae$1@ID-87429.news.dfncis.de>

I have an Oracle 8.1.6 Enterprise data warehouse with pretty detailed data (down to a second), with two dimension tables DIM_1, and DWTIME, and one huge fact table FACT_1.

The fact table FACT_1 contains billions of records and looks like this:

 DIM_1_ID (FK) number
 DWTIME_ID (FK) number



 VALUE number

... where VALUE is a measured fact, and remaining fields are contributed foreign keys from dimension tables DIM_1 and DWTIME.

The time dimension table DWTIME looks like this (my idea):

 DWTIME_ID (PK) number



 TIMESTAMP date
 SECOND number
 MINUTE number
 HOUR number
 DAYOFWEEK number
 DAY number
 MONTH number
 YEAR number

QUESTION: how can I make fast queries FROM-TO over FACT_1? The point is that, due to high cardinality of column DWTIME_ID in fact table FACT_1, I can not put bitmap indexes on that column!

QUESTION: related to previous question, how can one make fast from->to queries over tables similar to FACT_1, where instead of a DATE column, one has DATE_ID (number) coming from a time dimension table?

QUESTION: should I put DWTIME_ID in the time dimension table to be of format DATE, not NUMBER? My references are not clear -- sometimes it's DATE, sometimes NUMBER. This way I would have a column over which I could make from-to queries, but what's the use of time dimension table then (except for rolling up, which for me is not so important as from-to queries)?

SK Received on Wed Apr 17 2002 - 02:55:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US