Create and Populate a Date Dimension Table

Question

Post reply

Create and Populate a Date Dimension Table

Joey Morgan

SSC-Addicted

Points: 458
More actions
February 3, 2013 at 1:46 am

#273763

Comments posted to this topic are about the item Create and Populate a Date Dimension Table

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dcraday SSC Enthusiast Points: 126 More actions · Answer 1

Nice - But you have missed President's day as a Holiday. Third Monday of February.

Joey Morgan SSC-Addicted Points: 458 More actions · Answer 2

Thank you for pointing that out. I based the holidays on a list from the company where I worked at the time, and they didn't take that one off. It would be easy enough to add the code for that.

After MLK Day, insert:

--President's Day (3rd Monday)

([Month] = 2

AND [DAY_OF_WEEK] = 2

AND DATE_ID IN (SELECT

MIN(DATE_ID) + 14

FROM

DATE_DIM

WHERE

[Month] = 2

AND [DAY_OF_WEEK] = 2

GROUP BY

[Year])

)

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 3

Thank you for the informative script.

I noticed that many of the fields are defined as SMALLINT, even those that would never go over a TINYINT value. These include DAY_OF_WEEK (max 7), MONTH (max 12), and QUARTER (max 4). Is there a reason you chose SMALLINT over TINYINT in these cases?

Also, the SQL_DATE column is defined as a DATETIME (8 bytes) rather than a DATE (3 bytes), even though it appears that only whole dates will be used.

Changing the values to the smallest datatype necessary to hold the values changes the row width from 263 bytes to 93 bytes (by my calculations). I expect that would result in better performance because of decreased page reads. How would using the larger datatypes be a benefit?

Joey Morgan SSC-Addicted Points: 458 More actions · Answer 4

I originally learned the technique against SQL Server 2000, and never thought to improve it. Thanks for the suggestion! It's not bad for 25 years or so, but I can see it getting to be a worthwhile benefit if someone wanted to run a century and add other reporting fields.