Primary Key Data type in Time Dimension??

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 i
n
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!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
"Marcelo" <Marcelo@.discussions.microsoft.com> wrote in message
news:Marcelo@.discussions.microsoft.com:
> 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!|||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
"examnotes" <Marcelo@.discussions.microsoft.com>
wrote in
news:62A63A74-D1CE-4519-AA48-737FE514EB28@.microsoft.com:

> 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!
>