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 + @adddays  
    END  
    GO