原文:
《BI项目笔记》创建时间维度（1）
SSAS Date 维度基本上在所有的 Cube 设计过程中都存在，很难见到没有时间维度的 OLAP 数据库。但是根据不同的项目需求， Date 维度的设计可能不大相同，所以在设计时间维度的时候需要搞清楚几个问题：
- 你的业务涉及到的最低的细节级别是什么？比如按季度查看报表还是按月份，或者按周，或者再甚者按天。这个细节级别需要弄清楚，比如在一些销售数据统计，有的时候可能更多按季度或者按月来查看报表。但在有的监控一些机器运行数据的统计，可能会按照小时或者分钟来查看报表。
- 你的报表所需要时间显示的格式是什么，比如在英文系统中客户是希望显示月份的全称 January 或者简称 Jan 或者只喜欢看到数字1，2，3 到12 这种类型。包括显示具体天的时候是希望看到 10/1/2005 还是 2005/10/01 等格式。
- 还有没有一些特别的时间信息比如不仅仅需要自然年，而且还需要财年信息，以及其它是否是闰年，周末等这样的要求。
- 最重要的一点是客户喜欢按照哪一种或者哪几种层次结构来查看报表，比如第一层是年，通过年导航到月再导航到日期；还是说通过年直接导航到周再到具体的日期。
弄清楚上面这几方面的内容之后，心里大概知道时间属性的范围了，细到哪一种级别，由哪些特别的字段需要添加都在这个设计阶段完成。
一般情况下，可以自己写一个创建时间日期的数据仓库维度表，在这个脚本里面可以根据需要自定义一些特别的日期格式。
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
