Re: Design question
Date: Tue, 17 Feb 2009 00:57:55 -0800 (PST)
On Feb 16, 11:17 pm, Geoff Muldoon <geoff.muld..._at_trap.gmail.com> wrote:
> Mladen Gogala says...
> > On Mon, 16 Feb 2009 20:34:59 +0100, Matthias Hoys wrote:
> > > Hello group,
> > > Oracle version is 10g. 11g might be possible too. I'm currently involved
> > > with the pre-analysis of a new project I'll be working on.
> > > We will get historical environmental data (temperature, rainfall, ...)
> > > for about 70.000 different locations, and this for a period of 30 years,
> > > with 1 measurement for each day of the year. So the total amount of
> > > records will be around 750 million (70.000 x 365 x 30). This sounds
> > > pretty much like a datawarehouse, no ? ;)
> > Matthias, this looks pretty much like a classic star schema to me.
> > Locations will, of course, be the fact table and measurements will be
> > dimensions. You will need one table for each type of measure.
> Shouldn't that be measurement as a fact and location as a dimension?
Would this be ok:
- dimension table "Location' - dimension table "Time" - dimension table "Temperature" - fact table "Measurements": location_id, time_id, maxtemperature_id,mintemperature_id etc ...
- is it better to put the Time as a separate dimension, or can we put it as Date column in the Measurements table ? - what about the primary key on the fact table ? Does it have to be a natural PK or can it be a surrogate key ? - 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 ?
Matthias Received on Tue Feb 17 2009 - 02:57:55 CST