OK, I've found myself searching for this code a half dozen times over as many weeks. That tells me that I :
a) Need to keep better track of my source code
b) Need to share it on my blog
Caveats & Comments:
- This is "warts and all" utility code. It could be optimized, but for the few rows that a Date Dimension represents, why?
- One of the few cases where Dr. Kimball and his recommends meaningful surrogate keys is in the case of the date dimension. Our keys here use a YYYYMMDD or YYYYQQ format.
- This builds a Date Snowflake, which provides correctly ordered keys at any level in the dimension.
- I usually rejoin these tables in either the DSV or a SQL View to create the structure SSAS actually consumes.
- There is a stub for a Week Hierarchy, which sometimes is called for, and sometimes not.
- This was built in and is compatible with SQL 2005. It runs in SQL 2008 as well. In 2008, you could change the date type from DateTime to Date and save yourself some formatting overhead down the road.
/* DDL Begin */
/****** Object: Table [dbo].[dim_CalendarYear] Script Date: 11/02/2009 12:34:56 ******/
CREATE TABLE [dbo].[dim_CalendarYear](
[CalendarYearID] [int] NOT NULL,
[CalendarYearName] [varchar](10) NULL,
CONSTRAINT [PK_dim_CalendarYear] PRIMARY KEY CLUSTERED
(
[CalendarYearID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[dim_CalendarQuarter] Script Date: 11/02/2009 12:34:56 ******/
CREATE TABLE [dbo].[dim_CalendarQuarter](
[CalendarQuarterID] [int] NOT NULL,
[CalendarQuarterNumber] [int] NULL,
[CalendarQuarterName] [varchar](10) NULL,
[CalendarYearID] [int] NULL,
CONSTRAINT [PK_dim_CalendarQuarter] PRIMARY KEY CLUSTERED
(
[CalendarQuarterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[dim_CalendarMonth] Script Date: 11/02/2009 12:34:56 ******/
CREATE TABLE [dbo].[dim_CalendarMonth](
[CalendarMonthID] [int] NOT NULL,
[CalendarMonthNumber] [int] NULL,
[CalendarMonthName] [varchar](20) NULL,
[CalendarQuarterID] [int] NULL,
CONSTRAINT [PK_dim_CalendarMonth] PRIMARY KEY CLUSTERED
(
[CalendarMonthID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[dim_CalendarDay] Script Date: 11/02/2009 12:34:56 ******/
CREATE TABLE [dbo].[dim_CalendarDay](
[TimeID] [int] NOT NULL,
[SQLDate] [datetime] NULL,
[CalendarMonthID] [int] NULL,
[WeekID] [int] NULL,
CONSTRAINT [PK_dim_Date] PRIMARY KEY CLUSTERED
(
[TimeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/* DDL End */
/* Populate Begin */
DECLARE @StartSeedDate DATETIME
DECLARE @EndSeedDate DATETIME
SET @StartSeedDate = '01 Jan 2007'
SET @EndSeedDate = '31 Dec 2009'
-- Set defaults if none are supplied
IF @StartSeedDate IS NULL
SET @StartSeedDate = DATEADD(YYYY,-1,CONVERT(CHAR,GETDATE(),112))
IF @EndSeedDate IS NULL
SET @EndSeedDate = DATEADD(YYYY,1,CONVERT(CHAR,GETDATE(),112))
CREATE TABLE #TMP_DIMDATE (
SQLDATE DATETIME NULL)
WHILE @StartSeedDate <= @EndSeedDate
BEGIN
INSERT #TMP_DIMDATE
(SQLDATE)
VALUES(@StartSeedDate)
SELECT @StartSeedDate = DATEADD(DD,1,@StartSeedDate)
END
-- Year
INSERT dbo.dim_CalendarYear
SELECT DISTINCT
DATEPART(YEAR,SQLDATE) AS [CalendarYearID]
, DATENAME(YEAR,SQLDATE) AS [CalendarYearName]
FROM #TMP_DIMDATE
-- SELECT * FROM dbo.dim_CalendarYear
-- Quarter
INSERT dbo.dim_CalendarQuarter
SELECT DISTINCT
DATEPART(YYYY,SQLDATE) * 100 + DATEPART(QUARTER,SQLDATE) AS [CalendarQuarterID]
, DATEPART(YYYY,SQLDATE) * 100 + DATEPART(QUARTER,SQLDATE) AS [CalendarQuarterNumber]
, CAST(DATEPART(YYYY,SQLDATE) AS char(4)) + ' Q' + CAST(DATEPART(QUARTER,SQLDATE) AS char(1)) AS [CalendarQuarterName]
, DATEPART(YEAR,SQLDATE) AS [CalendarYearID]
FROM #TMP_DIMDATE
-- SELECT * FROM dbo.dim_CalendarQuarter
-- Month
INSERT dbo.dim_CalendarMonth
SELECT DISTINCT
DATEPART(YYYY,SQLDATE) * 100 + DATEPART(MONTH,SQLDATE) AS [CalendarMonthID]
, DATEPART(YYYY,SQLDATE) * 100 + DATEPART(MONTH,SQLDATE) AS [CalendarMonthNumber]
, DATENAME(YYYY,SQLDATE) + ' ' + DATENAME(MM,SQLDATE) AS [CalendarMonthName]
, DATEPART(YYYY,SQLDATE) * 100 + DATEPART(QUARTER,SQLDATE) AS [CalendarQuarterID]
FROM #TMP_DIMDATE
-- SELECT * FROM dbo.dim_CalendarMonth
-- Day
INSERT dbo.dim_CalendarDay
SELECT DISTINCT
DATEPART(YYYY,SQLDATE) * 10000 + DATEPART(MONTH,SQLDATE) * 100 + DATEPART(DAY,SQLDATE) AS [TimeID]
, SQLDATE
, DATEPART(YYYY,SQLDATE) * 100 + DATEPART(MONTH,SQLDATE) AS [CalendarMonthID]
, NULL as [ShopLocalMonthID]
FROM #TMP_DIMDATE
-- SELECT * FROM dbo.dim_CalendarDay
/* Populate End */
