首页 > 代码库 > 《BI项目笔记》创建时间维度（1）
《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 DimDateGOCREATE 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 DATETIMEDECLARE @EndDate DATETIMESELECT @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 ENDGO
----------------------------------------------------------------------- 加上视图的作用是因为在实际的项目开发中，SSAS 的数据源视图所有的表对象-- 应该都引用视图，这样当数据仓库中维度表或者事实表有小的改动就可以直接在-- 视图中修改，而可以避免修改 SSAS 项目。-- 这一点在 SSIS 开发中同样适用，所有在 SSIS 中配置的 SQL 语句都封装在存储-- 过程中，表封装在视图中。逻辑的修改直接体现在存储过程中，而不会修改 SSIS。----------------------------------------------------------------------IF OBJECT_ID(‘vDimDate‘, ‘V‘) IS NOT NULL DROP VIEW vDimDateGOCREATE VIEW vDimDateAS -- 可以根据需要实现一些计算列，这些计算列通常也可以在 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 1FROM DimDateGO
数据处理：
--新增CheckDate字段ALTER TABLE Tqc_Raw_Chemistry ADD CheckDate INT NULLALTER TABLE T_TIR_QualityModelAdmin ADD CheckDate INT NULLALTER TABLE T_QualMoisture_Middle ADD CheckDate INT NULL--将datetime转为intUPDATE Tqc_Raw_ChemistrySET CheckDate = CAST(REPLACE(CONVERT(CHAR(10), CheckTime, 120), ‘-‘, ‘‘) AS INT)UPDATE T_TIR_QualityModelAdminSET CheckDate = CAST(REPLACE(CONVERT(CHAR(10), F_CheckDate, 120), ‘-‘, ‘‘) AS INT)UPDATE T_QualMoisture_MiddleSET CheckDate = CAST(REPLACE(CONVERT(CHAR(10), T_Check_Date, 120), ‘-‘, ‘‘) AS INT)--删除CheckDate为NULL情况DELETE FROM Tqc_Raw_ChemistryWHERE CheckDate IS NULLDELETE FROM T_TIR_QualityModelAdminWHERE CheckDate IS NULLDELETE FROM T_QualMoisture_MiddleWHERE CheckDate IS NULL
《BI项目笔记》创建时间维度（1）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。