SSAS：自定义的日期维度设计

你的业务涉及到的最低的细节级别是什么？比如按季度查看报表还是按月份，或者按周，或者再甚者 按天。这个细节级别需要弄清楚，比如在一些销售数据统计，有的时候可能更多按季度或者按月来查看 报表。但在有的监控一些机器运行数据的统计，可能会按照小时或者分钟来查看报表。

你的报表所需要时间显示的格式是什么，比如在英文系统中客户是希望显示月份的全称 January 或 者简称 Jan 或者只喜欢看到数字1，2，3 到12 这种类型。包括显示具体天的时候是希望看到 10/1/2005 还是 2005/10/01 等格式。

还有没有一些特别的时间信息比如不仅仅需要自然年，而且还需要财年信息，以及其它是否是闰年， 周末等这样的要求。

最重要的一点是客户喜欢按照哪一种或者哪几种层次结构来查看报表，比如第一层是年，通过年导航 到月再导航到日期；还是说通过年直接导航到周再到具体的日期。

弄清楚上面这几方面的内容之后，心里大概知道时间属性的范围了，细到哪一种级别，由哪些特别的 字段需要添加都在这个设计阶段完成。

一般情况下，可以自己写一个创建时间日期的数据仓库维度表，在这个脚本里面可以根据需要自定义 一些特别的日期格式。

---------------------------------------------------------------------
-- BIWORK DimDate and vDimDate Demo
-- http://www.cnblogs.com/biwork 
----------------------------------------------------------------------
USE BIWORK_SSIS
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 = '2013-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

（编辑：源码网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!