SSAS Date 维度基本上在所有的 Cube 设计过程中都存在，很难见到没有时间维度的 OLAP 数据库。但是根据不同的项目需求， Date 维度的设计可能不大相同，所以在设计时间维度的时候需要搞清楚几个问题：
GO
SET NOCOUNT ON
IF OBJECT_ID('DimDate', 'U') IS NOT NULL
DROP TABLE DimDate
GO
CREATE TABLE DimDate
(
DateKey INT PRIMARY KEY ,
FullDate DATE NOT NULL ,
[DateName] NVARCHAR(20) ,
DayNumberOfWeek TINYINT NOT NULL ,
DayNameOfWeek NVARCHAR(10) NOT NULL ,
DayNumberOfMonth TINYINT NOT NULL ,
DayNumberOfYear SMALLINT NOT NULL ,
IsWeekend BIT NOT NULL ,
IsLeapYear BIT NOT NULL ,
WeekNumberOfYear TINYINT NOT NULL ,
EnglishMonthName NVARCHAR(10) NOT NULL ,
MonthNumberOfYear TINYINT NOT NULL ,
CalendarQuarter TINYINT NOT NULL ,
CalendarSemester TINYINT NOT NULL ,
CalendarYear SMALLINT NOT NULL ,
FiscalQuarter TINYINT NOT NULL ,
FiscalSemester TINYINT NOT NULL ,
FiscalYear SMALLINT NOT NULL
)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SELECT @StartDate = '2005-01-01' ,
@EndDate = '2014-12-31'
WHILE ( @StartDate <= @EndDate )
BEGIN
INSERT INTO DimDate
( DateKey ,
FullDate ,
[DateName] ,
DayNumberOfWeek ,
DayNameOfWeek ,
DayNumberOfMonth ,
DayNumberOfYear ,
IsWeekend ,
IsLeapYear ,
WeekNumberOfYear ,
EnglishMonthName ,
MonthNumberOfYear ,
CalendarQuarter ,
CalendarSemester ,
CalendarYear ,
FiscalQuarter ,
FiscalSemester ,
FiscalYear
)
SELECT CAST(CONVERT(VARCHAR(8), @StartDate, 112) AS INT) AS DateKey ,
CONVERT(VARCHAR(10), @StartDate, 20) AS FullDate ,
CONVERT(VARCHAR(20), @StartDate, 106) AS [DateName] ,
DATEPART(DW, @StartDate) AS DayNumberOfWeek ,
DATENAME(DW, @StartDate) AS DayNameOfWeek ,
DATENAME(DD, @StartDate) AS [DayOfMonth] ,
DATENAME(DY, @StartDate) AS [DayOfYear] ,
CASE WHEN DATEPART(DW, @StartDate) IN ( 1, 7 ) THEN 1
ELSE 0
END AS IsWeekend ,
CASE WHEN ( ( YEAR(@StartDate) % 4 = 0 )
AND ( YEAR(@StartDate) % 100 != 0
OR YEAR(@StartDate) % 400 = 0
)
) THEN 1
ELSE 0
END AS IsLeapYear ,
DATEPART(WW, @StartDate) AS WeekNumberOfYear ,
DATENAME(MM, @StartDate) AS EnglishMonthName ,
DATEPART(MM, @StartDate) AS MonthNumberOfYear ,
DATEPART(QQ, @StartDate) AS CalendarQuarter ,
CASE WHEN DATEPART(MM, @StartDate) BETWEEN 1 AND 6
THEN 1
ELSE 2
END AS CalendarSemester ,
DATEPART(YY, @StartDate) AS CalendarYear ,
CASE WHEN DATEPART(MM, @StartDate) BETWEEN 1 AND 6
THEN DATEPART(QQ, @StartDate) + 2
ELSE DATEPART(QQ, @StartDate) - 2
END AS FiscalQuarter ,
CASE WHEN DATEPART(MM, @StartDate) BETWEEN 1 AND 6
THEN 2
ELSE 1
END AS FiscalSemester ,
CASE WHEN DATEPART(MM, @StartDate) BETWEEN 1 AND 6
THEN DATEPART(YY, @StartDate)
ELSE DATEPART(YY, @StartDate) + 1
END AS FiscalYear
SET @StartDate = @StartDate + 1
END
GO
---------------------------------------------------------------------
-- 加上视图的作用是因为在实际的项目开发中，SSAS 的数据源视图所有的表对象
-- 应该都引用视图，这样当数据仓库中维度表或者事实表有小的改动就可以直接在
-- 视图中修改，而可以避免修改 SSAS 项目。
-- 这一点在 SSIS 开发中同样适用，所有在 SSIS 中配置的 SQL 语句都封装在存储
-- 过程中，表封装在视图中。逻辑的修改直接体现在存储过程中，而不会修改 SSIS。
----------------------------------------------------------------------
IF OBJECT_ID('vDimDate', 'V') IS NOT NULL
DROP VIEW vDimDate
GO
CREATE VIEW vDimDate
AS
-- 可以根据需要实现一些计算列，这些计算列通常也可以在 SSAS 视图中添加。
SELECT DateKey AS 'DateKey' ,
FullDate AS 'FullDate' ,
[DateName] AS 'DateName' ,
CONVERT(VARCHAR(2), DayNumberOfMonth) + ' ' + EnglishMonthName + ' '
+ CONVERT(CHAR(4), CalendarYear) AS 'FullDateName' , -- 1 July 2005
DayNumberOfWeek AS 'DayNumberOfWeek' ,
DayNameOfWeek AS 'DayNameOfWeek' ,
DayNumberOfMonth AS 'DayNumberOfMonth' ,
DayNumberOfYear AS 'DayNumberOfYear' ,
CASE WHEN IsWeekend = 1 THEN 'Weekend'
ELSE 'Weekday'
END AS 'WeekdayWeekend' ,
IsLeapYear AS 'IsLeapYear' ,
WeekNumberOfYear AS 'WeekNumberOfYear' ,
EnglishMonthName AS 'EnglishMonthName' ,
EnglishMonthName + ' ' + CONVERT(CHAR(4), CalendarYear) AS 'MonthName' , -- July 2005
CalendarYear * 100 + MonthNumberOfYear AS 'MonthKey' , -- 200507
MonthNumberOfYear AS 'MonthNumberOfYear' ,
CalendarQuarter AS 'CalendarQuarter' ,
CalendarSemester AS 'CalendarSemester' ,
CalendarYear AS 'CalendarYear' ,
CalendarYear * 100 + CalendarQuarter AS 'CalendarQuarterKey' , -- 200503
'CY ' + CONVERT(CHAR(4), CalendarYear) AS 'CalendarYearName' , -- CY 2005
'CY ' + CONVERT(CHAR(4), CalendarYear) + ' Qtr '
+ CONVERT(CHAR(1), CalendarQuarter) AS 'CalendarQuarterName' , -- CY 2005 Qtr 3
FiscalQuarter AS 'FiscalQuarter' ,
FiscalSemester AS 'FiscalSemester' ,
FiscalYear AS 'FiscalYear' ,
FiscalYear * 100 + FiscalQuarter AS 'FiscalQuarterKey' , -- 200601
'FY ' + CONVERT(CHAR(4), FiscalYear) AS 'FiscalYearName' , -- FY 2006
'FY ' + CONVERT(CHAR(4), FiscalYear) + ' Qtr '
+ CONVERT(CHAR(1), FiscalQuarter) AS 'FiscalQuarterName' -- FY 2006 Qtr 1
FROM DimDate
GO
--新增CheckDate字段
ALTER TABLE Tqc_Raw_Chemistry ADD CheckDate INT NULL
ALTER TABLE T_TIR_QualityModelAdmin ADD CheckDate INT NULL
ALTER TABLE T_QualMoisture_Middle ADD CheckDate INT NULL
--将datetime转为int
UPDATE Tqc_Raw_Chemistry
SET CheckDate = CAST(REPLACE(CONVERT(CHAR(10), CheckTime, 120), '-', '') AS INT)
UPDATE T_TIR_QualityModelAdmin
SET CheckDate = CAST(REPLACE(CONVERT(CHAR(10), F_CheckDate, 120), '-', '') AS INT)
UPDATE T_QualMoisture_Middle
SET CheckDate = CAST(REPLACE(CONVERT(CHAR(10), T_Check_Date, 120), '-', '') AS INT)
--删除CheckDate为NULL情况
DELETE FROM Tqc_Raw_Chemistry
WHERE CheckDate IS NULL
DELETE FROM T_TIR_QualityModelAdmin
WHERE CheckDate IS NULL
DELETE FROM T_QualMoisture_Middle
WHERE CheckDate IS NULL
