Re: Design question
Date: Sat, 21 Feb 2009 11:18:13 +0000 (UTC)
Message-ID: <gnontl$aje$1_at_solani.org>
On Sat, 21 Feb 2009 11:49:37 +1100, Geoff Muldoon wrote:
> Matthias Hoys says...
>>
>> "Mladen Gogala" <gogala.mladen_at_gmail.com> wrote in message
>> news:gnebgm$hfl$2_at_solani.org...
>> > On Tue, 17 Feb 2009 00:57:55 -0800, matthias.hoys wrote:
>> >
>> >> 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 ...
>
>> I have an additional question regarding the "Temperature" dimension.
>> These are values like 10.34, 25.40, -0.19 ... so these are floating
>> point values with a precision of 2 numbers.Should I create a separate
>> dimension for this attribute (with foreign keys mintemperature_id,
>> maxtemperature_id in the Facts table) or just put them as such in the
>> facts table ? I don't see the real advantage of creating a separate
>> dimension for them ?
> > Temperature as you describe is a fact attribute, not a dimension. > > Geoff M
I agree with Geoff. In addition to that, you should avoid foreign keys in the data warehouse. The primary function of the foreign keys is to prevent an inapropriate value to be entered into the database. Foreign keys are of critical importance for the OLTP databases. Data warehouses, on the other hand, contain the data that have already been checked, the data that could be trusted to be correct and consistent. Foreign keys will not help you there and may add significant processing requirements to the ETL procedures. Triggers are also not recommended in the data warehouse, for the very same reasons as foreign keys. Critical thing to understand is that a data warehouse is not just an overgrown version of the in-house OLTP database, it has to be structured differently and managed differently. Generally speaking, it's harder and more taxing to properly manage a DW-type database then to properly manage an OLTP database. There is a God and Ralph Kimball is her prophet. Please, read the DW toolkit book by Ralph Kimball. I also like "Mastering Data Warehouse Design" by Claudia Imhoff and others.
-- http://mgogala.freehostia.comReceived on Sat Feb 21 2009 - 05:18:13 CST