微软BI 之SSAS 系列 - 实现Cube 以及角色扮演维度，度量值格式化和计算成员的创建

在熟悉完下面这三种维度的创建方式之后，就可以开始创建我们的第一个 Cube 了。

SSAS 系列 - 自定义的日期维度设计

SSAS 系列 - 基于雪花模型的维度设计

SSAS系列 - 关于父子维度的设计

我们将使用下面的这些脚本来创建一些维度表和事实表，数据源的来源是 AdventureWorksDW2012，但由于数据列太多因此我精简了一些表并且自定义了 DimDate 表。

USE BIWORK_SSIS
GO
SET NOCOUNT ON
IF OBJECT_ID('FactInternetSales','U') IS NOT NULL
DROP TABLE FactInternetSales

IF OBJECT_ID('FactResellerSales','U') IS NOT NULL
DROP TABLE FactResellerSales

IF OBJECT_ID('DimEmployee','U') IS NOT NULL
DROP TABLE DimEmployee

IF OBJECT_ID('DimDate','U') IS NOT NULL
DROP TABLE DimDate 

IF OBJECT_ID('DimProduct','U') IS NOT NULL
DROP TABLE DimProduct 

IF OBJECT_ID('DimProductSubcategory','U') IS NOT NULL
DROP TABLE DimProductSubcategory

IF OBJECT_ID('DimProductCategory','U') IS NOT NULL
DROP TABLE DimProductCategory
GO

CREATE TABLE DimDate
(
    DateKey INT PRIMARY KEY,
    ShortDateName NVARCHAR(12) NOT NULL,
    FullDateName NVARCHAR(20)  NOT NULL,
    DayNumberOfWeek TINYINT NOT NULL,
    DayNameOfWeek NVARCHAR(10) NOT NULL,
    DayNumberOfMonth TINYINT NOT NULL,
    DayNumberOfYear SMALLINT NOT NULL,
    WeekNumberOfYear TINYINT NOT NULL,
    IsWeekend NVARCHAR(7) NOT NULL,
    IsLeapYear BIT NOT NULL,
    MonthKey INT NOT NULL,
    MonthNumberOfYear TINYINT NOT NULL,
    MonthNameOfYear NVARCHAR(10) NOT NULL,
    MonthNameWithYear NVARCHAR(20) NOT NULL,
    CalendarQuarterKey INT NOT NULL,
    CalendarQuarterNumber TINYINT NOT NULL,
    CalendarQuarterNameWithYear NVARCHAR(20) NOT NULL,
    CalendarSemesterNumber TINYINT NOT NULL,
    CalendarYearKey SMALLINT NOT NULL,
    CalendarYearName NVARCHAR(20) NOT NULL,
    FiscalQuarterKey INT,
    FiscalQuarterNumber TINYINT NOT NULL,
    FiscalQuarterName NVARCHAR(20),
    FiscalSemester TINYINT NOT NULL,
    FiscalYearKey SMALLINT NOT NULL,
    FiscalYearName NVARCHAR(20),
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT @StartDate = '2005-01-01',
       @EndDate = '2013-12-31'

WHILE (@StartDate <= @EndDate)
BEGIN
    INSERT INTO DimDate
    (
        DateKey,
        ShortDateName,
        FullDateName,
        DayNumberOfWeek,
        DayNameOfWeek,
        DayNumberOfMonth,
        DayNumberOfYear,
        WeekNumberOfYear,
        IsWeekend,
        IsLeapYear,
        MonthKey,
        MonthNumberOfYear,
        MonthNameOfYear,
        MonthNameWithYear,
        CalendarQuarterKey,
        CalendarQuarterNumber,
        CalendarQuarterNameWithYear,
        CalendarSemesterNumber,
        CalendarYearKey,
        CalendarYearName,
        FiscalQuarterNumber,
        FiscalSemester,
        FiscalYearKey
    )
    SELECT CAST(CONVERT(VARCHAR(8),@StartDate,112) AS INT) AS 'DateKey',
           CONVERT(VARCHAR(20), @StartDate,106) AS 'ShortDateName',
           CONVERT(VARCHAR(2),DATENAME(DD,@StartDate))
                    + ' '
                    + DATENAME(MM,@StartDate)
                    + ' '
                    + CONVERT(CHAR(4), DATEPART(YY,@StartDate)) AS 'FullDateName', -- 1 July 2005
           DATEPART(DW,@StartDate) AS 'DayNumberOfWeek',
           DATENAME(DW,@StartDate) AS 'DayNameOfWeek',
           DATENAME(DD,@StartDate) AS 'DayNumberOfMonth',
           DATENAME(DY,@StartDate) AS 'DayNumberOfYear',
           DATEPART(WW,@StartDate) AS 'WeekNumberOfYear',
           CASE WHEN DATEPART(DW,@StartDate) IN (1,7)
                    THEN 'Weekend'
                ELSE 'Weekday'
           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(YY,@StartDate) * 100 + DATEPART(MM,@StartDate) AS 'MonthKey', --
           DATEPART(MM,@StartDate) AS 'MonthNumberOfYear',
           DATENAME(MM,@StartDate) AS 'MonthNameOfYear',
           DATENAME(MM,@StartDate) + ' ' + CONVERT(CHAR(4),DATEPART(YY,@StartDate)) AS 'MonthNameWithYear',  -- July 2005
           DATEPART(YY,@StartDate) * 100 + DATEPART(QQ,@StartDate) AS 'CalendarQuarterKey',  --
           DATEPART(QQ,@StartDate) AS 'CalendarQuarterNumber',
           'CY ' + CONVERT(CHAR(4),DATEPART(YY,@StartDate))
                 + ' Qtr '
                 + CONVERT(CHAR(1), DATEPART(QQ,@StartDate)) AS 'CalendarQuarterNameWithYear', -- CY 2005 Qtr 3
           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
                    THEN 1
                ELSE 2
           END AS 'CalendarSemester',
           DATEPART(YY,@StartDate) AS 'CalendarYearKey',
           'CY ' + CONVERT(CHAR(4),DATEPART(YY,@StartDate)) AS 'CalendarYearName', -- CY 2005
           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' 

           UPDATE DimDate
           SET FiscalQuarterKey = FiscalYearKey * 100 + FiscalQuarterNumber,  --
               FiscalYearName = 'FY ' + CONVERT(CHAR(4), FiscalYearKey),   -- FY 2006
               FiscalQuarterName =  'FY ' + CONVERT(Char(4), FiscalYearKey) + ' Qtr ' + CONVERT(CHAR(1), FiscalQuarterNumber) -- FY 2006 Qtr 1
           WHERE DateKey = CONVERT(INT,CONVERT(VARCHAR(8),@StartDate,112))

    SET @StartDate = @StartDate + 1
END

SELECT EmployeeKey,
       ParentEmployeeKey,
       EmployeeNationalIDAlternateKey,
       CASE WHEN ISNULL(MiddleName,'') = ''  THEN FirstName +' '+ LastName
            ELSE FirstName +' '+ MiddleName +' '+LastName
       END AS FullName,
       Title
INTO DimEmployee
FROM AdventureWorksDW2012.dbo.DimEmployee

SELECT ProductKey,
       ProductAlternateKey,
       ProductSubcategoryKey,
       EnglishProductName,
       StandardCost,
       Color,
       SafetyStockLevel,
       ListPrice,
       Class,
       Size,
       StartDate,
       EndDate,
       [Status],
       ProductAlternateKey + ' (' + CONVERT (Char(10), StartDate, 120) + ')' AS ProductID
INTO DimProduct
FROM AdventureWorksDW2012.dbo.DimProduct

SELECT ProductSubcategoryKey,
       ProductSubcategoryAlternateKey,
       EnglishProductSubcategoryName,
       ProductCategoryKey
INTO DimProductSubcategory
FROM AdventureWorksDW2012.dbo.DimProductSubcategory

SELECT ProductCategoryKey,
       ProductCategoryAlternateKey,
       EnglishProductCategoryName
INTO DimProductCategory
FROM AdventureWorksDW2012.dbo.DimProductCategory 

SELECT ProductKey,
       OrderDateKey,
       EmployeeKey,
       SalesOrderLineNumber,
       SalesOrderNumber,
       UnitPrice,
       ProductStandardCost,
       SalesAmount
INTO FactResellerSales
FROM AdventureWorksDW2012.dbo.FactResellerSales

SELECT ProductKey,
       OrderDateKey,
       DueDateKey,
       ShipDateKey,
       SalesOrderNumber,
       SalesOrderLineNumber,
       OrderQuantity,
       UnitPrice,
       SalesAmount
INTO FactInternetSales
FROM AdventureWorksDW2012.dbo.FactInternetSales

------------------------------------------------------------------------------
-- Add Primary Key Constraint
------------------------------------------------------------------------------
ALTER TABLE DimEmployee
ADD CONSTRAINT PK_EmployeeKey PRIMARY KEY CLUSTERED (EmployeeKey)

ALTER TABLE DimProduct
ADD CONSTRAINT PK_ProductKey PRIMARY KEY CLUSTERED(ProductKey)

ALTER TABLE DimProductSubcategory
ADD CONSTRAINT PK_SubcategoryKey PRIMARY KEY CLUSTERED(ProductSubcategoryKey)

ALTER TABLE DimProductCategory
ADD CONSTRAINT PK_CategoryKey PRIMARY KEY CLUSTERED(ProductCategoryKey)

ALTER TABLE FactInternetSales
ADD CONSTRAINT PK_InternetSales PRIMARY KEY CLUSTERED(SalesOrderNumber,SalesOrderLineNumber)

ALTER TABLE FactResellerSales
ADD CONSTRAINT PK_ResellerSales PRIMARY KEY CLUSTERED(SalesOrderNumber,SalesOrderLineNumber)

------------------------------------------------------------------------------
-- Add Primary Key Constraint
------------------------------------------------------------------------------
ALTER TABLE FactResellerSales
ADD CONSTRAINT FK_Reseller_EmployeeKey FOREIGN KEY(EmployeeKey) REFERENCES DimEmployee(EmployeeKey)

ALTER TABLE FactResellerSales
ADD CONSTRAINT FK_Reseller_ProductKey FOREIGN KEY(ProductKey) REFERENCES DimProduct(ProductKey)

ALTER TABLE FactResellerSales
ADD CONSTRAINT FK_Reseller_OrderDateKey FOREIGN KEY(OrderDateKey) REFERENCES DimDate(DateKey) 

ALTER TABLE FactInternetSales
ADD CONSTRAINT FK_Internet_ProductKey FOREIGN KEY(ProductKey) REFERENCES DimProduct(ProductKey)

ALTER TABLE FactInternetSales
ADD CONSTRAINT FK_Internet_OrderDateKey FOREIGN KEY(OrderDateKey) REFERENCES DimDate(DateKey)

ALTER TABLE FactInternetSales
ADD CONSTRAINT FK_Internet_ShipDateKey FOREIGN KEY(ShipDateKey) REFERENCES DimDate(DateKey)

ALTER TABLE FactInternetSales
ADD CONSTRAINT FK_Internet_DueDateKey FOREIGN KEY(DueDateKey) REFERENCES DimDate(DateKey) 

ALTER TABLE DimProduct
ADD CONSTRAINT FK_Product_SubcatetoryKey FOREIGN KEY (ProductSubcategoryKey) REFERENCES DimProductSubcategory (ProductSubcategoryKey)

ALTER TABLE DimProductSubcategory
ADD CONSTRAINT FK_Subcategory_CatetoryKey FOREIGN KEY (ProductCategoryKey) REFERENCES DimProductCategory (ProductCategoryKey)

SELECT * FROM DimEmployee
SELECT * FROM DimDate
SELECT * FROM DimProduct
SELECT * FROM DimProductSubcategory
SELECT * FROM DimProductCategory
SELECT * FROM FactResellerSales
SELECT * FROM FactInternetSales

下面可以看到维度表和事实表，其中包括了常用的时间维度，父子维度和雪花型维度的概念。另外要注意到 FactInternetSales 表中的 OrderDateKey, DueDateKey, ShipDateKey 都关联到了 DimDate 的主键 DateKey。 在后面有一个概念 Role-Playing Dimension 角色扮演维度就和这里的几个时间列有关。

创建数据源，数据源视图以及时间维度，父子维度，标准维度等过程就不再重复了，可以参照文章开头部分的几篇文章。

下面显示的是 Product 维度以及维度属性，同时有两个自定义的层次结构 Product by Category 和 Size by Color。

时间维度 - Date 以及两个自定义的属性层次结构 Calendar Date 和 Fiscal Date 。

父子维度 Employee -

创建完各个维度之后，我们将创建多维数据集 Cube。指定数据源视图 DSV_BIWORK，并选择事实表，这个向导将为每一个事实表创建一个度量值组，包含了由表中各列所创建的度量值。

有两个事实表，因此就有两个度量值组，并且向导将为非维度键的事实表中的每一个数值列创建一个度量值。由于我们这里不需要那么多，所以只选择部分度量值。另外要注意，度量值的名称源于事实表中的列，所有名称由可能相同。但是在多维数据集中，由于度量值的名称必须是唯一的，所以向导会在重复的度量值名称后添加所属的度量值组名称。

下一步的时候多维数据集的向导识别了度量值组即之前的事实表与之相关的维度表，因此这里全部会显示出来。

为多维数据集取一个名称并保存。

可以看到在 Cube 文件夹中出现一个 BIWORK_FirstCube.cube ，这就是我们创建好的多维数据集。 最左侧能看到度量值组和度量值，左下侧可以看到多维数据集中的维度。

那么要注意到我们在数据库中的维度是 DimDate ，但是在多维数据集中就变成了三个时间维度 Order Date，Due Date 和 Ship Daet，这三个多维数据集维度就是角色扮演维度 Role-Playing Dimension。 因为它们本身就是 DimDate， 但是由于 FactInternetSales 中有三个时间列 OrderDateKey, DueDateKey 和 ShipDateKey 都关联到了 DimDate 表中的 DateKey。因此如果就只有一个 Date 时间维度，那么就分不清这个时间维度到底是指的 Order Date , Due Date 还是 Ship Date，这就是角色扮演维度的产生的原因。

角色扮演维度解释了数据库维度 Database Dimension 和多维数据集维度 Cube Dimension 的差异。一个维度的属性和数据包含在分析服务数据库中，这就是数据库维度。一个多维数据集可能包含一个或多个多维数据集维度，它引用了数据库维度的属性和数据。

维度用法显示了各个多维数据集的维度是如何与度量值组进行关联的，交叉的内容显示了关联的维度属性。

部署这个项目然后到 Browser 浏览器中查看多维数据集，注意在 SQL Server 2012 版本中取消了之前在 SQL Server 2008 R2 版本中出现的能够显示多维数据的设计，而变成了在 SSRS 报表开发时的多维数据集的设计样式。 另外，添加了一个 Excel 连接，其实是要求开发者使用 Excel 在 Excel 中查看分析服务中多维数据集的数据。

部署完成后也可以到分析服务中查看多维数据集，左边可以看到数据库维度的 Date 维度，右边创建完一个 MDX 查询页面后可以看到多维数据集中的角色扮演维度以及其它维度，度量值组和度量值。

由于 Order Date 角色扮演维度在两个度量值组中同时出现，所以其实它就可以认为是 Date 维度，为了精简一下命名，可以先添加一个新的时间维度，然后再把 Order Date 维度给删除掉。

先添加一个时间维度。

可以看到与 FactResellerSales 的关联关系，是通过 OrderDateKey 进行关联的，同样的道理 FactInternetSales 也是这样关联的。

删除 Order Date 维度。

直接使用 Date 维度，这样名称更加简洁一下，它的作用和 Order Date 的作用是一样的。

再次部署之后，我们就可以看到一个比较简洁的 Order Date 维度的替身 Date 维度了。

可以在刚才看到的浏览器中打开 Excel  工具，在这里面就会自动加载多维数据集中的维度和度量值组。比如要查看 Internet Sales Amount 和 Reseller Sales Amount 在 Product Category 下的表现情况，那么就可以很容易的拖拽实现。

也可以通过 MDX 查询实现这样的效果，查询在 Product Category  成员下的 Internet Sales Amount 和 Reseller Sales Amount 的情况。

但是在 MDX 查询中可以看到查询出来的结果并没有良好的格式化，因此回到开发设计中对度量值进行格式化，右键属性。

Reseller Sales Amount - Format String - 输入下面的格式 ，同理 Internet Sales Amount 也这么选择，表示金额的格式。

而对于 Reseller Sales Count, Order Quantity 和 Internet Sales Count 就只需要像下面这样设置就可以了。

重新部署之后在 MDX 中查询的结果 -

在 Excel 中看到的结果 -

有的时候我们可能需要在 MDX 中做出这样的查询，即通过 Internet Sales Amount 和 Reseller Sales Amount 计算销售总额，会通过定义一个计算成员来实现。

WITH MEMBER [Measures].[Sales Amount Total]
AS
   [Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]
,FORMAT_STRING ="$#,#"
SELECT {
        [Measures].[Internet Sales Amount],
        [Measures].[Reseller Sales Amount],
        [Measures].[Sales Amount Total]
       } ON COLUMNS,
NON EMPTY([Product].[Category].Members) ON ROWS
FROM [BIWORK_FirstCube]

如果是我们自己开发的 SSRS 报表来查询数据没有问题，但是可能有很多时候客户会直接从 Excel 上去查询这些数据，那么我们可以先在设计度量值的时候将这些总额设计好。可以通过在多维数据集中在某一个维度(一般指的是度量值维度)上添加一个计算成员，将相关的计算公式直接存储在这个成员中。

在多维数据集设计器中选择计算 Calculations 并在 Script Organizer 的第一个 Command 下右键新建一个计算成员。

定义一个名称并注意需要带上方括号 [Sales Amount Total]，将两个度量值拖到 Expression 中，并在 Format string 中指定一个带字符串的金额格式。

部署项目并重新连接就能看到度量值组中多了一个计算成员，可以切换到代码模式，就能看到这个计算的成员的创建就已经在服务器端存在了，实际上这个创建计算成员的语法和我们在 MDX 查询的语法是一样的。

CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[Sales Amount Total]
 AS [Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount],
FORMAT_STRING = "$#,#",
VISIBLE = 1  ;

既然已经存在这个度量值计算成员了，那么就直接可以在 MDX 中查询了。

并且在 Excel 中也可以直接使用这个计算成员了，当然对于使用者来说这就是一个度量值。

在分析服务中创建计算成员一来可以提高计算成员(度量值)的重用性，二来这种常用的度量值计算成员放在一起也便于组织查看和管理，可以避免在各个客户端 MDX 查询中的计算成员创建的不统一，最后就是如果用户并不是使用我们自定义开发的报表，而是通过像 Excel 这种客户端的工具，常用的汇总计算成员事先定义好后面使用就很方便了。

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server) 如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。