Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
 Posted - 2012-11-02 : 00:47:32
|
| 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?Thanks! |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-11-02 : 05:22:22
|
| 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. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-11-02 : 10:55:10
|
| 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.http://en.wikipedia.org/wiki/Degenerate_dimensionCODO ERGO SUM |
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-11-03 : 02:13:42
|
| Michael and Nigel, thank you both! Your suggestions make a lot of sense and confirm what I think I was thinking of. |
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-11-03 : 12:46:14
|
| 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 |
|
|
|
|
|
|
|
|
Star Schema Design - timestamps