DimDate OLAP中Date维度表结构的建议及表数据的生成

CREATE TABLE [dbo].[DimDate](
 [DateKey] [int] NOT NULL,
 [FullDateAlternateKey] [date] NOT NULL,
 [DayNumberOfWeek] [tinyint] NOT NULL,
 [EnglishDayNameOfWeek] [varchar](50) NOT NULL,
 [ChineseDayNameOfWeek] [varchar](50) NOT NULL,
 [DayNumberOfMonth] [tinyint] NOT NULL,
 [DayNumberOfYear] [smallint] NOT NULL,
 [WeekNumberOfYear] [tinyint] NOT NULL,
 [EnglishMonthName] [varchar](50) NOT NULL,
 [ChineseMonthName] [varchar](50) NOT NULL,
 [MonthNumberOfYear] [tinyint] NOT NULL,
 [CalendarQuarter] [tinyint] NOT NULL,
 [CalendarYear] [smallint] NOT NULL,
 [CalendarSemester] [tinyint] NOT NULL,
 CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED
(
 [DateKey] 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

SET ANSI_PADDING OFF
GO

truncate table dimDate
go
--生成一年的时期维护数据,生成从2010年开始到2041年的所有日期维度
declare @curDate datetime
declare @endDate datetime
set @curDate='2010-01-01 00:00:00'
set @endDate='2041-12-31 00:00:00'
WHILE (@curDate <= @endDate)
BEGIN
 Insert into dimDate(   
  DateKey,
 FullDateAlternateKey,
 DayNumberOfWeek,
 EnglishDayNameOfWeek,
 ChineseDayNameOfWeek,
 DayNumberOfMonth,
 DayNumberOfYear,
 WeekNumberOfYear,
 EnglishMonthName,
 ChineseMonthName,
 MonthNumberOfYear,
 CalendarQuarter,
 CalendarYear,
 CalendarSemester
 )
 SELECT
 Convert(varchar(8),@curDate,112) as DateKey,
 Convert(varchar(10),@curDate,120) as FullDateAlternateKey,
 datepart(weekday,@curDate) as DayNumberOfWeek,
(
  case   datepart(dw,@curDate)
  when   1   then   'Sunday'
  when   2   then   'Monday'
  when   3   then   'Tuesday'
  when   4   then   'Wednesday'
  when   5   then   'Thursday'
  when   6   then   'Friday'
  when   7   then   'Saturday'
  end
) as EnglishDayNameOfWeek,
(
  case   datepart(dw,@curDate)
  when   1   then   '星期日'
  when   2   then   '星期一'
  when   3   then   '星期二'
  when   4   then   '星期三'
  when   5   then   '星期四'
  when   6   then   '星期五'
  when   7   then   '星期六'
  end
) as ChineseDayNameOfWeek,
 datepart(day,@curDate) as DayNumberOfMonth,
 datepart(dayofyear,@curDate) as DayNumberOfYear,
 datepart(week, @curDate) as WeekNumberOfYear,
(
  case   datepart(MONTH,@curDate)
  when   1   then   'January'
  when   2   then   'February'
  when   3   then   'March'
  when   4   then   'April'
  when   5   then   'May'
  when   6   then   'June'
  when   7   then   'July'
  when   8   then   'August'
  when   9   then   'September'
  when   10   then  'October'
  when   11   then  'November'
  when   12  then   'December'
  end
)   as EnglishMonthName,
 (
  case   datepart(MONTH,@curDate)
  when   1   then   '一月'
  when   2   then   '二月'
  when   3   then   '三月'
  when   4   then   '四月'
  when   5   then   '五月'
  when   6   then   '六月'
  when   7   then   '七月'
  when   8   then   '八月'
  when   9   then   '九月'
  when   10   then  '十月'
  when   11   then  '十一月'
  when   12  then   '十二月'
  end
)  as ChineseMonthName,
 datepart(MONTH,@curDate) as MonthNumberOfYear,
 datepart(Quarter,@curDate) as CalendarQuarter,
 datepart(year,@curDate) as CalendarYear,
 (case when datepart(MONTH,@curDate)>6 then 1 else 2 end) as CalendarSemester
 set @curDate=@curDate+1
END

 

 --生成一年的时期维护数据,生成从2010年开始到2041年的所有日期维度(带小时的)

declare @curDate datetime
declare @endDate datetime
set @curDate='2010-01-01 00:00:00'
set @endDate='2041-12-31 00:00:00'
WHILE (@curDate <= @endDate)
BEGIN
 Insert into dimDate(   
  DateKey,
 FullDateAlternateKey,
 HourNumberOfDay,
 DayNumberOfWeek,
 EnglishDayNameOfWeek,
 ChineseDayNameOfWeek,
 DayNumberOfMonth,
 DayNumberOfYear,
 WeekNumberOfYear,
 EnglishMonthName,
 ChineseMonthName,
 MonthNumberOfYear,
 CalendarQuarter,
 CalendarYear,
 CalendarSemester
 )
 SELECT
 REPLACE(REPLACE(Convert(varchar(13),@curDate,120),'-',''),' ','') as DateKey,
 @curDate as FullDateAlternateKey,
 datepart(hour,@curDate) as HourNumberOfDay,
 datepart(weekday,@curDate) as DayNumberOfWeek,
(
  case   datepart(dw,@curDate)
  when   1   then   'Sunday'
  when   2   then   'Monday'
  when   3   then   'Tuesday'
  when   4   then   'Wednesday'
  when   5   then   'Thursday'
  when   6   then   'Friday'
  when   7   then   'Saturday'
  end
) as EnglishDayNameOfWeek,
(
  case   datepart(dw,@curDate)
  when   1   then   '星期日'
  when   2   then   '星期一'
  when   3   then   '星期二'
  when   4   then   '星期三'
  when   5   then   '星期四'
  when   6   then   '星期五'
  when   7   then   '星期六'
  end
) as ChineseDayNameOfWeek,
 datepart(day,@curDate) as DayNumberOfMonth,
 datepart(dayofyear,@curDate) as DayNumberOfYear,
 datepart(week, @curDate) as WeekNumberOfYear,
(
  case   datepart(MONTH,@curDate)
  when   1   then   'January'
  when   2   then   'February'
  when   3   then   'March'
  when   4   then   'April'
  when   5   then   'May'
  when   6   then   'June'
  when   7   then   'July'
  when   8   then   'August'
  when   9   then   'September'
  when   10   then  'October'
  when   11   then  'November'
  when   12  then   'December'
  end
)   as EnglishMonthName,
 (
  case   datepart(MONTH,@curDate)
  when   1   then   '一月'
  when   2   then   '二月'
  when   3   then   '三月'
  when   4   then   '四月'
  when   5   then   '五月'
  when   6   then   '六月'
  when   7   then   '七月'
  when   8   then   '八月'
  when   9   then   '九月'
  when   10   then  '十月'
  when   11   then  '十一月'
  when   12  then   '十二月'
  end
)  as ChineseMonthName,
 datepart(MONTH,@curDate) as MonthNumberOfYear,
 datepart(Quarter,@curDate) as CalendarQuarter,
 datepart(year,@curDate) as CalendarYear,
 (case when datepart(MONTH,@curDate)>6 then 1 else 2 end) as CalendarSemester
 set @curDate=DATEADD(HOUR,1, @curDate)
END