Time zone and aggregation by date

View previous topic View next topic Go down

Time zone and aggregation by date

Post  wjordan on Wed May 05, 2010 5:56 pm

We have transactions that occur in multiple time zones, but store in the DW all in one common format (EST) -- BI displays the time appropriately by location OR by user. We are building an aggregated fact table -- aggregating by date. We don't want to aggregate PST data by EST date. Has anyone found a good solution to this dilemna?

wjordan

Posts : 6
Join date : 2010-05-05

View user profile

Back to top Go down

Re: Time zone and aggregation by date

Post  ngalemmo on Wed May 05, 2010 6:24 pm

Is this a 24 hour operation? What does the business want to do?

It comes down to someone defining what the 'day' should be. Either they say the date is based on one time zone or they develop a list of regions, locations, offices or whatever and the time zone that defines the 'day' for that entity. Then build your aggregation accordingly.

Just hope they don't nit over what to do on the days the time changes between standard and daylight savings.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Time zone and aggregation by date

Post  wjordan on Thu May 06, 2010 7:19 am

Thanks, that makes sense. Yes, a 24 hour operation, so time zone can change the day. And the business isn't sure what they want. Looking for advise of a good solution. We have some European ops too which makes the issue worse.

wjordan

Posts : 6
Join date : 2010-05-05

View user profile

Back to top Go down

Re: Time zone and aggregation by date

Post  ngalemmo on Thu May 06, 2010 11:29 am

If they don't know what they want, the most general solution would be to include time zone as a dimension to the aggregate then aggregate by day for each time zone. If they don't like that idea, push for a rule to determine what a 'day' is. The big problem with using time zone is that everyone will have a different number for the day's activity.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

thanks

Post  wjordan on Thu May 06, 2010 12:21 pm

sounds like excellent advise. I'll run with it.
thx

wjordan

Posts : 6
Join date : 2010-05-05

View user profile

Back to top Go down

Re: Time zone and aggregation by date

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