Re: Design question

From: <matthias.hoys_at_gmail.com>
Date: Tue, 17 Feb 2009 00:57:55 -0800 (PST)
Message-ID: <9b33601f-b16c-4af3-aa5c-b7e123fbc097_at_k19g2000yqg.googlegroups.com>



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?
>
> GM

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 ...

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 ? - 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 ?

Thanks,
Matthias Received on Tue Feb 17 2009 - 02:57:55 CST

Original text of this message