Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Time dimension
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
... 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
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