CODEWITHSUNDEEP
mysqldatabasedatetimedata-warehousedimensional-modeling
mrk
mrk

Reputation: 3191

Date Dimension Generator for MySQL

I'm designing a data warehouse and I need a tool that can generate the Date Dimension. I'm using MySQL 5.x.

Thanks

Upvotes: 1

Views: 3293

Answers (2)

MOLAP
MOLAP

Reputation: 804

You should definitely check out this free date dimension feed on the Azure Data Market.

It's a date table feed designed for import into an Excel PowerPivot model, but can easily be used for other destinations as well (for example like a MySQL table).

The table contains columns particularly suitable for time business intelligence applications, and contains generic English language columns helping with creating a basic, all-purpose date dimension.

It also contains several other languages (including US, Hebrew, Danish, German and Bulgarian, and other languages are on the way).

Upvotes: 1

dlchet
dlchet

Reputation: 1063

You didn't specify if you are using a third-party ETL application or rolling your own.

If you are using a third-party application, there will most likely be a widget or function available to help generate your date dimension. For example, using the Pentaho Data Integration toolkit, see this excellent article from the O'Reilly Databases Blog: http://www.oreillynet.com/databases/blog/2007/09/kettle_tip_using_java_locales.html

If you are rolling your own, it is a pretty simple exercise to generate every date between two given dates. A stored procedure is going to be more performant, but writing the function in the language you are implementing ETL with will be more maintainable. The helpful links posted by @hafichuk are good examples of how to do the generation in stored procedures. Since you are designing the schema, you will have to write your own procedure which conforms with your definition of the date dimension, or at least modify those ones.

Finally, make sure to give yourself flexibility with the solution you choose - even though a date will only be generated once per production instance building of the "world", there will be a lot of other times when the same date dimension generation code will need to be used. (test runs, demo/staging deployments, in integration test suites...) Thus, it needs to be fast enough and/or flexible enough so that it is not a bottleneck. Generating your date dimension in the ETL language and doing so at the beginning of every integration test for the whole range of applicable dates will get old, fast.

Upvotes: 2

Related Questions