[Mondrian] Creating a time dimension based on foreign key toDateTime field

[Julian Hyde]

I'm guessing that dateid is an integer that doesn't have any structure to it
- i.e. can only be decoded by joining to the date_dim table.

You should create integer columns called the_year, the_month,
the_dayofmonth. Values for today would be 2008, 9, 23. Populate them using
Postgres's datetime functions (maybe EXTRACT(<timeunit> FROM <datetime
value>) if it's SQL:2003 compliant).

You can also have columns the_monthname, the_monthshortname, the_quarter
etc. if that's how you want to slice/display your data.

Make the columns NOT NULL.

Index the important columns, and use compound indexes for the common access
paths (e.g. the_year, the_month, the_date).

Then build your levels just like in the FoodMart schema.

Some people like to use surrogate values in their time dimension that look
like dates - e.g. today would be 20080923 - and can be decomposed using
modulo 100 and so forth. This is useful if you are eyeballing your data, but
not necessary by any means.

Some people try to scrimp on their time dimension - only creating values
where there are transactions or only for one or two weeks into the future.
This is a bad idea. Disk is cheap.

Some people try to make their time dimension run off a datetime value
directly, and using datetime functions to extract the year etc. on the fly.
This is assuming that your database is really really smart - which it isn't.
So don't do it.

Julian

> -----Original Message-----
> From: mondrian-bounces at pentaho.org 
> [mailto:mondrian-bounces at pentaho.org] On Behalf Of m h
> Sent: Tuesday, September 23, 2008 11:10 AM
> To: mondrian at pentaho.org
> Subject: [Mondrian] Creating a time dimension based on 
> foreign key toDateTime field
> 
> Hi Folks-
> 
> New to the list here.  Just started playing out with Mondrian.  Wrote
> up a basic schema yesterday and got the schema workbench connected to
> my db this morning (need to add jdbc jar to classpath in startup
> script).
> 
> My brief searching of the docs and searching the forums hasn't turned
> up an answer for my question, which is:
> 
> I have a dateid field in my fact table.  It's a foreign key to a
> date_dim table which has a DateTime field in it ("timestamp" in
> Postgres).  How do I generate my "Time" dimension schema here?
> 
> I'm all ears...
> 
> thanks much!
> 
> -matt
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
> 
> 
>