about time dimension implementation

[kettle_anonymous]

hi,


I have read some documents about datawarehouse time dimension implementation. Some documents about temporal implementation speak about a "Date Begin" and "Date End" attributes, other documents speak about a unique date timestamp and a "master" attribute that link a record with previous (time list of records)...



What is the best method? What method is commonly used in datawarehouse implementations?



In my opinion:
I think that the first method (2 timestamps) is more difficult to implement because I have to create triggers in order to maintain the "temporal referential integrity" but, once implemented, the table population phase is easier. The second one is more simple to implement but in the population phase I have to seach the previous record ID in order to insert it in the new record.



I found the first method implementation in:
developing time-oriented database applications in SQL (Richard T. Snodgrass)



What is the best implementation in order to use kettle to populate the tables?



Thanks in advance.
GR

[MattCasters]

Kettle standard implements date_from and date_to fields in all slowly changing dimension. (Dimension Lookup step)
I think by far this is the clearest and most transparent method. IMHO this is the case because comparing records is always difficult in reporting because SQL is not designed to do so.

Matt

[kettle_anonymous]

ok, thanks for the help.
I have take a look at the Dimension Lookup/Update and I see:


Date range start... and table date range end...



but there is another field: the version field



so please, give me some feedback if I have understood:



in my database I have to implement a fact table with for example:
date_from (default 1900)
date_to (default 2199)
version (default 1)



(where date_from and date_to are IDs in referential integrity with dimension time table)



when a new record version is inserted for the same date range the new record have to have a version+1 value in the version field?



thanks in advance
PS. excuse me but i'm a newbie in datawarehouse and kettle worlds

[MattCasters]

This is why they say that a little knowledge is dangerous... :-)

To make it clear: NO, you do not understand at all.

Please read-up on "Fact tables" and "Slowly Changing dimensions" FIRST and then come back to this forum. Try reading at least a few Ralph Kimball articles.

Thank you for your understanding,

Matt