Time zone and aggregation by date

[wjordan]

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?

[ngalemmo]

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.

[wjordan]

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.

[ngalemmo]

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.

[wjordan]

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

[Sponsored content]