{"id":465,"date":"2009-11-20T14:23:22","date_gmt":"2009-11-20T21:23:22","guid":{"rendered":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/?p=465"},"modified":"2009-11-20T14:23:22","modified_gmt":"2009-11-20T21:23:22","slug":"dynamodb-built-in-time-dimension-support","status":"publish","type":"post","link":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/2009\/11\/20\/dynamodb-built-in-time-dimension-support\/","title":{"rendered":"DynamoDB: Built in Time Dimension support!"},"content":{"rendered":"<p>DynamoDB (aka <a href=\"http:\/\/www.luciddb.org\">LucidDB<\/a>) is not just another column store database.  Our goal is being the best database for actually <em>doing<\/em> Business Intelligence; while that means being fast and handling large amounts of data there&#8217;s a lot of other things BI consultant\/developers need.  I&#8217;ll continue to post about some of the great BI features that DynamoDB has for the modern datasmiths.<\/p>\n<p>First feature to cover that&#8217;s dead easy, is the built in ability to <a href=\"http:\/\/pub.eigenbase.org\/wiki\/LucidDbAppLib_FISCAL_TIME_DIMENSION\">generate a time dimension<\/a>, including a Fiscal Calendar attributes.  If you&#8217;re using Mondrian (or come to that, your own custom SQL on a star schema) you need to have a time dimension.  <strong>Time is the most important dimension!<\/strong>  Every OLAP model I&#8217;ve ever built uses one!  It something that you, as a datasmith will need to do with every project; <strong>that&#8217;s why we&#8217;ve built it right into our database<\/strong>.<\/p>\n<p>Here&#8217;s a dead simple way to create a fully baked, ready to use Time Dimension to use with Mondrian.<\/p>\n<pre>-- Create a view that is our time dimension for 10 years, with our\n-- Fiscal calendar starting in March (3)\ncreate view dim_time as select * from\ntable(applib.fiscal_time_dimension (2000, 1, 1, 2009, 12, 31, 3));\n<\/pre>\n<p><strong>OK, that&#8217;s it.  You&#8217;ve created a Time Dimension!  <\/strong><em>* see NOTE at end of post.<\/p>\n<p><\/em>So, we&#8217;ve created our time dimension, complete with a Fiscal calendar for 10 years in a single statement!  Awesome &#8211; but what does it contain?<\/p>\n<pre>\n-- Structure of new time dimension\nselect \"TABLE_NAME\", \"COLUMN_NAME\", \"DATATYPE\" from sys_root.dba_columns\nwhere table_name = 'DIM_TIME';\n+-------------+---------------------------------+-----------+\n| TABLE_NAME  |           COLUMN_NAME           | DATATYPE  |\n+-------------+---------------------------------+-----------+\n| DIM_TIME    | FISCAL_YEAR_END_DATE            | DATE      |\n| DIM_TIME    | FISCAL_YEAR_START_DATE          | DATE      |\n| DIM_TIME    | FISCAL_QUARTER_NUMBER_IN_YEAR   | INTEGER   |\n| DIM_TIME    | FISCAL_QUARTER_END_DATE         | DATE      |\n| DIM_TIME    | FISCAL_QUARTER_START_DATE       | DATE      |\n| DIM_TIME    | FISCAL_MONTH_NUMBER_IN_YEAR     | INTEGER   |\n| DIM_TIME    | FISCAL_MONTH_NUMBER_IN_QUARTER  | INTEGER   |\n| DIM_TIME    | FISCAL_MONTH_END_DATE           | DATE      |\n| DIM_TIME    | FISCAL_MONTH_START_DATE         | DATE      |\n| DIM_TIME    | FISCAL_WEEK_NUMBER_IN_YEAR      | INTEGER   |\n| DIM_TIME    | FISCAL_WEEK_NUMBER_IN_QUARTER   | INTEGER   |\n| DIM_TIME    | FISCAL_WEEK_NUMBER_IN_MONTH     | INTEGER   |\n| DIM_TIME    | FISCAL_WEEK_END_DATE            | DATE      |\n| DIM_TIME    | FISCAL_WEEK_START_DATE          | DATE      |\n| DIM_TIME    | FISCAL_DAY_NUMBER_IN_YEAR       | INTEGER   |\n| DIM_TIME    | FISCAL_DAY_NUMBER_IN_QUARTER    | INTEGER   |\n| DIM_TIME    | FISCAL_YEAR                     | INTEGER   |\n| DIM_TIME    | YEAR_END_DATE                   | DATE      |\n| DIM_TIME    | YEAR_START_DATE                 | DATE      |\n| DIM_TIME    | QUARTER_END_DATE                | DATE      |\n| DIM_TIME    | QUARTER_START_DATE              | DATE      |\n| DIM_TIME    | MONTH_END_DATE                  | DATE      |\n| DIM_TIME    | MONTH_START_DATE                | DATE      |\n| DIM_TIME    | WEEK_END_DATE                   | DATE      |\n| DIM_TIME    | WEEK_START_DATE                 | DATE      |\n| DIM_TIME    | CALENDAR_QUARTER                | VARCHAR   |\n| DIM_TIME    | YR                              | INTEGER   |\n| DIM_TIME    | QUARTER                         | INTEGER   |\n| DIM_TIME    | MONTH_NUMBER_OVERALL            | INTEGER   |\n| DIM_TIME    | MONTH_NUMBER_IN_YEAR            | INTEGER   |\n| DIM_TIME    | MONTH_NUMBER_IN_QUARTER         | INTEGER   |\n| DIM_TIME    | MONTH_NAME                      | VARCHAR   |\n| DIM_TIME    | WEEK_NUMBER_OVERALL             | INTEGER   |\n| DIM_TIME    | WEEK_NUMBER_IN_YEAR             | INTEGER   |\n| DIM_TIME    | WEEK_NUMBER_IN_QUARTER          | INTEGER   |\n| DIM_TIME    | WEEK_NUMBER_IN_MONTH            | INTEGER   |\n| DIM_TIME    | DAY_FROM_JULIAN                 | INTEGER   |\n| DIM_TIME    | DAY_NUMBER_OVERALL              | INTEGER   |\n| DIM_TIME    | DAY_NUMBER_IN_YEAR              | INTEGER   |\n| DIM_TIME    | DAY_NUMBER_IN_QUARTER           | INTEGER   |\n| DIM_TIME    | DAY_NUMBER_IN_MONTH             | INTEGER   |\n| DIM_TIME    | DAY_NUMBER_IN_WEEK              | INTEGER   |\n| DIM_TIME    | WEEKEND                         | VARCHAR   |\n| DIM_TIME    | DAY_OF_WEEK                     | VARCHAR   |\n| DIM_TIME    | TIME_KEY                        | DATE      |\n| DIM_TIME    | TIME_KEY_SEQ                    | INTEGER   |\n+-------------+---------------------------------+-----------+\n\n-- Let's look at a few rows\nselect time_key_seq, time_key, yr, month_number_in_year, fiscal_year\n, fiscal_month_number_in_year from dim_time;\n+---------------+-------------+-------+-----------------------+--------------+------------------------------+\n| TIME_KEY_SEQ  |  TIME_KEY   |  YR   | MONTH_NUMBER_IN_YEAR  | FISCAL_YEAR  | FISCAL_MONTH_NUMBER_IN_YEAR  |\n+---------------+-------------+-------+-----------------------+--------------+------------------------------+\n| 1             | 2000-01-01  | 2000  | 1                     | 2000         | 11                           |\n| 2             | 2000-01-02  | 2000  | 1                     | 2000         | 11                           |\n| 3             | 2000-01-03  | 2000  | 1                     | 2000         | 11                           |\n| 4             | 2000-01-04  | 2000  | 1                     | 2000         | 11                           |\n| 5             | 2000-01-05  | 2000  | 1                     | 2000         | 11                           |\n| 6             | 2000-01-06  | 2000  | 1                     | 2000         | 11                           |\n| 7             | 2000-01-07  | 2000  | 1                     | 2000         | 11                           |\n| 8             | 2000-01-08  | 2000  | 1                     | 2000         | 11                           |\n| 9             | 2000-01-09  | 2000  | 1                     | 2000         | 11                           |\n| 10            | 2000-01-10  | 2000  | 1                     | 2000         | 11                           |\n+---------------+-------------+-------+-----------------------+--------------+------------------------------+\n<\/pre>\n<p>Generating the Time Dimension is accomplished using DynamoDBs ability to include Java based UDF Table Functions.  Table functions are really powerful &#8211; they allow a BI developer to write custom functions that output a &#8220;table&#8221; that can be queried like ANY OTHER TABLE (<em>mostly<\/em>).  Check out the wiki page <a href=\"http:\/\/pub.eigenbase.org\/wiki\/FarragoUdx\">FarragoUdx<\/a> if your interested.<\/p>\n<p>And of course: download LucidDB and give it a whirl!<\/p>\n<p><strong><em>NOTE: <\/em><\/strong><em>To be candid, doing it as a view isn&#8217;t the best approach.  For anything beyond tiny (5 million +) we should actually create the table, and do an INSERT INTO SELECT * FROM TABLE(fiscal_time_dimension).<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>DynamoDB (aka LucidDB) is not just another column store database. Our goal is being the best database for actually doing Business Intelligence; while that means being fast and handling large amounts of data there&#8217;s a lot of other things BI consultant\/developers need. I&#8217;ll continue to post about some of the great BI features that DynamoDB [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5,9],"tags":[],"_links":{"self":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/465"}],"collection":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/comments?post=465"}],"version-history":[{"count":0,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/465\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/media?parent=465"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/categories?post=465"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/tags?post=465"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}