Date Dimension, multiple timezone and offset attributes

View previous topic View next topic Go down

Date Dimension, multiple timezone and offset attributes

Post  bruce.szalwinski on Tue Dec 11, 2012 4:21 pm

I have a Date Dimension as given below. The warehouse currently stores metrics for multiple Customers but all under the same Date key, relative to the timestamp of the Company (the guys hosting the data warehouse for all of those Customers). The business has asked to report on metrics relative to the Customer's timezone. I understand about role playing dimensions and the design pattern of adding a date key for the Customer and another for the Company, no problem there. The issue is those pesky *_offset attributes. The attributes are updated at the start of the Company day, once again relative to the timezone of the Company. A very common query is:

select f.*
from fact f
inner join dim_dates d on d.dim_date_key = f.dim_customer_date_key -- role playing key, in timezone of Customer
where d.day_offset = -1;

If a Customer asks for day_offset = -1 at 8a in Seattle on 12/11/2012, they are expecting to see data for 12/10/2012. However, if at the exact same time a Customer in Sydney, AU asks for day_offset = -1, they are expecting to see data for 12/11/2012.

Any ideas on how to model this? Get the *_offset attributes into a different dimension? Sub dimensions of Date Dimension?

dim_dates

    dim_date_key
    cal_date
    year_start_dt
    ...
    year_offset
    qtr_offset
    month_offset
    day_offset

bruce.szalwinski

Posts : 8
Join date : 2012-12-11

View user profile

Back to top Go down

Re: Date Dimension, multiple timezone and offset attributes

Post  ian chapman on Thu Jan 03, 2013 12:02 pm

General date dimensions are only created at the 'date' level, i.e. 1 entry for each day. What you seem to be describing is the need to have a time dimension too. These two separate dimensions can then be used on the fact table, however this doesn't really overcome the issue of timezones or what level to store data in the time dimension (hour, minute, second millisecond ...).

I have used a transaction dimension that the 'actual' date and time that relates to the fact using 'TIMEZONE WITH TIME ZONE' datatype in Oracle. This allows you to store information on the date/time but also the timezone too. If this isn't available in your database, you can store a normal date/time together with information on the time zone in a seperate field. In then use an outrigger from the 'transaction dimension' to the date dimension.

ian chapman

Posts : 2
Join date : 2013-01-03

View user profile

Back to top Go down

Re: Date Dimension, multiple timezone and offset attributes

Post  bruce.szalwinski on Fri Jan 04, 2013 5:50 pm

Thanks for the reply. I also keep a Time dimension, although that seems to have fallen out of favor, so I also keep timestamps in the fact table directly. The *_offset values get updated every day. For example, today is January 4, 2013, so the day_offset would be 0. Tomorrow, all of the values shift one place. That is, January 5, 2013 would have a day_offset value of 0 and January 4, 2013 would have a value of -1. So if a report wanted to always get yesterday's data, they could write a query once "select ... where day_offset = -1". The issue is that the process that performs the update against the dimension assumes the timezone is PST, but not all of the consumers of the warehouse are in the PST timezone.

bruce.szalwinski

Posts : 8
Join date : 2012-12-11

View user profile

Back to top Go down

Re: Date Dimension, multiple timezone and offset attributes

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum