Timestamp dimensions in a data warehouse
From: Schauss, Peter (IT Solutions) <"Schauss,>
Date: Wed, 20 Apr 2011 18:35:03 +0000
Message-ID: <8AE45871F749FC4CBBE053CF2F8A493C03AA1028_at_XMBVAG74.northgrum.com>
I am in the early stages of designing a data warehouse intended to store what I will call metrics and diagnostic data. The granularity is, most likely, going to be at intervals of less than a minute. If I follow what I understand to be the classical data warehouse approach, I should have a dimension table with one row for each time interval instead of simply adding a timestamp column to the fact table. In Ralph Kimball's <The Data Warehouse Toolkit> the author justifies this approach by asserting that "most databases don't index SQL date calculations, so queries constraining on an SQL -calculated field wouldn't take advantage of an index".
Date: Wed, 20 Apr 2011 18:35:03 +0000
Message-ID: <8AE45871F749FC4CBBE053CF2F8A493C03AA1028_at_XMBVAG74.northgrum.com>
I am in the early stages of designing a data warehouse intended to store what I will call metrics and diagnostic data. The granularity is, most likely, going to be at intervals of less than a minute. If I follow what I understand to be the classical data warehouse approach, I should have a dimension table with one row for each time interval instead of simply adding a timestamp column to the fact table. In Ralph Kimball's <The Data Warehouse Toolkit> the author justifies this approach by asserting that "most databases don't index SQL date calculations, so queries constraining on an SQL -calculated field wouldn't take advantage of an index".
Is this statement still valid?
Would there be other reasons for putting the date in a dimension table?
As a further note, given the volume of data which we will be collecting, I will be partitioning the data and date may be one of the keys on which I choose to partition. I have not yet looked at the details of this, but would defining the date as dimension complicate matters?
In case it matters, the database will be Oracle 11g.
Thanks,
Peter Schauss
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 20 2011 - 13:35:03 CDT