[Mondrian] timezones in timedimension

Julian Hyde jhyde at pentaho.com
Wed Mar 20 12:12:52 EDT 2013


I don't fully understand your requirements.

If I am in California and you are in New York, and a purchase happened at 1am Monday NY time, is your requirement that I see it occurring on Sunday? Putting the fact data into different dimension members depending on who is asking the question is VERY difficult to implement efficiently.

Or maybe your requirement is to assign each fact to a particular day in a particular timezone. (E.g. I understand the advertising industry does everything based on the timezone in Madison avenue.) That is an easy requirement -- just add N hours to the UTC timestamp when calculating the day during ETL.

My advice is to stick to the tradiitional time dimension table structure. Do all the complex time calculations you like during ETL, pre-calculate all of the timestamp-to-year, timestamp-to-day, timestamp-to-day-in-timezone-where-transaction-occurred, but keep it really simple at run time. 

If necessary, you can have two foreign keys in the fact table: time_id and time_id_in_local_tz_where_transaction_occurred, and create two uses of the time dimension.

Julian


On Mar 20, 2013, at 9:03 AM, Oren Mazor <oren.mazor at shopify.com> wrote:

> Hey all,
> 
> so, all of my data is imported as UTC and stored as UTC (both facts and dimensions). but I want to be able to query with respect to a timezone…
> 
> I have some thoughts on how to do this, namely de-timezone my timedimension and assume that when facts point at a time member, that time member is in the native timezone. I'm not a fan, as that will make my ETL much less pleasnat than it could be.
> 
> is there any way to do this in mdx?
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian



More information about the Mondrian mailing list