设计 SSAS Cube

Ajit Kumar Thakur Microsoft

4.91/5 (6投票s)

2014年7月25日

CPOL

5分钟阅读

44677

1722

本文将逐步解释实现 SSAS Cube 的方法。

引言

本文将逐步解释如何实现 SSAS Cube。SSAS Cube 允许业务用户基于不同的维度快速分析不同的业务度量。SSAS Cube 是一个由维度和度量组成的数据库。SSAS Cube 设计过程需要数据仓库数据库作为输入数据的源。数据仓库数据库应采用星型模式，以避免任何设计复杂性。如果数据仓库采用雪花模式，则可以使用 SQL 视图连接多个维度表，并创建一个通用维度。

背景

Microsoft 提供了 SQL Server Analysis Service (SSAS) 来实现 Cube 数据库，该数据库将包含预定义的度量和维度。度量将根据不同的维度进行聚合。用户可以通过多个维度的不同分析轻松访问这些度量值。SSAS Cube 使用多维表达式 (MDX) 来从多个维度读取度量。SSAS Cube 的维护很简单。我们可以为任何新更改在 SSAS Cube 数据库中部署特定的维度或度量。

SSAS Cube 还支持关键绩效指标 (KPI) 来分析任何产品的性能。

使用代码

我使用 AdventureWorksDW2008R2 数据仓库数据库作为 SSAS Cube 的输入源。您可以在本文的下载源部分下载该数据库。下面是对实现 SSAS Cube 的业务需求分析。

数据仓库数据库为：AdventureWorksDW2008R2
业务分析所需的 Cube 为：SalesCube
所有可能的分析都将在业务交易上执行。业务交易存储在事实表中。

Reseller Sales 交易可在 FactResellerSales 中找到
Internet Sales 交易可在 FactInternetSales 中找到

SELECT [ProductKey],[OrderDateKey],[DueDateKey],[ShipDateKey],[ResellerKey],[EmployeeKey],
[PromotionKey],[CurrencyKey],[SalesTerritoryKey],[SalesOrderNumber],[SalesOrderLineNumber]
,[RevisionNumber],[OrderQuantity],[UnitPrice],[ExtendedAmount],[UnitPriceDiscountPct]
,[DiscountAmount],[ProductStandardCost],[TotalProductCost],[SalesAmount],[TaxAmt],[Freight]
,[CarrierTrackingNumber],[CustomerPONumber]
FROM [AdventureWorksDW2008R2].[dbo].[FactResellerSales]

SELECT [ProductKey],[OrderDateKey],[DueDateKey],[ShipDateKey],
[PromotionKey],[CurrencyKey],[SalesTerritoryKey],[SalesOrderNumber],[SalesOrderLineNumber]
,[RevisionNumber],[OrderQuantity],[UnitPrice],[ExtendedAmount],[UnitPriceDiscountPct]
,[DiscountAmount],[ProductStandardCost],[TotalProductCost],[SalesAmount],[TaxAmt],[Freight]
,[CarrierTrackingNumber],[CustomerPONumber]
FROM [AdventureWorksDW2008R2].[dbo].[FactInternetSales]

可以通过以下维度分析 Fact 表 Reseller Sales 和 Internet Sales：

产品维度 (产品、产品类别和产品子类别)
日期维度 (日历年、日历季度等)
地域维度 (销售城市、销售州)
货币维度 (货币名称)
员工维度 (员工姓名)

这些维度可以应用于事实表 (Reseller sales 和 Internet sales)，并可以描述以下度量：

订单数量 (按产品、按地域、按日历日期、按员工)
销售金额 (按产品、按地域、按日历日期)
税费金额 (按产品、按地域、按日历日期)
运费 (按产品、按地域、按日历日期)
总销售订单 (按产品、按地域、按日历日期)

上述度量的业务维度分析在数据仓库数据库中也是可能的。我们可以向业务用户提供 SSRS 报表，他们可以使用 T-SQL 从 DWH 数据库获取数据。但这会非常慢，因为 DWH 数据库的尺寸会非常大。DWH 数据库在其表中包含大量的业务数据交易。DWH 数据库使用计划作业来插入新的业务交易、更新现有交易、更新现有维度、插入新维度等。

/*************Sample T-SQL Query to analyse business data ******************/

--Year wise Reseller Sales
Select D.CalendarYear,Sum(S.SalesAmount)SalesAmount from FactResellerSales S
INNER JOIN DimDate D ON S.OrderDateKey=D.DateKey
Group By D.CalendarYear

--Product category wise Reseller Sales
Select  PC.EnglishProductCategoryName,Sum(S.SalesAmount) SalesAmount from FactResellerSales S
INNER JOIN DimProduct P ON S.ProductKey=P.ProductKey
INNER JOIN DimProductSubcategory PS ON p.ProductSubcategoryKey=PS.ProductSubcategoryKey
INNER JOIN DimProductCategory PC on PS.ProductCategoryKey=PC.ProductCategoryKey
Group By PC.EnglishProductCategoryName

--Product category wise and year wise Reseller Sales
Select  PC.EnglishProductCategoryName,D.CalendarYear,Sum(S.SalesAmount) SalesAmount from FactResellerSales S
INNER JOIN DimDate D ON D.DateKey=S.OrderDateKey
INNER JOIN DimProduct P ON S.ProductKey=P.ProductKey
INNER JOIN DimProductSubcategory PS ON p.ProductSubcategoryKey=PS.ProductSubcategoryKey
INNER JOIN DimProductCategory PC on PS.ProductCategoryKey=PC.ProductCategoryKey
Group By PC.EnglishProductCategoryName,D.CalendarYear
ORDER By PC.EnglishProductCategoryName,D.CalendarYear

--Territory wise Reseller Sales
Select G.SalesTerritoryCountry, PC.EnglishProductCategoryName,D.CalendarYear,Sum(S.SalesAmount) SalesAmount from FactResellerSales S
INNER JOIN DimDate D ON D.DateKey=S.OrderDateKey
INNER JOIN DimSalesTerritory G ON S.SalesTerritoryKey=G.SalesTerritoryKey
INNER JOIN DimProduct P ON S.ProductKey=P.ProductKey
INNER JOIN DimProductSubcategory PS ON p.ProductSubcategoryKey=PS.ProductSubcategoryKey
INNER JOIN DimProductCategory PC on PS.ProductCategoryKey=PC.ProductCategoryKey
Group By G.SalesTerritoryCountry, PC.EnglishProductCategoryName,D.CalendarYear
ORDER By PC.EnglishProductCategoryName,D.CalendarYear

--Product wise sales with Calendar Year in column value 
With Sales
AS
(
Select  PC.EnglishProductCategoryName As ProductCategory,D.CalendarYear,Sum(S.SalesAmount) SalesAmount 
from FactResellerSales S
INNER JOIN DimDate D ON D.DateKey=S.OrderDateKey
INNER JOIN DimProduct P ON S.ProductKey=P.ProductKey
INNER JOIN DimProductSubcategory PS ON p.ProductSubcategoryKey=PS.ProductSubcategoryKey
INNER JOIN DimProductCategory PC on PS.ProductCategoryKey=PC.ProductCategoryKey
Group By PC.EnglishProductCategoryName,D.CalendarYear
)
Select  ProductCategory, [2005],[2006],[2007],[2008]
From Sales
Pivot
(
	Sum(SalesAmount) For CalendarYear IN ([2005],[2006],[2007],[2008])
)P

我们可以看到，简单的业务数据分析需要与维度表进行多次连接。当表的大小非常大时，连接过程会花费大量时间。当分析过程的级别变得复杂时，T-SQL 会花费更多时间。

例如：按年份划分的经销商销售详情，以产品类别作为透视列
按产品划分的经销商销售，以日历年作为透视列

当这些数据在 SSAS Cube 中可用时，MDX 可以在几秒钟内读取所有简单和复杂的分析。业务分析过程变得非常容易。MDX 可以在单个查询中从多个维度读取 Cube 度量。

实现 SSAS Cube 的分步过程

使用 Microsoft Business Intelligence Development Studio (BIDS) 创建新的 SSAS 项目

为 SalesCube 解决方案定义数据源

通过右键单击数据源，会出现数据源向导，定义 SalesCube 的新数据源

在数据源中定义新的数据连接

提供源数据仓库数据库信息

提供数据源的模拟信息。如果当前用户凭据选项不起作用，请在模拟中提供 Windows 用户名和密码。

分配数据源名称并单击完成。数据源将出现在 SalesCube 解决方案中。

为 SalesCube 解决方案定义数据源视图

在 SalesCube 解决方案资源管理器中右键单击数据源视图，会出现数据源视图向导

选择用于 Cube 输入的数据源

选择 Cube 中使用的表和视图。我选择了 FactInternetSales 和 FactResellerSales

单击添加相关表按钮。它将添加所有必需的维度表。

添加所有必需表后的数据源视图。

您可以使用“添加/删除对象”按钮向数据源视图添加或删除任何对象

我已从数据源视图中删除了 DimPromotion、DimProduct 和 DimReseller 对象

Dimproduct 维度表包含产品详细信息。但产品类别和产品子类别存储在其他维度表 Dimproductcategory 和 Dimproductsubcategory 中。因此，如果我们想根据产品类别或产品子类别对任何度量进行分析，将无法获得。如果我们包含所有 3 个维度表，将使 Cube 更加复杂。因此，我们可以编写 SQL 命名查询来在一个表集中获取完整的产品。

单击新建命名查询。会出现“创建命名查询”对话框。在此命名查询中编写所需的 SQL 代码。

Select DP.*,PC.EnglishProductCategoryName,SPC.EnglishProductSubcategoryName from DimProduct DP
LEFT JOIN DimProductSubCategory SPC ON DP.ProductSubcategoryKey=SPC.ProductSubcategoryKey
LEFT JOIN DimProductCategory PC ON pc.ProductCategoryKey=SPC.ProductCategoryKey