Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Star Schema Design - timestamps
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

384 Posts

Posted - 11/02/2012 :  00:47:32  Show Profile  Reply with Quote
I thought I'd post a new topic since this is similar but not the same as the one I just finished. I'm going to post one more to separate these out.

I'd like to expand on the idea of a date dimension. Say for example, your business is a public transit system (buses) and the "transaction" that occurs is when a rider boards the bus. Planners want to see very detailed information, so I can't pre-aggregate the records in a fact table; I need to be able to show 1 record per transaction (boarding). There is a timestamp on each of these transactions.

The question is, would it make sense to store the timestamp of the transaction in the Date Dimension table or create a complete new table solely for time dimensions. It seems like if you did that, you wouldn't be able to produce cross-dimensional reports, but I may be completely off base. Would it be more prudent to expand the hierarchy of the date dimension table to include time?


Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 11/02/2012 :  05:22:22  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Usually you would have a date dimension and a time dimension.
Time dimension is usually per minute.
You can combine the two in reports.
You might want to store the time to more accuracy for drill down but that would be detail and outside the aggregation.

I would start off with that and see how you get on but I would be surprised if you have an issue.

Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 11/02/2012 :  10:55:10  Show Profile  Reply with Quote
For certain queries, it is good to store the full datetime together in the fact table. You should probably also have separate dimension keys for date and time.

Otherwise, it becomes hard to write queries that look at specific date ranges. For example, all trips that started between 11:30 last Tuesday and 9:45 on Thursday. If you only have the date and time in separate columns, a query for that is very complex and can be a performance problem.

You can view the datetime as a degenerate dimension, meaning that it has no associated dimension table.

Go to Top of Page

Constraint Violating Yak Guru

384 Posts

Posted - 11/03/2012 :  02:13:42  Show Profile  Reply with Quote
Michael and Nigel, thank you both! Your suggestions make a lot of sense and confirm what I think I was thinking of.
Go to Top of Page

Constraint Violating Yak Guru

384 Posts

Posted - 11/03/2012 :  12:46:14  Show Profile  Reply with Quote
Michael, if you're still monitoring this, is the following sort of what you're alluding to? Basically, I'd allow the timestamps in the fact table, then create a timedimension table (using table variable for the reader who wants to test and not drop tables)...then the timekey in the fact table would relate to the timekey in this dimTime table?

declare @DimTime table (TimeKey int primary key identity not null, MinuteOfDay time)
insert into @DimTime (MinuteOfDay)

select DATEADD(mi, number, '00:00:00')
from master..spt_values
where type='p' and number<1440

Edited by - flamblaster on 11/03/2012 12:47:34
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000