SSAS中日期维度表的建立

--创建Dim_Date
CREATE TABLE [dbo].[Dim_Date](
    [Datekey] [varchar](10) NOT NULL,
    [Year] [int] NULL,
    [Quarter] [int] NULL,
    [QuarterCN] [varchar](8) NULL,
    [Mnoth] [int] NULL,
    [MonthCN] [varchar](6) NULL,
    [Ten] [int] NULL,
    [TenCN] [varchar](4) NULL,
    [Week] [int] NULL,
    [WeekDay] [nvarchar](30) NULL,
    [Day] [int] NULL,
    [Date] [varchar](10) NULL,
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

--清空Dim_Date
TRUNCATE TABLE dbo.Dim_Date;
--循环插入数据
DECLARE @BeginDate DATE;
SET @BeginDate=‘2009-1-1‘;
WHILE @BeginDate<GETDATE()
BEGIN
INSERT INTO Dim_Date
    SELECT CONVERT(VARCHAR(10),@BeginDate,112) AS Datekey, --主键
    YEAR(@BeginDate) AS Year,--年
    DATEPART(Quarter ,@BeginDate) AS Quarter,
    CASE 
        WHEN DATEPART(Quarter ,@BeginDate)=1 THEN ‘第一季度‘ 
        WHEN DATEPART(Quarter,@BeginDate)=2 THEN  ‘第二季度‘
        WHEN DATEPART(Quarter ,@BeginDate)=3 THEN ‘第三季度‘ 
        WHEN DATEPART(Quarter,@BeginDate)=4 THEN  ‘第四季度‘
        END AS  QuarterCN,
        MONTH(@BeginDate) AS Mnoth,
    CASE 
        WHEN MONTH(@BeginDate)=1 THEN ‘一月‘
        WHEN MONTH(@BeginDate)=2 THEN ‘二月‘
        WHEN MONTH(@BeginDate)=3 THEN ‘三月‘
        WHEN MONTH(@BeginDate)=4 THEN ‘四月‘
        WHEN MONTH(@BeginDate)=5 THEN ‘五月‘
        WHEN MONTH(@BeginDate)=6 THEN ‘六月‘
        WHEN MONTH(@BeginDate)=7 THEN ‘七月‘
        WHEN MONTH(@BeginDate)=8 THEN ‘八月‘
        WHEN MONTH(@BeginDate)=9 THEN ‘九月‘
        WHEN MONTH(@BeginDate)=10 THEN ‘十月‘
        WHEN MONTH(@BeginDate)=11 THEN ‘十一月‘
        ELSE ‘十二月‘
    END  AS MonthCN,
    CASE 
        WHEN DAY(@BeginDate)<=10 THEN 1
        WHEN DAY(@BeginDate)>20 THEN 3
        ELSE 2
    END AS Ten,--旬
    CASE 
        WHEN DAY(@BeginDate)<=10 THEN ‘上旬‘
        WHEN DAY(@BeginDate)>20 THEN ‘下旬‘
        ELSE ‘中旬‘
    END AS TenCN,
    DATEPART(week,@BeginDate) AS Week,--周
    DATENAME(weekday,@BeginDate) AS WeekDay,--星期
    DAY(@BeginDate) AS Day,
    CONVERT(VARCHAR(10),@BeginDate,120) AS Date
    SET @BeginDate=DATEADD(D,1,@BeginDate);
END