US and Worldwide: +1 (866) 660-7555
Results 1 to 4 of 4

Thread: about time dimension implementation

  1. #1
    Join Date
    Sep 2005
    Posts
    1,404

    Default about time dimension implementation

    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

  2. #2
    Join Date
    Nov 1999
    Posts
    9,677

    Default RE: about time dimension implementation

    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
    Matt Casters, Chief Data Integration
    Pentaho, Open Source Business Intelligence
    http://www.pentaho.org -- mcasters@pentaho.org

    Author of the book Pentaho Kettle Solutions by Wiley. Also available as e-Book and on the Kindle reading applications (iPhone, iPad, Android, Kindle devices, ...)

    Join us on IRC server Freenode.net, channel ##pentaho

  3. #3
    Join Date
    Sep 2005
    Posts
    1,404

    Default RE: about time dimension implementation

    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

  4. #4
    Join Date
    Nov 1999
    Posts
    9,677

    Default RE: about time dimension implementation

    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
    Matt Casters, Chief Data Integration
    Pentaho, Open Source Business Intelligence
    http://www.pentaho.org -- mcasters@pentaho.org

    Author of the book Pentaho Kettle Solutions by Wiley. Also available as e-Book and on the Kindle reading applications (iPhone, iPad, Android, Kindle devices, ...)

    Join us on IRC server Freenode.net, channel ##pentaho

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •