[Mondrian] timezones in timedimension
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…
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.
> 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
> Mondrian mailing list
> Mondrian at pentaho.org
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Mondrian