SSAS中日期维度表的建立

CREATE TABLE [dbo].[dim_date](
[Datekey] [int] NOT NULL,
[FullDateAlternateKey] [date] NULL,
[Year] [int] NULL,
[Quarter] [int] NULL,
[QuarterKey] [varchar](12) NULL,
[QuarterCN] [varchar](8) NULL,
[QuarterName] [varchar](20) NULL,
[Month] [int] NULL,
[MonthKey] [varchar](6) NULL,
[MonthCN] [varchar](6) NULL,
[MonthName] [varchar](7) NULL,
[Ten] [int] NULL,
[TenCN] [varchar](4) NULL,
[Week] [int] NULL,
[WeekDay] [nvarchar](30) NULL,
[Day] [int] NULL,
CONSTRAINT [PK__Dim_Date__189A5F094B0D2BCF] 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, --主键
@BeginDate AS FullDateAlternateKey,
YEAR(@BeginDate) AS Year,--年
DATEPART(Quarter ,@BeginDate) AS Quarter,
CAST(YEAR(@BeginDate) AS VARCHAR(4))+' Qtr '+CAST(DATEPART(Quarter ,@BeginDate) AS VARCHAR(2)) AS QuarterKey,
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,
CAST(YEAR(@BeginDate) AS VARCHAR(4))+'年第'+CAST(DATEpart(Quarter ,@BeginDate) AS VARCHAR(2))+'季度' AS QuarterKey,
MONTH(@BeginDate) AS Mnoth,
CONVERT(VARCHAR(6),@BeginDate,112) AS MonthKey,
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,
CONVERT(VARCHAR(7),@BeginDate,120) AS MonthName,
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

SET @BeginDate=DATEADD(D,1,@BeginDate);
END