Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: How-to Date Dimension.

  1. #1
    Join Date
    Mar 2009
    Posts
    19

    Lightbulb How-to Date Dimension.

    Probably many of you already know this, but it took me a while to figure out how to make a date dimension without a loop, and without using the code on some post I found. It looked evil.

    I am sure you can make the transformation I made look nicer but I hope it can help whoever is looking to make a date dimension without using a database.

    Good luck, if anyone needs help with this let me know.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2007
    Posts
    830

    Default

    Leonardo,
    You're right. You don't need a database for creating the data for a date dimension. I don't like those examples either.

    There is an example that generates the dates in a different way, also without a DB, in samples/transformations inside the Kettle directory,
    regards
    mc

  3. #3
    Join Date
    Nov 2008
    Posts
    777

    Default

    So where do all the descriptive attributes get filled in, i.e., week number, month name, month abbrev, day of week, day of year, etc?

    Personally, I like using the date functions in MySQL to fill in those attributes. The week number is especially tricky because the ISO Standard (used in Europe) defines it differently than what is commonly used in the United States. The MySQL function WEEK(date[, mode]) allows you to choose.
    Last edited by darrell.nelson; 02-09-2010 at 08:58 PM.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  4. #4
    Join Date
    Sep 2007
    Posts
    830

    Default

    The sample ktr has all those descriptions.
    The disadvantage of using MySQL is that you are tied to a RDBMS.
    Besides that, maybe it's a matter of taste,
    Ultimately, when the time dimension is loaded, nobody cares where the data came from

    mc

  5. #5
    Join Date
    Apr 2007
    Posts
    1,998

    Default

    I cunningly use a database view to expose all those things, using the date functions mentioned. Then you dont have to worry about loading it into a real physical table.

  6. #6
    Join Date
    Nov 1999
    Posts
    9,727

    Default

    The ideal date dimension generator is a great topic and the source of endless discussions.

    Roland Bouman had this to say about it:

    http://www.oreillynet.com/databases/...a_locales.html

    Cheers,
    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

  7. #7
    Join Date
    Mar 2009
    Posts
    19

    Default

    Just added this transformation so anyone who needs to make one has a place to start. With kettle in the script object there are day of the week functions if I remember correctly. I just needed the date_nkey, date_id, year, month, and day.

    I will check out the example Maria.

    I hope it can help someone in this generation or in future ones.

    Kettle ftw

  8. #8
    Join Date
    Feb 2009
    Posts
    296

    Default

    I took some of my time to create a How To on this topic.
    The posting shows people how to use PDI to create a full date dimension with many attributes, flags and formats.

    http://www.fabianschladitz.de/index....di-kettle.html

    I hope it helps a bit.
    Fabian,
    doing ETL with his hands bound on his back

  9. #9
    Join Date
    May 2010
    Posts
    21

    Default

    Thanks a lot fabianS for your great tutorial.
    I used it right away and it worked.

    Vielen Dank!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.