sql server data warehouse: Primary Key Data type in Time Dimension???? -- sql server data warehouse

Primary Key Data type in Time Dimension????

Marcelo
1/29/2005 11:47:02 AM

sql server data warehouse:

I have to create a Time dimension with day grain in a Datawarehouse system
and I donâ€™t know what is the best data type for the primary key...

For example
1) I could put Number(8) datatype, then the dates will be: 20050114,
20050115, 20050116.... Then in the fact tables I put the Number(8) datatype
in the date fields... But in my reporting tools I have to put the conversion
function to show the dates in the right format.
2) Or I could put Date datatype, then the dates will be: 01/14/2005,
01/15/2005, 01/16/2005.... Then in the fact tables I put the Date datatype in
the date fields...

Itâ€™s the Date primary key a bad datatype? (Very slow)

What is the best Primary Key Data type in Time Dimension???

Thanks!

Re: Primary Key Data type in Time Dimension????

Allan Mitchell
1/30/2005 8:51:49 AM

In my opinion the key should be as small as you can get it.

Depending on how many days you want to store you could have

Smallint (4 bytes) == over 170 years
Int (8 bytes) == a very long time

[quoted text, click to view]

Re: Primary Key Data type in Time Dimension????

Steve Schoon
2/2/2005 7:42:05 PM

I have my date dimension using an INT primary key like 20050202.
This has been running for about 6 months now, and it is really
feeling like a good decision.

As for your reporting tool issue, you can just put additional
columns on your time dimension like

calendar_date DATETIME,
month_name VARCHAR(10),
day_name VARCHAR(10),
day_of_week VARCHAR(10),
....etc...

So you can have nice pre-formatted things to use in your
reporting tools.

It also makes for nice roll-up groupings if you include things
like

fiscal_period,
week_of_year,
quarter_of_year,
....etc...

Good luck,

Steve

"=?Utf-8?B?TWFyY2Vsbw==?=" <Marcelo@discussions.microsoft.com>
wrote in
news:62A63A74-D1CE-4519-AA48-737FE514EB28@microsoft.com:

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server data warehouse group.