1

The two most important fields, that are everywhere in our warehouse, are the UserAccountKey and the DateKey.

  • DateKey is (int, not null)
  • UserAccountKey is (int, not null)

Are these the correct data type?

The WH was created in 2006 so type Date wasn't an option, although we are now running 2008-R2 so it is now an option for the next iteration of the WH. It's a well established WH with multiple cubes built from it and a whole reporting system. Additional question to the above is when the WH was created would the architect have been better having the DateKey as type Char(8) rather than INT?

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • also see http://stackoverflow.com/questions/8415715/using-a-date-field-as-primary-key-of-a-date-dimension-with-mysql/8416548#8416548 – Damir Sudarevic Jun 24 '12 at 15:36
  • @DamirSudarevic so maybe it was set up as an integer to make things easy to partition; if it had been type char(8) this wouldn't have been so easy? – whytheq Jun 24 '12 at 15:44
  • 2
    Why would he/she double the key size (in this and all fact tables)? INT = 4 byte; char(8) = 8 byte? If you often need `'YYYYMMDD'` you can always get it from date dimension, and if it is not there, add it. – Damir Sudarevic Jun 24 '12 at 15:51
  • @DamirSudarevic thanks...effectively you are helping answer the question; sounds like size might have been a bit reason for the choice of `int` – whytheq Jun 26 '12 at 17:04

1 Answers1

9

There's actually a data type especially for dates. I'd change the DateKey type from int to date.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 1
    +1: by making it a `date`, it enables all kinds of date-related functionality, like *DateKey + 1 week* which is written [`dateadd(week, 1, DateKey)`](http://msdn.microsoft.com/en-us/library/ms186819.aspx). – wallyk Jun 23 '12 at 15:31
  • ok - so this is one of the new data types? and even though it looks like 20120622 it can have a type date? when WH was set up I wonder why he used int?! – whytheq Jun 23 '12 at 17:08
  • `date` was introduced in SQL Server 2008, so it might not have been around when the WH was created. – Andomar Jun 23 '12 at 17:17
  • yep ; the WH was created in 2006 so type `Date` wasn't an option. It's a well established WH with multiple cubes built from it and a whole reporting system. I'll add these details to the OP. – whytheq Jun 24 '12 at 09:27
  • In a warehouse environment often dates are integers for ease of joining to the Calendar table, which has the actual *date* type in there. Often, I include both the integer YYYYMMDD as well as a date / datetime datatype in the fact table, so I can both do date arithemetic as well as filter based on "business" dates, like workdays, holidays, etc, with an easy join to the other dimension. – N West Jul 06 '12 at 13:10