Re: Time if day in the time dimension

Barry,

The facilities on your comuter system may be key to the way forward. Also, will you only have one time field or several e.g. time booked/started/completed.
Select queries will foreseeably need to calculate time taken etc. and if start/finish times might span midnight (in local or central time zone) you will want to treat this as a mathematical field - preferably an extension of the date field. NCR Teradata allows YYYYMMDDHHMM.

The issue of time zone, I suggest, depends on how it varies and how often. If the difference between two zones is a constant no of hours then I suggest the central time equivalent should not be calculated and stored on each record. If it varies and the changes are on different dates, e.g. between US EST, British Summer Time and Central European Time then you would either need tables showing the difference between each zone at different dates/times or add to each record.

Adding the time differential instead of the actual equivalent time would save you space.

Ken_Hansen

In article <8nraml$a5d$1_at_nnrp1.deja.com>,   bmeltz_at_mediaone.net wrote:
> This did not get much response in the comp.databases.olap forum, so I
> thought I would try it here. Sorry for any duplication for folks who
> subscribe to both groups.
>
> With my new organization, our time dimension will likely go to the
 hour
> (if not minute) level of granularity. This is new to me, since
> previously, the finest grain of time has always been the day. By the
> way, the warehouse will be dimensional in a relational database (your
> basic ROLAP).
>
> I was just going to (and may likely still) add a level or two to your
> basic time dimension, minute and hour. They are just different levels
> of detail, after all. Also, since the data will span multiple time
> zones, my first inclination was two time keys, one GMT and one local
> (actually, it was my second inclination, the first was to have each
> time record combining GMT and local variations with a time zone
> attribute for each time zone, but this was just a fleeting thought
 that
> gave me a headache and was quickly discarded).
>
> Looking for new ideas and approaches (and maybe even validation of my
> initial thoughts), I checked the usual suspects. One stop was Ralph
> Kimball's article list where I ran across 'Think Globally, Act
 Locally'
> ( http://www.intelligententerprise.com/9812/warehouse.shtml ). In it,
> Ralph does articulate the two time key idea, but he suggests that time
> of day be expressed in the fact table. This seems odd to me, since
> from a basic conceptual level, time is a 'by', not a 'what'. Also,
> time of day is, as previously stated, simply a lower level
> in the overall time hierarchy.
>
> Does anyone have any comment on this article and area in general?
 What
> have folks used in the past and with what degrees of success?
>
> Thanks,
> Barry
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Aug 25 2000 - 14:38:37 CEST