title: date/time dimension
body: <p>I am designing a data warehouse and I have a sticky issue with time. The grain I need is hourly (to calculate aggregate counts of events per hour) and I also have to accommodate a shift pattern that does not conveniently fit inside a 24 hour period (in fact it is possible that 'blue' shift wont cover the same time of day for several days).</p>
  
  <p>With this in mind I am contemplating one of 3 approaches</p>
  
  <ol>
  <li>a single time dimension with 175K rows in it.</li>
  <li>a snowflake time dimension with 7300 rows in a calendar dimension and 175k rows in a time dimension</li>
  <li>separate dimensions so that the fact table has foreign keys for the event date and for the event time.</li>
  </ol>
  
  <p>I am tending towards approach 3 as it allows the small calendar dimension to be referenced separately in joins, but I would appreciate any thoughts.</p>