Date Dimension, multiple timezone and offset attributes

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

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.

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.