微软BI 之SSAS 系列 - 自定义的日期维度设计 - BIWORK

微软BI 之SSAS 系列 - 自定义的日期维度设计

2013-12-22 18:34 BIWORK 阅读(7912) 评论(14) 编辑收藏举报

SSAS Date 维度基本上在所有的 Cube 设计过程中都存在，很难见到没有时间维度的 OLAP 数据库。但是根据不同的项目需求， Date 维度的设计可能不大相同，所以在设计时间维度的时候需要搞清楚几个问题：

你的业务涉及到的最低的细节级别是什么？比如按季度查看报表还是按月份，或者按周，或者再甚者按天。这个细节级别需要弄清楚，比如在一些销售数据统计，有的时候可能更多按季度或者按月来查看报表。但在有的监控一些机器运行数据的统计，可能会按照小时或者分钟来查看报表。
你的报表所需要时间显示的格式是什么，比如在英文系统中客户是希望显示月份的全称 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

在我的这个示例中，财年是以微软的财年为例子的。比如2008年7月是自然月，但是财年就被称为 2009财年的1月，它是从每年的7月开始算的。下面的这幅图主要是展示了各个字段上时间日期的格式是以及字段类型等。并且在后面设计维度的时候，我们往往选择 Key 会考虑使用整形数据字段，但是在 Name Column 的时候就会使用具体的描述内容。 Attribute Type 后面也能看得到，它的主要作用是为具体的时间日期字段指定日期属性。日期属性的指定能够让 SSAS Cube 在内部聚合的时候知道某个字段的含义，这个字段是描述日期，还是年还是月。在 MDX 的时间相关的层级导航或者查询中，比如说 YTD() 函数的使用就跟设置属性为 Year 相关的维度属性相关，可以参考我的这篇 MDX笔记。

新建一个 SSAS 项目并创建好数据源和数据源视图，数据源视图中就是上面创建的视图。

创建一个简单的时间维度，选择 vDimDate，并在 Key Column 中选择 DateKey, Name Column 选择 FullDateName。实际上，这里就一个属性，但是这个属性是由两部分组成的，一个是 KEY 一个是 NAME。

然后选择其它的相应的属性，这里面基本上都是选择的数值类型的属性，因为我们一会还要修改它们，为他们配置相应的 Name Column - 提供信息标签。我们同时为这些属性选择好相应的 Attribute Type，如下图所示。

修改维度名称 Date ，那么保存后就可以看到一个维度和它的维度属性了。

Date 维度它的类型会自动设置为 Time 的，不是 Time 类型的维度在 MDX 的查询中有很多时间函数可能就无法使用了。并且在 SSAS Cube 的处理过程中，就不会把这个维度当作特殊的时间维度去考虑，因此这里会自动设置为 Time 类型。

这样的结构就是一个维度和它下面的维度属性，如果仅这样部署到 SSAS 分析服务中，我们将看到的是一堆数字 KEY 表示的信息，那么这些数据就失去了"信息"的意义。

因此我们需要按照这个图来修改每一个属性，为它们指定相应的 NAME Column，这样相当于为这个数字添上了一个标签。

如上图所示，下图中的 CalendarYear 这个属性，它的 Key Column 就是 CalendarYear ，它的 Attribute Type 是 Year, 它的 Name Column 是 Calendar Year Name。

按照上面的配置修改完维度属性之后，也将名字改的简单一些。

部署之后可以看到每一个维度属性的 Name Column 展示出来的信息了，并且注意在 SSAS 中有这样的一个概念 - 维度中的属性实际上指的属性层次结构，每一个属性层次结构都包含两层。第一层是以 ALL 为代表的成员，第二层是以各个属性值表示的成员。ALL 表示的就是对下面所有属性的一个聚合，在和度量值结合起来看就会很容易理解的。

维度其实就是属性和层次结构组成的，但是除了上面的属性层次结构之外，还包括下面的用户自定义层次结构。那么这种主要是根据用户的需求来决定的，比如用户通常会根据年来聚合，或者再细看季度方面的数据，然后再是月或者天。因此下面创建两个日期自定义层次结构，一个是自然年度的，一个是财年年度的层次结构。

默认情况下，各个属性是和维度主 KEY 关联的，那么这样在层次结构关系中可能每次的上下次层次聚合都需要通过 Date Key 来进行关联，比如说不能通过 Month 来直接找到季度方面的成员，也不能通过年来找到具体季度的成员，因此需要对属性之间的关系做出一定的调整，提高 SSAS 处理属性聚合时的效率。