[Mondrian] timezones in timedimension

Oren Mazor oren.mazor at shopify.com
Sat Mar 23 19:19:49 EDT 2013


Hey Julian, thanks for the feedback.

The reason I ask is that I have a multi-tenant data cube, and different tenants would like to see things in their native timezone. 

Having a second time_id for the tenant timezone is the easy solution, but if the tenant timezone changes, those all need to be updated.

I already use views extensively to load my data in my schema, so I was hoping to be able to load the time dimension with a sql query that casts it somehow. not sure if this is actually possible in any meaningful way…

cheers
Oren

On 2013-03-20, at 12:12 PM, Julian Hyde <jhyde at pentaho.com> wrote:

> 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
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20130323/a8bf2930/attachment.html 


More information about the Mondrian mailing list