PDA

View Full Version : DynamoDB: Built in Time Dimension support!



ngoodman
11-20-2009, 11:44 PM
DynamoDB (aka LucidDB (http://www.luciddb.org)) 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’s a lot of other things BI consultant/developers need. I’ll continue to post about some of the great BI features that DynamoDB has for the modern datasmiths.

First feature to cover that’s dead easy, is the built in ability to generate a time dimension (http://pub.eigenbase.org/wiki/LucidDbAppLib_FISCAL_TIME_DIMENSION), including a Fiscal Calendar attributes. If you’re using Mondrian (or come to that, your own custom SQL on a star schema) you need to have a time dimension. Time is the most important dimension! Every OLAP model I’ve ever built uses one! It something that you, as a datasmith will need to do with every project; that’s why we’ve built it right into our database.

Here’s a dead simple way to create a fully baked, ready to use Time Dimension to use with Mondrian.

-- Create a view that is our time dimension for 10 years, with our-- Fiscal calendar starting in March (3)create view dim_time as select * fromtable(applib.fiscal_time_dimension (2000, 1, 1, 2009, 12, 31, 3));OK, that’s it. You’ve created a Time Dimension! * see NOTE at end of post.

So, we’ve created our time dimension, complete with a Fiscal calendar for 10 years in a single statement! Awesome - but what does it contain?

-- Structure of new time dimensionselect "TABLE_NAME", "COLUMN_NAME", "DATATYPE" from sys_root.dba_columnswhere table_name = 'DIM_TIME';+-------------+---------------------------------+-----------+| TABLE_NAME | COLUMN_NAME | DATATYPE |+-------------+---------------------------------+-----------+| DIM_TIME | FISCAL_YEAR_END_DATE | DATE || DIM_TIME | FISCAL_YEAR_START_DATE | DATE || DIM_TIME | FISCAL_QUARTER_NUMBER_IN_YEAR | INTEGER || DIM_TIME | FISCAL_QUARTER_END_DATE | DATE || DIM_TIME | FISCAL_QUARTER_START_DATE | DATE || DIM_TIME | FISCAL_MONTH_NUMBER_IN_YEAR | INTEGER || DIM_TIME | FISCAL_MONTH_NUMBER_IN_QUARTER | INTEGER || DIM_TIME | FISCAL_MONTH_END_DATE | DATE || DIM_TIME | FISCAL_MONTH_START_DATE | DATE || DIM_TIME | FISCAL_WEEK_NUMBER_IN_YEAR | INTEGER || DIM_TIME | FISCAL_WEEK_NUMBER_IN_QUARTER | INTEGER || DIM_TIME | FISCAL_WEEK_NUMBER_IN_MONTH | INTEGER || DIM_TIME | FISCAL_WEEK_END_DATE | DATE || DIM_TIME | FISCAL_WEEK_START_DATE | DATE || DIM_TIME | FISCAL_DAY_NUMBER_IN_YEAR | INTEGER || DIM_TIME | FISCAL_DAY_NUMBER_IN_QUARTER | INTEGER || DIM_TIME | FISCAL_YEAR | INTEGER || DIM_TIME | YEAR_END_DATE | DATE || DIM_TIME | YEAR_START_DATE | DATE || DIM_TIME | QUARTER_END_DATE | DATE || DIM_TIME | QUARTER_START_DATE | DATE || DIM_TIME | MONTH_END_DATE | DATE || DIM_TIME | MONTH_START_DATE | DATE || DIM_TIME | WEEK_END_DATE | DATE || DIM_TIME | WEEK_START_DATE | DATE || DIM_TIME | CALENDAR_QUARTER | VARCHAR || DIM_TIME | YR | INTEGER || DIM_TIME | QUARTER | INTEGER || DIM_TIME | MONTH_NUMBER_OVERALL | INTEGER || DIM_TIME | MONTH_NUMBER_IN_YEAR | INTEGER || DIM_TIME | MONTH_NUMBER_IN_QUARTER | INTEGER || DIM_TIME | MONTH_NAME | VARCHAR || DIM_TIME | WEEK_NUMBER_OVERALL | INTEGER || DIM_TIME | WEEK_NUMBER_IN_YEAR | INTEGER || DIM_TIME | WEEK_NUMBER_IN_QUARTER | INTEGER || DIM_TIME | WEEK_NUMBER_IN_MONTH | INTEGER || DIM_TIME | DAY_FROM_JULIAN | INTEGER || DIM_TIME | DAY_NUMBER_OVERALL | INTEGER || DIM_TIME | DAY_NUMBER_IN_YEAR | INTEGER || DIM_TIME | DAY_NUMBER_IN_QUARTER | INTEGER || DIM_TIME | DAY_NUMBER_IN_MONTH | INTEGER || DIM_TIME | DAY_NUMBER_IN_WEEK | INTEGER || DIM_TIME | WEEKEND | VARCHAR || DIM_TIME | DAY_OF_WEEK | VARCHAR || DIM_TIME | TIME_KEY | DATE || DIM_TIME | TIME_KEY_SEQ | INTEGER |+-------------+---------------------------------+-----------+-- Let's look at a few rowsselect time_key_seq, time_key, yr, month_number_in_year, fiscal_year, fiscal_month_number_in_year from dim_time;+---------------+-------------+-------+-----------------------+--------------+------------------------------+| TIME_KEY_SEQ | TIME_KEY | YR | MONTH_NUMBER_IN_YEAR | FISCAL_YEAR | FISCAL_MONTH_NUMBER_IN_YEAR |+---------------+-------------+-------+-----------------------+--------------+------------------------------+| 1 | 2000-01-01 | 2000 | 1 | 2000 | 11 || 2 | 2000-01-02 | 2000 | 1 | 2000 | 11 || 3 | 2000-01-03 | 2000 | 1 | 2000 | 11 || 4 | 2000-01-04 | 2000 | 1 | 2000 | 11 || 5 | 2000-01-05 | 2000 | 1 | 2000 | 11 || 6 | 2000-01-06 | 2000 | 1 | 2000 | 11 || 7 | 2000-01-07 | 2000 | 1 | 2000 | 11 || 8 | 2000-01-08 | 2000 | 1 | 2000 | 11 || 9 | 2000-01-09 | 2000 | 1 | 2000 | 11 || 10 | 2000-01-10 | 2000 | 1 | 2000 | 11 |+---------------+-------------+-------+-----------------------+--------------+------------------------------+Generating the Time Dimension is accomplished using DynamoDBs ability to include Java based UDF Table Functions. Table functions are really powerful - they allow a BI developer to write custom functions that output a “table” that can be queried like ANY OTHER TABLE (mostly). Check out the wiki page FarragoUdx (http://pub.eigenbase.org/wiki/FarragoUdx) if your interested.

And of course: download LucidDB and give it a whirl!

NOTE: To be candid, doing it as a view isn’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).



More... (http://www.nicholasgoodman.com/bt/blog/2009/11/20/dynamodb-built-in-time-dimension-support/)