5

I have a need to store a fairly large history of data. I have been researching the best ways to store such an archive. It seems that a datawarehouse approach is what I need to tackle. It seems highly recommended to use a date dimension table rather than a date itself. Can anyone please explain to me why a separate table would be better? I don't have a need to summarize any of the data, just access it quickly and efficiently for any give day in the past. I'm sure I'm missing something, but I just can't see how storing the dates in a separate table is any better than just storing a date in my archive.

I have found these enlightening posts, but nothing that quite answers my question.

Community
  • 1
  • 1
RubberDuck
  • 11,933
  • 4
  • 50
  • 95

2 Answers2

8

Well, one advantage is that as a dimension you can store many other attributes of the date in that other table - is it a holiday, is it a weekday, what fiscal quarter is it in, what is the UTC offset for a specific (or multiple) time zone(s), etc. etc. Some of those you could calculate at runtime, but in a lot of cases it's better (or only possible) to pre-calculate.

Another is that if you just store the DATE in the table, you only have one option for indicating a missing date (NULL) or you need to start making up meaningless token dates like 1900-01-01 to mean one thing (missing because you don't know) and 1899-12-31 to mean another (missing because the task is still running, the person is still alive, etc). If you use a dimension, you can have multiple rows that represent specific reasons why the DATE is unknown/missing, without any "magic" values.

Personally, I would prefer to just store a DATE, because it is smaller than an INT (!) and it keeps all kinds of date-related properties, the ability to perform date math etc. If the reason the date is missing is important, I could always add a column to the table to indicate that. But I am answering with someone else's data warehousing hat on.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 1
    Another advantage is being able to use time intelligence features if you go down the SSAS path and build cubes/tabular models. – TTeeple Jan 23 '14 at 15:56
1

Lets say you've got a thousand entries per day for the last year. If you've a date dimension your query grabs the date in the date dimension and then uses the join to collect the one thousand entries you're interested in. If there's no date dimension your query reads all 365 thousand rows to find the one thousand you want. Quicker, more efficient.

OTTA
  • 1,071
  • 7
  • 8
  • 2
    You mean your query wouldn't be able to use an index on a date column to seek and ignore the other 364 thousand rows? – Aaron Bertrand Jan 23 '14 at 15:46
  • Of course it could, I was just making a general point about how data warehouses are structured. – OTTA Jan 23 '14 at 15:52
  • 1
    That's what I'm not quite understanding. If you can index the date first as part of the pk, then why bother with a char(8) pk and fk on a separate table? – RubberDuck Jan 23 '14 at 16:43