Re: Design question

From: Geoff Muldoon <>
Date: Wed, 18 Feb 2009 09:46:50 +1100
Message-ID: <>

In article <9b33601f-b16c-4af3-aa5c-b7e123fbc097>, says...

> Would this be ok:
> - dimension table "Location'

Yes, definitely.

> - dimension table "Time"

Yes, definitely.

> - dimension table "Temperature"

Maybe. What attributes other than a simple numerical value are there? It could be that "Temperature" is actually a candidate as one of your fact tables:
location_id, time_id, maxtemp_val, mintemp_val or
location_id, time_id, temp_type_id (using Temp Type Dimension), temp_val

"Rainfall" eg. could then be another, rather than your idea of a single "Measurement" fact table.

> Some questions:
> - is it better to put the Time as a separate dimension, or can we put
> it as Date column in the Measurements table ?

I definitely use a date dimension. Each entry has other attributes like year, month, day-of-week, day-of-year, week-of-year, etc. Given your OP maybe even a column for "season".

> - what about the primary key on the fact table ? Does it have to be a
> natural PK or can it be a surrogate key ?

Mine always uses a surrogate, but that's because of particular design decisions. Either can work.

> - in a DWH, why are Time dimensions often split-up into different
> columns as day, week, year (number datatype) instead of just one Date
> column ?

Query efficiency. Your "season" example?

Other suggestions:

During the design phase, hammer your end users on definitions. Is snowfall the same as rainfall?

Consider then relative storage v. query speed benefits of time serialling (using a start date and end date rather than an individual record for each date instance) for potentially "slowly changing facts": rainfall = 0 in a location for weeks on end.

Don't be afraid of seeing your total index storage approach or even exceed actual data storage.

Use lots of Bitmap indexes. Set the instance parameters for star schema transformation.

In addition of the Kimball book already mentioned, there's a good Oraclespecific  data warehouse book (can't put hands on my copy to check the title) by Bert Scalzo.

Be pragmatic in your design. I use elements of both the Kimball and Inman approaches. There is no one "right way".

Geoff M Received on Tue Feb 17 2009 - 16:46:50 CST

Original text of this message