Cube中时间维度

创建DW中时间维度表DimDate

CREATE TABLE [dbo].[DimDate]( [TimeKey] int NOT NULL, [TheDate] datetime NULL, --DateTime格式的日期 [TheDateName] nvarchar(10) NULL, --日期名称 [TheYear] smallint NULL,--年份 [TheYearName] nvarchar(10)NULL,--年份名称 [TheMonth] smallint NULL,--月份 [TheMonthName] nvarchar(10)NULL,--月份名称 [TheDay] smallint NULL,--日 [TheDayName] nvarchar(10)NULL,--日的名称 [TheQuarter] smallint NULL,--季度 [TheQuarterName] nvarchar(10) NULL,--季度名称 [TheWeek] smallint NULL,--星期 [TheWeekName] nvarchar(10)NULL,--星期名称 [Vacation_Mark] smallint NULL--节假日标志)ON [PRIMARY]

向维度中添加数据：

DECLARE@TheDate datetime,@TheDateName nvarchar(10), @TheYear smallint, @TheYearName nvarchar(10),@TheMonth smallint,@TheMonthName nvarchar(10),@TheDay smallint,@TheDayName nvarchar(10),@TheQuarter smallint,@TheQuarterName nvarchar(10),@TheWeek smallint, @TheWeekName nvarchar(10),@Vacation_Mark smallint,@timeKey int,@dDate DATETIME,--存储起始日期和结束日期@adddays smallint--存储日期增量set @adddays = 1 --日期增量set @dDate = '1/1/2005'--当前日期WHILE @dDate <= '12/31/2020'--结束日期BEGIN set @TheDate = @dDate set @timeKey = cast((left(convert(nvarchar,@TheDate,23),4)+substring(convert(nvarchar,@TheDate,23),6,2)+substring(convert(nvarchar,@TheDate,23),9,2))as int) set @TheDateName = REPLACE(CONVERT(nvarchar(20),@dDate,111),'/','-') set @TheYear = DATENAME(yy, @dDate) set @TheYearName = CAST(@TheYear as nvarchar)+'年' set @TheMonth = DATENAME(mm, @dDate) set @TheMonthName =CAST(@TheMonth as nvarchar)+'月' set @TheDay = DATENAME(dd, @dDate) set @TheDayName = CAST(@TheDay as nvarchar)+'日' set @TheQuarter = DATENAME(Quarter, @dDate) set @TheQuarterName = '第' + CAST(DATENAME(Quarter, @dDate)as varchar(1))+'季度' set @TheWeek = DATEPART(dw, @dDate) set @TheWeekName = DATENAME(dw,@dDate) set @Vacation_Mark = CASE WHEN(@TheWeek = 1 OR @TheWeek = 7)THEN 1 ELSE 0 END insert INTO DimDate(TimeKey,TheDate,TheDateName,TheYear,TheYearName,TheMonth,TheMonthName,TheDay, TheDayName,TheQuarter,TheQuarterName,TheWeek,TheWeekName,Vacation_Mark)VALUES ( @timeKey,@TheDate, @TheDateName,@TheYear,@TheYearName,@TheMonth,@TheMonthName,@TheDay,@TheDayName,@TheQuarter, @TheQuarterName,@TheWeek,@TheWeekName,@Vacation_Mark) set @dDate = @dDate + @adddaysENDGO

注：数据库Microsoft SQL Server 2005，sql脚本来自网上，根据项目需要进行了修改。